Migrations

Keep schema changes as numbered, forward-only steps recorded in the database, and run them as a deploy step rather than at boot.

SyntaxCREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)

Your code expects certain tables and columns. Those have to be created somewhere, and "somewhere" cannot be a command a person remembers to run. A migration is one numbered step that changes the schema, stored in your repository next to the code that needs it.

Numbered and forward-only

Each step gets a number: 0001_create_users.sql, 0002_add_users_name.sql. The database holds a small table recording which numbers have been applied. On each run you apply the steps above that mark, in order, and record each one as you go. Running it twice is then harmless, which is what makes it safe to run automatically.

Two rules keep this working. A released migration is never edited: the databases that already ran it will not run it again, so an edit only changes what new environments get. And prefer forward-only: a "down" migration that drops a column is rarely what you want during an incident, because the data is already gone. Fix a bad migration with another migration.

Run them on deploy, not at boot

It is tempting to migrate in main before the server starts. With more than one replica, several processes then race on the same schema, and a slow ALTER TABLE delays every pod's readiness. Run migrations as their own step: a job that finishes before the new version rolls out. If you do migrate from the application, take a database lock first so only one process proceeds.

Rolling deploys need additive steps

During a rollout the old and new code run at once, so each step must suit both. Add a nullable column, deploy code that writes it, backfill, and only then make it NOT NULL. Renaming a column in one step breaks whichever version is not yet replaced.

Example

Example · go
package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "modernc.org/sqlite"
)

// One entry per version, in order. Entries are never edited or reordered
// once they have shipped, because other databases have already applied them.
var migrations = []string{
	`CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL)`,
	`ALTER TABLE users ADD COLUMN name TEXT`,
}

// migrate applies whatever is missing. Running it again does nothing.
func migrate(db *sql.DB) error {
	if _, err := db.Exec(`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)`); err != nil {
		return fmt.Errorf("create version table: %w", err)
	}

	var current int
	// COALESCE turns "no rows yet" into 0, so a fresh database starts at step 1.
	if err := db.QueryRow(`SELECT COALESCE(MAX(version), 0) FROM schema_version`).Scan(&current); err != nil {
		return fmt.Errorf("read version: %w", err)
	}

	for i := current; i < len(migrations); i++ {
		if _, err := db.Exec(migrations[i]); err != nil {
			return fmt.Errorf("migration %d: %w", i+1, err)
		}
		if _, err := db.Exec(`INSERT INTO schema_version (version) VALUES (?)`, i+1); err != nil {
			return fmt.Errorf("record version %d: %w", i+1, err)
		}
	}
	return nil
}

func main() {
	db, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.SetMaxOpenConns(1) // one connection, so every statement sees the same in-memory database

	if err := migrate(db); err != nil { // applies both steps
		log.Fatal(err)
	}
	if err := migrate(db); err != nil { // nothing left to apply
		log.Fatal(err)
	}

	var version int
	if err := db.QueryRow(`SELECT MAX(version) FROM schema_version`).Scan(&version); err != nil {
		log.Fatal(err)
	}
	fmt.Println("schema version:", version)
}

// Output:
// schema version: 2

When to use it

  • Create the schema for a new service in a way that CI, a laptop and production all reach the same state.
  • Add a column without an outage while a rolling deploy has two versions of the code running.
  • Make a repeated or retried deploy job safe by recording applied versions and locking.

More examples

Migrations as .sql files compiled into the binary

SQL belongs in .sql files that a database reviewer can read, and embed puts them inside the binary so there is no directory to copy into the container. The zero padding is not decoration: without it, 10 sorts before 9 in a plain string comparison, so step 10 would be applied before step 9.

Example · go
package migrate

import (
	"embed"
	"fmt"
	"io/fs"
	"sort"
)

// The files live in migrations/ next to this file:
//
//	0001_create_users.sql
//	0002_add_users_name.sql
//
//go:embed migrations/*.sql
var files embed.FS

// Steps returns the migration bodies in filename order. Zero-padded numbers mean
// plain string sorting is the right order; 10 would sort before 9 without them.
func Steps() ([]string, error) {
	names, err := fs.Glob(files, "migrations/*.sql")
	if err != nil {
		return nil, err
	}
	sort.Strings(names)

	steps := make([]string, 0, len(names))
	for _, name := range names {
		body, err := fs.ReadFile(files, name)
		if err != nil {
			return nil, fmt.Errorf("read %s: %w", name, err)
		}
		steps = append(steps, string(body))
	}
	return steps, nil
}

A separate command, run as a deploy step

A separate binary means migrating is a deliberate step in the pipeline, not a side effect of starting the server. The advisory lock makes a retried or duplicated job safe: the second copy blocks until the first finishes instead of running the same ALTER twice.

Example · go
package main

import (
	"context"
	"database/sql"
	"log"
	"os"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

// cmd/migrate: its own binary, run before the new server version rolls out.
func main() {
	db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Minute)
	defer cancel()

	// Only one holder at a time, cluster-wide. A second copy of this job waits here.
	// The lock is released when this connection closes.
	if _, err := db.ExecContext(ctx, "SELECT pg_advisory_lock($1)", 20240601); err != nil {
		log.Fatal(err)
	}

	if err := run(ctx, db); err != nil { // applies the pending steps
		log.Fatal(err)
	}
	log.Println("migrations applied")
}

Renaming a column across three deploys

The single statement everyone reaches for first, ALTER TABLE users RENAME COLUMN name TO full_name, breaks every replica still running the old code during the rollout. Splitting it across three deploys means both versions always see a schema they can work with. Big backfills are usually done in batches rather than one UPDATE.

Example · sql
-- 0007_add_full_name.sql
-- Step 1: additive only. Old code ignores the new column; new code writes both.
ALTER TABLE users ADD COLUMN full_name TEXT;

-- 0008_backfill_full_name.sql
-- Step 2: after every replica runs code that writes full_name.
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- 0009_drop_name.sql
-- Step 3: only once no running version reads name any more.
ALTER TABLE users DROP COLUMN name;

Related

go sql basicsgo embedgo k8s manifestsgo health checksgo ci

Discussion

  • Be the first to comment on this lesson.