pgx Testing With Testcontainers and pgmock in Go Suites

Tests for database code fall into two camps that argue past each other. One says: mock the database, so tests are fast and need nothing installed. The other says: use a real database, because the database is what the code is for. Both are right about something, and the useful question is what each kind of test can and cannot catch. This article builds one repository two ways with pgx v5.11.0 on Go 1.25.6 and PostgreSQL 18.6, includes a deliberate bug in its SQL, and shows which test finds it.

It belongs to the database cornerstone. The same split for database/sql is in testing against PostgreSQL and rollback tests, schema tests in testing migrations in CI, and the repository shape in small store interfaces.

A repository that is easy to test

package main

import (
	"context"

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

// DB is the part of a pool or a transaction the repository uses; pgxpool.Pool, pgx.Tx and the mock all satisfy it.
type DB interface {
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
}

type User struct {
	ID    int64
	Email string
	Plan  string
}

type Repo struct{ DB DB }

// ByEmail contains a typo on purpose: the column is called email, not mail.
func (r Repo) ByEmail(ctx context.Context, email string) (User, error) {
	var u User
	err := r.DB.QueryRow(ctx, "select id, email, plan from users674 where mail = $1", email).Scan(&u.ID, &u.Email, &u.Plan)
	return u, err
}

func (r Repo) SetPlan(ctx context.Context, id int64, plan string) error {
	_, err := r.DB.Exec(ctx, "update users674 set plan = $2 where id = $1", id, plan)
	return err
}

The DB interface lists only what the repository uses, QueryRow and Exec. *pgxpool.Pool satisfies it, so does pgx.Tx (so the repository works inside a transaction), and so does the mock. That is the whole design: small interface, defined where it is used. The ByEmail query has a typo on purpose: it filters on a column named mail, while the table's column is email.

The tests

package main

import (
	"context"
	"errors"
	"os"
	"testing"
	"time"

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

var pool *pgxpool.Pool

func TestMain(m *testing.M) {
	ctx := context.Background()
	start := time.Now()
	var err error
	pool, err = pgxpool.New(ctx, os.Getenv("PG_DSN"))
	if err != nil {
		panic(err)
	}
	_, err = pool.Exec(ctx, "create table users674 (id bigint primary key, email text not null unique, plan text not null default 'free')")
	if err != nil {
		panic(err)
	}
	println("schema created in", time.Since(start).Round(time.Millisecond).String())
	code := m.Run()
	pool.Close()
	os.Exit(code)
}

// A unit test with a mock: fast, no database, and it checks that the code scans what the query returns.
func TestMockedByEmail(t *testing.T) {
	mock, err := pgxmock.NewPool()
	if err != nil {
		t.Fatal(err)
	}
	defer mock.Close()
	mock.ExpectQuery("select id, email, plan from users674").WithArgs("[email protected]").
		WillReturnRows(pgxmock.NewRows([]string{"id", "email", "plan"}).AddRow(int64(1), "[email protected]", "pro"))
	u, err := Repo{mock}.ByEmail(context.Background(), "[email protected]")
	if err != nil || u.Plan != "pro" {
		t.Fatalf("got %+v, %v", u, err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
	t.Log("the mock accepted the query, although its WHERE clause names a column that does not exist")
}

// The same code against a real PostgreSQL: the typo is a SQLSTATE 42703 from the server.
func TestRealDatabaseCatchesTheTypo(t *testing.T) {
	_, err := Repo{pool}.ByEmail(context.Background(), "[email protected]")
	var pg *pgconn.PgError
	if !errors.As(err, &pg) || pg.Code != "42703" {
		t.Fatalf("expected an undefined column error, got %v", err)
	}
	t.Logf("the real server answered: %s (%s)", pg.Message, pg.Code)
}

// What a real database is for: constraints, types and behaviour the mock cannot reproduce.
func TestRealDatabaseEnforcesConstraints(t *testing.T) {
	ctx := context.Background()
	_, err := pool.Exec(ctx, "insert into users674 (id, email) values (1, '[email protected]'), (2, '[email protected]')")
	if err != nil {
		t.Fatal(err)
	}
	_, err = pool.Exec(ctx, "insert into users674 (id, email) values (3, '[email protected]')")
	var pg *pgconn.PgError
	if !errors.As(err, &pg) || pg.Code != "23505" {
		t.Fatalf("expected a unique violation, got %v", err)
	}
	if err := (Repo{pool}).SetPlan(ctx, 1, "pro"); err != nil {
		t.Fatal(err)
	}
	var plan string
	if err := pool.QueryRow(ctx, "select plan from users674 where id = 1").Scan(&plan); err != nil || plan != "pro" {
		t.Fatalf("plan %q, %v", plan, err)
	}
	t.Logf("unique violation %s detected, SetPlan verified by reading the row back", pg.Code)
}

set -e
trap 'docker rm -f gh-lab-pg680 >/dev/null 2>&1' EXIT
docker rm -f gh-lab-pg680 >/dev/null 2>&1 || true
docker run -d --name gh-lab-pg680 -p 127.0.0.1:55680:5432 -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app postgres:18-alpine >/dev/null
for i in $(seq 1 60); do
  docker exec gh-lab-pg680 pg_isready -U postgres -d app >/dev/null 2>&1 && break
  sleep 1
done
sleep 2
PG_DSN="postgres://postgres:[email protected]:55680/app?sslmode=disable" go test -trimpath -v -race -count=1 .

schema created in 26ms
=== RUN   TestMockedByEmail
    repo_test.go:51: the mock accepted the query, although its WHERE clause names a column that does not exist
--- PASS: TestMockedByEmail (0.00s)
=== RUN   TestRealDatabaseCatchesTheTypo
    repo_test.go:61: the real server answered: column "mail" does not exist (42703)
--- PASS: TestRealDatabaseCatchesTheTypo (0.00s)
=== RUN   TestRealDatabaseEnforcesConstraints
    repo_test.go:83: unique violation 23505 detected, SetPlan verified by reading the row back
--- PASS: TestRealDatabaseEnforcesConstraints (0.00s)
PASS
ok  	example.test/lab	1.439s

The script starts a throwaway postgres:18-alpine container, waits for it and runs the tests with the connection string in an environment variable; TestMain creates the schema once for the package, which took 26 milliseconds, and every test shares the pool. The three tests show what each approach sees.

TestMockedByEmail uses pgxmock. pgxmock.NewPool() returns a fake that implements the pool interface. The test tells it what to expect, a query matching the regular expression select id, email, plan from users674 with one argument, and what to return, one row of three columns. It then calls the repository and checks the result and that every expectation was met. It passed, in under a millisecond, and the log line explains why that is not good news: the mock accepted the query although its where clause names a column that does not exist. The expectation matched only the beginning of the SQL, as expectations usually do, and a mock cannot know anything about the schema.

TestRealDatabaseCatchesTheTypo runs the same method against PostgreSQL. The server answered column "mail" does not exist (42703), and the test asserts on the SQLSTATE with errors.As. This is the bug the mock hid, found by the first test that touched a real server.

TestRealDatabaseEnforcesConstraints shows other things only a server can check. It inserts two users, tries a third with a duplicate email and gets 23505, calls SetPlan and verifies the effect by reading the row back. Constraints, types, defaults, collation, transaction behaviour and the actual SQL semantics all live in the database.

What each kind of test is for

Use a mock for the code around the query: that a pgx.ErrNoRows becomes a not found error, that an error from Exec is wrapped, that a retry happens on a deadlock error you inject (mock.ExpectQuery(...).WillReturnError(&pgconn.PgError{Code: "40001"})), that a transaction is rolled back on failure. These are paths that are hard to trigger in a real database and easy to script. Use a real database for the SQL itself: every query in the repository should run at least once against a server in a test, with data that exercises the filter, the join and the ordering. The two together cover most of the code.

A rule of thumb that keeps the balance: if the test would still pass after you delete the where clause from the SQL, it is testing the Go code and not the query. Keep those, and add one that fails without the where.

Running a real database in tests

One container per package, not per test. Starting PostgreSQL takes a second or two; creating a schema takes milliseconds. Start it in TestMain (or in a script as here), create the schema once, and isolate tests inside it.

Isolate tests. Three styles, in increasing cost: run each test in a transaction that you roll back at the end (fast, and unsuitable for code that opens its own transactions); give each test its own schema (create schema t123, with search_path set); or give each test its own database, created from a template database that already has the migrations applied, which is fast and fully isolated; see testing migrations in CI. Unique keys per test (random emails, generated ids) also let tests share tables when the code allows it.

Testcontainers. The testcontainers-go module for PostgreSQL starts the container from the test, so no script is needed: tcpostgres.Run(ctx, "postgres:18-alpine", tcpostgres.WithDatabase("app"), ...) returns a container whose ConnectionString you pass to pgxpool.New. It worked in this lab when tried, and it prints a banner with a timestamp and the Docker host on startup, which is why the lab uses a plain script instead, and it needs Docker access from wherever the tests run. It is the more convenient choice on a developer machine, and a service container in the CI configuration is simpler there.

Speed up tests by running the container's data directory on a tmpfs (--tmpfs /var/lib/postgresql/data), setting fsync=off and synchronous_commit=off for the test server (never in production), and running packages in parallel against separate databases.

Keeping both honest

• Fail the build on drift: a CI step that applies migrations to an empty database and runs the repository tests means a schema change that breaks a query breaks the build.
• Assert on SQLSTATE, not messages. The test above does, with errors.As and the code, and survives wording changes.
• Give mocks specific expectations (exact arguments, a pattern with the table and key columns) and call ExpectationsWereMet, so a mock test fails when the code stops running the query.
• Do not mock what you do not own beyond the boundary: mock your own small interface, as here, rather than every pgx type.
• Test with the race detector, as the lab does, since pools and goroutines are the usual place for data races.

pgmock, the protocol level fake server by the pgx author, is a third option for testing how a client behaves against a misbehaving server, at the wire level; its published version predates pgx v5's own protocol package, so check compatibility before using it. The libraries are documented at pgxmock and testcontainers for Go, and the container image at Docker Hub.