pgsandpit

package module
v0.2.0 Latest Latest
Warning

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

Go to latest
Published: Nov 7, 2026 License: CC0-1.0 Imports: 16 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.

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).

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 (10 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.
  • 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 (SET, advisory locks, temporary tables shared across *sql.DB values) need real separate connections.

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 = 10 * time.Second

DefaultTimeout is how long a call waits for the session while another transaction holds it, unless the *sql.DB was built with Builder.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")
)

Functions

This section is empty.

Types

type Builder

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

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

func (*Builder) Conn added in v0.2.0

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

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 (*Builder) Connector added in v0.2.0

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

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.

func (*Builder) DB

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

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

func (*Builder) ReadOnly

func (b *Builder) ReadOnly() *Builder

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.

func (*Builder) Timeout

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

Timeout sets how long a call on the *sql.DB waits for the session while another transaction holds it. The default is DefaultTimeout.

type Sandpit

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

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

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.

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.

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) With

func (s *Session) With() *Builder

With starts building a *sql.DB with options. The Session itself is not changed.

Jump to

Keyboard shortcuts

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