Versioned, Idempotent Migrations

ApplyMigrations from the last lesson has one flaw you'll hit the first time you actually use it: run it twice and the second CREATE TABLE fails, because the table from the first run is still there. In production this happens constantly — a deploy retries, two instances start at once, someone reruns the entrypoint by hand — and "the migrator can't tell what it already did" turns a routine restart into an incident.

The tempting fix is CREATE TABLE IF NOT EXISTS on everything. Don't. It doesn't make the migrator idempotent — it makes failure silent. A migration that's supposed to add a column will still error on the second run (IF NOT EXISTS doesn't exist for ALTER TABLE ADD COLUMN in most dialects), and one that's supposed to fail loudly because the schema drifted now just... does nothing, and you find out later. IF NOT EXISTS answers "does this object exist," which is not the question a migrator needs answered. The question is "which migrations, by position, have already run against this database."

Track the version, not the state

db.Exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER NOT NULL)`)

One table, one row, one integer: how far through the list this database has gotten. Every other migration in the project can now be written normally — plain CREATE TABLE, plain ALTER TABLE, no defensive IF NOT EXISTS anywhere — because the migrator itself decides whether to run them, by comparing the stored version against the length of the slice. Migrations 0..version-1 already happened; run version..len-1.

Record progress as you go, not at the end

The version updates after each successful migration, not once at the very end:

if _, err := db.Exec(migrations[version]); err != nil {
	return applied, fmt.Errorf("migration %d: %w", version+1, err)
}
version++
db.Exec(`UPDATE schema_migrations SET version = ?`, version)

This is what makes a failure recoverable instead of stuck. If migration 4 of 6 fails, migrations 1–3 are already recorded as applied — fixing migration 4 and rerunning starts at 4, not at 1, and doesn't re-attempt the three CREATE TABLEs that would now fail as duplicates. Batch the bookkeeping to "update once at the end" instead, and a partial failure leaves you with a half-migrated schema and a version number claiming none of it happened — worse than no tracking at all, because now you have to figure out by hand what actually ran.

Try it

Implement MigrateUp. It applies whichever migrations, by position, this database hasn't already recorded, records progress as each one succeeds, and returns how many it newly applied.