Schema migrations & evolving a database

Key takeaways A migration is a versioned, ordered change to your database’s schema, kept as a file in version control and applied by a tool that records which migrations have already run. Migrations turn schema changes into something reproducible across every environment, reviewable like code, and reversible via paired up and down steps. On a live database you change the schema in backwards-compatible stages so the running app never breaks. This is how the design from data modeling keeps evolving safely.

Your schema is never truly finished. Features get added, columns get renamed, tables get split — the shape of your data changes for as long as the app lives. The question is how you make those changes: as risky, undocumented, one-off surgery, or as ordinary, version-controlled steps. Migrations are the second answer, and they’re one of the habits that separates a hobby database from a production one.

The problem with changing schemas by hand

Suppose you ALTER TABLE on your laptop to add a column. It works. But now your database and everyone else’s have quietly diverged: your teammate’s copy doesn’t have the column, staging doesn’t, production doesn’t. There’s no record of what you changed or why, no way to reproduce it reliably, and no way to review it before it hits real data. Multiply that across months and a team, and your environments drift into subtly different shapes — the source of “works on my machine” bugs that are miserable to track down.

The fix is to treat a schema change exactly like a code change: write it down, check it in, and apply it the same way everywhere.

A migration is a checked-in, ordered change

A migration is a small file describing one schema change, stored in your repository alongside your code. Each has a version — usually a timestamp or sequence number — that fixes its place in an ordered list. A migration tool keeps a little table in the database recording which migrations have already run, so when you say “migrate,” it applies exactly the ones that haven’t, in order, and stops.

-- 20260804_add_recorded_flag.up.sql
ALTER TABLE calls ADD COLUMN recorded BOOLEAN NOT NULL DEFAULT false;

Because every environment applies the same files in the same order, they all converge on the same schema. New developer joining? They run the migrations from zero and get a database identical to production’s shape. That reproducibility is the whole point.

Up and down: applying and reversing

Most migrations come as a matched pair. The up migration makes the change; the down migration undoes it, so you can roll back if a deploy goes wrong:

-- up: apply the change
ALTER TABLE calls ADD COLUMN recorded BOOLEAN NOT NULL DEFAULT false;

-- down: reverse it
ALTER TABLE calls DROP COLUMN recorded;

The tool tracks a “current version” and moves you forward or backward through the ordered list. Forward applies pending ups; rolling back one step runs the matching down. Not every change is cleanly reversible — a down that drops a column throws away the data in it — so a good down migration is a best effort to restore the previous shape, and you lean on backups for the cases a rollback can’t recover.

The dangerous part: changing a live database

Adding a table nobody uses yet is easy. The hard migrations change data or columns that a running application is actively reading and writing. The trap is a change that’s fine after it finishes but breaks the app during the moment old code meets new schema, or new code meets old schema.

The rule is to make every change backwards-compatible at each step, so at no point is the deployed code out of step with the schema as it exists right then. A rename — which looks trivial — is the classic example of what not to do in one shot, because the instant you rename a column, the old still-running code that references the old name breaks.

The expand-and-contract pattern

The standard way to make a breaking change safely is to split it into small, individually-safe steps — often called expand and contract. To rename label to talkgroup_label:

Expand. Add the new talkgroup_label column (nullable, or with a default). The old column still exists; nothing reading label breaks.
Backfill and dual-write. Deploy code that writes both columns, and run a migration to copy existing label values into talkgroup_label. Now both are populated and correct.
Migrate reads. Deploy code that reads the new column. The old one is now unused but still present, so a rollback is still safe.
Contract. In a later migration, once you’re confident, drop the old label column.

Each step leaves a database and an app that agree with each other, so you can deploy — and roll back — at any point without a broken window. The same shape works for splitting a table, changing a type, or adding a NOT NULL constraint to existing data (add nullable, backfill, then enforce). It’s more steps than a raw ALTER, and that’s exactly why it’s safe.

Migrations are part of your deploy

The last habit: migrations run as a step in your deployment, in order, before or alongside the code that depends on them — never as a manual afterthought someone might forget. Combined with backups and a rollback plan, that makes evolving a live database a routine, low-drama event instead of a held-breath one. Your schema stays a living, version-controlled artifact that any environment can rebuild exactly, which is the foundation everything operational later in this module — replication, backups, scaling — is built on.

Quick check: what's the main reason to use migration files instead of running ALTER TABLE by hand?

Recap

A migration is a versioned, ordered schema change kept in version control and applied by a tool that records which have already run.
Migrations make schema changes reproducible across environments, reviewable like code, and reversible through paired up and down steps.
Up applies a change; down reverses it — though some downs can’t recover data, which is what backups are for.
Changing a live database is the dangerous part: keep every step backwards-compatible so the running app never meets a schema it doesn’t expect.
The expand-and-contract pattern splits a breaking change (like a rename) into add, backfill, dual-write, switch reads, then drop — each step individually safe.
Run migrations as part of your deploy, in order, not as a manual afterthought.

Next up: SQL vs. NoSQL.

Frequently asked questions

Why not just change the schema by hand when I need to?

Because a hand-run ALTER TABLE leaves no record of what changed, can’t be reproduced on another environment, and drifts out of sync between your laptop, staging, and production. A migration is a checked-in file that any environment can apply in the same order, so every database ends up with the identical, known schema.

What are up and down migrations?

The up migration applies a change — add a column, create a table. The down migration reverses it — drop that column, drop the table — so you can roll back if something goes wrong. Each migration is a matched pair, and the tool tracks which ones have run so it applies each exactly once.

How do I change a schema on a live app without downtime?

Make the change in backwards-compatible steps. Add new columns as nullable or with a default, deploy code that writes both old and new, backfill existing rows, then switch reads over and finally remove the old column in a later migration. Each step leaves the running app working against the schema as it is at that moment.