pgcleaner

README

pgcleaner

Put a PostgreSQL database back to its baseline after each Go test: a small analogue of Ruby's DatabaseCleaner, built on go-txdb. It knows nothing about your ORM: it hands out a *sql.DB and runs one setup callback (migrations, fixtures) per process.

Why?

Tests that touch a database must not leak into each other: a row one test inserts cannot change what the next one sees, or the suite passes in one order and fails in another. There are three ways to get there, and most ecosystems ship an answer to this out of the box:

1. Run each test in a transaction and roll it back. Nothing is ever committed, so there is nothing to clean, and it costs almost nothing: a rollback is far cheaper than recreating data. This is the default almost everywhere: Rails (use_transactional_tests), Django (TestCase), Laravel (RefreshDatabase, DatabaseTransactions), Spring (@Transactional tests roll back by default) and Elixir's Ecto (SQL.Sandbox).
2. Truncate or delete the tables after the test. Slower, but the data is really committed, so a second connection can see it. This is what those same frameworks fall back to when a test cannot share one connection: Django's TransactionTestCase, Laravel's DatabaseTruncation, and the :truncation and :deletion strategies of Ruby's DatabaseCleaner, which this package is modeled on.
3. Create a fresh database per test. Fully isolated, but creating and migrating one takes far longer than a rollback.

Go's standard testing package has no such thing, so every project writes its own: open a *sql.Tx in each test, hand it around, remember to roll back. That works until the code under test opens a transaction of its own (a *sql.Tx cannot be nested), or the code takes a *sql.DB and not a *sql.Tx, and then it is rewritten per project.

go-txdb solves the hard part of the first approach: it is a database/sql driver that puts the whole connection inside one transaction, turns the code's own Begin/Commit/Rollback into savepoints, and rolls back on Close. The code under test just receives a *sql.DB. pgcleaner adds what is left around it, the same way DatabaseCleaner wraps Ruby's primitives: the choice between the strategies above, a setup step that runs once per process (migrations, fixtures), the table lists and foreign-key order for truncation and deletion, a guard against wiping the wrong database, and scratch databases.

Pick Transaction unless the test cannot use the handle it is given. The usual reason is code that talks to the database over its own connection: an HTTP server in another process, a worker with its own pool, a LISTEN/NOTIFY consumer. Their connections cannot see an uncommitted transaction, so use Truncation or Deletion for those tests.

Strategy	How a test is undone	Use when
Transaction()	the whole test runs in one rolled-back tx	the default; the test uses the *sql.DB it is given
Truncation()	TRUNCATE … RESTART IDENTITY CASCADE after	another connection must see the rows (a server in another process)
Deletion()	DELETE after, children before parents	few tables touched, or no TRUNCATE privilege
Null()	nothing	you clean by hand

Truncation and Deletion accept Only(...) and Except(...), and never touch the migration bookkeeping tables of golang-migrate, goose, Atlas, river, Rails and the like.

Usage

A hypothetical users package whose Service takes a *sql.DB. The test package builds one Cleaner; every test asks it for a database and uses it like any other.

package users_test

import (
	"context"
	"database/sql"
	"os"
	"testing"

	"github.com/mokevnin/pgcleaner"
	"github.com/stretchr/testify/require"

	"example.com/app/users"
)

// One Cleaner per test package.
var cleaner = pgcleaner.New(os.Getenv("TEST_DATABASE_URL"),
	pgcleaner.WithSetup(func(ctx context.Context, db *sql.DB) error {
		// Once per process, committed: migrate the schema, load the fixtures.
		return migrate(ctx, db)
	}),
)

func TestRegisterStoresTheUser(t *testing.T) {
	t.Parallel()
	svc := users.NewService(cleaner.DB(t)) // the baseline; rolled back when the test ends

	_, err := svc.Register(t.Context(), "bob@example.com")
	require.NoError(t, err)

	got, err := svc.ByEmail(t.Context(), "bob@example.com")
	require.NoError(t, err)
	require.Equal(t, "bob@example.com", got.Email)
}

func TestRegisterRejectsADuplicate(t *testing.T) {
	t.Parallel()
	svc := users.NewService(cleaner.DB(t))
	_, err := svc.Register(t.Context(), "ann@example.com")
	require.NoError(t, err)

	_, err = svc.Register(t.Context(), "ann@example.com")

	// Bob from the test above is not here, and Ann will not be in the next one.
	require.ErrorIs(t, err, users.ErrEmailTaken)
}

Register may open its own db.BeginTx inside: it becomes a savepoint of the test's transaction and is still undone with it.

When a test cannot use the handle it is given (the code under test runs in another process or on its own pool), pick another strategy for that package:

var cleaner = pgcleaner.New(url,
	pgcleaner.WithStrategy(pgcleaner.Truncation(pgcleaner.Except("countries"))),
	pgcleaner.WithSetup(migrateAndSeed),
)

One strategy before the suite, another per test, as in DatabaseCleaner's clean_with:

func TestMain(m *testing.M) {
	_ = cleaner.CleanWith(context.Background(), pgcleaner.Truncation())
	os.Exit(m.Run())
}

Without testing, use Start / Session.Clean, or Cleaning(ctx, func(db *sql.DB) error).

Transaction limits a session to one connection, because a second one would open a second transaction that does not see the first one's writes. The code under test may still call Begin/Commit/Rollback: they become savepoints. Do not run sessions of the committing strategies in parallel against one database.

Safety

Truncation and Deletion refuse to run unless the database name contains test, e2e or scratch, so a URL left pointing at a development database is not emptied. Replace the check with WithGuard(func(name string) bool), or pass nil to allow everything.

Parallel test packages

go test ./... runs one process per package. Against one database they fight over the same rows and locks, so the usual fix is -p 1, which also serializes building and linking the test binaries (the slow half of many suites). WithTemplate gives every process a database of its own instead:

var cleaner = pgcleaner.New(os.Getenv("DATABASE_URL"),
	// The schema, built once per version into a template database.
	pgcleaner.WithTemplate(schemaHash, func(ctx context.Context, db *sql.DB, url string) error {
		return migrate(ctx, db)
	}),
	// What differs per process: runs on the process's own database every time.
	pgcleaner.WithSetup(loadFixtures),
)

The databases are siblings of the one in the URL: <name>_tpl_<hash>, the template, built by whichever process comes first and under an advisory lock; and <name>_w<i>, the workers. A process leases the lowest free worker with a session advisory lock, which the server releases when the process ends however it ends, so there is nothing to clean up and the number of databases stays at the number of concurrent processes. A worker is cloned from the template when it is missing or was cloned from an older version. Bump the version (a hash of the migration files, say) when the schema changes; older templates are dropped.

Keep what has relative dates in WithSetup, not in build: a template is built once and lives on. cleaner.URL(ctx) is the database to hand to code that opens its own connection. The role needs the right to create databases. In the suite this was extracted from, 131 s with -p 1 became about 30 s.

Scratch databases

ScratchDatabase(t, adminURL) creates an empty, uniquely named database and drops it when the test ends: for migrations on a fresh instance, and the way this package tests itself.

How fast is it?

One test's whole lifecycle against a 60-table schema: get a database at the baseline, insert two rows, undo it. Measured with go test -bench . (see bench_test.go), Apple M4, PostgreSQL 18.6 on localhost:

Approach	Per test	Grows with
Transaction()	~3.4 ms	nothing (mostly one connection)
Deletion()	~1.5 ms	tables and rows left behind
Truncation()	~36 ms	the number of tables
CREATE DATABASE … TEMPLATE + drop	~78 ms	the size of the template database

Read it as orders of magnitude, not a ranking to copy: with two rows and a warm pool Deletion wins by a couple of milliseconds, and it loses as tests write to more tables. What does not change is that Transaction costs the same however big the schema or the test's data is, and it is the only one whose tests can run with t.Parallel() against one database (as long as they do not contend for the same rows), because every session has its own transaction. A database per test from a template is exact and needs no cleanup logic, but at tens of milliseconds it is the slowest and adds up over a few thousand tests.

testcontainers is not an alternative to any of these: it provisions the server (about 1.3 s for a PostgreSQL 17 container with a warm image on the same machine), once per suite. pgcleaner is what runs inside it, per test. Start the container in TestMain, point the Cleaner at it, and keep the isolation here.

Development

Tools are pinned by mise. The suite needs a PostgreSQL server whose role can create databases (PGCLEANER_TEST_URL, default 127.0.0.1:15432):

mise run test          # go test -race -shuffle=on
mise run check         # golangci-lint, typos, gitleaks, actionlint, zizmor
mise run check:vulns   # govulncheck (nightly in CI, not part of check)

Documentation

Overview

Package pgcleaner puts a PostgreSQL database back to its baseline after a test, the way Ruby's DatabaseCleaner does: pick a strategy, start a session, clean it.

Three strategies are built in:

• Transaction runs the whole test inside one transaction and rolls it back. The fastest, but the test must use the *sql.DB it is given: another connection (a server in another process, a second pool) cannot see its rows.
• Truncation commits normally and runs TRUNCATE on the tables afterwards.
• Deletion commits normally and runs DELETE on the tables afterwards, children before parents.

Null cleans nothing. The package knows nothing about an ORM: it hands out a *database/sql.DB and runs one setup callback (migrations, fixtures) per process.

Index

• func DefaultGuard(database string) bool
• func ScratchDatabase(t testing.TB, adminURL string) string
• type Cleaner
  • func New(url string, opts ...Option) *Cleaner
  • func (c *Cleaner) CleanWith(ctx context.Context, s Strategy) error
  • func (c *Cleaner) Cleaning(ctx context.Context, fn func(db *sql.DB) error) error
  • func (c *Cleaner) Close() error
  • func (c *Cleaner) DB(t testing.TB) *sql.DB
  • func (c *Cleaner) Start(ctx context.Context) (*Session, error)
  • func (c *Cleaner) URL(ctx context.Context) (string, error)
• type Option
  • func WithDriver(name string) Option
  • func WithGuard(allow func(database string) bool) Option
  • func WithSetup(fn func(ctx context.Context, db *sql.DB) error) Option
  • func WithStrategy(s Strategy) Option
  • func WithTemplate(version string, build func(ctx context.Context, db *sql.DB, url string) error) Option
• type Session
  • func (s *Session) Clean(ctx context.Context) error
  • func (s *Session) DB() *sql.DB
• type Strategy
  • func Deletion(opts ...TableOption) Strategy
  • func Null() Strategy
  • func Transaction() Strategy
  • func Truncation(opts ...TableOption) Strategy
• type TableOption
  • func Except(tables ...string) TableOption
  • func Only(tables ...string) TableOption

Functions

func DefaultGuard

func DefaultGuard(database string) bool

DefaultGuard allows a database whose name contains "test", "e2e" or "scratch". Wiping a database is the one irreversible thing this package does, so a URL that still points at a development database is refused rather than emptied.

func ScratchDatabase

func ScratchDatabase(t testing.TB, adminURL string) string

ScratchDatabase creates an empty, uniquely named database on the server behind adminURL and returns its URL; the database is dropped when the test ends. It is for code that needs a database with no schema at all (migrations on a fresh instance), which a pre-migrated one cannot give. The role in adminURL must be able to create databases.

Types

type Cleaner

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

Cleaner starts cleaning sessions against one database. Build it once per test process (a package variable next to TestMain) and share it between tests.

func New

func New(url string, opts ...Option) *Cleaner

New returns a Cleaner for the database at url. Nothing connects until the first session starts.

func (*Cleaner) CleanWith

func (c *Cleaner) CleanWith(ctx context.Context, s Strategy) error

CleanWith cleans once with the given strategy, whatever the Cleaner's own is. The usual use is a Truncation before the suite, then Transaction per test.

func (*Cleaner) Cleaning

func (c *Cleaner) Cleaning(ctx context.Context, fn func(db *sql.DB) error) error

Cleaning runs fn in a session and cleans it afterwards, even when fn fails.

func (*Cleaner) Close

func (c *Cleaner) Close() error

Close releases the shared pool the committing strategies use and the worker database lease. A Cleaner that only ran Transaction on its own database holds nothing to close.

func (*Cleaner) DB

func (c *Cleaner) DB(t testing.TB) *sql.DB

DB starts a session for the test and cleans it when the test ends. A failure to start or to clean fails the test.

func (*Cleaner) Start

func (c *Cleaner) Start(ctx context.Context) (*Session, error)

Start begins a session with the Cleaner's strategy.

func (*Cleaner) URL

func (c *Cleaner) URL(ctx context.Context) (string, error)

URL is the URL of the database the Cleaner works on: the one it was built with, or, with WithTemplate, this process's worker database. It starts what is needed (the lease, the setup) if that has not happened yet.

type Option

type Option func(*Cleaner)

Option tunes a Cleaner.

func WithDriver

func WithDriver(name string) Option

WithDriver names the database/sql driver the URL is opened with. The default is "pgx", registered by this package; any other driver must be imported by the caller.

func WithGuard

func WithGuard(allow func(database string) bool) Option

WithGuard replaces the check that the database may be wiped, run before Truncation and Deletion touch it (see DefaultGuard). A nil guard allows every database.

func WithSetup

func WithSetup(fn func(ctx context.Context, db *sql.DB) error) Option

WithSetup registers the callback that brings the database to its baseline: migrate the schema, load the fixtures. It runs once per Cleaner, on a committing connection, before the first session starts. Its error fails every session.

func WithStrategy

func WithStrategy(s Strategy) Option

WithStrategy sets the strategy Cleaner.Start and Cleaner.DB use. The default is Transaction.

func WithTemplate

func WithTemplate(version string, build func(ctx context.Context, db *sql.DB, url string) error) Option

WithTemplate makes the Cleaner work on a database of its own instead of the one in its URL, so that test processes (one per package under `go test ./...`) run in parallel without sharing rows or locks.

Two kinds of database are kept next to the one in the URL, called base:

• base_tpl_<hash of version>, the template (a base too long for a 63-byte name is shortened, with a hash of it). It is built once per version, by whichever process comes first and under an advisory lock, by running build on an empty database; others wait for it. A template that exists is always complete. Older versions are dropped.
• base_w<i>, the workers. A process leases the lowest free index with a session advisory lock, which the server releases when the process ends, however it ends: there is no cleanup hook to forget, and the number of databases stays bounded by the number of concurrent processes. A worker is cloned from the template when it is missing or was cloned from another version, and is otherwise reused as it is.

version names everything that decides what build produces: bump it when the schema changes (a hash of the migration files, say). build gets the template database and its URL, for a tool that opens its own connection.

What differs per process belongs in WithSetup, which runs on the worker every time (fixtures with relative dates, for one): it also wipes whatever an earlier process left. The database to hand to code that opens its own connection is Cleaner.URL.

The role needs the right to create databases. A Cleaner whose first session starts inside a testing/synctest bubble must be closed inside it, or never: a test process just ends.

type Session

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

Session is one test's view of the database, from Cleaner.Start to Session.Clean.

func (*Session) Clean

func (s *Session) Clean(ctx context.Context) error

Clean undoes what the session did. A session is cleaned once.

func (*Session) DB

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

DB is the handle the code under test must use.

type Strategy

type Strategy interface {
	// contains filtered or unexported methods
}

Strategy is how a session undoes a test's changes.

func Deletion

func Deletion(opts ...TableOption) Strategy

Deletion empties the tables with DELETE after the test, children before parents. It is the cheaper choice when a test touches few of many tables, and the one that works without the TRUNCATE privilege. Sequences are not reset. A foreign-key cycle among the cleaned tables is an error: use Truncation there.

Migration bookkeeping tables are never cleaned (see Except).

func Null

func Null() Strategy

Null cleans nothing: the session is the committing pool, untouched afterwards.

func Transaction

func Transaction() Strategy

Transaction rolls the test back. Every session owns one connection with one open transaction; the code under test sees it as an ordinary *sql.DB, and its own Begin/Commit/Rollback become savepoints, so nested transactions work and still vanish with the session. The connection is limited to one: a second would start a second transaction that does not see the first one's writes.

func Truncation

func Truncation(opts ...TableOption) Strategy

Truncation empties the tables with TRUNCATE ... RESTART IDENTITY CASCADE after the test. Rows are committed, so any connection sees them (a server under test in another process, a second pool), at the price of a slower clean and sessions that must not run in parallel against one database.

Migration bookkeeping tables are never truncated (see Except).

type TableOption

type TableOption func(*filter)

TableOption narrows the tables Truncation and Deletion clean.

func Except

func Except(tables ...string) TableOption

Except leaves these tables alone, on top of the migration tables.

func Only

func Only(tables ...string) TableOption

Only cleans just these tables.