Test Database Migrations in CI for Go Projects Reliably

Migrations are code that runs once, in production, against data you cannot afford to lose. They are also the code least likely to have a test, because they look like configuration: a folder of SQL. The failures are predictable: a file that does not apply on an empty database because it relies on a table that was created by hand, a Down that does not undo its Up, two branches that each add migration 12. All of them can be found in CI in a couple of seconds. This article builds the tests with goose v3.26.0 against PostgreSQL 18.6 on Go 1.25.6, and checks that the checks work.

It belongs to the database cornerstone. The tools are compared in the migration tools roundup, safe statements in zero downtime migrations, and the testing style in testing against PostgreSQL.

What to test

Four properties are worth a test each, in order of value:

1. Every migration applies, in order, from an empty database. This catches files that depend on state that exists only in somebody's database, typos, and ordering mistakes.
2. Up, down to zero, up gives the same schema. This proves Down undoes Up, if you support rollbacks at all.
3. The test can fail. A check that has never been seen to fail is a check you cannot trust, so include a deliberately broken case.
4. The result matches what the application expects. Optional: compare the migrated schema with the one your queries need, or run the repository tests on top of it.

A fifth, running the latest migrations against a copy of production data to see how long they take and whether they hold locks, is a staging concern covered in the zero downtime article.

The migrations and the tests

The migrations are three small goose files: a users table, a plan column with an index, and an orders table with a foreign key and a check constraint.

-- +goose Up
create table users654 (id bigserial primary key, email text not null unique);

-- +goose Down
drop table users654;

-- +goose Up
alter table users654 add column plan text not null default 'free';
create index users654_plan on users654 (plan);

-- +goose Down
drop index users654_plan;
alter table users654 drop column plan;

-- +goose Up
create table orders654 (id bigserial primary key, user_id bigint not null references users654, total int not null check (total >= 0));

-- +goose Down
drop table orders654;

They are embedded in the test binary with //go:embed, the same way the service embeds them, so the test applies exactly the files that will ship.

package main

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"embed"
	"fmt"
	"io/fs"
	"testing"
	"testing/fstest"

	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var migrations embed.FS

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

// newDB gives each test its own empty database, so tests do not see each other's tables.
func newDB(t *testing.T, tag string) *sql.DB {
	t.Helper()
	root, err := sql.Open("pgx", admin)
	if err != nil {
		t.Fatal(err)
	}
	defer root.Close()
	name := fmt.Sprintf("mig654_%x", sha256.Sum256([]byte(t.Name()+tag)))[:16]
	if _, err := root.Exec("drop database if exists " + name); err != nil {
		t.Fatal(err)
	}
	if _, err := root.Exec("create database " + name); err != nil {
		t.Fatal(err)
	}
	db, err := sql.Open("pgx", fmt.Sprintf("postgres://postgres:[email protected]:55432/%s?sslmode=disable", name))
	if err != nil {
		t.Fatal(err)
	}
	t.Cleanup(func() {
		db.Close()
		r, _ := sql.Open("pgx", admin)
		defer r.Close()
		r.Exec("drop database if exists " + name + " with (force)")
	})
	return db
}

// fingerprint is a hash of the shape of the schema: tables, columns, types, defaults, constraints and indexes.
func fingerprint(t *testing.T, db *sql.DB) string {
	t.Helper()
	h := sha256.New()
	for _, q := range []string{
		"select table_name, column_name, data_type, is_nullable, coalesce(column_default, '') from information_schema.columns where table_schema = 'public' and table_name not like 'goose%' order by 1, 2",
		"select conrelid::regclass::text, conname, pg_get_constraintdef(oid) from pg_constraint where connamespace = 'public'::regnamespace order by 1, 2",
		"select tablename, indexname, indexdef from pg_indexes where schemaname = 'public' and tablename not like 'goose%' order by 1, 2",
	} {
		rows, err := db.Query(q)
		if err != nil {
			t.Fatal(err)
		}
		cols, _ := rows.Columns()
		for rows.Next() {
			vals := make([]string, len(cols))
			ptrs := make([]any, len(cols))
			for i := range vals {
				ptrs[i] = &vals[i]
			}
			if err := rows.Scan(ptrs...); err != nil {
				t.Fatal(err)
			}
			fmt.Fprintln(h, vals)
		}
		rows.Close()
	}
	return fmt.Sprintf("%x", h.Sum(nil))[:12]
}

func provider(t *testing.T, db *sql.DB, fsys fs.FS) *goose.Provider {
	t.Helper()
	p, err := goose.NewProvider(goose.DialectPostgres, db, fsys)
	if err != nil {
		t.Fatal(err)
	}
	return p
}

func sub(t *testing.T) fs.FS {
	s, err := fs.Sub(migrations, "migrations")
	if err != nil {
		t.Fatal(err)
	}
	return s
}

// Every migration applies, from nothing, in order.
func TestUpFromEmpty(t *testing.T) {
	db := newDB(t, "")
	res, err := provider(t, db, sub(t)).Up(context.Background())
	if err != nil {
		t.Fatal(err)
	}
	t.Logf("applied %d migrations, schema fingerprint %s", len(res), fingerprint(t, db))
}

// Down undoes exactly what Up did: up, down to zero, and the schema is empty again; up once more and it matches the first time.
func TestUpDownUp(t *testing.T) {
	ctx := context.Background()
	db := newDB(t, "")
	p := provider(t, db, sub(t))
	if _, err := p.Up(ctx); err != nil {
		t.Fatal(err)
	}
	first := fingerprint(t, db)
	if _, err := p.DownTo(ctx, 0); err != nil {
		t.Fatal(err)
	}
	empty := fingerprint(t, db)
	if _, err := p.Up(ctx); err != nil {
		t.Fatal(err)
	}
	second := fingerprint(t, db)
	t.Logf("after up %s, after down to zero %s, after up again %s", first, empty, second)
	if first != second {
		t.Fatalf("the schema after up, down and up differs: %s then %s", first, second)
	}
}

// The check itself must be able to fail: a migration whose Down forgets to drop its index leaves the second schema different.
func TestBrokenDownIsDetected(t *testing.T) {
	ctx := context.Background()
	db := newDB(t, "")
	broken := fstest.MapFS{
		"00001_t.sql": {Data: []byte("-- +goose Up\ncreate table t654 (id int primary key, n int);\ncreate index t654_n on t654 (n);\n-- +goose Down\ndrop table t654;\n")},
		"00002_u.sql": {Data: []byte("-- +goose Up\ncreate table u654 (id int);\n-- +goose Down\nselect 1;\n")}, // forgets to drop u654
	}
	p := provider(t, db, broken)
	if _, err := p.Up(ctx); err != nil {
		t.Fatal(err)
	}
	if _, err := p.DownTo(ctx, 0); err != nil {
		t.Fatal(err)
	}
	if got := fingerprint(t, db); got == fingerprint(t, newDB(t, "empty")) {
		t.Fatal("the leftover table was not noticed")
	}
	t.Log("the leftover table u654 shows up in the fingerprint, so the up, down and up test would fail for this migration")
}

=== RUN   TestUpFromEmpty
    migrations_test.go:104: applied 3 migrations, schema fingerprint f56a74df9ea5
--- PASS: TestUpFromEmpty (0.15s)
=== RUN   TestUpDownUp
    migrations_test.go:124: after up f56a74df9ea5, after down to zero 6d71287d9db2, after up again f56a74df9ea5
--- PASS: TestUpDownUp (0.12s)
=== RUN   TestBrokenDownIsDetected
    migrations_test.go:148: the leftover table u654 shows up in the fingerprint, so the up, down and up test would fail for this migration
--- PASS: TestBrokenDownIsDetected (0.23s)
PASS
ok  	example.test/lab	1.842s

A database per test

newDB creates a database with a name derived from a hash of the test name, drops any leftover with that name, and returns a connection to it. A t.Cleanup drops it afterwards with with (force), which disconnects stragglers. Each test sees an empty database, nothing is shared, and tests can call t.Parallel(). Creating a database costs a fraction of a second; for a large suite, create a template database once with the migrations applied and clone it with create database ... template, which is much faster for tests that only need the final schema.

Use a role that may create databases and point it at a throwaway PostgreSQL, a service container in CI. Never run this against a shared database, because the tests drop databases.

The fingerprint

Comparing schemas by eye does not scale, so fingerprint reduces the schema to a short hash. It queries information_schema.columns for each table, column, type, nullability and default, pg_constraint for constraint definitions, and pg_indexes for index definitions, in a fixed order, and hashes the rows. It leaves out the migration tool's own table. Two databases with the same fingerprint have the same tables, columns, constraints and indexes. If you add enums, views, functions or triggers, add queries for them, or compare the output of pg_dump --schema-only.

The three tests

TestUpFromEmpty applies all migrations with a goose Provider and logs the result: three migrations applied, fingerprint f56a74df9ea5. If any file fails, the test fails with the error from the database, usually pointing at the line.

TestUpDownUp applies everything, records the fingerprint, runs DownTo(ctx, 0) to roll back every migration, records the fingerprint of the now empty schema, applies again and compares. The log shows after up f56a74df9ea5, after down to zero 6d71287d9db2, after up again f56a74df9ea5. The first and the last are equal, which is the property. The middle one is the fingerprint of a database with no application tables, and that it differs from the others shows the fingerprint is sensitive to the schema.

TestBrokenDownIsDetected is the check on the check. It uses an in-memory file system with two migrations, the second of which has a Down that does nothing (select 1) and so leaves its table behind. After applying and rolling back, the fingerprint is compared to that of a brand new empty database, and they differ, because the leftover table is visible. The test passes when the leftover is noticed, and logs that the up, down and up test would have failed for this set. Without a case like this, a fingerprint function that always returned the same string would pass every test.

All three ran in under two seconds in total.

Making it a CI job

The pieces in a pipeline:

1. A PostgreSQL service of the same major version as production, started before the tests. Pin the version: a migration that works on 16 can behave differently on 18.
2. The test command, go test ./migrations/..., with the connection string in an environment variable.
3. A check for duplicate or out of order versions. Two branches that both add 00004_ produce two files with the same number. goose reports it as an error when it loads the folder, and the first test fails. For timestamp named files, the equivalent is a check that the new file's timestamp is later than the newest on the main branch.
4. A check that no applied file was edited. The runners do not checksum, so compare against the main branch in CI: fail if a file that exists on main has changed (git diff --name-status origin/main and reject M on files under migrations/). Atlas does this with atlas.sum; see Atlas migrations.
5. A lint step for dangerous statements, such as create index without concurrently on large tables, alter column type, or drop column. A simple grep that requires an explicit marker comment to allow them makes the author think.

Going further

• Test with data. Insert rows before the last migration, apply it, and assert on the result. Migrations that backfill or convert columns are where data bugs appear, and an empty database cannot show them. Keep the fixture small and in the test.
• Compare with the application's expectations. Run the repository tests against the migrated schema in the same job, so a migration that renames a column breaks the build that needs it.
• Test a rollback only if you will use it. Many teams roll forward only and keep Down for development. If you do, the up, down, up test is the guard; if you do not, drop Down from the files and the test, and say so in the repository.
• Record how long migrations take on a realistic copy, and fail the pipeline above a budget, as suggested in zero downtime migrations.

When the tests are not enough

The tests prove a migration applies and reverses on an empty or small database. They do not prove it is safe on a big, busy table: that needs the lock and duration checks from the zero downtime article, run on production sized data. And they do not prove the migration is right for your data: a conversion that parses a text column into dates will pass on three clean rows and fail on the dirty one in production, so run it against a copy of real data before the release. The goose Provider and its options are documented on pkg.go.dev, and the catalog queries used in the fingerprint in the PostgreSQL manual's pages on information_schema and system catalogs.