postgres

Overview ¶

Package postgres gives an integration test a real, migrated, empty PostgreSQL database of its own.

Design ¶

One Postgres container is started per test binary (per image) with testcontainers-go and shared by every test in it. Migrations are applied once, into a template database; each test then gets its own database made with CREATE DATABASE ... TEMPLATE .... Copying a template is a file copy inside Postgres rather than a replay of the migration history, so the cost of setting up a test stays constant as the number of migrations grows.

Every database gets a unique name, so tests may call t.Parallel freely. The database is dropped WITH (FORCE) when the test ends, even if the test leaked connections.

Usage ¶

func TestMain(m *testing.M) {
	code := m.Run()
	_ = postgres.Cleanup() // optional: stop the container right away
	os.Exit(code)
}

func TestSomething(t *testing.T) {
	t.Parallel()

	db := postgres.New(t, postgres.WithMigrationsDir("../../migrations"))

	// db.DB is a *sqlx.DB and db.SQL a *sql.DB on an empty, fully
	// migrated database. db.URL is its connection string, for handing
	// to a subprocess or to a different driver.
	var n int
	if err := db.DB.Get(&n, "SELECT count(*) FROM users"); err != nil {
		t.Fatal(err)
	}
}

The migrations directory is required. A relative path is resolved against the working directory of the test binary, which `go test` sets to the directory of the package under test.

Migration bookkeeping table ¶

sql-migrate records applied migrations in a table. Its Go API defaults to "gorp_migrations", while its CLI reads the table name from dbconfig.yml. If the two disagree, the tests and the CLI keep separate bookkeeping and disagree about what has been applied. New therefore defaults to "migrations", the name most dbconfig.yml files use; set WithMigrationsTable to whatever the `table:` key of your dbconfig.yml says. The table is configured per migration run, so sql-migrate's global migrate.SetTable setting is neither needed nor modified.

Driver ¶

The package registers and uses github.com/lib/pq (driver name "postgres"). URL is a standard postgres:// URL, so code that prefers pgx can open it with sql.Open("pgx", db.URL) after importing github.com/jackc/pgx/v5/stdlib.