How to Write Go Integration Tests Against PostgreSQL

Go database code is where mocks stop helping. The bugs that reach production are a unique constraint the code did not expect, an ON CONFLICT clause that updates the wrong column, a migration that fails on existing data. A mocked *sql.DB passes all of those, because it returns whatever the test told it to.

Testing against a real Postgres catches them, and the work is in making that fast and isolated: one server per run, migrations applied once, and tests that do not see each other's rows. This guide compares the four common setups, starting with the one where all three are built in.

Go Postgres testing options at a glance

encore test: Postgres for every test run

Encore combines an open source infrastructure SDK for TypeScript and Go with a platform that provisions and deploys into your own AWS or GCP account. A database is declared in the service package with its migrations:

-- users/migrations/1_create_users.up.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

// users/users.go
package users

import (
	"context"

	"encore.dev/storage/sqldb"
)

var db = sqldb.NewDatabase("users", sqldb.DatabaseConfig{Migrations: "./migrations"})

type SignupParams struct {
	Email string
}

type User struct {
	ID int64
}

// Signup creates a user, or returns the existing one for the same email.
//
//encore:api public method=POST path=/signup
func Signup(ctx context.Context, p *SignupParams) (*User, error) {
	u := &User{}
	err := db.QueryRow(ctx, `
		INSERT INTO users (email) VALUES ($1)
		ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
		RETURNING id`, p.Email).Scan(&u.ID)
	return u, err
}

// countUsers takes the database as a parameter so tests can pass their own.
func countUsers(ctx context.Context, q *sqldb.Database) (int, error) {
	var n int
	err := q.QueryRow(ctx, `SELECT count(*) FROM users`).Scan(&n)
	return n, err
}

The tests are plain Go tests. One calls the endpoint against the shared test database, the other asks for its own database:

// users/users_test.go
package users

import (
	"context"
	"testing"

	"encore.dev/et"
)

// Runs against the shared test database, so it uses an email no other test uses.
func TestSignupIsIdempotent(t *testing.T) {
	t.Parallel()
	ctx := context.Background()
	email := t.Name() + "@example.com"

	first, err := Signup(ctx, &SignupParams{Email: email})
	if err != nil {
		t.Fatal(err)
	}
	second, err := Signup(ctx, &SignupParams{Email: email})
	if err != nil {
		t.Fatal(err)
	}
	if first.ID != second.ID {
		t.Fatalf("signup twice created two users: %d and %d", first.ID, second.ID)
	}
}

// Gets its own fully migrated, empty database, so the count is exact.
func TestCountUsers(t *testing.T) {
	ctx := context.Background()
	tdb, err := et.NewTestDatabase(ctx, "users")
	if err != nil {
		t.Fatal(err)
	}
	for _, email := range []string{"a@example.com", "b@example.com"} {
		if _, err := tdb.Exec(ctx, `INSERT INTO users (email) VALUES ($1)`, email); err != nil {
			t.Fatal(err)
		}
	}

	n, err := countUsers(ctx, tdb)
	if err != nil {
		t.Fatal(err)
	}
	if n != 2 {
		t.Fatalf("got %d users, want 2", n)
	}
}

Run them with encore test ./..., which takes the same flags as go test. Per the testing docs, Encore sets up the databases in a separate test cluster configured to skip fsync and use an in-memory filesystem, runs the migrations into a template database, and clones it whenever a test calls et.NewTestDatabase. There is no container code in the test, no connection string and no cleanup.

The trade-off between the two tests is deliberate: the default shared database is fast, but tests must not depend on each other's rows, which is why TestSignupIsIdempotent uses an email derived from t.Name(). A test that asserts an exact count needs an empty database, so TestCountUsers takes a clone, and countUsers accepts the database as a parameter to make that possible. For setup that needs more privileges, such as TRUNCATE, et.SQLDBWithSuperuser returns a superuser connection.

Traces for every test request show up in the local development dashboard at localhost:9400, so a failing test shows the exact queries it ran. In CI, the same encore test command runs with Docker available.

Option 1: Testcontainers for Go

Testcontainers for Go starts a Postgres container from test code, typically once in TestMain:

pg, err := postgres.Run(ctx, "postgres:18",
	postgres.WithDatabase("users"),
	postgres.WithUsername("test"),
	postgres.WithPassword("test"),
	postgres.BasicWaitStrategies(),
)
if err != nil {
	log.Fatal(err)
}
defer testcontainers.TerminateContainer(pg)
dsn, err := pg.ConnectionString(ctx, "sslmode=disable")

It works with any Go code and any migration tool, and the module supports snapshots, so you can migrate once and restore the snapshot between tests. You write and maintain the setup, the migration step and the connection wiring yourself, and starting a container adds a few seconds per package that uses it.

Option 2: template databases with pgtestdb

pgtestdb uses Postgres template databases to give each test its own migrated database. Migrations run once, and each test then gets a clone in about 20 ms according to the project. It has connectors for goose, golang-migrate, Atlas and other migration tools.

You bring the Postgres server, usually from Compose or a CI service container, and the database is passed into your code as a *sql.DB. It is the same isolation model as et.NewTestDatabase, as a library for code that is not on Encore.

Option 3: a shared Compose database

The simplest setup: a postgres service in compose.yaml, go test with a connection string from the environment, and a helper that truncates tables between tests. It needs no libraries, and it is also where flaky tests come from: t.Parallel() tests that share tables, truncation that misses a new table, and a local database whose schema drifted from the migrations.

Good to know

• Disable durability on any test server you run yourself (fsync=off, synchronous_commit=off, full_page_writes=off). It is safe for throwaway data and makes write-heavy tests much faster.
• Wrapping each test in a transaction that is rolled back is fast, but it hides bugs in code that manages its own transactions and cannot test behavior across connections.
• Test the migrations themselves. A test run that applies every migration from scratch catches a broken migration before deploy does.

How to choose

Use encore test when your services and databases are on Encore: the database, migrations and isolation come with the declaration, and et.NewTestDatabase gives a test an empty migrated database in one line. Use pgtestdb for the same isolation in an existing Go codebase with your own Postgres server, Testcontainers when tests should start their own Postgres without anything running beforehand, and a shared Compose database only for small suites that do not run in parallel.

Related guides

• How to add a database to a Go API
• Go ORMs
• The best Go backend frameworks
• Microservices in Go