Integration Tests for Go APIs Against a Real Database

A handler test with a fake store tells you that your handler works with your idea of a store. It cannot tell you whether the SQL parses, whether the unique index exists, whether two requests racing to insert the same email end up as one row and one 409, or whether the migration you shipped last month matches what the queries expect. These are the failures that reach production, and the only test that sees them is one that talks to the real database.

This article builds an integration test setup for a Go API with PostgreSQL: isolated per test, parallel, fast enough to run on every commit, and honest about what it covers. It complements table-driven handler tests, which stay as the fast layer, and leads into Testcontainers and testing against a real Postgres, which cover how the database gets started. The lab starts a throwaway PostgreSQL 18 container itself. It ran on Go 1.25.6.

What integration tests are for

Decide up front which layer answers which question, or you will either test everything slowly or nothing realistically.

The integration layer should be thinner than the unit layer. A good split is dozens of handler tests and a few tens of integration tests aimed at the places where the database is the source of truth.

The application under test

A small users API with a Store that talks to PostgreSQL through pgx, and two routes. The part to look at is how a duplicate email is handled.

package main

import (
	"context"
	"encoding/json"
	"errors"
	"net/http"

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

const migration = `
CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);`

type User struct {
	ID    int64  `json:"id"`
	Email string `json:"email"`
	Name  string `json:"name"`
}

var ErrDuplicate = errors.New("email already registered")

type Store struct{ pool *pgxpool.Pool }

func (s *Store) Create(ctx context.Context, email, name string) (User, error) {
	u := User{Email: email, Name: name}
	err := s.pool.QueryRow(ctx, `INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id`, email, name).Scan(&u.ID)
	var pg *pgconn.PgError
	if errors.As(err, &pg) && pg.Code == "23505" { // unique_violation: the database is the arbiter, not a prior SELECT
		return User{}, ErrDuplicate
	}
	return u, err
}

func (s *Store) List(ctx context.Context) ([]User, error) {
	rows, err := s.pool.Query(ctx, `SELECT id, email, name FROM users ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []User{}
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.ID, &u.Email, &u.Name); err != nil {
			return nil, err
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

func NewHandler(s *Store) http.Handler {
	mux := http.NewServeMux()
	reply := func(w http.ResponseWriter, status int, v any) {
		w.Header().Set("Content-Type", "application/json")
		w.WriteHeader(status)
		json.NewEncoder(w).Encode(v)
	}
	mux.HandleFunc("POST /users", func(w http.ResponseWriter, r *http.Request) {
		var in struct{ Email, Name string }
		if json.NewDecoder(r.Body).Decode(&in) != nil || in.Email == "" || in.Name == "" {
			reply(w, http.StatusUnprocessableEntity, map[string]string{"error": "email and name are required"})
			return
		}
		u, err := s.Create(r.Context(), in.Email, in.Name)
		switch {
		case errors.Is(err, ErrDuplicate):
			reply(w, http.StatusConflict, map[string]string{"error": err.Error()})
		case err != nil:
			reply(w, http.StatusInternalServerError, map[string]string{"error": "internal error"})
		default:
			reply(w, http.StatusCreated, u)
		}
	})
	mux.HandleFunc("GET /users", func(w http.ResponseWriter, r *http.Request) {
		us, err := s.List(r.Context())
		if err != nil {
			reply(w, http.StatusInternalServerError, map[string]string{"error": "internal error"})
			return
		}
		reply(w, http.StatusOK, us)
	})
	return mux
}

func main() {}

Create does not check whether the email exists before inserting. It inserts and examines the error: PostgreSQL's code 23505 is a unique violation, and the store turns it into ErrDuplicate, which the handler maps to 409. A SELECT followed by an INSERT would leave a window in which two requests both see no row and both insert; the constraint closes the window, and the code reports what the database decided. This is exactly the type of behaviour a mock would hide, because a mock returns whatever it was told to.

One schema per test

Isolation is the core design question. The common options:

• Transaction per test, rolled back at the end. Fast and tidy, but the code under test cannot use its own transactions without savepoints, and anything that commits (or runs on another connection) escapes. A poor fit for a service that handles concurrency.
• Truncate tables between tests. Simple, but tests cannot run in parallel against the same tables.
• A database per test. Fully isolated, but creating a database from a template costs tens of milliseconds and a lot of server resources when parallel.
• A schema per test. Each test gets it_<pid>_<n>, a pool whose connections have that schema as their search_path, and the real migration applied inside it. Creation takes a few milliseconds. Tests run in parallel and cannot see each other's rows, and the application code needs no changes because unqualified table names resolve through the search path.

The helper implements the last one:

package main

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

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

var schemaCounter atomic.Int64

// newStore gives the test its own schema inside a shared database: isolated, real, and cheap to create.
func newStore(t *testing.T) *Store {
	t.Helper()
	dsn := os.Getenv("DATABASE_URL")
	if dsn == "" {
		t.Skip("DATABASE_URL not set; skipping integration test")
	}
	ctx := context.Background()
	schema := fmt.Sprintf("it_%d_%d", os.Getpid(), schemaCounter.Add(1))

	admin, err := pgxpool.New(ctx, dsn)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := admin.Exec(ctx, "CREATE SCHEMA "+schema); err != nil {
		t.Fatal(err)
	}

	cfg, _ := pgxpool.ParseConfig(dsn)
	cfg.ConnConfig.RuntimeParams["search_path"] = schema // every connection of this pool sees only this schema
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil {
		t.Fatal(err)
	}
	if _, err := pool.Exec(ctx, migration); err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() {
		pool.Close()
		admin.Exec(ctx, "DROP SCHEMA "+schema+" CASCADE")
		admin.Close()
	})
	return &Store{pool}
}

Notes on the helper. It reads DATABASE_URL and calls t.Skip if it is missing, so go test ./... on a laptop without a database passes cleanly instead of failing noisily. The schema name includes the process id and a counter so concurrent test binaries (different packages run in parallel by go test) cannot collide. It sets search_path as a runtime parameter on the pool config, which applies to every connection the pool opens, and that is why this works with pools and not just with a single connection. t.Cleanup drops the schema with CASCADE even when the test fails, so leftovers do not accumulate.

It applies migration, the same SQL the application ships. In a real project run your actual migration tool against the new schema, and make sure it respects the search path. If you keep migrations in files, the test setup and the deploy should read the same ones, or the tests prove nothing about production.

The tests

package main

import (
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
	"strings"
	"sync"
	"testing"
)

func post(t *testing.T, h http.Handler, body string) *httptest.ResponseRecorder {
	t.Helper()
	rec := httptest.NewRecorder()
	h.ServeHTTP(rec, httptest.NewRequest("POST", "/users", strings.NewReader(body)))
	return rec
}

func TestCreateAndList(t *testing.T) {
	t.Parallel()
	h := NewHandler(newStore(t))
	if rec := post(t, h, `{"email":"[email protected]","name":"Ada"}`); rec.Code != 201 {
		t.Fatalf("create = %d %s", rec.Code, rec.Body)
	}
	post(t, h, `{"email":"[email protected]","name":"Bob"}`)

	rec := httptest.NewRecorder()
	h.ServeHTTP(rec, httptest.NewRequest("GET", "/users", nil))
	var got []User
	json.Unmarshal(rec.Body.Bytes(), &got)
	if len(got) != 2 || got[0].Email != "[email protected]" || got[1].ID <= got[0].ID {
		t.Fatalf("list = %+v", got)
	}
}

func TestDuplicateEmailIsConflict(t *testing.T) {
	t.Parallel()
	h := NewHandler(newStore(t))
	post(t, h, `{"email":"[email protected]","name":"Ada"}`)
	rec := post(t, h, `{"email":"[email protected]","name":"Impostor"}`)
	if rec.Code != 409 {
		t.Fatalf("duplicate = %d %s, want 409", rec.Code, rec.Body)
	}
}

// Only a real database can answer this: do concurrent duplicate inserts both succeed?
func TestConcurrentDuplicatesCreateOneUser(t *testing.T) {
	t.Parallel()
	h := NewHandler(newStore(t))
	var wg sync.WaitGroup
	codes := make(chan int, 20)
	for i := 0; i < 20; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			codes <- post(t, h, `{"email":"[email protected]","name":"Racer"}`).Code
		}()
	}
	wg.Wait()
	close(codes)
	created, conflicts := 0, 0
	for c := range codes {
		switch c {
		case 201:
			created++
		case 409:
			conflicts++
		}
	}
	fmt.Printf("    concurrent inserts: %d created, %d conflicts\n", created, conflicts)
	if created != 1 || conflicts != 19 {
		t.Fatalf("created=%d conflicts=%d", created, conflicts)
	}
}

func TestSchemasAreIsolated(t *testing.T) {
	t.Parallel()
	a, b := newStore(t), newStore(t)
	post(t, NewHandler(a), `{"email":"[email protected]","name":"A"}`)
	users, _ := b.List(t.Context())
	if len(users) != 0 {
		t.Fatalf("schema b sees %d users from schema a", len(users))
	}
}

func TestValidation(t *testing.T) {
	t.Parallel()
	h := NewHandler(newStore(t))
	for _, body := range []string{`{}`, `{"email":"[email protected]"}`, `not json`} {
		if rec := post(t, h, body); rec.Code != 422 {
			t.Errorf("%q = %d, want 422", body, rec.Code)
		}
	}
}

Each test calls t.Parallel() and builds its own store. The five tests:

1. Create and list checks that data written through the API reads back, in order, with increasing ids.
2. Duplicate email expects 409 on the second insert.
3. Concurrent duplicates fires twenty requests at once with the same email.
4. Schema isolation verifies that two helpers produce stores that do not see each other's data. It is a test of the test setup, and it is worth having: the day someone breaks the isolation, many unrelated tests would start failing randomly.
5. Validation hits the handler with bad bodies, which never reach the database. It is cheap to keep here to confirm the wiring.

Running it

docker run -d --rm --name pg-it-lab -e POSTGRES_PASSWORD=secret -p 55433:5432 postgres:18 >/dev/null
for i in $(seq 1 60); do docker exec pg-it-lab pg_isready -U postgres >/dev/null 2>&1 && break; sleep 1; done
sleep 2
DATABASE_URL='postgres://postgres:secret@localhost:55433/postgres' go test -race -count=1 -v . | grep -E '^(=== RUN|--- |PASS|FAIL|ok|    concurrent)' | sed -E 's/ \([0-9.]+s\)//' | grep -v '=== RUN'
echo "--- and without a database:"
go test -count=1 -v . | grep -E '^(--- |ok)' | sed -E 's/ \([0-9.]+s\)//'
docker stop pg-it-lab >/dev/null

--- PASS: TestValidation
--- PASS: TestDuplicateEmailIsConflict
--- PASS: TestCreateAndList
    concurrent inserts: 1 created, 19 conflicts
--- PASS: TestConcurrentDuplicatesCreateOneUser
--- PASS: TestSchemasAreIsolated
PASS
ok  	example.test/lab	1.496s
--- and without a database:
--- SKIP: TestSchemasAreIsolated
--- SKIP: TestConcurrentDuplicatesCreateOneUser
--- SKIP: TestCreateAndList
--- SKIP: TestValidation
--- SKIP: TestDuplicateEmailIsConflict
ok  	example.test/lab	0.319s

The first block shows the tests passing under -race, and a line printed from the concurrency test: 1 created, 19 conflicts. Twenty goroutines raced to insert the same email, and the unique index let exactly one through. A mock store cannot make that claim, and a check-then-insert implementation would likely have shown two or three created on a fast machine.

The second block runs the same tests without DATABASE_URL, and every test reports SKIP with an ok overall. That is the behaviour you want for contributors who only run unit tests, with CI configured to always set the variable.

Getting a database to test against

The test code only needs a URL. How you provide one depends on where the tests run:

• Locally: docker compose up -d db from the project, or the container started in the lab's script. Document the one command in the README.
• In CI: use the CI system's service containers (a postgres service in GitHub Actions or GitLab CI), exposed on a known port and waited on for readiness.
• From the tests themselves: Testcontainers for Go starts a container per test run and gives you the URL. It removes the manual step at the cost of requiring Docker access from the test process. See the Testcontainers article.

Pin the same major version of PostgreSQL that you run in production. A test passing on 18 and a production on 14 hides differences in planner behaviour, error messages and available features.

Making these tests fast

The five tests above finished in about a second including race detector overhead. The costs to keep an eye on:

Starting the database. Do it once per test run, not per package or per test. A TestMain that starts or connects can share one server across tests in a package, and CI can share one across packages.

Creating schemas and running migrations. A few milliseconds each. If a migration set grows to hundreds of statements, create a template schema once and copy its structure per test, or use CREATE DATABASE ... TEMPLATE for a template database.

Pools. Each test opens a pool. Set MaxConns low in test configs (2 to 4) so parallel tests do not exhaust the server's max_connections.

Seed data. Create only what the test reads. A shared fixture of a thousand rows is slow and couples tests to each other.

Testing transaction behaviour

Transactions are the second thing a mock cannot check. Suppose creating a user also writes an audit row, and the audit insert fails halfway. The question is whether the user row exists afterwards. A test for it needs a way to make the second statement fail, and a real database gives you several: a constraint on the audit table, a CHECK that rejects a value, or a trigger that raises an error.

The shape of such a test is always the same: arrange the failure, call the API, assert on the state of every table, not just on the response. A 500 is the easy half. The valuable assertion is that SELECT count(*) FROM users is zero, because that proves the transaction rolled back and nothing half-written is visible. If the code under test uses sql.Tx or pgx.Tx, test the commit path and the rollback path both, and test what happens when the context is cancelled in the middle, as described in cancelling database queries.

Isolation level is the next step: two goroutines that read-then-write the same row under READ COMMITTED can lose an update, and under SERIALIZABLE one of them gets a serialisation failure that the code must retry. These behaviours are the database's, and a test is the only way to confirm that your code handles them.

Running the cases as a table

The five tests above are separate functions because each one tells a different story. When a family of cases differs only by input and expected status, use the table style from the handler tests, with the schema created once per row. Keep the row struct small and the setup in a helper:

for _, tc := range cases {
	t.Run(tc.name, func(t *testing.T) {
		t.Parallel()
		h := NewHandler(newStore(t))
		for _, pre := range tc.existing {
			post(t, h, pre)
		}
		rec := post(t, h, tc.body)
		if rec.Code != tc.want {
			t.Fatalf("%s = %d %s, want %d", tc.name, rec.Code, rec.Body, tc.want)
		}
	})
}

The existing field is the setup for each row: requests that put the database in the state the case needs. Going through the API for setup is slower than inserting rows directly, and it avoids duplicating the application's knowledge of what a valid row looks like. When setup through the API becomes expensive, add a small test-only function that inserts through the store.

Wiring it into CI

CI needs three things: a database, the URL in an environment variable, and a readiness wait. In GitHub Actions, a service container does all three:

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:18
        env:
          POSTGRES_PASSWORD: secret
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 2s --health-timeout 3s --health-retries 20
    env:
      DATABASE_URL: postgres://postgres:secret@localhost:5432/postgres
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-go@v5
        with:
          go-version-file: go.mod
      - run: go test -race -count=1 ./...

The health options make the job wait until the server accepts connections, so the first test does not race the database start-up. Add a guard to the pipeline that fails if integration tests were skipped in CI: for example, a final step that greps the verbose output for SKIP in a package that is supposed to have a database, or a test-level check like if os.Getenv("CI") != "" && dsn == "" { t.Fatal(...) } in the helper. Skips that nobody notices are how an integration suite quietly stops running.

When a test fails

An integration failure has more suspects than a unit failure: the code, the migration, the database version, leftover state, the network. Two habits shorten the search. First, make the helper print the schema name in failure output, so you can inspect a failed test's data by keeping the schema (an environment variable like KEEP_SCHEMAS=1 that disables the cleanup). Second, turn on statement logging for the test server when you need it: log_statement = 'all' shows exactly what the application sent. Most "mysterious" failures turn out to be a query that does something other than intended, and the log shows it in seconds.

Pitfalls

Time. Tests that depend on now() are flaky around boundaries. Pass the time in from the test, or use a clock interface, as in the injected clock article.

Sequences and ordering. Do not assert on specific ids unless you control the sequence. Assert on relationships: the second id is greater than the first.

Leaking containers and schemas. If a test run is killed, cleanup does not run. Name resources with a recognisable prefix and sweep them occasionally, and use --rm on containers so they vanish when stopped.

Cross-test coupling through the pool. If you reuse a pool between tests and one test changes a session setting, the next inherits it. Per-test pools, as here, make that impossible.

Testing the driver instead of your code. Do not write integration tests whose only content is that pgx can insert a row. Aim at your queries, your constraints and your transaction boundaries.

Treating a green run as proof of correctness under load. These tests check behaviour, not capacity. For throughput, see load testing an API.

Beyond PostgreSQL

The pattern generalises. For MySQL, a schema is a database, so you create a database per test. For SQLite, a fresh in-memory database per test is trivial but differs from production in locking, types and SQL dialect; SQLite in memory covers when that trade is acceptable. For Redis, a key prefix per test or SELECT of a numbered database works. In all cases the rule is the same: one set of data per test, created by real code, destroyed by cleanup.

The PostgreSQL documentation on schemas and the search path explains the mechanism the helper relies on, and the standard library's testing package documentation covers t.Parallel, t.Cleanup and t.Skip.