sqlc Testing With Real Databases in CI Pipelines Safely

The queries sqlc generates are thin. Most of the logic that can go wrong is in the SQL itself: a join that duplicates rows, a where clause that misses the null case, a unique constraint that does not exist yet. A test that replaces the database with a mock checks that the Go code calls the method it was told to call, and says nothing about any of those. The tests worth writing run the real queries against a real PostgreSQL.

This article builds a test setup for that, with one schema per test so tests can run in parallel and clean up after themselves. It runs on PostgreSQL 18.6 with sqlc v1.30.0, pgx v5.11.0 and Go 1.25.6. It builds on the sqlc tutorial, complements the pgx testing article and testing migrations in CI, and sits in the database cornerstone.

Why not a mock

The generated Querier interface (with emit_interface: true) makes a fake easy to write, and for a handler that only decides what to do with a returned user, a fake is fine. For the queries themselves it is the wrong tool, for three reasons.

• A fake returns what you tell it. The bug is in what PostgreSQL returns for the real SQL, which the fake never executes.
• Constraints and defaults live in the database. A unique index, a check, a default that fills a column: none exist in a fake, and they are where much of the behaviour is.
• Error values come from the driver. The test for "duplicate email becomes a conflict" needs a real SQLSTATE 23505, with the wrapping pgx gives it.

A real database costs a service container in CI and a few milliseconds per test. For most services that is a good trade.

Isolation: one schema per test

The goal is tests that cannot see each other's rows, so that they can run in parallel and in any order. Three approaches exist:

A schema per test is the middle road. It gives each test its own copy of every table in the same database, creates in milliseconds, and works for code that begins its own transactions, which the rollback approach cannot support. Its one limit is that it isolates tables and not cluster wide objects: roles, extensions and enum types created in public are shared, so the schema file should create types inside the schema, which set search_path achieves for unqualified names.

The setup has one trick that makes the generated code work unchanged: the queries are written without a schema name (from users), so they resolve through search_path. Setting that as a runtime parameter of the connection points every query on that pool at the test's schema.

package main

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

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

const base = "postgres://postgres:[email protected]:55432/sqlc0697?sslmode=disable"

var schemaSQL []byte
var n atomic.Int64

func TestMain(m *testing.M) {
	var err error
	schemaSQL, err = os.ReadFile("schema.sql")
	if err != nil {
		panic(err)
	}
	os.Exit(m.Run())
}

// testDB gives each test its own schema, so tests run in parallel and leave nothing behind.
func testDB(t *testing.T) *Queries {
	t.Helper()
	ctx := context.Background()
	name := fmt.Sprintf("t%d", n.Add(1))
	admin, err := pgx.Connect(ctx, base)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "create schema "+name); err != nil {
		t.Fatal(err)
	}
	cfg, _ := pgxpool.ParseConfig(base)
	cfg.ConnConfig.RuntimeParams["search_path"] = name
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := pool.Exec(ctx, string(schemaSQL)); err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() {
		pool.Close()
		_, _ = admin.Exec(ctx, "drop schema "+name+" cascade")
		_ = admin.Close(ctx)
	})
	return New(pool)
}

func TestAddAndUpgrade(t *testing.T) {
	t.Parallel()
	q, ctx := testDB(t), context.Background()
	u, err := q.AddUser(ctx, "[email protected]")
	if err != nil || u.Plan != "free" {
		t.Fatalf("AddUser = %+v, %v", u, err)
	}
	u, err = q.Upgrade(ctx, "[email protected]")
	if err != nil || u.Plan != "pro" {
		t.Fatalf("Upgrade = %+v, %v", u, err)
	}
}

func TestDuplicateEmail(t *testing.T) {
	t.Parallel()
	q, ctx := testDB(t), context.Background()
	if _, err := q.AddUser(ctx, "[email protected]"); err != nil {
		t.Fatal(err)
	}
	if _, err := q.AddUser(ctx, "[email protected]"); err == nil {
		t.Fatal("a duplicate email was accepted")
	}
}

func TestIsolation(t *testing.T) {
	t.Parallel()
	q, ctx := testDB(t), context.Background()
	c, err := q.CountUsers(ctx)
	if err != nil || c != 0 {
		t.Fatalf("a fresh schema should be empty, got %d, %v", c, err)
	}
}

func TestMissingUser(t *testing.T) {
	t.Parallel()
	q, ctx := testDB(t), context.Background()
	if _, err := q.Upgrade(ctx, "[email protected]"); err != pgx.ErrNoRows {
		t.Fatalf("want pgx.ErrNoRows, got %v", err)
	}
}

The helper testDB does five things.

1. Names the schema from an atomic counter (t1, t2, ...). It does not use the test name, because test names contain characters that need quoting and subtests repeat.
2. Creates it over an admin connection.
3. Opens a pool whose search_path runtime parameter is the new schema, so every connection in the pool resolves users to t1.users.
4. Loads schema.sql through that pool, so the tables land in the schema. The file is read once in TestMain.
5. Registers a cleanup that closes the pool and runs drop schema ... cascade, so the schema disappears when the test ends, pass or fail.

New(pool) then gives the test a Queries over its own world.

The code under test

The queries are three: add a user, upgrade a user to the pro plan, and count users. The schema has a unique email:

create table users (
    id    bigserial primary key,
    email text not null unique,
    plan  text not null default 'free'
);

-- name: AddUser :one
insert into users (email) values ($1) returning *;

-- name: Upgrade :one
update users set plan = 'pro' where email = $1 returning *;

-- name: CountUsers :one
select count(*) from users;

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: main
        out: .
        sql_package: pgx/v5

package main

import "fmt"

func main() { fmt.Println("see the tests") }

The test file (shown above) asserts four behaviours: that a new user gets the default plan free and an upgrade makes it pro; that adding the same email twice fails; that a fresh schema is empty; and that upgrading a user that does not exist returns pgx.ErrNoRows.

The run

set -e
export PGOPTIONS='-c client_min_messages=warning'
DB=sqlc0697
docker exec -e PGOPTIONS="$PGOPTIONS" gh-lab-pg psql -U postgres -qc "drop database if exists $DB" -c "create database $DB"
"$HOME/go/bin/sqlc" generate
go test -count=1 -v ./... 2>&1 | sed -E 's/\([0-9.]+s\)/(Ns)/; s/[0-9]+\.[0-9]+s$/Ns/'
docker exec gh-lab-pg psql -U postgres -d $DB -Atc "select count(*) || ' test schemas left behind' from pg_namespace where nspname ~ '^t[0-9]+$'"

=== RUN   TestAddAndUpgrade
=== PAUSE TestAddAndUpgrade
=== RUN   TestDuplicateEmail
=== PAUSE TestDuplicateEmail
=== RUN   TestIsolation
=== PAUSE TestIsolation
=== RUN   TestMissingUser
=== PAUSE TestMissingUser
=== CONT  TestAddAndUpgrade
=== CONT  TestIsolation
=== CONT  TestMissingUser
=== CONT  TestDuplicateEmail
--- PASS: TestMissingUser (Ns)
--- PASS: TestIsolation (Ns)
--- PASS: TestDuplicateEmail (Ns)
--- PASS: TestAddAndUpgrade (Ns)
PASS
ok  	example.test/lab	Ns
0 test schemas left behind

All four tests passed. They ran in parallel (each printed === PAUSE and then === CONT), against one database, in four schemas, with no coordination between them. The final command counted schemas whose names match the helper's naming pattern after the run and printed 0 test schemas left behind, which is the check that cleanup works. Leaked schemas are the failure mode of this design, so a line like that belongs in CI as well: a test run that leaves more than zero is a bug in the helper, found before the database fills with debris.

The sed in run.sh replaces the timings with Ns so the recorded output is stable. It does not change what ran.

What to assert

Test behaviour that lives in the SQL:

• Defaults and generated values: the plan was free without the insert mentioning it.
• Constraints: a duplicate email must fail. Assert on the error code and not the text; the unique violation article shows how to read 23505 from the driver error.
• Absence: a missing row returns pgx.ErrNoRows, and an update that matches nothing returns zero rows from :execrows.
• Joins and aggregates with a row that has no match, since those are where results go wrong silently.
• Ordering and pagination, with enough rows to cross a page boundary.

Leave out what the type system already guarantees: that the field Plan exists and is a string.

Running it in CI

A service container is the simplest source of PostgreSQL. In GitHub Actions it is a services: block with the postgres:18-alpine image, a health check and the connection string in an environment variable. The lab hard codes the connection string to keep the listing short; in a real project read it from an environment variable, so the same tests run locally against a Docker container and in CI against the service. Two points matter more than the YAML:

• Pin the same major version as production. A query that works on 16 and fails on 18 is a finding, and the way to find it is to run the tests on the version you deploy.
• Run sqlc generate and sqlc diff in the same pipeline, as the vet and lint article shows, so the tests run against code that matches the queries.

Testcontainers is the alternative when you want the test to start its own database, with no CI configuration. It adds a Docker dependency to every developer's test run and a few seconds of start up, which is a fair price for a self contained suite.

Pitfalls

• Qualified names in queries (public.users) bypass search_path and hit the shared schema. Write unqualified names.
• Enum types and functions created in public are shared between tests and fail with "already exists" under parallelism. Create them in the schema file, which runs per test.
• Sequences keep going only inside their schema, so ids start at 1 in every test. That is usually what you want. Tests that assert on a specific id are fragile for other reasons.
• Forgetting t.Parallel() makes the schema per test pointless, since the run is serial and could have truncated tables instead.
• A cleanup that runs while the pool is still open can block on a connection that holds a lock on the schema. The helper closes the pool first, then drops.

Checklist

• Run queries against real PostgreSQL of the production major version.
• One schema per test, search_path on the pool, cleanup with drop schema cascade.
• Mark tests parallel, and assert that no schemas remain.
• Test constraints and absence, not only the happy path.
• Keep a few tests that go through the full stack (handler to database) with the same helper.

The setup relies on PostgreSQL's schema search path, pgx's runtime parameters and Go's parallel tests.