Migrations as Versioned Scripts

Somebody adds a column with a client, forgets, and the next person deploys a build that needs it. The error arrives in production: Unknown column 'status' in 'where clause'. Nobody can say which environment has the column, or in what order the changes were supposed to land.

The fix is not discipline. Discipline fails the first time two people deploy in the same hour. The fix is to treat the schema as versioned state: every change is a file with a name, every file that has run is recorded in the database, and starting the application runs whatever is missing. After that, "which columns does production have" has an answer you can query instead of an answer you remember.

The schema is state, so it needs a version and a record

Application code carries its version in the artefact you deploy. The schema has no such thing unless you build one. Two pieces make it work: a directory of numbered files, and a table inside the same database that lists the ones already applied.

CREATE TABLE schema_migrations (
  version    TEXT PRIMARY KEY,
  checksum   TEXT NOT NULL,
  applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Three columns earn their place. version is the filename stem, so it sorts and it is unique. checksum is what lets the runner notice that a file it already ran has been edited since. applied_at is the column you will read at two in the morning when you are working out whether a change landed before or after a problem started.

The table goes in the database rather than in a file beside the application, because one database is usually shared by several deploy targets, and because the record has to move atomically with the change it describes. A file can be written after a change succeeds but before the process dies, and then you have a schema that is ahead of its own bookkeeping.

One file per change, named so that order is not an opinion

A migration is one file, one change, named with a zero-padded sequence:

migrations/
  001-create-orders.sql
  002-add-orders-region.sql
  003-orders-region-not-null.sql

The number is the order, and nothing else decides it. That matters more than it sounds. If migrations were applied in directory-listing order, the result would depend on the filesystem, and the same repository would produce different schemas on different machines. Zero padding keeps 010 after 009 in a plain string sort, which is the trap that catches everybody once.

"One change" is doing quiet work in that sentence too. A migration with four unrelated statements is a migration you cannot revert in part, cannot reason about from its name, and cannot apply to a database that already has three of the four. Split it.

What the runner does, and what it refuses to do

Here is the whole decision procedure in SQLite, using the standard library's sqlite3 module. The directory, the table and the files from the previous two sections are the only inputs:

import glob, os, sqlite3, zlib

def checksum(path):
    with open(path, "rb") as fh:
        return "%08x" % (zlib.crc32(fh.read()) & 0xFFFFFFFF)

def migrate(con, files):
    applied = dict(con.execute("SELECT version, checksum FROM schema_migrations"))
    ran = []
    for path in files:
        version, cs = os.path.basename(path)[:-4], checksum(path)
        if version in applied:
            if applied[version] != cs:
                raise RuntimeError(
                    f"{version} was already applied with checksum {applied[version]} "
                    f"but the file now hashes to {cs}")
            continue
        with con:                                # one transaction per migration
            con.executescript(open(path).read())
            con.execute("INSERT INTO schema_migrations (version, checksum) VALUES (?,?)",
                        (version, cs))
        ran.append(version)
    return ran

Run against three databases that have deliberately diverged, a fresh one, one where the second migration never landed, and one where a file was edited after it ran:

files on disk and their checksums:
   001-create-orders.sql b41e29ed
   002-add-orders-region.sql a6936266
   003-orders-region-not-null.sql 3b173fe7

fresh.db  ran: ['001-create-orders', '002-add-orders-region', '003-orders-region-not-null']
fresh.db  ran again: []
partial.db ran: ['002-add-orders-region', '003-orders-region-not-null']
drifted.db, after editing 002:
   002-add-orders-region.sql 02346ec3
  refused: 002-add-orders-region was already applied with checksum a6936266 but the file now hashes to 02346ec3

The second line is the one people forget to check. A runner that is correct on a fresh database and wrong the second time it runs is worse than no runner, because it will quietly try to create tables that exist. The fresh database ran nothing on the second pass, which is the property that makes the migration set idempotent as a whole.

An edited migration is a different migration

Editing 002 in place is the mistake this design refuses to absorb. Your laptop applied the old version, production applied the old version, and the file now describes something else. Nothing about the SQL is wrong; the problem is that the file is no longer a description of what happened.

The checksum is a short string, and short is the point: it is a collision detector for accidents, not a security control. CRC-32 survives a truncated file, a lost WHERE clause and a reordered statement, which are the accidents that occur. If you want the stronger guarantee, use SHA-256 from hashlib; there is no reason to be clever here, and the column is text either way.

When the check fires, the repair is a new migration, never a force flag. The old file stays exactly as it was applied, because it is now a historical record. The runner prints the two checksums so you can see which file moved.

A real rollback is usually a second migration

Migration tools offer a down direction, and the offer is a trap in production. Applying the inverse of a change does not restore the previous state, and the reason is always the same: data.

Drop a column in the up direction and there is nothing for the down to restore but an empty column. A backfill that set region = 'IE' cannot be reversed, because the values that were there before are gone. A migration that narrowed a type is lossy for the same reason. So the honest answer to "how do I roll this back" is: write a new migration that moves the schema forward to the state you want, and keep the numbered history intact.

That is not an argument against the down direction. It is useful on a developer machine and in a test suite, where the data is disposable. It is not a production plan, and a release process that assumes it is one will fail at the moment it is needed.

A migration has to be safe against the schema that already exists

A migration verified against an empty database has been verified against the one environment it will never meet again. The production table has rows, locks, replication and a second version of the application running while you deploy. Three habits follow from that.

Write the change so it works whether or not the previous state is what you expect: DROP TABLE IF EXISTS, ADD COLUMN IF NOT EXISTS where the engine supports it, and a lookup against schema_migrations in production before the deploy. Assume the table is not empty, because it is not. And expect the migration to run while an older build of the application is still serving requests, which is the subject of the next lesson.

MySQL adds a mechanical wrinkle that has bitten every runner written for it. DDL statements cause an implicit commit:

BEGIN;
INSERT INTO t VALUES (1);
CREATE TABLE later (id INT PRIMARY KEY);   -- commits here
INSERT INTO t VALUES (2);
ROLLBACK;

SELECT COUNT(*) AS rows_after_rollback FROM t;
+---------------------+
| rows_after_rollback |
+---------------------+
|                   2 |
+---------------------+

Both rows survived a ROLLBACK, because CREATE TABLE committed the transaction that surrounded it. A multi-statement migration on MySQL is not one unit of work, so a failure halfway through leaves the database in an intermediate state. This is the strongest argument for one change per file, and it is why the runner above records the version in the same transaction as the change: on MySQL that transaction has already ended by the time the DDL returns, so the record and the schema can only be kept together by keeping each migration down to one DDL statement.

The application asks the database what to run at boot

Running migrations on boot is the simplest deployment that works. The process connects, reads the applied versions, runs what is missing in order, and starts serving. Two variants are common. In the first, the application connects with a normal account and checks that everything is applied, then refuses to start if a migration is pending; a separate step runs them. In the second, the application runs them itself under a lock so that two replicas starting at once cannot both apply the same file.

The lock is not optional, and it is cheap to add. MySQL gives you GET_LOCK(name, timeout), PostgreSQL has advisory locks, and SQLite takes an exclusive lock on the first write in a transaction. The window is small, two replicas starting within a second of each other, and the failure is a duplicate table error that crashes one replica at exactly the moment you are deploying. Take the lock.

CREATE TABLE IF NOT EXISTS audit_log (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event     VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);