Dan's profile photo
GitHub icon LinkedIn icon

go / migrate

updated May 03, 2026

I use plain SQL migration files in db/migrate/, applied by a small Go runner. No ORM, no DSL, no external migration tool. Each version is a pair of files (*.up.sql + optional *.down.sql) that own their own BEGIN/COMMIT. The runner sends each file as one simple-query batch.

Migration files

Each version lives as a pair under db/migrate/:

db/migrate/20260430165330_clear_bad_ai_tam_estimates.up.sql
db/migrate/20260430165330_clear_bad_ai_tam_estimates.down.sql

The .down.sql is optional. Drop it to mark a migration non-reversible.

A transactional migration wraps statements in BEGIN/COMMIT:

BEGIN;

DELETE FROM tam_estimates
WHERE updated_by_user_id = 1 AND tam_usd >= 200000000000;

COMMIT;

A CREATE INDEX CONCURRENTLY migration omits BEGIN/COMMIT and sets lock_timeout = '0' on the first line:

SET lock_timeout = '0';
CREATE INDEX CONCURRENTLY index_people_on_email
  ON people (email);

The SQL file owns its transaction stance. The runner doesn't wrap and doesn't branch.

Generator

A genmigration subcommand scaffolds the file pair:

go run ./cmd/db genmigration add_status_to_jobs
go run ./cmd/db genmigration --concurrent people email

The transactional form scaffolds a BEGIN/COMMIT skeleton. The --concurrent <table> <column> form prefills CREATE INDEX CONCURRENTLY with the index name index_<table>_on_<column>, since that's the dominant non-transactional case in practice.

const (
	transactionalUpScaffold = `BEGIN;

-- write your changes here

COMMIT;
`
	transactionalDownScaffold = `BEGIN;

-- reverse of the up file; delete this file if not reversible

COMMIT;
`
)

var (
	concurrentUpScaffold = template.Must(template.New("up").Parse(
		`-- One statement only. SET lock_timeout = 0 so CREATE INDEX
-- CONCURRENTLY can complete its multi-phase lock acquisition.
SET lock_timeout = '0';
CREATE INDEX CONCURRENTLY index_{{.Table}}_on_{{.Column}} ON {{.Table}} ({{.Column}});
`))
	concurrentDownScaffold = template.Must(template.New("down").Parse(
		`SET lock_timeout = '0';
DROP INDEX CONCURRENTLY IF EXISTS index_{{.Table}}_on_{{.Column}};
`))
)

Runner

The runner reads files from disk at startup. No //go:embed: deploys ship the binary plus the migration directory. (Embedding trades that for one fewer thing to deploy; either is fine: swap os.ReadFile/os.ReadDir for embed.FS.ReadFile/ReadDir.)

const (
	migrateDir   = "db/migrate"
	upSuffix     = ".up.sql"
	downSuffix   = ".down.sql"
	stampLen     = 14
	undefinedTbl = "42P01" // PostgreSQL undefined_table
)

type migration struct {
	upPath   string
	downPath string
}

runMigrate lints first, opens a dedicated connection, sets safety timeouts, and applies pending migrations:

func runMigrate(ctx context.Context) error {
	if err := lintBeforeRun(); err != nil {
		return err
	}
	conn, err := connectMigrate(ctx)
	if err != nil {
		return err
	}
	defer conn.Close(ctx)

	if err := setMigrateTimeouts(ctx, conn); err != nil {
		return err
	}

	files, err := loadMigrations(migrateDir)
	if err != nil {
		return err
	}
	applied, err := appliedVersions(ctx, conn)
	if err != nil {
		return err
	}

	for _, ver := range pendingVersions(files, applied) {
		m := files[ver]
		sql, err := os.ReadFile(m.upPath)
		if err != nil {
			return fmt.Errorf("read %s: %w", m.upPath, err)
		}
		if err := applyMigration(ctx, conn, injectBookkeeping(string(sql), ver, false)); err != nil {
			return fmt.Errorf("migrate %s: %w", ver, err)
		}
		// Reset session-scoped GUCs the migration may have changed
		// (most commonly `SET lock_timeout = '0';` in CONCURRENTLY).
		if err := setMigrateTimeouts(ctx, conn); err != nil {
			return fmt.Errorf("reset timeouts after %s: %w", ver, err)
		}
		fmt.Printf("pg migrated %s\n", ver)
	}

	return dumpSchema()
}

Three details worth calling out:

Dedicated connection. Use pgx.Connect(ctx, url) directly, not a pool. Migrations rely on session-level SET commands (lock_timeout, statement_timeout) persisting across the whole run; pools can't guarantee that.

Simple-query batch. Send the file as one batch via conn.PgConn().Exec(ctx, sql).ReadAll(). The pgx extended protocol creates an implicit transaction per query, which breaks CONCURRENTLY. The simple protocol just hands the string to Postgres and reads results back.

func applyMigration(ctx context.Context, conn *pgx.Conn, sql string) error {
	if strings.TrimSpace(sql) == "" {
		return nil
	}
	_, err := conn.PgConn().Exec(ctx, sql).ReadAll()
	return err
}

Reset timeouts between migrations. A CONCURRENTLY migration sets lock_timeout = '0' to disable the safety net. Without re-applying the default after the migration runs, a subsequent transactional migration on the same connection silently runs without the 10s timeout.

func setMigrateTimeouts(ctx context.Context, conn *pgx.Conn) error {
	if _, err := conn.Exec(ctx, "SET lock_timeout = '10s'"); err != nil {
		return fmt.Errorf("set lock_timeout: %w", err)
	}
	if _, err := conn.Exec(ctx, "SET statement_timeout = '10min'"); err != nil {
		return fmt.Errorf("set statement_timeout: %w", err)
	}
	return nil
}

Bookkeeping

The runner records each applied version in schema_migrations (create the table in your first migration with version text PRIMARY KEY). For transactional files, splice the INSERT before the final COMMIT; so the migration and its bookkeeping commit atomically:

var commitLineRE = regexp.MustCompile(`(?im)^\s*COMMIT\s*;\s*$`)

// injectBookkeeping splices an INSERT (or DELETE on rollback)
// onto the line above the file's final COMMIT;, so the
// bookkeeping rides the migration's transaction.
func injectBookkeeping(sql, version string, rollback bool) string {
	var stmt string
	if rollback {
		stmt = fmt.Sprintf("DELETE FROM schema_migrations WHERE version = '%s';", version)
	} else {
		stmt = fmt.Sprintf("INSERT INTO schema_migrations (version) VALUES ('%s');", version)
	}

	locs := commitLineRE.FindAllStringIndex(sql, -1)
	if len(locs) == 0 {
		// CONCURRENTLY file: no COMMIT to splice into; append.
		// Bookkeeping then runs auto-committed, leaving a small
		// race where a crash between the index build and the
		// INSERT leaves the version unrecorded. Recovery is
		// manual.
		if strings.TrimSpace(sql) == "" {
			return stmt + "\n"
		}
		return strings.TrimRight(sql, "\n") + "\n" + stmt + "\n"
	}
	last := locs[len(locs)-1]
	return sql[:last[0]] + stmt + "\n" + sql[last[0]:]
}

version is a validated 14-digit timestamp (see splitFilename below), so string interpolation here is safe.

Connection guard

A small belt-and-suspenders guard refuses to run migrations against a non-localhost host unless APP_ENV names a deployed environment:

func guardConnectionTarget(dsn, appEnv string) error {
	cfg, err := pgx.ParseConfig(dsn)
	if err != nil {
		return fmt.Errorf("parse DATABASE_URL: %w", err)
	}
	switch cfg.Host {
	case "localhost", "127.0.0.1", "::1":
		return nil
	}
	switch appEnv {
	case "production", "staging":
		return nil
	}
	return fmt.Errorf("refusing to run migrations against non-localhost host %q. "+
		"Set APP_ENV=production or APP_ENV=staging to name the deployed environment",
		cfg.Host)
}

The blast radius of an accidental production migration run is the whole production database. Cheap explicit guard.

File parsing

Filenames must be <14-digit-stamp>_<name>.{up,down}.sql. Anything else (READMEs, stray editor files) is ignored:

func splitFilename(name string) (version, direction string, ok bool) {
	var suffix string
	switch {
	case strings.HasSuffix(name, upSuffix):
		suffix, direction = upSuffix, "up"
	case strings.HasSuffix(name, downSuffix):
		suffix, direction = downSuffix, "down"
	default:
		return "", "", false
	}
	base := strings.TrimSuffix(name, suffix)
	if len(base) <= stampLen+1 || base[stampLen] != '_' {
		return "", "", false
	}
	for i := 0; i < stampLen; i++ {
		if c := base[i]; c < '0' || c > '9' {
			return "", "", false
		}
	}
	return base[:stampLen], direction, true
}

A version with only a .down.sql (no .up.sql) is dropped during load. It can never be applied or rolled back coherently.

Linter

migrate and rollback lint the migration directory before opening any connection. There's no separate lint subcommand; unsafe migrations never reach the database in the first place.

Rules apply at the file level. Each rule has a pattern regex (the unsafe shape), an optional exclude regex (a paired safe pattern that lets the file pass), and an optional skipIfCreateTable flag (greenfield indexes and FKs in CREATE TABLE files are safe):

type lintRule struct {
	name              string
	pattern           *regexp.Regexp
	exclude           *regexp.Regexp
	skipIfCreateTable bool
	message           string
}

var lintRules = []lintRule{
	{
		name:              "non-concurrent-index",
		pattern:           regexp.MustCompile(`(?i)CREATE\s+(UNIQUE\s+)?INDEX\s+`),
		exclude:           regexp.MustCompile(`(?i)CREATE\s+(UNIQUE\s+)?INDEX\s+CONCURRENTLY`),
		skipIfCreateTable: true,
		message: "CREATE INDEX without CONCURRENTLY blocks writes. " +
			"Use CREATE INDEX CONCURRENTLY in a separate non-transactional file.",
	},
	{
		name:              "fk-without-not-valid",
		pattern:           regexp.MustCompile(`(?i)REFERENCES\s+\w+\s*\([^)]+\)`),
		exclude:           regexp.MustCompile(`(?i)NOT\s+VALID`),
		skipIfCreateTable: true,
		message: "REFERENCES without NOT VALID blocks writes on both tables. " +
			"Add NOT VALID, then VALIDATE in a separate migration.",
	},
	{
		name:    "set-not-null",
		pattern: regexp.MustCompile(`(?i)ALTER\s+(TABLE\s+\w+\s+)?ALTER\s+(COLUMN\s+)?\w+\s+SET\s+NOT\s+NULL`),
		message: "SET NOT NULL blocks reads/writes while checking every row. " +
			"Use a CHECK constraint with NOT VALID instead.",
	},
	{
		name:    "check-without-not-valid",
		pattern: regexp.MustCompile(`(?i)ADD\s+CONSTRAINT\s+\w+\s+CHECK\s*\([^)]+\)`),
		exclude: regexp.MustCompile(`(?i)NOT\s+VALID`),
		message: "CHECK constraint without NOT VALID blocks reads/writes. " +
			"Add NOT VALID, then VALIDATE in a separate migration.",
	},
}

See postgres / safe migrations for why each pattern is unsafe.

Strip comments before matching

Match against content with -- line comments stripped, so -- TODO: switch to CREATE INDEX CONCURRENTLY doesn't make the file appear to use CONCURRENTLY when it doesn't:

// stripLineComments replaces every `--` line comment with
// spaces of equal length, preserving newlines and overall
// byte length so line numbers and offsets line up with the
// original content.
func stripLineComments(s string) string {
	var b strings.Builder
	b.Grow(len(s))
	i := 0
	for i < len(s) {
		if i+1 < len(s) && s[i] == '-' && s[i+1] == '-' {
			for i < len(s) && s[i] != '\n' {
				b.WriteByte(' ')
				i++
			}
			continue
		}
		b.WriteByte(s[i])
		i++
	}
	return b.String()
}

Equal-length replacement keeps line numbers in error messages aligned with the original file.

File-level coherence checks

Beyond per-rule pattern matching, two file-wide checks on .up.sql:

hasBegin := beginRE.MatchString(stripped)
hasCommit := commitRE.MatchString(stripped)
switch {
case hasConcurrently && (hasBegin || hasCommit):
	// CONCURRENTLY can't run inside a transaction. Hard error.
case !hasConcurrently && !hasCreateTable && (!hasBegin || !hasCommit):
	// .up.sql without CREATE TABLE should wrap in BEGIN/COMMIT.
	// Warning, not error.
}

The first is a hard error: PostgreSQL rejects CONCURRENTLY inside a transaction block. The second is a warning that catches missing transaction wrappers.

CLI

One binary with subcommands:

go run ./cmd/db migrate              # apply pending
go run ./cmd/db rollback [steps]     # revert last N (default 1)
go run ./cmd/db genmigration <name>
go run ./cmd/db genmigration --concurrent <table> <column>

migrate and rollback lint first; genmigration writes files and stops. There's no separate dblint because lint is part of migrate/rollback.

Schema dump

After every successful migrate/rollback in development, regenerate db/schema.sql:

func dumpSchema() error {
	if env.String("APP_ENV", "development") != "development" {
		return nil
	}
	url := env.String("DATABASE_URL", defaultDatabaseURL)

	out, err := os.Create(filepath.Join("db", "schema.sql"))
	if err != nil {
		return fmt.Errorf("create db/schema.sql: %w", err)
	}
	defer out.Close()

	cmd := exec.Command("pg_dump", "--schema-only", "--no-owner", "--no-acl", url)
	cmd.Stdout = out
	cmd.Stderr = os.Stderr
	return cmd.Run()
}

db/schema.sql is checked in. Reviewers see the full schema diff alongside the migration SQL in PRs.

When to use

When not to use

← All articles