pgsandpit

package module
v0.6.0 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Nov 8, 2026 License: CC0-1.0 Imports: 17 Imported by: 0

README

pgsandpit

GitHub Tag Go Reference test codecov AI Generated

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.

Usage

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 session's *pgx.Conn, for example to use pgx's API:

db := s.With().Conn(func(c *pgx.Conn) {
	_, err := c.CopyFrom(t.Context(), pgx.Identifier{"users"}, []string{"email"}, pgx.CopyFromRows(rows))
	require.NoError(t, 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(c *pgx.Conn) {
		if _, err := c.Exec(context.Background(), "SET TIME ZONE 'UTC'"); err != nil {
			panic(err)
		}
	})

func TestFoo(t *testing.T) {
	s := base.Session(t)
	db := s.DB()                        // waits at most 5 seconds for the session
	// ...
}
  • ReadOnly makes every *sql.DB of the session read-only. Conn is not affected by it, so it can insert the data the test reads.
  • Timeout is the default of the session's *sql.DB values, which s.With().Timeout(...) replaces. It also bounds how long the end of the test waits for a session that is still in use.
  • Connector is applied to every *sql.DB of the session, each time one is made. A Connector set with s.With().Connector(...) is given the connector the session's returned.
  • Conn is called once, when Session makes the session, with the session's *pgx.Conn. A parameter it SETs applies to every *sql.DB of the session and is reset when the session is rolled back.
  • Cache makes Session return the session cached for t if there is one, and otherwise open one and cache it, so helpers can get the session from t alone. The options of the call that opened the session apply, and those of later calls are ignored. A subtest has its own t, so it gets its own session rather than its parent's.
  • Interleave lets 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)

err := svc.Transfer(ctx, s.DB(), from, to, 100)
require.Error(t, err)

assert.Equal(t, []pgsandpit.TxResult{pgsandpit.TxRolledBack}, s.TxResults())
  • TxCommitted: Commit succeeded.
  • TxRolledBack: Rollback was called, or the transaction's context was canceled. database/sql rolls back a canceled transaction in another goroutine, so its result may appear shortly after the cancellation.
  • TxCommitFailed: Commit was 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.

Behavior

  • Isolation: each Session has 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: Begin creates a savepoint, Commit releases it, and Rollback rolls back to it. TxOptions are ignored. Committing a transaction that hit an error rolls it back and returns pgx.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 the Begin that holds the session. This is usually a transaction left open, or a *sql.DB used outside a transaction while that transaction is open in the same goroutine.

  • Interleave: code that begins a transaction on a *sql.DB and then, before the transaction ends, uses the *sql.DB outside 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. With sandpit.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 Commit of the earlier one rolls both back and returns an error wrapping pgsandpit.ErrTxOrder, and so do the later one's statements, Commit and Rollback.
    • 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.DB runs each statement with transaction_read_only on, so PostgreSQL rejects writes, including writes inside functions.

  • Rows are read in full before a query returns, so an open *sql.Rows does 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.

Out of scope

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.

Running the tests

docker compose up -d
make test

Documentation

Overview

Package pgsandpit gives each test a PostgreSQL session wrapped in a transaction that is rolled back when the test ends, and hands the test *sql.DB values that run on that session.

var sandpit = pgsandpit.New("postgres://postgres@localhost:5432/postgres")

func TestFoo(t *testing.T) {
	t.Parallel()
	s := sandpit.Session(t)
	db := s.DB()
	ro := s.With().ReadOnly().DB()
	// db and ro share the session: ro sees what db wrote, and nothing is
	// left in the database after the test.
}

Index

Constants

View Source
const DefaultTimeout = 60 * time.Second

DefaultTimeout is how long a call waits for the session while another transaction holds it, unless the session was built with SessionBuilder.Timeout or the *sql.DB with DBBuilder.Timeout.

Variables

View Source
var (
	// ErrTimeout is returned, wrapped, when a call waited longer than the
	// timeout for the session. It usually means a transaction was left open,
	// or the *sql.DB was used outside a transaction while that transaction was
	// open in the same goroutine.
	ErrTimeout = errors.New("pgsandpit: timed out waiting for the session")

	// ErrSessionClosed is returned when a *sql.DB is used after its session
	// ended.
	ErrSessionClosed = errors.New("pgsandpit: session is closed")

	// ErrTxOrder is returned, wrapped, with SessionBuilder.Interleave, when
	// nested transactions do not end in the reverse order they began, such as
	// when transactions in different goroutines overlap. The transactions that
	// could not end as asked are rolled back.
	ErrTxOrder = errors.New("pgsandpit: transactions ended out of order")
)

Functions

func Register added in v0.5.0

func Register(name string, sp *Sandpit)

Register makes sp available under name, so that tests can choose a database by name 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:

func init() {
	pgsandpit.Register("testdb1", pgsandpit.New("postgres://..."))
}

Register panics if sp is nil or name is already registered. Register and Lookup are safe for concurrent use.

Types

type DBBuilder added in v0.3.0

type DBBuilder struct {
	// contains filtered or unexported fields
}

DBBuilder builds a *sql.DB for a Session. Each method returns a new DBBuilder, so a DBBuilder can be shared and branched.

func (*DBBuilder) Conn added in v0.3.0

func (b *DBBuilder) Conn(f func(c *pgx.Conn)) *DBBuilder

Conn sets a function that DB calls once, before it returns, with the session's *pgx.Conn. DB waits for the session as the *sql.DB's calls do, and holds it while f runs.

What f runs, runs in the session's transaction as it is: it is not read-only, and a statement that fails aborts the session's transaction. f must not begin, commit or roll back a transaction, nor use c after it returns.

func (*DBBuilder) Connector added in v0.3.0

func (b *DBBuilder) Connector(f func(driver.Connector) driver.Connector) *DBBuilder

Connector sets a function that is given the connector pgsandpit made for the *sql.DB and returns the connector the *sql.DB is opened with, as by sql.OpenDB. If the session was built with SessionBuilder.Connector, f is given the connector that function returned.

func (*DBBuilder) DB added in v0.3.0

func (b *DBBuilder) DB() *sql.DB

DB returns the *sql.DB. It is closed when the test ends; closing it earlier does not end the session.

func (*DBBuilder) ReadOnly added in v0.3.0

func (b *DBBuilder) ReadOnly() *DBBuilder

ReadOnly makes the *sql.DB reject writes. It still sees what the other *sql.DB values of the session wrote. A rejected write fails only that statement, as in a read-only transaction outside a test, except that inside an application transaction it aborts that transaction. Every *sql.DB of a session built with SessionBuilder.ReadOnly is read-only.

func (*DBBuilder) Timeout added in v0.3.0

func (b *DBBuilder) Timeout(d time.Duration) *DBBuilder

Timeout sets how long a call on the *sql.DB waits for the session while another transaction holds it. The default is the session's timeout (see SessionBuilder.Timeout).

type Sandpit

type Sandpit struct {
	// contains filtered or unexported fields
}

Sandpit opens sessions on one database. Create it once per test package.

func Lookup added in v0.5.0

func Lookup(name string) *Sandpit

Lookup returns the Sandpit registered under name. It panics if name is not registered.

func New

func New(dsn string) *Sandpit

New returns a Sandpit for the database dsn points to. It does not connect until the first Session call.

func NewWithConfig added in v0.2.0

func NewWithConfig(cfg *pgx.ConnConfig, opts ...stdlib.OptionOpenDB) *Sandpit

NewWithConfig returns a Sandpit that connects with a copy of cfg and opts, as stdlib.OpenDB does. It does not connect until the first Session call.

pgsandpit replaces cfg.BuildContextWatcherHandler, so that a canceled context cancels the query instead of closing the session's connection.

The connections are pooled across sessions: a hook such as stdlib.OptionAfterConnect runs once per connection, not once per session.

func (*Sandpit) Close

func (sp *Sandpit) Close() error

Close closes the connection pool the sessions are taken from. Call it from TestMain after the tests have run, if at all.

func (*Sandpit) Session

func (sp *Sandpit) Session(t *testing.T) *Session

Session opens a connection, begins a transaction on it, and returns a Session for it. The transaction is rolled back and the connection closed when t and its subtests finish. Session fails t if it cannot connect. It is the same as sp.With().Session(t).

func (*Sandpit) With added in v0.3.0

func (sp *Sandpit) With() *SessionBuilder

With starts building a Session with options. The Sandpit itself is not changed.

type Session

type Session struct {
	// contains filtered or unexported fields
}

Session is one PostgreSQL connection with one transaction open on it. Every *sql.DB made from a Session runs on that connection, so they all see each other's changes. Only one statement, or one application transaction, runs on the session at a time; the others wait. With SessionBuilder.Interleave, statements and transactions do not wait for an open application transaction: they run, or nest, inside it (see there).

func (*Session) DB

func (s *Session) DB() *sql.DB

DB returns a *sql.DB that runs on the session. It is the same as s.With().DB().

func (*Session) TxResults added in v0.4.0

func (s *Session) TxResults() []TxResult

TxResults returns how the application transactions of every *sql.DB of the session ended, in the order they ended. A transaction that is still open is not included. It does not wait for the session, so it can be called while a transaction is open.

database/sql rolls back a transaction whose context was canceled in another goroutine, so its result may appear shortly after the cancellation.

func (*Session) With

func (s *Session) With() *DBBuilder

With starts building a *sql.DB with options, starting from the defaults the session was built with. The Session itself is not changed.

type SessionBuilder added in v0.3.0

type SessionBuilder struct {
	// contains filtered or unexported fields
}

SessionBuilder builds a Session for a Sandpit. Each method returns a new SessionBuilder, so a SessionBuilder can be shared and branched, and made before the tests run:

var base = sandpit.With().Timeout(5 * time.Second)

func TestFoo(t *testing.T) {
	s := base.Session(t)
	// ...
}

func (*SessionBuilder) Cache added in v0.5.0

func (b *SessionBuilder) Cache() *SessionBuilder

Cache makes Session return the session cached for t in the Sandpit if there is one, and otherwise open one and cache it until t and its subtests finish, so that helpers can get the session from t alone.

The options of the call that opened the session apply: those of a later call that finds it in the cache are ignored. Only sessions built with Cache are cached. A subtest has its own t, so it gets its own session rather than its parent's.

func (*SessionBuilder) Conn added in v0.3.0

func (b *SessionBuilder) Conn(f func(c *pgx.Conn)) *SessionBuilder

Conn sets a function that Session calls once, before it returns, with the session's *pgx.Conn, for example to SET a parameter for the session or to insert data the test needs. A parameter SET this way applies to every *sql.DB of the session and is reset when the session's transaction is rolled back.

What f runs, runs in the session's transaction as it is: a statement that fails aborts the session's transaction. f must not begin, commit or roll back a transaction, nor use c after it returns.

func (*SessionBuilder) Connector added in v0.3.0

Connector sets a function that is given the connector pgsandpit made for each *sql.DB of the session and returns the connector the *sql.DB is opened with. It is called each time a *sql.DB is made. A function set with DBBuilder.Connector is given what f returns.

func (*SessionBuilder) Interleave added in v0.6.0

func (b *SessionBuilder) Interleave() *SessionBuilder

Interleave lets a statement run outside an application transaction while that transaction is open, instead of waiting for it to end. The statement runs at once, inside the open transaction, 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.

It is for code that begins a transaction on a *sql.DB and then, before the transaction ends, uses the *sql.DB outside the transaction, which runs on another connection outside a test. Without Interleave, such a statement waits for the transaction, which waits for the statement, until ErrTimeout.

A transaction begun while another is open does not wait for it either: it nests inside it, in a savepoint inside the open transaction's savepoint. 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 Commit of the earlier one rolls both back and returns an error wrapping ErrTxOrder, and the later one's Commit or Rollback returns such an error.

Statements still run one at a time. A statement or a transaction in another goroutine also runs inside an open transaction instead of waiting for it, so transactions that overlap in different goroutines can end out of order.

func (*SessionBuilder) ReadOnly added in v0.3.0

func (b *SessionBuilder) ReadOnly() *SessionBuilder

ReadOnly makes every *sql.DB of the session reject writes, as DBBuilder.ReadOnly does. A function set with Conn is not affected by it, so it can insert the data the test reads.

func (*SessionBuilder) Session added in v0.3.0

func (b *SessionBuilder) Session(t *testing.T) *Session

Session opens a connection, begins a transaction on it, and returns a Session for it. The transaction is rolled back and the connection closed when t and its subtests finish. Session fails t if it cannot connect. With Cache, it returns the session cached for t if there is one.

func (*SessionBuilder) Timeout added in v0.3.0

func (b *SessionBuilder) Timeout(d time.Duration) *SessionBuilder

Timeout sets how long a call waits for the session while another transaction holds it. It is the default of DBBuilder.Timeout for the *sql.DB values of the session, and also bounds how long the end of the test waits for the session. The default is DefaultTimeout.

type TxResult added in v0.4.0

type TxResult int

TxResult is how an application transaction ended.

const (
	// TxCommitted means Commit succeeded.
	TxCommitted TxResult = iota
	// TxRolledBack means the transaction ended with a rollback: Rollback was
	// called, or database/sql rolled it back because its context was canceled.
	TxRolledBack
	// TxCommitFailed means Commit was called but did not commit, such as when
	// the transaction had hit an error, which rolls it back and makes Commit
	// return pgx.ErrTxCommitRollback.
	TxCommitFailed
)

func (TxResult) String added in v0.4.0

func (r TxResult) String() string

String returns the name of the constant, so that a failed assertion on TxResults reads well.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL