Database Migration Management (golang-migrate)

Overview

Database schema migration is the practice of versioning and applying incremental changes to a database’s structure — creating tables, adding columns, modifying indexes, altering constraints — in a controlled, reversible, and reproducible way. Without a migration framework, schema changes are applied manually via SQL scripts or ad-hoc commands, leading to environment drift (production has columns that staging doesn’t), unreproducible state (no one remembers which ALTER TABLE statements have been run), and deployment fear (will this change break something, and can we roll back?).

The golang-migrate library solves this by organizing schema changes as numbered migration files, each with an “up” (apply) and “down” (rollback) direction. Migrations are applied in order, tracked in a schema_migrations table, and can be embedded directly into Go binaries using go:embed so that the application carries its own schema definitions. This eliminates the deployment dependency on having migration files available on the filesystem — the binary itself knows how to create or update its database schema.

This topic covers the golang-migrate migration file format, the go:embed integration pattern, programmatic migration execution, rollback strategies, and best practices for writing safe, reversible migrations in production systems.

Core Concepts

How It Works

A golang-migrate setup consists of migration SQL files, an embedded filesystem, and programmatic migration execution during application startup.

Migration File Structure

# Directory layout for embedded migrations
migrations/
├── 000001_create_users.up.sql
├── 000001_create_users.down.sql
├── 000002_add_user_email_index.up.sql
├── 000002_add_user_email_index.down.sql
├── 000003_create_projects.up.sql
├── 000003_create_projects.down.sql
├── 000004_add_project_status.up.sql
└── 000004_add_project_status.down.sql

-- 000001_create_users.up.sql
CREATE TABLE IF NOT EXISTS users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username    VARCHAR(255) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);

-- 000001_create_users.down.sql
DROP TABLE IF EXISTS users;

-- 000004_add_project_status.up.sql
-- Use a transaction for multi-statement migrations.
BEGIN;

CREATE TYPE project_status AS ENUM ('active', 'archived', 'deleted');

ALTER TABLE projects
    ADD COLUMN IF NOT EXISTS status project_status NOT NULL DEFAULT 'active';

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_projects_status
    ON projects(status)
    WHERE status = 'active';

COMMIT;

-- 000004_add_project_status.down.sql
BEGIN;

DROP INDEX IF EXISTS idx_projects_status;

ALTER TABLE projects DROP COLUMN IF EXISTS status;

DROP TYPE IF EXISTS project_status;

COMMIT;

Embedding and Running Migrations in Go

package migrations

import (
    "database/sql"
    "embed"
    "fmt"

    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    "github.com/golang-migrate/migrate/v4/source/iofs"
)

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

// Run applies all pending migrations to the database.
// It returns the number of applied migrations or an error.
func Run(db *sql.DB) (int, error) {
    // Create a source driver from the embedded filesystem.
    source, err := iofs.New(migrationFS, ".")
    if err != nil {
        return 0, fmt.Errorf("creating migration source: %w", err)
    }

    // Create a database driver for PostgreSQL.
    driver, err := postgres.WithInstance(db, &postgres.Config{
        MigrationsTable: "schema_migrations",
    })
    if err != nil {
        return 0, fmt.Errorf("creating migration driver: %w", err)
    }

    // Create the migrate instance.
    m, err := migrate.NewWithInstance("iofs", source, "postgres", driver)
    if err != nil {
        return 0, fmt.Errorf("creating migrate instance: %w", err)
    }

    // Get current version before migration.
    versionBefore, _, _ := m.Version()

    // Apply all pending migrations.
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return 0, fmt.Errorf("running migrations: %w", err)
    }

    // Get version after migration.
    versionAfter, _, _ := m.Version()
    applied := int(versionAfter) - int(versionBefore)

    return applied, nil
}

Application Startup Integration

package main

import (
    "database/sql"
    "log"

    _ "github.com/lib/pq"
    "example.com/app/internal/migrations"
)

func main() {
    db, err := sql.Open("postgres", "postgres://user:pass@localhost/appdb?sslmode=disable")
    if err != nil {
        log.Fatalf("connecting to database: %v", err)
    }
    defer db.Close()

    // Run migrations on startup — the binary carries its own schema.
    applied, err := migrations.Run(db)
    if err != nil {
        log.Fatalf("running migrations: %v", err)
    }
    if applied > 0 {
        log.Printf("applied %d migration(s)", applied)
    }

    // Start the application...
    startServer(db)
}

CLI Usage for Development

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

# Create a new migration pair
migrate create -ext sql -dir migrations -seq add_api_keys_table

# Apply all pending migrations
migrate -database "postgres://localhost/appdb?sslmode=disable" \
        -path migrations up

# Roll back the last migration
migrate -database "postgres://localhost/appdb?sslmode=disable" \
        -path migrations down 1

# Check current migration version
migrate -database "postgres://localhost/appdb?sslmode=disable" \
        -path migrations version

# Force version (to recover from dirty state)
migrate -database "postgres://localhost/appdb?sslmode=disable" \
        -path migrations force 3

Practical Applications

A typical Go service uses golang-migrate with embedded SQL files to manage its PostgreSQL schema. Every schema change — new tables, index additions, enum type modifications, constraint alterations — goes through the migration system. This ensures that every environment (development, staging, production) evolves through the same sequence of schema changes, eliminating the “it works on my machine” problem where a developer’s local database has a column that production doesn’t.

Migrations are embedded into the server binary using go:embed. When the server starts, it checks the schema_migrations table, applies any pending migrations, and then begins serving requests. This means deploying a new version of the service that requires schema changes is a single operation: deploy the new binary and it migrates its own database. No separate migration step, no coordination between deployment scripts, no “did someone remember to run the migrations?” anxiety.

For rollbacks, a common approach is a “forward-only” strategy for production: rather than running down migrations (which risk data loss), the team writes a new forward migration that undoes the unwanted change. Down migrations are reserved for development workflows where developers need to reset their local database to test migration changes. This pragmatic split acknowledges that production rollbacks have different safety requirements than development iteration.

Implementation Patterns

Timestamp-Based Version Numbers

Using sequential numbers (000001, 000002) works for small teams but causes merge conflicts when multiple developers create migrations simultaneously — both might create migration 000005. Timestamp-based prefixes (20260411143000) avoid this because two developers creating migrations at different times get unique version numbers. golang-migrate sorts by the numeric prefix, so timestamp ordering works correctly.

# Timestamp-based migration creation
migrate create -ext sql -dir migrations \
    -seq -digits 14 add_billing_table

# Produces:
# 20260411143000_add_billing_table.up.sql
# 20260411143000_add_billing_table.down.sql

Transactional Migrations

Wrap multi-statement migrations in explicit transactions so that a failure in any statement rolls back the entire migration, leaving the database in its pre-migration state. Note that some DDL operations (like CREATE INDEX CONCURRENTLY in PostgreSQL) cannot run inside a transaction — these must be in their own non-transactional migration file with a comment explaining why.

Testing Migrations

Write integration tests that apply all migrations up, verify the schema matches expectations, then apply all down migrations and verify the database is clean. This catches subtle bugs like a down migration that drops a table but doesn’t drop the associated custom type, leaving orphaned types that prevent re-application.

// migrations_test.go — verify up/down roundtrip
func TestMigrations(t *testing.T) {
    db := setupTestDB(t)
    defer db.Close()

    m := setupMigrate(t, db)

    // Apply all migrations up.
    if err := m.Up(); err != nil {
        t.Fatalf("migrate up: %v", err)
    }

    // Verify expected tables exist.
    assertTableExists(t, db, "users")
    assertTableExists(t, db, "projects")

    // Roll all migrations back down.
    if err := m.Down(); err != nil {
        t.Fatalf("migrate down: %v", err)
    }

    // Verify all tables are removed.
    assertTableNotExists(t, db, "users")
    assertTableNotExists(t, db, "projects")
}

Common Pitfalls

Locking Tables During Migration

Running ALTER TABLE on a large table acquires an exclusive lock in most databases, blocking all reads and writes until the migration completes. For a table with millions of rows, adding a column with a default value can lock the table for minutes. Use database-specific strategies to avoid this: in PostgreSQL, add nullable columns first (instant, no lock), then backfill defaults in batches, then add the NOT NULL constraint. Never add a NOT NULL column with a DEFAULT in a single statement on a large production table.

Irreversible Down Migrations

A down migration that drops a column destroys data permanently. If the up migration added a column and populated it, the down migration can drop the column, but re-running up will create an empty column. For data-carrying columns, consider whether the down migration should preserve data in a temporary table or JSON column. If rollback would cause unacceptable data loss, document the migration as irreversible and make the down migration raise an error rather than silently destroying data.

Forgetting the Embed Directive

If you add new migration files but forget to ensure they’re captured by the //go:embed *.sql directive (e.g., by placing them in a subdirectory not covered by the embed pattern), the binary will be compiled without them. The application will start, check migrations, find nothing new to apply, and silently run with an outdated schema. Add a test that counts embedded migration files and compares against the filesystem to catch this at build time.

Concurrent Migration Races

Without advisory locking, deploying multiple instances simultaneously can cause migration races: two instances both see version N as current, both try to apply version N+1, and one fails. golang-migrate’s PostgreSQL driver supports advisory locks by default, but you must verify this is enabled in your configuration. For other databases without built-in advisory locks, use an external lock mechanism (Redis, Consul) or ensure only one instance runs migrations (e.g., a Kubernetes init container).

Further Reading