How a Migration Runner Records and Orders Applied Versions

A schema change applied by hand is an event. A schema change applied by a runner is a state transition, and the difference between the two is a single table that records what has already happened. Every migration tool, whatever its file format or command name, is organised around that table: it reads the set of files it can see, subtracts the set the table says are applied, and executes the remainder in order. Remove the table and the runner has no way to distinguish a fresh database from one that was built six months ago and has drifted.

The interesting engineering is not the file parsing. It is the ordering rule, the identity of a migration, and the concurrency control that keeps two processes from applying the same change twice.

The ledger row is the unit of state

A minimal ledger carries four things per applied migration: an identifier, a checksum of the file contents, a timestamp of application, and enough metadata to tell a human which script ran. The identifier is normally the file name prefix, and the ordering is normally lexicographic over that prefix, which is why runners use either a zero-padded sequence number or a UTC timestamp.

CREATE TABLE schema_migrations (
    version     varchar(64)  NOT NULL PRIMARY KEY,
    checksum    char(64)     NOT NULL,
    applied_at  timestamptz  NOT NULL DEFAULT now(),
    execution_ms integer     NOT NULL
);

The primary key does real work. It makes a second application of the same version fail loudly rather than silently duplicating an index or a column, and it makes the runner’s decision idempotent at the storage layer rather than only in application code. A runner that instead does SELECT count(*) against a version string and then inserts is relying on a read-modify-write that a concurrent process can interleave with.

Timestamp-prefixed versions buy collision resistance across branches at the cost of readability. A version like 20250302143012_add_invoice_status sorts correctly under plain string comparison because the timestamp is fixed width, and two developers working in parallel almost never generate the same second. Sequence numbers are easier to read and guarantee a total order, but two branches that both take the number 0042 produce a conflict that the ledger cannot resolve on its own: the primary key rejects the second file, and a human has to renumber it.

Ordering, and why out-of-order application diverges

The runner applies pending migrations in version order, not in the order they arrived in the repository. That distinction matters when a long-lived branch merges a migration numbered below migrations already applied on the main line. Three policies exist and each has a different failure shape.

Strict ordering refuses to run: a pending migration with a version lower than the highest applied version is an error. This is the safest policy, because the schema at any commit is a deterministic function of the file set, and a database built from scratch and a database migrated forward end in the same state. The cost is that the developer must rebase and renumber, which is a merge conflict in a file name.

Allow-out-of-order applies the lower version anyway, appending a row whose version sorts before existing rows. The resulting schema is no longer a function of the file list alone; it depends on the history of merges. Two environments can then hold the same set of files and different schemas, because one applied migration 42 before 40 and the other after. Since migrations routinely assume their predecessors’ effects, as when a backfill reads a column added three files earlier, out-of-order application can produce a schema that is merely different, or a migration that fails on one environment and not another.

Rebase-and-renumber keeps strict ordering and pushes the conflict into the developer’s hands at merge time. It is the policy most production runners default to, and it is the reason migration file names are treated as append-only.

What the checksum protects

The checksum exists to detect edits to files that have already run. Once a migration is applied somewhere that matters, the file is history: its text must not change, because the database that ran it is evidence of the old text and any database that has not yet run it will execute the new text. Two environments then diverge while both report “up to date”.

A runner compares the stored checksum with the current file on every run and fails on a mismatch. The alternative, silently updating the checksum, destroys the only signal that the file set is not reproducible. The correct repair is a new migration that reverses or amends the change, never an edit to the old one.

The subtle case is formatting. A checksum over raw bytes changes when a line ending changes, when a formatter rewrites whitespace, or when the file is checked out with different line endings on different platforms. Runners that normalise line endings before hashing avoid a class of false positives; runners that hash raw bytes require a .gitattributes rule pinning the files to a single ending. Either choice is defensible, but the choice must be made once, because switching the normalisation rule invalidates every stored checksum at once.

Two runners, one lock

A ledger row is written after the migration executes, so between the decision to apply and the insert there is a window in which a second process can make the same decision. A rollout that starts several application instances simultaneously, each running migrations at boot, hits this window routinely.

The fix is a database-level lock held for the duration of the run. PostgreSQL’s advisory locks are the common choice because they are named, session-scoped, and require no table:

SELECT pg_advisory_lock(7214512);   -- arbitrary but constant application key

A second runner blocks on the same call until the first releases, then re-reads the ledger and finds nothing pending. The lock is a mutex over the ledger, not over the schema; it does not stop ordinary queries, only other runners.

MySQL provides GET_LOCK, a named lock scoped to a session, which plays the same role: the runner calls it with a fixed name and blocks until it is granted. A row lock on a dedicated single-row table is a portable substitute for engines without a named lock primitive. In every case the lock must be released on every exit path, including failure, or the next rollout blocks indefinitely; session-scoped locks released by disconnection are the reason to prefer them over locks recorded in a table, which survive a client crash.

The failure mode when the lock is missing is not a duplicate column error in the obvious case. It is two transactions both reading “not applied”, both running the DDL, and one of them waiting on the other’s lock inside the DDL itself, which is exactly the lock queue behaviour that makes DDL dangerous. One of the two then fails after the other commits, and whether the failure leaves the schema consistent depends entirely on the next section.

Where the transaction boundary fails

The clean model is one transaction per migration: begin, execute every statement in the file, insert the ledger row, commit. Either the schema change and its record appear together or neither does. This is achievable in PostgreSQL, SQLite, and SQL Server, all of which support transactional DDL.

It is not achievable in MySQL. Every DDL statement implicitly commits the open transaction before it runs and starts a new one afterwards, so a file with three statements can leave the first two applied when the third fails, with no ledger row. MySQL 8.0’s atomic DDL makes each individual statement crash-safe, in that the data dictionary, storage engine, and binary log agree on the outcome of one statement, but it does not make a group of statements rollback-able. Runners handle this by recording a “failed” or “dirty” state instead of a clean version, and by requiring manual intervention before the next run.

The practical consequence is that migration files should be written as if each could be interrupted between statements: one logical change per file, statements ordered so that a prefix of the file is a valid schema, and no file that depends on a statement later in the same file having succeeded. This also reduces the damage from a long-running change, since the metadata-only versus rewriting distinction determines how long each statement holds its lock.

The complementary discipline is deploy ordering. A migration that drops a column will break an application version that still reads it, and the ledger cannot express that dependency at all: it knows only about the database. Coordinating the two sides is the subject of expand and contract schema changes, and it is the reason the ledger records timestamps rather than only versions: the gap between a migration’s application time and the deploy that depends on it is the window in which both schema shapes must work.

What the ledger cannot establish

The table records what ran, not whether the database matches the files. A migration applied by hand and then recorded, a restored dump from an environment that never ran a migration, or a migration whose statements partially executed outside a transaction all produce a ledger that claims completeness. Runners that support a schema-diff mode compare the live catalogue against the accumulated file set and report the difference, which is the only check that catches these cases.

The second limit is that the ledger is a single linear history. Branching schemas, per-tenant databases at different versions, and long-lived feature branches all need either a version column per database or an explicit compatibility window. A single ledger per database is a sound design precisely because it makes those cases visible as operational problems rather than hiding them.