Migrations and Schema Ownership

Two instances of the service start at the same moment during a rolling deploy. Both connect, both read the migrations directory, both see that version 4 has not been applied, and both apply it. One of them wins and the other exits with a duplicate key error, or worse, both get halfway through an ALTER TABLE that is not transactional and the schema is now neither version. The application code was not the problem. The problem is that two processes were allowed to decide the schema at once.

Schema changes are the one part of a service where the order of events matters more than the code that performs them. This lesson fixes the ownership question first, then builds a runner small enough to read in one sitting, then breaks it on purpose so the failure is recognisable in a log at three in the morning.

The application owns its reads and writes; a deploy step owns the schema

There is a decision to make before any code: which process is allowed to change the shape of the database. Three answers are common and only one of them survives contact with a second instance.

Running migrations at startup is defensible for a small service and it is the option this lesson implements, because the interesting engineering is the lock rather than the deployment topology. Whichever you pick, the same artifact has to be able to do both jobs: a binary that only migrates when an environment variable says so, rather than a separate one-off tool built from different code.

What does not work is init(). A function with no return value cannot report that the database is unreachable, so the failure surfaces as a panic or as a log line nobody reads, and there is no way to run the binary without triggering it. The same argument rules out a "preflight check" that creates tables if they are missing: it turns a schema change into a hidden side effect of starting a process, which is exactly the property migrations exist to remove.

Embedded files beat a directory that has to be copied

A migrations directory next to the binary is a deployment liability: the container image has to include it, the working directory has to be right, and the file that runs in staging is not necessarily the one that runs in production. embed.FS puts the SQL inside the binary, so the artifact that was tested is the artifact that migrates.

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

type migration struct {
	version    int
	name       string
	statements []string
}

func load() ([]migration, error) {
	entries, err := fs.ReadDir(migrationFiles, "migrations")
	if err != nil {
		return nil, err
	}
	var out []migration
	for _, e := range entries {
		if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
			continue
		}
		prefix, _, ok := strings.Cut(e.Name(), "_")
		if !ok {
			return nil, fmt.Errorf("migration %q has no version prefix", e.Name())
		}
		version, err := strconv.Atoi(prefix)
		if err != nil {
			return nil, fmt.Errorf("migration %q: %w", e.Name(), err)
		}
		body, err := migrationFiles.ReadFile("migrations/" + e.Name())
		if err != nil {
			return nil, err
		}
		out = append(out, migration{
			version:    version,
			name:       e.Name(),
			statements: splitStatements(string(body)),
		})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].version < out[j].version })
	return out, nil
}

The version is a number parsed from the filename, not a timestamp, and the sort is explicit. Timestamp prefixes are popular because two people adding migrations on the same branch never collide, and they are a real trade-off rather than a mistake: with numbers, the next free version is a merge conflict waiting to happen. What matters either way is that the ordering rule is written down in the directory, because the apply order is the one thing a migration runner must never get wrong.

The bookkeeping table is part of the runner. One row per applied migration, with the primary key doing the work of "applied exactly once":

CREATE TABLE IF NOT EXISTS schema_migrations (
	version    BIGINT       NOT NULL,
	name       VARCHAR(255) NOT NULL,
	applied_at DATETIME     NOT NULL,
	PRIMARY KEY (version)
) ENGINE=InnoDB;

Two instances starting together produce a duplicate key, not a warning

Here is the race, with the lock switched off on purpose. Migration 0001_create_products.sql creates a table and ends with SELECT SLEEP(1.5) to stand in for the slow migration you actually have, an index build or a backfill. Instance B starts 250 milliseconds after instance A.

--- instance-a
13:12:00 instance-a: 2 migration(s) on disk, 0 already applied
13:12:02 instance-a: applied 0001_create_products.sql
13:12:02 instance-a: applied 0002_add_slug.sql
13:12:02 instance-a: done
--- instance-b
13:12:00 instance-b: 2 migration(s) on disk, 0 already applied
13:12:02 instance-b: instance-b: applying 0001_create_products.sql: Error 1062 (23000): Duplicate entry '1' for key 'schema_migrations.PRIMARY'

Both read 0 already applied, which is the whole bug in one line. B did not re-run the CREATE TABLE, because that statement is guarded by IF NOT EXISTS and returned happily. B failed on the bookkeeping insert, one statement before it would have written a second, conflicting version row. Change the migration to something without a guard and B fails earlier with Error 1060 Duplicate column name: the same race, a different message, and in both cases the schema itself is probably fine while the process is dead. That is the good version of this failure. The bad version is a migration that is not idempotent and is not caught by a constraint, and then it runs twice on a table that had rows.

GET_LOCK holds a name on the server, not in your process

The fix is an advisory lock that both processes ask the database for, by name. In MySQL that is GET_LOCK, which is held by a session and released by RELEASE_LOCK, by the session ending, or by the server restarting. It is not transactional and it does not lock a table; it locks a string that both processes agree on.

const (
	lockName    = "go_m5_lab.migrate"
	lockTimeout = 10 // seconds GET_LOCK waits before giving up
)

func acquireLock(db *sql.DB) error {
	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()
	var got sql.NullInt64
	if err := db.QueryRowContext(ctx, "SELECT GET_LOCK(?, ?)", lockName, lockTimeout).Scan(&got); err != nil {
		return err
	}
	if !got.Valid || got.Int64 != 1 {
		return fmt.Errorf("could not take migration lock %q within %ds", lockName, lockTimeout)
	}
	return nil
}

Three details decide whether this works. GET_LOCK returns 1 when the lock was taken, 0 when the wait expired, and NULL on an error, so the scan target is sql.NullInt64 and not int. The lock is held by the session, which means the pool must not hand the connection to somebody else mid-migration, so set SetMaxOpenConns(1) for the runner, since everything it does is sequential anyway. And the timeout has to be longer than your slowest migration, because the lock is the only thing standing between a second instance and the same ALTER TABLE.

Same two processes, lock switched on:

--- instance-a
13:12:04 instance-a: 2 migration(s) on disk, 0 already applied
13:12:06 instance-a: applied 0001_create_products.sql
13:12:06 instance-a: applied 0002_add_slug.sql
13:12:06 instance-a: done
--- instance-b
13:12:06 instance-b: 2 migration(s) on disk, 2 already applied
13:12:06 instance-b: done

B waited. When it got the lock, A had already applied both migrations and recorded them, so B's re-read of schema_migrations returned 2 already applied and it did nothing. That re-read is the part people leave out: taking the lock and then applying the list you read before the lock is the same race with extra steps. The order is always lock, read, apply, release.

The wait is visible when both processes want the same name, and this run also shows the re-read doing its job. Instance A starts first and holds the lock through the slow migration; instance B starts 200 milliseconds later and waits:

--- instance-a
13:16:58 instance-a: took the migration lock "go_m5_lab.migrate" after 1ms
13:16:59 instance-a: 4 migration(s) on disk, 0 already applied
13:17:01 instance-a: applied 0001_create_products.sql
13:17:01 instance-a: applied 0002_add_slug.sql
13:17:01 instance-a: applied 0003_create_ledger.sql
13:17:01 instance-a: applied 0004_ledger_idempotency.sql
13:17:01 instance-a: done
--- instance-b
13:17:01 instance-b: took the migration lock "go_m5_lab.migrate" after 1.918s
13:17:01 instance-b: 4 migration(s) on disk, 4 already applied
13:17:01 instance-b: done

B spent 1.918 seconds inside GET_LOCK and then found four migrations applied, because it read the ledger after acquiring the lock rather than before. The only thing the lock buys you is that ordering, which is why the re-read is not an optimisation.

DDL is not transactional, so the ledger and the schema can disagree

It is tempting to wrap a migration and its bookkeeping row in one transaction. In MySQL, DDL causes an implicit commit, so the CREATE TABLE or ALTER TABLE inside your transaction is committed whether or not the transaction commits. The transaction covers the ledger row and nothing else.

That leaves one real failure mode. A migration with two statements runs the first, the second fails, and the process exits with no ledger row: the schema is half-changed and the runner will try the whole file again on the next start. This is not hypothetical. It happened while writing this lesson. The first version of the idempotency migration added idem_key VARCHAR(80) NOT NULL DEFAULT '' and then a unique index on it, and the index creation failed with Error 1062 Duplicate entry '' for key 'ledger.uq_ledger_idem' because every existing row had the default. The column stayed, the index did not, and the next attempt failed with Error 1060 Duplicate column name 'idem_key'. Cleaning that up took a manual ALTER TABLE, which is exactly the kind of intervention migrations are meant to eliminate.

Two habits come out of it. Keep each migration to one statement where you can, so a failure cannot leave a half-applied file. And treat the runner as something you have to be able to recover by hand: log the statement that failed as well as the filename, because the difference between "the file failed" and "statement two of the file failed" is the difference between re-running and repairing.

Use golang-migrate or goose unless you have a reason not to

A hand-rolled runner is about two hundred lines and it teaches the mechanics, which is why this lesson builds one. A production service should usually reach for a tool instead, and the reason is not the apply loop. It is everything around it.

golang-migrate tracks a version and a dirty flag in its own table, so a migration that died halfway is recorded as dirty and refuses to run until a human resolves it. It reads up and down files from a file:// path or an embedded source, registers MySQL as a database driver, and takes a MySQL advisory lock of its own before applying anything: the driver builds the lock name from the database and migrations table, then runs SELECT GET_LOCK(?, 10) with a hardcoded ten-second wait, releasing it with RELEASE_LOCK when it is done. Its CLI prints what it applied and how long it took:

$ migrate -path ./maintool -database "mysql://user:pass@tcp(127.0.0.1:3306)/go_m5_migrate" version
error: no migration
$ migrate -path ./maintool -database "mysql://user:pass@tcp(127.0.0.1:3306)/go_m5_migrate" up
1/u create_products (8.796583ms)
$ migrate ... version
1
$ migrate ... down 1
1/d create_products (7.554375ms)

The trade is dependency weight for behaviour you would otherwise write and test yourself. The library pulls in a driver for every database it supports unless you build the CLI with only the one you use, and its own CLI is a thin wrapper that needs to be built with the right build tag to know about MySQL at all. goose is the other common choice and offers Go migrations alongside SQL files, which is worth having when a data migration needs real logic rather than a statement; both tools lock, both record what they applied, and both are a better default than a file in your repository that three people have edited since the last deploy.

if err := exec(db, createMigrationsTable); err != nil {
	return err
}

if useLock {
	t0 := time.Now()
	if err := acquireLock(db); err != nil {
		return err
	}
	defer func() {
		if err := releaseLock(db); err != nil {
			log.Printf("releasing the lock failed: %v", err)
		}
	}()
	log.Printf("took the migration lock after %v", time.Since(t0).Round(time.Millisecond))
}

// Read the ledger after the lock, never before it.
migrations, err := load()
done, err := applied(db)
...
for _, m := range migrations {
	if done[m.version] {
		continue
	}
	if err := apply(ctx, db, m); err != nil {
		return fmt.Errorf("applying %s: %w", m.name, err)
	}
}