PostgreSQL integration testing done right in Go

If you aren’t using pgtestdb you’re living under a rock.

There are really only five ways to test code that talks to Postgres, and four of them are bad. Either you boot a container and pray, share one database and gamble, fake transactions at the driver level, or truncate everything and go single-file. pgtestdb is the first option I’ve seen that is none of those things - isolated, parallel, and genuinely fast, all at once.

The testcontainers trap

The modern default for database tests is containers. testcontainers-go (and ory/dockertest, which is the same idea) boots a Postgres in Docker, waits for it to be healthy, runs your migrations, lets the tests run, and tears everything down.

Nothing is wrong with the approach conceptually - a real database, real migrations, real isolation. The problem is physics:

• Startup happens per package. A container needs its image, its healthcheck, and a warm Postgres before a single test runs. That is seconds even on a fast machine, and it happens once per package. go test ./... across 30 packages with database tests means 30 Postgres boots.
• Migrations run from scratch, every single time. The container starts empty, so the whole migration history replays from zero. Hundreds of migrations plus the seed data they create, burned on every local run and every CI build.
• Nothing gets reused. Containers are deliberately ephemeral and isolated, so you cannot amortize the cost across packages or across runs. Wall-clock time scales linearly with the number of packages, forever.

So the “modern” approach is correct, and also exactly the reason your database test suite takes ten minutes.

One shared database (only if you’re already multi-tenant)

There is one scenario where a single shared database genuinely works: when multi-tenancy is already part of your domain. If every meaningful table in your schema has a tenant_id - not a column you invented to make tests pass, but the same one your production queries filter on - you can point all the tests at one database and have each test operate on its own tenant. No test-only structure is introduced; the tests are just using the isolation your domain already provides.

That is still an optimistic scenario, for a few reasons:

• Not everything is tenant-scoped. Lookup tables, config, settings, countries - the global rows that have no natural tenant. Any parallel test touching them touches the same rows as every other test.
• IDs are global, not per-tenant. Sequences live at the table level, so a test that inserts a row and asserts id == 1 breaks the moment another test inserted first - even under a different tenant. You learn to never trust absolute IDs.
• Isolation is logical, not physical. The whole scheme is one forgotten WHERE tenant_id = ? away from reading another test’s data - and the failure is usually silent, because the leaked rows often make the query return something plausible. Tests that pass for the wrong reasons are how you get 2 a.m. flakiness.

It works, until the day one query forgets its filter. The containment is a convention, not a guarantee.

Driver-level isolation

The clever-sounding shortcut: register a driver that wraps your real driver, and every statement you run goes through it. go-txdb opens one transaction per connection and rolls it back when the connection closes - so the shared test database looks pristine after every test, no truncation, no reloading.

The idea has real appeal: it plugs into your existing *sql.DB, and from the database’s point of view the state is clean at the end of every run. But zoom in on what it actually does:

• Each test gets its own transaction only if you make it so. Transactions are keyed by the identifier you pass to sql.Open("txdb", id); every handle opened with the same identifier lands on the same *sql.Tx behind a driver-wide mutex. Open a second handle with the same identifier and it reads the first handle’s uncommitted inserts - nothing rolls back until the last handle closes. That is the README’s canonical setup - one shared *sql.DB - so a suite that follows it runs every test inside one transaction: test B executes inside test A’s transaction, reads A’s uncommitted rows, and fights A’s row locks. To actually get per-test transactions you must open, use, and close a unique identifier inside each test. Nothing enforces that discipline - one shared handle, one reused identifier, or one forgotten defer db.Close() collapses every test back into a single shared transaction.
• Your application’s transactions become savepoints. db.Begin() runs SAVEPOINT tx_1; Commit() runs RELEASE SAVEPOINT tx_1; Rollback() runs ROLLBACK TO SAVEPOINT tx_1. The transaction your test opens is not a transaction. It is a bookmark inside one giant transaction, and everything you “commit” still vanishes when the outer transaction finally rolls back. (If your driver doesn’t support savepoints, Begin/Commit/Rollback become silent no-ops instead - your transaction does literally nothing.)
• Connection pooling is a lie. One transaction means one real connection, so maxOpenConns is meaningless and everything serializes. Rows are eagerly buffered in memory (every query slurps the entire result set before returning), so streaming and lock semantics both silently differ from production.

So the isolation go-txdb offers is “the database looks untouched afterward” - the same guarantee every other approach in this post already makes - without giving tests any actual separation from each other, and with quietly rewritten transaction semantics your application depends on. The fix for a shared database is not to fake transactions at the driver level; it is to stop sharing the database.

No parallel tests, truncate everything (terrible)

The last stop before giving up: run every test sequentially, and wipe all tables at the start of each test.

I used to work at a company that did exactly this. It needs no explanation because it is the default answer - but let’s spell out what it costs anyway:

• Everything runs alone. One test at a time means wall-clock time is the sum of every test. The measured response is to write fewer database tests, which is a worse outcome than any slowness.
• Truncate is not free. Truncating fails if another test still holds a connection. Getting it right with foreign keys needs CASCADE and the accompanying paranoia. Identity sequences don’t reset unless you know the incantation, so “cleared” tables are not actually empty from the test’s point of view.
• Order becomes a feature. A test that forgets to truncate passes because the previous test left behind exactly the rows it needed. Your suite starts passing or failing based on test ordering, and nobody can explain why.

It is unpredictable, non-deterministic, and absolutely terrible.

pgtestdb: template databases done right

pgtestdb takes a fifth path, and the trick is that Postgres has had the right mechanism built in for decades: template databases.

What are template databases?

Any Postgres database can be marked as a template. Once marked, other databases can be cloned from it in one statement:

CREATE DATABASE my_test_db TEMPLATE my_template;

This is a file-level copy, not a logical one - no pg_dump, no replaying inserts. The clone comes out as a complete, independent, fully functional database with the exact schema and data of the template. Templates are read-only, so a clone can never mutate the template, and clones can never see each other’s changes. Complete isolation, for the cost of copying some files.

That’s precisely what you want for tests: a fully migrated, correct starting point, cloned cheaply and independently for every test.

How pgtestdb uses them

The flow behind pgtestdb.New(t, conf, migrator):

1. First call of a run - pgtestdb checks whether a template for your migrations exists. If not, it creates an empty database, runs your migrations exactly once, and marks the result as a template.
2. Every other call - it just clones the template. CREATE DATABASE ... TEMPLATE ... takes on the order of 10-20ms, no matter how large your schema is.
3. Templates are keyed by migration hash. The template’s name is a hash of your migration definitions, so it is reused across all tests, across all packages, and across separate test runs. Your migrations only run again when the migrations actually change. Old templates linger as leftovers - drop them yourself when you clean up.
4. It is concurrency-safe out of the box. Advisory locks (Postgres-level) and Go-level locks make sure only one test ever creates the template, even under go test -parallel with dozens of packages racing to provision at once. The answer to “can I run my tests in parallel?” is “yes, please do - that’s the point.”
5. Cleanup is automatic and deliberate. On success, a t.Cleanup hook drops the test’s clone - no leaked databases. On failure, the clone is left alive, and pgtestdb logs its connection string so you can psql into the exact state that broke and poke around. Failure debugging becomes dramatically easier.

What it looks like

// NewDB is a helper returning an open connection to a unique, isolated,
// fully migrated test database.
func NewDB(t *testing.T) *sql.DB {
	t.Helper()

	conf := pgtestdb.Config{
		DriverName: "pgx",
		User:       "postgres",
		Password:   "password",
		Host:       "localhost",
		Port:       "5433", // dedicated test server, NOT your dev/prod DB
		Options:    "sslmode=disable",
	}

	// Pick the migrator for your framework: golang-migrate, goose, atlas,
	// dbmate, tern, sql-migrate, pgmigrate, or write your own Migrator.
	var migrator pgtestdb.Migrator = golangmigrator.New(...)

	return pgtestdb.New(t, conf, migrator)
}

func TestCreateUser(t *testing.T) {
	t.Parallel() // every test gets its own database; parallel is the point
	db := NewDB(t)

	userID, err := createUser(db, "ada@example.com")
	require.NoError(t, err)

	var email string
	err = db.QueryRow(
		"SELECT email FROM users WHERE id = $1", userID,
	).Scan(&email)
	require.NoError(t, err)
	assert.Equal(t, "ada@example.com", email)
}

That is the entire ceremony. No SetupSuite, no tracking which test “owns” the shared database, no truncation incantations, no waiting on container healthchecks. NewDB(t) is the whole abstraction, and it composes with anything - testify suites, t.Parallel(), subtests, benchmarks (it accepts testing.TB, so *testing.T, *testing.B, and *testing.F all work).

Making it faster

The clone itself is milliseconds, but the size of the Postgres server still matters. pgtestdb recommends a dedicated, RAM-backed test server - the README’s docker-compose mounts a tmpfs volume for the data directory and turns off fsync:

services:
  pgtestdb:
    image: postgres:15
    environment:
      POSTGRES_PASSWORD: password
    volumes:
      - type: tmpfs
        target: /var/lib/postgresql/data/
    command:
      - "postgres"
      - "-c" # fsync off: the data is throwaway, no durability needed
      - "fsync=off"
    ports:
      - "5433:5432"

fsync=off and a ramdisk are catastrophically wrong for production and perfectly right for a test server whose data survives for seconds. With that setup, the numbers are: ~500ms once to prepare the template with about a thousand migrations, ~10ms per clone after that. A thousand tests in parallel each get their own pristine database in the time the old approach took to boot one container.

The trade-offs

• You need a dedicated server with admin rights. pgtestdb creates and drops databases and roles (pgtdbuser by default), so it must not touch anything you care about. Test server only - this is a hard rule, not a suggestion.
• One migration run per schema version, not per test. If your migration framework can’t hash its own state, the existing migrators handle it, and the Migrator interface is small enough to implement yourself.
• It replaces nothing else. You still write unit tests, you still use dependency injection, you still mock things that deserve mocking. The database is just no longer one of them - which is the goal, because most real applications have serious logic living in Postgres that is miserable to fake.

The testcontainers approach is slow, the shared-database approach is a gamble, go-txdb turns your application’s transactions into savepoints that vanish on rollback, and truncating everything is how you teach your team to hate database tests. pgtestdb gives every test a full, real, isolated database in milliseconds - and runs them all in parallel. If you’re still staring at a ten-minute database test suite, you know what to do.