Database

Testing Goose Database Migrations in Go: A Practical Guide

HelpMeTest

17 May 2026 — 11 min read

Goose is the most widely-used database migration tool in the Go ecosystem, but its simplicity makes it easy to ship migrations without testing them. This guide covers a complete testing strategy using testcontainers-go, Go's standard testing package, up/down migration verification, and SQL assertion patterns that integrate naturally with existing Go test suites.

Key Takeaways

Test migrations as part of your Go test suite, not separately. Goose's Go API makes it straightforward to apply and roll back migrations inside standard Go tests. There's no reason to maintain a separate migration test harness.

testcontainers-go gives you a real database in every test run. The container starts in under 10 seconds and gives you a fresh PostgreSQL (or MySQL) instance with no state bleed between tests. It's the right tool for migration testing in Go.

Test the down migration for every file. The goose.Down() function is your rollback mechanism. If it panics or errors, you have no recovery path after a failed production deployment.

Why Goose Migrations Need Their Own Tests

Goose is appealingly simple. Drop a .sql file in a directory, run goose up, and your database schema is updated. That simplicity makes it easy to skip testing entirely — and easy to ship migrations that fail in production.

The most common Goose failure modes in production:

Down migration is missing or broken. Goose requires a -- +goose Down section in every SQL migration. If it's empty or incorrect, goose down is a no-op. You discover this when you need to roll back a failed deployment.

Migration assumes empty tables. An ALTER TABLE ADD COLUMN NOT NULL works on an empty development database and fails with a constraint violation on the production table with 10 million rows.

Naming collision on merge. Two branches create 00012_add_index.sql and 00012_create_audit_log.sql. Goose applies them in filesystem order, which varies by OS. The behavior is non-deterministic.

Go migration file has a runtime error. Goose supports Go migration files (.go) with Up and Down functions. A nil pointer dereference or unchecked error in a Go migration crashes the migration run and may leave the database in a half-applied state.

A test suite that runs before every production deployment catches all of these.

Goose Migration File Structure

Goose SQL migrations use embedded directive comments to separate the up and down sections:

-- 00001_create_users.sql
-- +goose Up
CREATE TABLE users (
    id          BIGSERIAL       PRIMARY KEY,
    email       VARCHAR(255)    NOT NULL,
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

-- +goose Down
DROP INDEX IF EXISTS idx_users_email;
DROP TABLE IF EXISTS users;

-- 00002_add_subscription_tier.sql
-- +goose Up
ALTER TABLE users
    ADD COLUMN subscription_tier VARCHAR(20) NOT NULL DEFAULT 'free';

CREATE INDEX idx_users_tier ON users(subscription_tier);

-- +goose Down
DROP INDEX IF EXISTS idx_users_tier;
ALTER TABLE users DROP COLUMN IF EXISTS subscription_tier;

Goose Go migration files have explicit function signatures:

// 00003_backfill_display_names.go
package migrations

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/pressly/goose/v3"
)

func init() {
    goose.AddMigrationContext(upBackfillDisplayNames, downBackfillDisplayNames)
}

func upBackfillDisplayNames(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        ALTER TABLE users
            ADD COLUMN IF NOT EXISTS display_name VARCHAR(255);

        UPDATE users
        SET display_name = TRIM(
            COALESCE(first_name, '') || ' ' || COALESCE(last_name, ''))
        WHERE display_name IS NULL;
    `)
    return err
}

func downBackfillDisplayNames(ctx context.Context, tx *sql.Tx) error {
    _, err := tx.ExecContext(ctx, `
        ALTER TABLE users DROP COLUMN IF EXISTS display_name;
    `)
    return err
}

Setting Up testcontainers-go

Add the dependency:

go get github.com/testcontainers/testcontainers-go
go get github.com/testcontainers/testcontainers-go/modules/postgres
go get github.com/pressly/goose/v3
go get github.com/jackc/pgx/v5/stdlib  # or lib/pq

A reusable test helper that starts PostgreSQL and returns a connection:

// testhelpers/db.go
package testhelpers

import (
    "context"
    "database/sql"
    "fmt"
    "testing"
    "time"

    _ "github.com/jackc/pgx/v5/stdlib"
    "github.com/testcontainers/testcontainers-go"
    "github.com/testcontainers/testcontainers-go/modules/postgres"
    "github.com/testcontainers/testcontainers-go/wait"
)

type TestDB struct {
    Container testcontainers.Container
    DB        *sql.DB
    DSN       string
}

func StartPostgres(t *testing.T) *TestDB {
    t.Helper()
    ctx := context.Background()

    container, err := postgres.RunContainer(ctx,
        testcontainers.WithImage("postgres:16-alpine"),
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("testuser"),
        postgres.WithPassword("testpass"),
        testcontainers.WithWaitStrategy(
            wait.ForLog("database system is ready to accept connections").
                WithOccurrence(2).
                WithStartupTimeout(30*time.Second),
        ),
    )
    if err != nil {
        t.Fatalf("failed to start postgres container: %v", err)
    }

    t.Cleanup(func() {
        if err := container.Terminate(ctx); err != nil {
            t.Errorf("failed to terminate container: %v", err)
        }
    })

    dsn, err := container.ConnectionString(ctx, "sslmode=disable")
    if err != nil {
        t.Fatalf("failed to get connection string: %v", err)
    }

    db, err := sql.Open("pgx", dsn)
    if err != nil {
        t.Fatalf("failed to open database: %v", err)
    }
    db.SetMaxOpenConns(10)
    db.SetConnMaxLifetime(5 * time.Minute)

    if err := db.PingContext(ctx); err != nil {
        t.Fatalf("failed to ping database: %v", err)
    }

    t.Cleanup(func() { db.Close() })

    return &TestDB{Container: container, DB: db, DSN: dsn}
}

Testing Up and Down Migrations

The core migration test applies all migrations, checks the resulting schema, rolls back, and verifies the schema is restored:

// migrations/migrations_test.go
package migrations_test

import (
    "context"
    "database/sql"
    "embed"
    "testing"

    "github.com/pressly/goose/v3"
    "github.com/stretchr/testify/assert"
    "github.com/stretchr/testify/require"

    "yourmodule/testhelpers"
)

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

func applyMigrations(t *testing.T, db *sql.DB, direction string) {
    t.Helper()
    goose.SetBaseFS(migrationsFS)

    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        db,
        migrationsFS,
    )
    require.NoError(t, err)

    ctx := context.Background()
    switch direction {
    case "up":
        _, err = provider.Up(ctx)
    case "down":
        _, err = provider.Down(ctx)
    default:
        t.Fatalf("unknown direction: %s", direction)
    }
    require.NoError(t, err)
}

func TestMigrateUp(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)

    applyMigrations(t, testDB.DB, "up")

    // Verify users table exists with correct columns
    rows, err := testDB.DB.QueryContext(context.Background(), `
        SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name = 'users'
        ORDER BY ordinal_position
    `)
    require.NoError(t, err)
    defer rows.Close()

    columns := make(map[string]struct{ dataType, nullable string })
    for rows.Next() {
        var name, dataType, nullable string
        require.NoError(t, rows.Scan(&name, &dataType, &nullable))
        columns[name] = struct{ dataType, nullable string }{dataType, nullable}
    }
    require.NoError(t, rows.Err())

    assert.Contains(t, columns, "id")
    assert.Contains(t, columns, "email")
    assert.Contains(t, columns, "created_at")
    assert.Equal(t, "NO", columns["email"].nullable, "email should be NOT NULL")
}

func TestMigrateDown(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)

    // Apply all migrations
    applyMigrations(t, testDB.DB, "up")

    // Roll back all migrations
    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        testDB.DB,
        migrationsFS,
    )
    require.NoError(t, err)

    ctx := context.Background()
    _, err = provider.DownTo(ctx, 0) // Roll back to version 0 (empty schema)
    require.NoError(t, err)

    // Verify users table no longer exists
    var exists bool
    err = testDB.DB.QueryRowContext(ctx, `
        SELECT EXISTS (
            SELECT FROM information_schema.tables
            WHERE table_name = 'users'
        )
    `).Scan(&exists)
    require.NoError(t, err)
    assert.False(t, exists, "users table should not exist after full downgrade")
}

Testing Each Migration Step Individually

For detailed validation, test each migration version independently:

func TestEachMigrationUpDown(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)
    ctx := context.Background()

    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        testDB.DB,
        migrationsFS,
    )
    require.NoError(t, err)

    migrations, err := provider.ListSources()
    require.NoError(t, err)

    for _, m := range migrations {
        t.Run(fmt.Sprintf("migration_%d_%s", m.Version, m.Type), func(t *testing.T) {
            // Apply this migration
            result, err := provider.UpTo(ctx, m.Version)
            require.NoError(t, err, "failed to apply migration %d", m.Version)
            assert.Len(t, result, 1)
            assert.Equal(t, goose.StateApplied, result[0].State)

            // Roll it back
            result, err = provider.Down(ctx)
            require.NoError(t, err, "failed to roll back migration %d", m.Version)
            assert.Len(t, result, 1)
            assert.Equal(t, goose.StateRolledBack, result[0].State)

            // Apply it again for the next iteration
            _, err = provider.UpTo(ctx, m.Version)
            require.NoError(t, err)
        })
    }
}

SQL Verification After Migrations

Schema assertions should go beyond just checking table existence. Verify indexes, foreign keys, constraints, and sequences:

func TestSchemaConstraints(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)
    applyMigrations(t, testDB.DB, "up")
    ctx := context.Background()

    t.Run("unique_index_on_email", func(t *testing.T) {
        var indexExists bool
        err := testDB.DB.QueryRowContext(ctx, `
            SELECT EXISTS (
                SELECT FROM pg_indexes
                WHERE tablename = 'users'
                  AND indexname = 'idx_users_email'
            )
        `).Scan(&indexExists)
        require.NoError(t, err)
        assert.True(t, indexExists, "unique index on email should exist")
    })

    t.Run("foreign_key_orders_to_users", func(t *testing.T) {
        var fkCount int
        err := testDB.DB.QueryRowContext(ctx, `
            SELECT COUNT(*)
            FROM information_schema.referential_constraints rc
            JOIN information_schema.key_column_usage kcu
              ON rc.constraint_name = kcu.constraint_name
            WHERE kcu.table_name = 'orders'
              AND kcu.column_name = 'user_id'
        `).Scan(&fkCount)
        require.NoError(t, err)
        assert.Equal(t, 1, fkCount, "orders.user_id should have a foreign key to users")
    })

    t.Run("subscription_tier_default_is_free", func(t *testing.T) {
        // Insert a user without specifying subscription_tier
        _, err := testDB.DB.ExecContext(ctx, `
            INSERT INTO users (email) VALUES ('test@example.com')
        `)
        require.NoError(t, err)

        var tier string
        err = testDB.DB.QueryRowContext(ctx, `
            SELECT subscription_tier FROM users WHERE email = 'test@example.com'
        `).Scan(&tier)
        require.NoError(t, err)
        assert.Equal(t, "free", tier)
    })
}

Testing Data Migrations in Go

Go migration files are where data migrations live. Testing them requires inserting data before the migration and asserting the result after:

func TestBackfillDisplayNames(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)
    ctx := context.Background()

    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        testDB.DB,
        migrationsFS,
    )
    require.NoError(t, err)

    // Apply up to (but not including) the backfill migration (version 3)
    _, err = provider.UpTo(ctx, 2)
    require.NoError(t, err)

    // Insert test data in the pre-migration state
    _, err = testDB.DB.ExecContext(ctx, `
        INSERT INTO users (id, email, first_name, last_name, created_at)
        VALUES
          (1, 'alice@example.com', 'Alice', 'Smith', NOW()),
          (2, 'bob@example.com',   'Bob',   NULL,    NOW()),
          (3, 'anon@example.com',  NULL,    NULL,    NOW())
    `)
    require.NoError(t, err)

    // Apply the backfill migration
    _, err = provider.UpTo(ctx, 3)
    require.NoError(t, err)

    // Verify backfill results
    type userRow struct {
        ID          int
        DisplayName sql.NullString
    }

    rows, err := testDB.DB.QueryContext(ctx,
        "SELECT id, display_name FROM users ORDER BY id")
    require.NoError(t, err)
    defer rows.Close()

    var users []userRow
    for rows.Next() {
        var u userRow
        require.NoError(t, rows.Scan(&u.ID, &u.DisplayName))
        users = append(users, u)
    }
    require.NoError(t, rows.Err())

    require.Len(t, users, 3)
    assert.Equal(t, "Alice Smith", users[0].DisplayName.String)
    assert.Equal(t, "Bob", users[1].DisplayName.String)
    assert.Equal(t, "", users[2].DisplayName.String)
}

Testing Migration Idempotency

Some migrations should be safe to run twice. Test this explicitly for any migration that uses CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, or INSERT ... ON CONFLICT:

func TestMigrationIdempotency(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)
    ctx := context.Background()

    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        testDB.DB,
        migrationsFS,
    )
    require.NoError(t, err)

    // Apply all migrations
    _, err = provider.Up(ctx)
    require.NoError(t, err)

    // Get the current state
    var userCount int
    err = testDB.DB.QueryRowContext(ctx,
        "SELECT COUNT(*) FROM users").Scan(&userCount)
    require.NoError(t, err)

    // Re-run the idempotent parts of migrations manually
    // (simulating a partial re-run scenario)
    _, err = testDB.DB.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS users (
            id          BIGSERIAL       PRIMARY KEY,
            email       VARCHAR(255)    NOT NULL,
            created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW()
        )
    `)
    require.NoError(t, err, "idempotent CREATE TABLE IF NOT EXISTS should not error")

    // Verify row count is unchanged
    var userCountAfter int
    err = testDB.DB.QueryRowContext(ctx,
        "SELECT COUNT(*) FROM users").Scan(&userCountAfter)
    require.NoError(t, err)
    assert.Equal(t, userCount, userCountAfter,
        "idempotent migration should not change row count")
}

Parallel Test Isolation

Running migration tests in parallel speeds up the test suite significantly. Use a separate database per parallel test:

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

    // Each parallel test gets its own container (expensive but isolated)
    // OR use a shared container with per-test schema isolation

    testDB := testhelpers.StartPostgres(t)

    // Use a unique schema per test to isolate parallel runs
    schemaName := fmt.Sprintf("test_%s", strings.ReplaceAll(t.Name(), "/", "_"))
    _, err := testDB.DB.ExecContext(context.Background(),
        fmt.Sprintf("CREATE SCHEMA IF NOT EXISTS %s", schemaName))
    require.NoError(t, err)

    t.Cleanup(func() {
        testDB.DB.ExecContext(context.Background(),
            fmt.Sprintf("DROP SCHEMA IF EXISTS %s CASCADE", schemaName))
    })

    // Configure goose to use this schema
    goose.SetTableName(schemaName + ".goose_db_version")
    // ... run migrations targeting this schema
}

For large test suites, sharing one PostgreSQL container with per-test schemas is more efficient than starting a container per test. Use TestMain to manage the shared container:

// migrations/main_test.go
package migrations_test

import (
    "os"
    "testing"

    "yourmodule/testhelpers"
)

var sharedDB *testhelpers.TestDB

func TestMain(m *testing.M) {
    // Start one container for the entire test package
    // testcontainers-go supports this via a package-scoped container
    os.Exit(m.Run())
}

Checking Migration Version Conflicts

When two branches add migrations with the same version number, Goose will apply them in filesystem sort order — which is non-deterministic and environment-dependent. Detect this in CI:

#!/bin/bash
<span class="hljs-comment"># check-migration-conflicts.sh
MIGRATION_DIR=<span class="hljs-string">"./migrations"

<span class="hljs-comment"># Extract version numbers from SQL and Go migration files
versions=$(<span class="hljs-built_in">ls <span class="hljs-string">"$MIGRATION_DIR"/*.sql <span class="hljs-string">"$MIGRATION_DIR"/*.go 2>/dev/null \
    <span class="hljs-pipe">| grep -E <span class="hljs-string">'[0-9]+_' \
    <span class="hljs-pipe">| sed <span class="hljs-string">'s/.*\/\([0-9]*\)_.*/\1/' \
    <span class="hljs-pipe">| <span class="hljs-built_in">sort)

duplicates=$(<span class="hljs-built_in">echo <span class="hljs-string">"$versions" <span class="hljs-pipe">| <span class="hljs-built_in">uniq -d)

<span class="hljs-keyword">if [ -n <span class="hljs-string">"$duplicates" ]; <span class="hljs-keyword">then
    <span class="hljs-built_in">echo <span class="hljs-string">"ERROR: Duplicate migration version numbers detected: $duplicates"
    <span class="hljs-built_in">echo <span class="hljs-string">"This causes non-deterministic behavior when both branches are merged."
    <span class="hljs-built_in">exit 1
<span class="hljs-keyword">fi

<span class="hljs-built_in">echo <span class="hljs-string">"No migration version conflicts."

Integration with Go Test Suite

Migration tests integrate naturally with the standard Go test suite. Place them in the migrations package or alongside your database layer:

// internal/db/db_test.go
package db_test

import (
    "testing"

    "yourmodule/internal/db"
    "yourmodule/testhelpers"
)

func TestDatabaseLayer(t *testing.T) {
    testDB := testhelpers.StartPostgres(t)

    // Apply migrations
    err := db.RunMigrations(testDB.DSN)
    require.NoError(t, err)

    // Test your repository layer against a fully migrated database
    repo := db.NewUserRepository(testDB.DB)

    t.Run("CreateUser", func(t *testing.T) {
        user, err := repo.Create(context.Background(), &db.User{
            Email: "test@example.com",
        })
        require.NoError(t, err)
        assert.NotZero(t, user.ID)
        assert.Equal(t, "free", user.SubscriptionTier)
    })
}

Your db.RunMigrations function wraps the Goose provider:

// internal/db/migrations.go
package db

import (
    "database/sql"
    "embed"

    "github.com/pressly/goose/v3"
)

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

func RunMigrations(dsn string) error {
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        return fmt.Errorf("open db: %w", err)
    }
    defer db.Close()

    provider, err := goose.NewProvider(
        goose.DialectPostgres,
        db,
        migrationsFS,
    )
    if err != nil {
        return fmt.Errorf("create goose provider: %w", err)
    }

    _, err = provider.Up(context.Background())
    return err
}

Embedding migrations with //go:embed ensures the migration files are bundled into the test binary — no file path dependencies, no "migrations not found" errors in CI.

CI Integration

A complete GitHub Actions workflow for Goose migration testing in Go:

name: Goose Migration Tests

on:
  push:
    paths:
      - 'migrations/**'
      - 'internal/db/**'
  pull_request:

jobs:
  migration-tests:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - name: Set up Go
        uses: actions/setup-go@v5
        with:
          go-version: '1.22'

      - name: Check for migration version conflicts
        run: bash scripts/check-migration-conflicts.sh

      - name: Run migration tests
        run: go test ./migrations/... ./internal/db/... -v -run TestMigrat
        env:
          DOCKER_HOST: unix:///var/run/docker.sock

      - name: Run full database integration tests
        run: go test ./internal/db/... -v -timeout 120s
        env:
          DOCKER_HOST: unix:///var/run/docker.sock

Note that testcontainers-go uses Docker on the host, which is available by default on GitHub Actions runners. No separate services: block is needed — testcontainers manages its own containers.

Summary

A complete Goose testing strategy in Go has four components:

Version conflict check (shell script, runs in pre-commit and CI) — catches duplicate version numbers before they cause non-deterministic behavior on merge.
Full up/down cycle test (testcontainers-go, runs in CI) — applies all migrations to head, rolls back to base, verifies the schema is clean at each end.
Per-migration up/down test (testcontainers-go, runs in CI on migration changes) — validates that each migration can be individually applied and reversed.
Data migration tests (testcontainers-go, runs in CI) — inserts pre-migration data, applies the migration, asserts the transformation was correct.

All four tests use standard Go test tooling (go test, testing.T, testify), run in CI without external dependencies beyond Docker, and integrate naturally with your existing repository and service layer tests. There's no reason to treat migration testing as a separate concern — it belongs in the same test suite as the rest of your database code.

HelpMeTest can run your Goose migration tests automatically on every pull request, catching up/down failures and schema drift before they reach production — sign up free