Schema Migrations System Design

The schema changes constantly early on and keeps changing forever. A new column, a new table, an index somebody needed at three in the morning. Every one of those changes has to be applied to a database that already holds data, in an order, on every machine that runs the application, and in production exactly once.

The usual answer is a directory of numbered migration files, each with an up and a down. It works, and it costs: every model change needs a hand-written SQL file that duplicates what the model already says, the two drift, and the down script is written by somebody who will never run it and is therefore usually wrong.

Grit derives the schema from the models with GORM AutoMigrate instead, which removes the duplication and the drift. That creates a different problem. With no up script there is nothing to write a down script against, so a bad run looks unrecoverable. It is not, because of a fact about AutoMigrate that can be used: it only ever adds. It creates a table, adds a column, builds an index. It never drops a column. The reverse of a run does not have to be written, it can be computed.

The system has to be able to:

• Apply the schema the models describe to a database at any prior state.
• Be safe to run twice: the second run is a no-op, not an error.
• Record what each run actually changed, rather than what it was asked to change.
• Undo a run by dropping exactly what that run added, newest first.
• Refuse to undo the run that built the schema in the first place.
• Never drop data without printing what will be lost and being told to go ahead.
• Work the same on SQLite, Postgres and MySQL.

Functional requirements

• A migrate command that applies the models to the database.
• Idempotent runs: nothing to do means nothing done and an exit code of zero.
• A schema snapshot taken before and after each run.
• Per-run history of the tables, columns and indexes added.
• A migrate down command that reverses a named run or the latest one.
• A dry run that prints the statements without issuing them.
• The first run against an empty database marked as a baseline, which down refuses by default.
• Seeding as a separate, repeatable command rather than part of migration.

Non-functional requirements

• Idempotent. running twice does nothing the second time. Anything else makes deployment pipelines conditional, and conditional deployment steps get skipped.
• Honest. the history records what the database reported changing, taken from before and after snapshots, not what the code intended.
• Loud about loss. dropping a column loses every value written into it. The command prints each statement and will not proceed without an explicit flag.
• Portable. three database engines, same commands. The snapshot reads from the information the driver exposes rather than from engine-specific SQL.
• Ordered. a rollback reverses in the opposite order to application, because an index belongs to a column and a column to a table.

Numbers for a mid-sized deployment. They are here to size the thing, not to predict your traffic: change an assumption and the sums below move with it.

Assumptions

Snapshot, migrate, snapshot, diff, record. The rollback reads the record and inverts each entry.

Core components

• Model registry. the list of models to migrate. Generated resources append to it, so a new resource is migrated because it exists rather than because somebody remembered.
• Schema snapshot. what the database currently has: tables, their columns, their indexes. Taken from driver metadata so one implementation covers three engines.
• AutoMigrate. GORM applying the models. The property the whole design rests on: it adds and never drops.
• Diff. after minus before. Each entry is a kind, a table and a name, and each kind has an exact inverse.
• Run history. two tables. A run, with when it happened and which version of the framework did it, and its changes.
• Rollback. reads a run and issues the inverse of each change in reverse order, after printing them all.

Request flow

1. 1The command runs, from the API directory in development or from anywhere as a built binary, loading the same configuration the server does.
2. 2The current schema is read: every table, its columns, its indexes. An empty result here means this run is the baseline.
3. 3AutoMigrate applies the models. It creates what is missing and leaves what exists, which is what makes a second run a no-op.
4. 4The schema is read again, the same way.
5. 5After minus before gives the changes. They are recorded against a run, with the time and the framework version, so the history says what happened rather than what was requested.
6. 6Later, a rollback is asked for: the latest run, or one named by its identifier.
7. 7The run is loaded with its changes. A run marked as the baseline is refused, because undoing the run that built the schema drops every table in it.
8. 8Each change is printed with the statement that will reverse it. Nothing is issued until the operator confirms, because an added column that has been in production for a week has a week of data in it that the drop will take with it.

Data flow

• The diff is computed from the database, not predicted from the models. A column AutoMigrate declined to add is not in the history, which is correct: the rollback should not try to drop it.
• Run identifiers are timestamps, pushed past the latest recorded run when the clock would otherwise collide, so two runs in the same second get distinct identifiers.
• The inverse order matters. An index is dropped before its column, a column before its table.
• Seeding is deliberately not part of this. A migration changes shape, a seed writes rows, and conflating them makes the migration non-repeatable.

Core types

Design principles applied

• Derive, do not duplicate. the model is the schema. A migration file restating it is a second source of truth, and two sources of truth is one more than is useful.
• Record the effect, not the intent. taking the diff from the database means the history is true even when AutoMigrate did something other than what was expected.
• Destructive operations ask. the rollback prints every statement and needs a flag. Making it easy to run would make it easy to run by accident.

Patterns

• Migration is a deploy-time operation, so throughput is irrelevant and latency only matters because it holds up a deploy.
• The snapshot is proportional to the number of tables, not the number of rows, so it does not get slower as the data grows.
• The expensive changes are index builds and table rewrites on large tables, and those are properties of the change. The dry run is the place to catch them.
• Multiple instances starting at once can all try to migrate. Running the command as its own deploy step rather than at server boot avoids that entirely, and is what the generated pipelines do.
• The history grows by a few thousand rows a year, which never needs managing.
• AutoMigrate never dropping anything means an old instance rolling alongside a new one keeps working, because the columns it knows about are all still there.

What breaks first

• Changes AutoMigrate will not make. renaming a column, changing a type narrowly, adding a not-null column to a populated table. These need a deliberate step, and the tool not doing them silently is better than doing them wrongly.
• Data migrations. a shape change often needs the existing rows reshaped too, and nothing about the schema diff knows that.
• Concurrent migration at boot. several instances starting together can race, and on some engines the loser gets an error rather than a no-op.
• Rollback loses data. computable does not mean safe. Dropping a column that has been live for a week drops a week of writes.

What to do about it

• Treat a rename as add, backfill, drop. three deliberate steps, each reversible, instead of one operation the tool cannot do. Slower and survivable.
• Put data changes in a seed or a job. they are repeatable, idempotent code with tests, which a migration file is not a good place for.
• Migrate as a deploy step. one process, before the new instances start. This is what the generated pipelines and Dockerfiles do, and it removes the race rather than handling it.
• Snapshot before rolling back. a database backup immediately before a down run turns an unrecoverable mistake into a restore.