Scrolls
3 min read
On this page

Migrations & Testing

Database schemas change over time. Migrations track those changes in version-controlled files that can be applied forward and rolled back. Testing database code requires a real database -- mocking SQL leads to tests that pass while production breaks. This topic covers migration tools, testing with real databases, and patterns for reliable database tests.

Migration Tools

goose

goose is a lightweight migration tool that supports SQL and Go migrations:

go install github.com/pressly/goose/v3/cmd/goose@latest

Create a migration:

goose -dir migrations create add_users_table sql

This creates a timestamped file:

-- migrations/20250115100000_add_users_table.sql

-- +goose Up
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_users_email ON users (email);

-- +goose Down
DROP TABLE users;

Run migrations:

goose -dir migrations postgres "postgres://user:pass@localhost:5432/mydb" up

golang-migrate

golang-migrate uses numbered pairs of up/down files:

go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

migrate create -ext sql -dir migrations -seq add_users_table

This creates two files:

migrations/
  000001_add_users_table.up.sql
  000001_add_users_table.down.sql

-- 000001_add_users_table.up.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 000001_add_users_table.down.sql
DROP TABLE users;

Run migrations:

migrate -path migrations -database "postgres://user:pass@localhost:5432/mydb" up

Choosing Between Them

Feature          goose            golang-migrate
--------------------------------------------------
SQL migrations   yes              yes
Go migrations    yes              yes
File naming      timestamps       sequential numbers
Embedding        go:embed         go:embed
Popularity       growing          established

Both work well. Pick one and stick with it.

Running Migrations in Code

Embed migrations in your binary and run them at startup:

import (
    "embed"
    "database/sql"
    "github.com/pressly/goose/v3"
)

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

func runMigrations(db *sql.DB) error {
    goose.SetBaseFS(migrations)
    if err := goose.SetDialect("postgres"); err != nil {
        return err
    }
    return goose.Up(db, "migrations")
}

Run migrations before starting the server:

func main() {
    db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatal(err)
    }
    if err := runMigrations(db); err != nil {
        log.Fatal("migrations failed:", err)
    }
    // Start server...
}

Best Practices for Migrations

  • Every migration must have a down. Even if you never roll back in production, the down migration is essential for development and testing.
  • Never edit a migration that has been applied. Create a new migration instead. Editing an applied migration causes checksum mismatches.
  • Keep migrations small. One logical change per migration. "Add users table" and "add orders table" should be separate migrations.
  • Test migrations against a real database. A migration that works on SQLite may fail on PostgreSQL.

Testing with a Real Database

Mocking database/sql is painful and gives false confidence. The queries that matter -- JOINs, constraints, indexes -- only work against a real database. Use testcontainers-go to spin up a real PostgreSQL instance for tests.

testcontainers-go

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

    "github.com/testcontainers/testcontainers-go"
    "github.com/testcontainers/testcontainers-go/modules/postgres"
    "github.com/testcontainers/testcontainers-go/wait"
)

func setupTestDB(t *testing.T) *sql.DB {
    t.Helper()
    ctx := context.Background()

    container, err := postgres.Run(ctx,
        "postgres:16-alpine",
        postgres.WithDatabase("testdb"),
        postgres.WithUsername("test"),
        postgres.WithPassword("test"),
        testcontainers.WithWaitStrategy(
            wait.ForLog("database system is ready to accept connections").
                WithOccurrence(2),
        ),
    )
    if err != nil {
        t.Fatal("starting postgres container:", err)
    }
    t.Cleanup(func() { container.Terminate(ctx) })

    connStr, err := container.ConnectionString(ctx, "sslmode=disable")
    if err != nil {
        t.Fatal("getting connection string:", err)
    }

    db, err := sql.Open("pgx", connStr)
    if err != nil {
        t.Fatal("opening database:", err)
    }
    t.Cleanup(func() { db.Close() })

    if err := runMigrations(db); err != nil {
        t.Fatal("running migrations:", err)
    }

    return db
}

Using the Test Database

func TestUserStore_Create(t *testing.T) {
    db := setupTestDB(t)
    store := NewUserStore(db)

    user := &User{Name: "Alice", Email: "alice@example.com"}
    err := store.Create(context.Background(), user)
    if err != nil {
        t.Fatal("creating user:", err)
    }

    if user.ID == 0 {
        t.Error("expected user ID to be set")
    }

    // Verify it was persisted
    got, err := store.GetByID(context.Background(), user.ID)
    if err != nil {
        t.Fatal("getting user:", err)
    }
    if got.Name != "Alice" {
        t.Errorf("got name %q, want %q", got.Name, "Alice")
    }
}

Truncating Between Tests

Tests should not depend on each other. Truncate all tables between tests:

func truncateTables(t *testing.T, db *sql.DB) {
    t.Helper()
    tables := []string{"users", "orders", "products"}
    for _, table := range tables {
        _, err := db.Exec("TRUNCATE TABLE " + table + " CASCADE")
        if err != nil {
            t.Fatal("truncating", table, ":", err)
        }
    }
}

func TestUserStore_List(t *testing.T) {
    db := setupTestDB(t)
    truncateTables(t, db)

    store := NewUserStore(db)
    // Test starts with empty tables...
}

For faster tests, share one container across all tests in a package and truncate between each test:

var testDB *sql.DB

func TestMain(m *testing.M) {
    // Setup container once for all tests
    ctx := context.Background()
    container, db := startContainer(ctx)
    testDB = db

    code := m.Run()

    db.Close()
    container.Terminate(ctx)
    os.Exit(code)
}

func TestUserCreate(t *testing.T) {
    truncateTables(t, testDB)
    // ...
}

t.Cleanup for Teardown

t.Cleanup registers functions that run after the test (and its subtests) finish. It is Go's answer to setUp/tearDown:

func setupTestUser(t *testing.T, db *sql.DB) *User {
    t.Helper()
    user := &User{Name: "Test User", Email: "test@example.com"}
    _, err := db.Exec(
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
        user.Name, user.Email,
    )
    if err != nil {
        t.Fatal("inserting test user:", err)
    }

    t.Cleanup(func() {
        db.Exec("DELETE FROM users WHERE email = $1", user.Email)
    })

    return user
}

Cleanup functions run in LIFO order (last registered runs first), so resources are torn down in the reverse order of creation.

Build Tags for Integration Tests

Integration tests (those needing a database) are slower than unit tests. Separate them with build tags:

//go:build integration

package store_test

import "testing"

func TestUserStore_Integration(t *testing.T) {
    db := setupTestDB(t)
    // ...
}

Run only unit tests (fast):

go test ./...

Run integration tests too:

go test -tags integration ./...

This keeps go test fast during development while CI runs the full suite.

Testing Transactions

Test that transactions actually roll back on failure:

func TestTransfer_InsufficientFunds(t *testing.T) {
    db := setupTestDB(t)
    truncateTables(t, db)

    // Setup: account with $100
    _, err := db.Exec(
        "INSERT INTO accounts (id, balance) VALUES ($1, $2)", 1, 100.0,
    )
    if err != nil {
        t.Fatal(err)
    }
    _, err = db.Exec(
        "INSERT INTO accounts (id, balance) VALUES ($1, $2)", 2, 0.0,
    )
    if err != nil {
        t.Fatal(err)
    }

    // Attempt transfer of $200 from account with $100
    err = TransferFunds(context.Background(), db, 1, 2, 200.0)
    if err == nil {
        t.Fatal("expected error for insufficient funds")
    }

    // Verify no money moved (transaction rolled back)
    var balance float64
    db.QueryRow("SELECT balance FROM accounts WHERE id = $1", 1).Scan(&balance)
    if balance != 100.0 {
        t.Errorf("source balance = %f, want 100.0", balance)
    }
}

Common Pitfalls

  • Mocking the database instead of testing against a real one. Mocks do not catch SQL syntax errors, constraint violations, or type mismatches. Use testcontainers for real database tests.
  • Not cleaning up between tests. Tests that depend on data from other tests are flaky. Truncate tables or use transactions that roll back.
  • Editing applied migrations. Once a migration is applied (especially in production), it is immutable. Create a new migration for changes.
  • Running integration tests in every go test invocation. Use build tags to separate fast unit tests from slow integration tests.
  • Sharing test database state across parallel tests. If tests run in parallel and share a database, use separate schemas or unique data to avoid conflicts.
  • Forgetting to run migrations in test setup. The test database starts empty. Always run migrations before testing.

Key Takeaways

  • Use goose or golang-migrate for versioned, reversible database migrations.
  • Embed migrations in the binary with go:embed and run them at application startup.
  • Test database code against a real database using testcontainers-go.
  • Truncate tables between tests to ensure isolation.
  • Use t.Cleanup for teardown and TestMain for one-time setup.
  • Separate integration tests with //go:build integration build tags.
  • Never mock database/sql. Test the real queries against a real database.