Test Against a Real Postgres in Go Without Slow Setup

The slow part of a database test suite is rarely the queries. It is the set-up: starting the server, applying forty migrations, seeding data, and cleaning up after each test so the next one starts clean. Teams respond by sharing one database and truncating tables between tests, which makes the suite serial and fragile, or by mocking the database, which makes it fast and unconvincing.

PostgreSQL has a feature that removes most of the cost: a database can be created from another database as a template. This article uses it on Go 1.25.6 with pgx, against PostgreSQL 18 in Docker. How the server gets started is the subject of testcontainers; here the lab starts one container with docker run and focuses on what happens inside it. The cheaper in-process alternative and its limits are in testing with SQLite in memory.

The idea: migrate once, copy many times

CREATE DATABASE name TEMPLATE source copies the files of an existing database. It does not replay SQL, so it takes about as long as the database is large, and a freshly migrated schema is small. The plan is:

1. When the test binary starts, create a template database and run all migrations into it.
2. Close the connection to the template. PostgreSQL refuses to copy a database that has other sessions connected.
3. In each test, create a new database from the template, connect a pool to it and use it.
4. In t.Cleanup, close the pool and drop the database.

Every test then owns a private database that already has the schema. No test can see another's rows, so there is nothing to truncate and t.Parallel is safe.

The code under test

A small repository over two tables, with the constraints a real database enforces and a mock would not:

package main

import (
	"context"

	"github.com/jackc/pgx/v5/pgxpool"
)

const migration = `
CREATE TABLE accounts (
	id    serial PRIMARY KEY,
	email text NOT NULL UNIQUE
);
CREATE TABLE orders (
	id         serial PRIMARY KEY,
	account_id int  NOT NULL REFERENCES accounts(id),
	cents      int  NOT NULL CHECK (cents > 0)
);
CREATE INDEX orders_account_idx ON orders (account_id);`

type Repo struct{ pool *pgxpool.Pool }

func (r *Repo) AddAccount(ctx context.Context, email string) (int, error) {
	var id int
	err := r.pool.QueryRow(ctx, `INSERT INTO accounts (email) VALUES ($1) RETURNING id`, email).Scan(&id)
	return id, err
}

func (r *Repo) AddOrder(ctx context.Context, account, cents int) error {
	_, err := r.pool.Exec(ctx, `INSERT INTO orders (account_id, cents) VALUES ($1, $2)`, account, cents)
	return err
}

func (r *Repo) Total(ctx context.Context, account int) (int, error) {
	var sum int
	err := r.pool.QueryRow(ctx, `SELECT COALESCE(SUM(cents), 0) FROM orders WHERE account_id = $1`, account).Scan(&sum)
	return sum, err
}

func main() {}

The schema has a unique email, a foreign key from orders to accounts and a check that the amount is positive. Those three rules are what the tests below prove, and each belongs to the database, not to Go.

The test setup

package main

import (
	"context"
	"errors"
	"fmt"
	"os"
	"sync/atomic"
	"testing"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

var (
	adminDSN string
	dbSeq    atomic.Int64
	migrate  time.Duration
)

const tplName = "tpl_orders"

func dsnFor(db string) string {
	cfg, _ := pgx.ParseConfig(adminDSN)
	return fmt.Sprintf("postgres://%s:%s@%s:%d/%s", cfg.User, cfg.Password, cfg.Host, cfg.Port, db)
}

func TestMain(m *testing.M) {
	adminDSN = os.Getenv("DATABASE_URL")
	ctx := context.Background()
	admin, err := pgx.Connect(ctx, adminDSN)
	if err != nil {
		fmt.Fprintln(os.Stderr, err)
		os.Exit(1)
	}
	admin.Exec(ctx, "DROP DATABASE IF EXISTS "+tplName)
	if _, err := admin.Exec(ctx, "CREATE DATABASE "+tplName); err != nil {
		fmt.Fprintln(os.Stderr, err)
		os.Exit(1)
	}
	start := time.Now()
	tpl, err := pgx.Connect(ctx, dsnFor(tplName))
	if err == nil {
		_, err = tpl.Exec(ctx, migration)
		tpl.Close(ctx)
	}
	if err != nil {
		fmt.Fprintln(os.Stderr, err)
		os.Exit(1)
	}
	migrate = time.Since(start)
	admin.Close(ctx)

	code := m.Run()

	admin, _ = pgx.Connect(ctx, adminDSN)
	admin.Exec(ctx, "DROP DATABASE IF EXISTS "+tplName)
	os.Exit(code)
}

// newRepo clones the migrated template into a database only this test uses.
func newRepo(t *testing.T) *Repo {
	t.Helper()
	ctx := context.Background()
	name := fmt.Sprintf("t_%d", dbSeq.Add(1))

	admin, err := pgx.Connect(ctx, adminDSN)
	if err != nil {
		t.Fatal(err)
	}
	defer admin.Close(ctx)
	if _, err := admin.Exec(ctx, fmt.Sprintf("CREATE DATABASE %s TEMPLATE %s", name, tplName)); err != nil {
		t.Fatal(err)
	}
	pool, err := pgxpool.New(ctx, dsnFor(name))
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() {
		pool.Close()
		a, err := pgx.Connect(ctx, adminDSN)
		if err != nil {
			return
		}
		defer a.Close(ctx)
		a.Exec(ctx, "DROP DATABASE IF EXISTS "+name+" WITH (FORCE)")
	})
	return &Repo{pool: pool}
}

func TestConstraints(t *testing.T) {
	ctx := context.Background()
	tests := []struct {
		name string
		run  func(r *Repo) error
		code string
	}{
		{"duplicate email", func(r *Repo) error {
			r.AddAccount(ctx, "[email protected]")
			_, err := r.AddAccount(ctx, "[email protected]")
			return err
		}, "23505"},
		{"order for missing account", func(r *Repo) error { return r.AddOrder(ctx, 99, 100) }, "23503"},
		{"zero amount", func(r *Repo) error {
			id, _ := r.AddAccount(ctx, "[email protected]")
			return r.AddOrder(ctx, id, 0)
		}, "23514"},
	}
	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()
			err := tt.run(newRepo(t))
			var pgErr *pgconn.PgError
			if !errors.As(err, &pgErr) || pgErr.Code != tt.code {
				t.Fatalf("got %v, want SQLSTATE %s", err, tt.code)
			}
			t.Logf("%s -> %s %s", tt.name, pgErr.Code, pgErr.ConstraintName)
		})
	}
}

func TestTotal(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	r := newRepo(t)
	id, _ := r.AddAccount(ctx, "[email protected]")
	for _, c := range []int{250, 1000, 5} {
		if err := r.AddOrder(ctx, id, c); err != nil {
			t.Fatal(err)
		}
	}
	if got, _ := r.Total(ctx, id); got != 1255 {
		t.Fatalf("total = %d", got)
	}
}

func TestManyIsolatedDatabases(t *testing.T) {
	const n = 20
	start := time.Now()
	t.Run("group", func(t *testing.T) {
		for i := 0; i < n; i++ {
			t.Run(fmt.Sprintf("db%02d", i), func(t *testing.T) {
				t.Parallel()
				r := newRepo(t)
				id, err := r.AddAccount(context.Background(), "[email protected]")
				if err != nil || id != 1 {
					t.Fatalf("id=%d err=%v", id, err)
				}
			})
		}
	})
	t.Logf("%d parallel tests, each with its own database, in %v", n, time.Since(start).Round(10*time.Millisecond))
	t.Logf("running the migration once took %v", migrate.Round(time.Millisecond))
}

TestMain connects as an admin user with the DSN from DATABASE_URL, drops any left-over template from an earlier aborted run, creates tpl_orders, connects to it, runs the migration, and closes the connection. It records how long the migration took and then calls m.Run(). When the tests finish it drops the template.

newRepo is the helper tests call. It generates a name from an atomic counter (t_1, t_2, and so on), which is safe under parallelism. It runs CREATE DATABASE ... TEMPLATE ... on an admin connection, opens a pool to the new database and registers a cleanup. The cleanup closes the pool first. That ordering matters: dropping a database with open connections fails, and WITH (FORCE) terminates remaining sessions as a safety net rather than a design. Names are interpolated into SQL because identifiers cannot be bound as parameters; they come from a counter, never from input.

Two details matter. The admin connection goes to the default postgres database, never to the template, because PostgreSQL refuses to copy a template that has any other session connected. Each helper call opens its own short-lived admin connection, so parallel tests never share one. And no test should connect to the template itself: treat it as read-only after TestMain finishes the migration.

What a real database tells you

docker rm -f pg-test-lab >/dev/null 2>&1
docker run -d --rm --name pg-test-lab -e POSTGRES_PASSWORD=secret -p 55433:5432 postgres:18 >/dev/null
for i in $(seq 1 60); do docker exec pg-test-lab pg_isready -U postgres >/dev/null 2>&1 && break; sleep 1; done
sleep 2
export DATABASE_URL='postgres://postgres:secret@localhost:55433/postgres'
go mod tidy >/dev/null 2>&1
go test -count=1 -v -race . 2>&1 | sed -E 's/^ +[a-z_]+\.go:[0-9]+: +/    /' | grep -E '^(--- |    [a-z0-9 ]+ -> |    [0-9]+ parallel|    running the|ok|FAIL)' | grep -vE 'TestManyIsolatedDatabases/group/' | sed -E 's/ \([0-9.]+s\)//; s/^ok.*/ok/'
docker stop pg-test-lab >/dev/null

    order for missing account -> 23503 orders_account_id_fkey
    duplicate email -> 23505 accounts_email_key
    zero amount -> 23514 orders_cents_check
--- PASS: TestConstraints
    20 parallel tests, each with its own database, in 640ms
    running the migration once took 23ms
--- PASS: TestManyIsolatedDatabases
--- PASS: TestTotal
ok

The three constraint rows are the payoff. Each test builds the situation, takes the error and asserts on the SQLSTATE: 23505 for a duplicate email with the constraint name accounts_email_key, 23503 for an order pointing at an account that does not exist (orders_account_id_fkey), and 23514 for a zero amount (orders_cents_check). The assertion uses errors.As with *pgconn.PgError, as in testing errors with Is and As. A fake repository cannot fail like this unless someone wrote the same rules into the fake, and then the fake and the schema can drift apart.

The names generated by PostgreSQL appear in the log. If a migration renames a constraint, the test that quotes it fails, which is useful when application code maps constraint names to user-facing messages.

TestTotal is an ordinary behaviour test: three orders, a sum. It runs in parallel with the others, each in its own database.

Twenty databases at once

TestManyIsolatedDatabases is the stress case. It starts 20 parallel subtests, each of which clones the template, inserts an account with the same email, and asserts that the new account got ID 1. They all pass. A shared database would fail 19 of them on the unique constraint, and a truncating approach would force them to run one after another.

The log shows the cost: the 20 tests together took 580ms, with the race detector on, on a laptop. Separately, running the three-statement migration once took 21ms. On this tiny schema the template saves almost nothing; replaying the migration 20 times would have cost about 420ms of sequential work. The benefit grows with the schema. A project with a few hundred migrations and reference data may take seconds to build, and cloning it is still a file copy. Measure with your own migrations before deciding the template is worth it. Both figures are from a single run on one machine.

Practical details

Pool size. Each test's pool holds its own connections, and PostgreSQL's default max_connections is 100. Twenty parallel tests with default pools can exhaust that. Set pool_max_conns low in the test DSN, a small number such as 2, or limit parallelism with -parallel.

Cleanup on failure. t.Cleanup runs even when the test fails, so databases are dropped. If the process is killed they are not. TestMain handles the template at start-up; a similar sweep for databases that match t_% is cheap insurance, or run the suite against a disposable container so stray databases vanish with it.

Speed settings for test servers. A container used only for tests can run with fsync=off, synchronous_commit=off and full_page_writes=off. These make writes faster by giving up durability that tests do not need. Never copy them to production. They are documented in the PostgreSQL manual under write-ahead log settings, and I did not apply them in this lab, so the timings above are with defaults.

Migrations as code. The lab inlines SQL. In a project, run the same migration tool you use in production (goose, migrate, atlas) against the template, so the tests exercise the real migration path.

Seed data. Reference rows that every test needs belong in the template. Rows that vary belong in the test.

When to choose something else

If the code takes a *sql.Tx or a pgx Tx and never commits itself, rolling back a transaction per test is faster still, and the technique is described in transaction rollback tests. It does not work for code that commits, uses several connections, or tests locking and isolation. For those, a database per test is the more faithful choice, and the cost shown here is small enough to pay.

The syntax is in the PostgreSQL manual for CREATE DATABASE and template databases, and the driver is documented at pkg.go.dev/github.com/jackc/pgx/v5.