go / migrate

updated March 22, 2026

I use plain SQL migration files with -- migrate:up / -- migrate:down markers, embedded into the Go binary with //go:embed. No ORM, no DSL, no external migration tool. The migration runner operates on the DB wrapper from pgxpool.

Migration files

Migrations live in db/migrate/ as timestamped SQL files:

-- migrate:up

CREATE TABLE teams (
  id bigserial PRIMARY KEY,
  name text NOT NULL UNIQUE,
  region text NOT NULL,
  seed integer NOT NULL
);

CREATE TABLE source_projections (
  id bigserial PRIMARY KEY,
  fetched_at timestamptz NOT NULL DEFAULT now(),
  team text NOT NULL,
  source text NOT NULL,
  round text NOT NULL,
  probability numeric NOT NULL
);

CREATE INDEX idx_source_projections_team_source
  ON source_projections (team, source, round, fetched_at);

-- migrate:down

DROP TABLE IF EXISTS source_projections;
DROP TABLE IF EXISTS teams;

Key conventions:

• One pair of -- migrate:up / -- migrate:down markers per file
• bigserial PRIMARY KEY for IDs
• timestamptz NOT NULL DEFAULT now() for timestamps
• Indexes in CREATE TABLE migrations are safe (no CONCURRENTLY needed)
• CREATE INDEX CONCURRENTLY must go in its own migration file, separate from transactional DDL (the runner detects and handles this)
• Drop tables in reverse dependency order in the down section

Embedding and parsing

Embed the migration directory at compile time:

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

Parse each file by splitting on the -- migrate:down marker:

type Migration struct {
	Version string // e.g. "20260322160000"
	Name    string // e.g. "create_schema"
	Up      string // SQL after "-- migrate:up"
	Down    string // SQL after "-- migrate:down"
}

func ParseMigration(filename, content string) (Migration, error) {
	base := strings.TrimSuffix(filename, ".sql")
	parts := strings.SplitN(base, "_", 2)
	if len(parts) != 2 {
		return Migration{}, fmt.Errorf("invalid migration filename: %s", filename)
	}

	m := Migration{
		Version: parts[0],
		Name:    parts[1],
	}

	sections := strings.SplitN(content, "-- migrate:down", 2)
	if len(sections) != 2 {
		return Migration{}, fmt.Errorf("%s: missing '-- migrate:down' marker", filename)
	}

	upPart := strings.Replace(sections[0], "-- migrate:up", "", 1)

	m.Up = strings.TrimSpace(upPart)
	m.Down = strings.TrimSpace(sections[1])

	if m.Up == "" {
		return Migration{}, fmt.Errorf("%s: empty up migration", filename)
	}

	return m, nil
}

func LoadMigrations() ([]Migration, error) {
	entries, err := migrations.ReadDir("migrate")
	if err != nil {
		return nil, fmt.Errorf("read migrate dir: %w", err)
	}

	var ms []Migration
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		content, err := migrations.ReadFile("migrate/" + e.Name())
		if err != nil {
			return nil, fmt.Errorf("read %s: %w", e.Name(), err)
		}
		m, err := ParseMigration(e.Name(), string(content))
		if err != nil {
			return nil, err
		}
		ms = append(ms, m)
	}

	sort.Slice(ms, func(i, j int) bool {
		return ms[i].Version < ms[j].Version
	})

	return ms, nil
}

LoadMigrations returns all migrations sorted by version. The binary contains the SQL — no files to deploy or path to configure.

Runner

The migration runner acquires a single connection from the pool, creates a schema_migrations tracking table, sets safe timeouts, and applies pending migrations.

A dedicated pgx.Conn (via github.com/jackc/pgx/v5) is necessary because pgxpool.Pool doesn't forward pgx.QueryExecModeSimpleProtocol to the underlying connection and releases connections between calls, making session-level SET statements no-ops. Migrations containing CONCURRENTLY use pgx's simple query protocol to avoid the implicit transaction that the extended protocol creates. Each statement is executed individually because PostgreSQL still treats multiple statements in a single simple-protocol Query message as a transaction block:

func (db *DB) Migrate() error {
	ctx := context.Background()

	// Acquire a single connection for the entire migration run so that
	// SET statements persist and QueryExecModeSimpleProtocol reaches
	// pgx.Conn directly (the pool layer does not forward it).
	poolConn, err := db.Acquire(ctx)
	if err != nil {
		return fmt.Errorf("acquire conn: %w", err)
	}
	defer poolConn.Release()
	conn := poolConn.Conn()

	_, err = conn.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS schema_migrations (
			version text PRIMARY KEY,
			applied_at timestamptz NOT NULL DEFAULT now()
		)
	`)
	if err != nil {
		return fmt.Errorf("create schema_migrations: %w", err)
	}

	_, err = conn.Exec(ctx, "SET lock_timeout = '10s'")
	if err != nil {
		return fmt.Errorf("set lock_timeout: %w", err)
	}
	_, err = conn.Exec(ctx, "SET statement_timeout = '10min'")
	if err != nil {
		return fmt.Errorf("set statement_timeout: %w", err)
	}

	ms, err := LoadMigrations()
	if err != nil {
		return err
	}

	rows, err := conn.Query(ctx, "SELECT version FROM schema_migrations ORDER BY version")
	if err != nil {
		return fmt.Errorf("query schema_migrations: %w", err)
	}
	defer rows.Close()

	applied := make(map[string]bool)
	for rows.Next() {
		var v string
		if err := rows.Scan(&v); err != nil {
			return err
		}
		applied[v] = true
	}
	if err := rows.Err(); err != nil {
		return err
	}

	for _, m := range ms {
		if applied[m.Version] {
			continue
		}
		fmt.Printf("  migrate: %s_%s\n", m.Version, m.Name)
		// CONCURRENTLY cannot run inside a transaction block.
		// Simple protocol avoids the extended-protocol implicit
		// transaction, but PostgreSQL still treats multiple
		// statements in a single Query message as a transaction
		// block. Execute each statement individually.
		var execErr error
		if strings.Contains(strings.ToUpper(m.Up), "CONCURRENTLY") {
			for _, stmt := range splitStatements(m.Up) {
				if _, execErr = conn.Exec(ctx, stmt, pgx.QueryExecModeSimpleProtocol); execErr != nil {
					break
				}
			}
		} else {
			_, execErr = conn.Exec(ctx, m.Up)
		}
		if execErr != nil {
			return fmt.Errorf("migrate %s: %w", m.Version, execErr)
		}
		_, err = conn.Exec(ctx, "INSERT INTO schema_migrations (version) VALUES ($1)", m.Version)
		if err != nil {
			return fmt.Errorf("record %s: %w", m.Version, err)
		}
	}

	return nil
}

A helper splits SQL on semicolons for the per-statement execution:

func splitStatements(sql string) []string {
	var stmts []string
	for _, s := range strings.Split(sql, ";") {
		s = strings.TrimSpace(s)
		if s != "" {
			stmts = append(stmts, s)
		}
	}
	return stmts
}

Rollback reverts the most recently applied migration:

func (db *DB) Rollback() error {
	ctx := context.Background()

	ms, err := LoadMigrations()
	if err != nil {
		return err
	}

	var latest string
	err = db.QueryRow(ctx,
		"SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1",
	).Scan(&latest)
	if err != nil {
		return fmt.Errorf("no migrations to rollback: %w", err)
	}

	for _, m := range ms {
		if m.Version == latest {
			if m.Down == "" {
				return fmt.Errorf("migration %s has no down section", m.Version)
			}
			fmt.Printf("  rollback: %s_%s\n", m.Version, m.Name)
			_, err := db.Exec(ctx, m.Down)
			if err != nil {
				return fmt.Errorf("rollback %s: %w", m.Version, err)
			}
			_, err = db.Exec(ctx, "DELETE FROM schema_migrations WHERE version = $1", m.Version)
			if err != nil {
				return fmt.Errorf("delete version %s: %w", m.Version, err)
			}
			return nil
		}
	}

	return fmt.Errorf("migration %s not found in files", latest)
}

The CLI entry point dispatches to migrate, rollback, or dump-schema:

// cmd/db-migrate/main.go
func main() {
	url := env.String("DATABASE_URL", "")
	if url == "" {
		fmt.Fprintln(os.Stderr, "DATABASE_URL not set")
		os.Exit(1)
	}

	d, err := db.NewDB(url)
	if err != nil {
		fmt.Fprintf(os.Stderr, "connect: %v\n", err)
		os.Exit(1)
	}
	defer d.Close()

	cmd := "migrate"
	if len(os.Args) > 1 {
		cmd = os.Args[1]
	}

	switch cmd {
	case "migrate":
		if err := d.Migrate(); err != nil {
			fmt.Fprintf(os.Stderr, "migrate: %v\n", err)
			os.Exit(1)
		}
	case "rollback":
		if err := d.Rollback(); err != nil {
			fmt.Fprintf(os.Stderr, "rollback: %v\n", err)
			os.Exit(1)
		}
	case "dump-schema":
		if err := db.DumpSchema(url, "db/schema.sql"); err != nil {
			fmt.Fprintf(os.Stderr, "dump schema: %v\n", err)
			os.Exit(1)
		}
		fmt.Println("  wrote db/schema.sql")
	}
}

Usage:

go run ./cmd/db-migrate              # apply pending migrations
go run ./cmd/db-migrate rollback     # revert last migration
go run ./cmd/db-migrate dump-schema  # write db/schema.sql

Generator

A small CLI generates timestamped migration files:

// cmd/db-gen-migration/main.go
func main() {
	if len(os.Args) < 2 {
		fmt.Fprintln(os.Stderr, "usage: go run ./cmd/db-gen-migration <migration_name>")
		os.Exit(1)
	}

	name := strings.TrimSpace(os.Args[1])
	if name == "" {
		fmt.Fprintln(os.Stderr, "migration name required")
		os.Exit(1)
	}

	name = strings.ToLower(strings.ReplaceAll(name, "-", "_"))

	timestamp := time.Now().UTC().Format("20060102150405")
	filename := fmt.Sprintf("%s_%s.sql", timestamp, name)
	path := filepath.Join("db", "migrate", filename)

	content := "-- migrate:up\n\n\n-- migrate:down\n"

	if err := os.WriteFile(path, []byte(content), 0644); err != nil {
		fmt.Fprintf(os.Stderr, "write: %v\n", err)
		os.Exit(1)
	}
	fmt.Printf("created: %s\n", path)
}

Usage:

go run ./cmd/db-gen-migration add_score_to_teams
# created: db/migrate/20260322220000_add_score_to_teams.sql

Linter

A linter checks migration files for unsafe DDL patterns. See postgres / safe migrations for the patterns.

type LintCheck struct {
	Name    string
	Match   func(content string) bool
	Message string
}

type LintError struct {
	File    string
	Check   string
	Message string
}

var DefaultChecks = []LintCheck{
	{
		Name: "non-concurrent index",
		Match: func(content string) bool {
			if createTableRe.MatchString(content) {
				return false
			}
			return createIndexRe.MatchString(content) && !concurrentlyRe.MatchString(content)
		},
		Message: "Use CREATE INDEX CONCURRENTLY outside a transaction.",
	},
	{
		Name: "foreign key without NOT VALID",
		Match: func(content string) bool {
			if createTableRe.MatchString(content) {
				return false
			}
			return referencesRe.MatchString(content) && !notValidRe.MatchString(content)
		},
		Message: "Add NOT VALID, then VALIDATE in a separate migration.",
	},
	{
		Name: "SET NOT NULL directly",
		Match: func(content string) bool {
			return setNotNullRe.MatchString(content)
		},
		Message: "Use a CHECK constraint with NOT VALID instead.",
	},
	{
		Name: "check constraint without NOT VALID",
		Match: func(content string) bool {
			return addCheckRe.MatchString(content) && !notValidRe.MatchString(content)
		},
		Message: "Add NOT VALID, then VALIDATE in a separate migration.",
	},
}

Each check has a Match function rather than a regex, allowing skip logic (e.g. indexes inside CREATE TABLE are safe). The linter reads the same embedded files as the runner:

func LintMigrations() []LintError {
	ms, err := LoadMigrations()
	if err != nil {
		return []LintError{{File: "(load)", Check: "parse", Message: err.Error()}}
	}

	var errors []LintError
	for _, m := range ms {
		filename := fmt.Sprintf("%s_%s.sql", m.Version, m.Name)
		for _, check := range DefaultChecks {
			if check.Match(m.Up) {
				errors = append(errors, LintError{
					File:    filename,
					Check:   check.Name,
					Message: check.Message,
				})
			}
		}
	}

	return errors
}

The CLI entry point:

// cmd/dblint/main.go
func main() {
	errors := db.LintMigrations()
	if len(errors) == 0 {
		fmt.Println("dblint: all migrations ok")
		return
	}
	for _, e := range errors {
		fmt.Printf("%s: %s\n  %s\n", e.File, e.Check, e.Message)
	}
	os.Exit(1)
}

Run it before committing migration changes:

go run ./cmd/dblint
# dblint: all migrations ok

Schema dump

After migrating, dump the schema for code review diffs:

func DumpSchema(databaseURL, outputPath string) error {
	cmd := exec.Command("pg_dump", "--schema-only", "--no-owner", "--no-acl", databaseURL)
	out, err := cmd.Output()
	if err != nil {
		return fmt.Errorf("pg_dump: %w", err)
	}
	if err := os.WriteFile(outputPath, out, 0644); err != nil {
		return fmt.Errorf("write %s: %w", outputPath, err)
	}
	return nil
}

This writes db/schema.sql, which is checked into version control. Reviewers see the full schema diff alongside the migration SQL.

Test helper

A test helper connects to a dedicated test database on a separate Postgres cluster, truncates tables, and cleans up after the test:

const testURL = "postgres://postgres@localhost:5433/myapp_test"

func NewTestDB(t *testing.T) *DB {
	t.Helper()

	db, err := NewDB(testURL)
	if err != nil {
		t.Skipf("test database not available: %v", err)
	}
	t.Cleanup(func() { db.Close() })

	_, err = db.Exec(context.Background(), `
		TRUNCATE
			source_projections,
			teams
		RESTART IDENTITY CASCADE
	`)
	if err != nil {
		t.Fatalf("truncate tables: %v", err)
	}

	return db
}

Tests skip gracefully when the database isn't available, so go test ./... works without Postgres running.

Database setup

A shell script creates dev and test databases and runs migrations on both:

#!/bin/bash
set -euo pipefail

createdb -p 5432 -U postgres myapp_dev 2>/dev/null || true
createdb -p 5433 -U postgres myapp_test 2>/dev/null || true

echo "Running migrations on myapp_dev..."
DATABASE_URL="postgres://postgres@localhost:5432/myapp_dev" go run ./cmd/db-migrate

echo "Dumping schema..."
DATABASE_URL="postgres://postgres@localhost:5432/myapp_dev" go run ./cmd/db-migrate dump-schema

echo "Running migrations on myapp_test..."
DATABASE_URL="postgres://postgres@localhost:5433/myapp_test" go run ./cmd/db-migrate

echo "Done."

This uses separate dev and test Postgres clusters to avoid port conflicts and test isolation.

Compared to Ruby

This is a Go port of the migration pattern from ruby / db. The main differences:

• SQL files instead of Ruby classes (no initialize, up, down methods)
• //go:embed instead of Dir glob at runtime
• Compiled regex instead of Ruby Regexp
• The migration, linter, and generator are separate CLI commands instead of Rake tasks

← All articles