pgsandpit is a Go testing library for PostgreSQL. It gives each test its own session wrapped in a transaction, hands the test *sql.DB values that run on that session, and rolls everything back when the test ends.
package myapp_test
import (
"testing"
"github.com/winebarrel/pgsandpit"
)
var sandpit = pgsandpit.New("postgres://postgres@localhost:5432/postgres")
func TestReport(t *testing.T) {
t.Parallel()
s := sandpit.Session(t) // one connection, one transaction; rolled back when t ends
db := s.DB() // a *sql.DB on the session
ro := s.With().ReadOnly().DB() // another one that rejects writes
db.Exec(`INSERT INTO orders ...`) // ro sees this: both share the session
svc := NewReportService(db, ro)
// ...
}Options for a *sql.DB are chained after With():
db := s.With().ReadOnly().Timeout(5 * time.Second).DB()Connector is given the connector pgsandpit made for the *sql.DB and returns the connector to open it with, as sql.OpenDB takes one. For example, a connector that wraps it can hook into the queries:
db := s.With().Connector(func(c driver.Connector) driver.Connector {
return &MyConnector{c}
}).DB()Conn is called once, when DB makes the *sql.DB, with the test the session was opened for and the session's *pgx.Conn, for example to use pgx's API:
db := s.With().Conn(func(t *testing.T, c *pgx.Conn) {
_, err := c.CopyFrom(t.Context(), pgx.Identifier{"users"}, []string{"email"}, pgx.CopyFromRows(rows))
if err != nil {
t.Fatal(err)
}
}).DB()What it runs, runs in the session's transaction as it is: it is not read-only, and a failing statement aborts the session's transaction. It must not begin, commit or roll back a transaction.
Options for a session are chained after sandpit.With(). A SessionBuilder can be made once and shared by the tests:
var base = sandpit.With().
Timeout(5 * time.Second).
Conn(func(t *testing.T, c *pgx.Conn) {
if _, err := c.Exec(t.Context(), "SET TIME ZONE 'UTC'"); err != nil {
t.Fatal(err)
}
})
func TestFoo(t *testing.T) {
s := base.Session(t)
db := s.DB() // waits at most 5 seconds for the session
// ...
}ReadOnlymakes every*sql.DBof the session read-only.Connis not affected by it, so it can insert the data the test reads.Timeoutis the default of the session's*sql.DBvalues, whichs.With().Timeout(...)replaces. It also bounds how long the end of the test waits for a session that is still in use.Connectoris applied to every*sql.DBof the session, each time one is made. AConnectorset withs.With().Connector(...)is given the connector the session's returned.Connis called once, whenSessionmakes the session, with thetgiven toSessionand the session's*pgx.Conn. A parameter itSETs applies to every*sql.DBof the session and is reset when the session is rolled back.CachemakesSessionreturn the session cached fortif there is one, and otherwise open one and cache it, so helpers can get the session fromtalone. The options of the call that opened the session apply, and those of later calls are ignored. A subtest has its ownt, so it gets its own session rather than its parent's.Interleavelets a statement, or a transaction, run while an application transaction is open, instead of waiting for it to end (see Interleave below).
var cached = sandpit.With().Cache()
func insertOrder(t *testing.T) {
t.Helper()
db := cached.Session(t).DB()
// ...
}
func TestOrder(t *testing.T) {
insertOrder(t)
s := cached.Session(t) // the session insertOrder used
// ...
}TxResults returns how the transactions of the session's *sql.DB values ended, in order, so a test can check that the code under test rolled back:
s := sandpit.Session(t)
if err := svc.Transfer(ctx, s.DB(), from, to, 100); err == nil {
t.Fatal("Transfer must fail")
}
want := []pgsandpit.TxResult{pgsandpit.TxRolledBack}
if got := s.TxResults(); !slices.Equal(got, want) {
t.Errorf("TxResults() = %v, want %v", got, want)
}TxCommitted:Commitsucceeded.TxRolledBack:Rollbackwas called, or the transaction's context was canceled.database/sqlrolls back a canceled transaction in another goroutine, so its result may appear shortly after the cancellation.TxCommitFailed:Commitwas called but did not commit, such as when the transaction had hit an error.
A transaction that is still open is not included. Savepoints the application creates with SQL are not transactions here, so they are not included either.
To configure pgx, pass a *pgx.ConnConfig and the options of stdlib.OpenDB:
cfg, err := pgx.ParseConfig("postgres://postgres@localhost:5432/postgres")
if err != nil {
log.Fatal(err)
}
sandpit := pgsandpit.NewWithConfig(cfg,
stdlib.OptionAfterConnect(func(ctx context.Context, c *pgx.Conn) error {
_, err := c.Exec(ctx, "SET TIME ZONE 'UTC'")
return err
}),
)The connections are pooled across sessions, so OptionAfterConnect runs once per connection, not once per session. pgsandpit replaces BuildContextWatcherHandler (see Cancellation below).
To choose a database by name, for example in a table-driven test, register a Sandpit under a name and get it with Lookup. Each test package runs in its own process, so a package shared by the tests can register the names in its init function:
// internal/testdb/testdb.go
package testdb
func init() {
pgsandpit.Register("testdb1", pgsandpit.New("postgres://xxx"))
pgsandpit.Register("testdb2", pgsandpit.New("postgres://yyy"))
}import _ "example.com/myapp/internal/testdb"
func TestFoo(t *testing.T) {
s := pgsandpit.Lookup("testdb1").Session(t)
// ...
}Register panics if the name is already registered, and Lookup panics if it is not registered. A Sandpit does not connect until the first Session call, so registering one does not connect to the database.
-
Isolation: each
Sessionhas its own connection and transaction, so parallel tests do not see each other's data. Tests that write the same rows or unique keys still wait for each other's locks until the first test ends. -
Transactions:
Begincreates a savepoint,Commitreleases it, andRollbackrolls back to it.TxOptionsare ignored. Committing a transaction that hit an error rolls it back and returnspgx.ErrTxCommitRollback, as PostgreSQL does. -
Statements outside a transaction each run in their own savepoint, so a failing statement (such as a unique violation) fails alone, as in autocommit mode, instead of aborting the rest of the test.
-
Serialization: one statement, or one transaction, runs on a session at a time; the others wait. A call that waits longer than the timeout (60 seconds by default) returns an error wrapping
pgsandpit.ErrTimeout, with the stack of theBeginthat holds the session. This is usually a transaction left open, or a*sql.DBused outside a transaction while that transaction is open in the same goroutine. -
Interleave: code that begins a transaction on a
*sql.DBand then, before the transaction ends, uses the*sql.DBoutside the transaction, or begins another transaction on it, runs on several connections outside a test, but on one session in a test, where the second would wait for the first until it times out. Withsandpit.With().Interleave(), it does not wait:- A statement outside the open transaction runs inside it, in a savepoint of its own: it sees what the transaction wrote, a failure fails only that statement, and what it writes is rolled back with the transaction if the transaction rolls back.
- A transaction begun while another is open nests inside it. Nested transactions must end in the reverse order they began. If one ends before a transaction begun after it, PostgreSQL ends the later one with it: a
Commitof the earlier one rolls both back and returns an error wrappingpgsandpit.ErrTxOrder, and so do the later one's statements,CommitandRollback. - Statements still run one at a time. Statements and transactions in other goroutines do not wait for an open transaction either, so transactions that overlap in different goroutines can end out of order.
- A transaction left open at the end of the test fails the test with the stack of its
Begin.
db := sandpit.With().Interleave().Session(t).DB() tx, _ := db.Begin() tx.Exec(`INSERT INTO orders ...`) db.QueryRow(`SELECT count(*) FROM orders`) // runs inside tx instead of waiting for it inner, _ := db.Begin() // nests inside tx inner.Commit() tx.Commit()
-
Read-only: a read-only
*sql.DBruns each statement withtransaction_read_onlyon, so PostgreSQL rejects writes, including writes inside functions. -
Rows are read in full before a query returns, so an open
*sql.Rowsdoes not hold the session. -
Cancellation: a canceled context cancels the query on the server and leaves the session usable. A transaction whose context is canceled is rolled back.
Tests of concurrency itself (lock waits, SELECT ... FOR UPDATE, isolation levels) and of session state changed through a *sql.DB (SET, advisory locks, temporary tables shared across *sql.DB values) need real separate connections. A parameter that every *sql.DB of the session needs can be set with SessionBuilder.Conn.
docker compose up -d
make test