Database Migrations Explained: How Schema Changes Work

A database migration is a small, version-controlled script that changes a database schema — adding a column, creating a table, renaming an index — in a way that can be applied, tracked, and reversed like any other piece of code. Instead of editing a production schema by hand, you write the change once, run it through a migration tool, and that tool records exactly which changes have already been applied.

Why you can’t just edit the schema by hand

A schema isn’t a single artifact you can diff and redeploy — it’s a stateful thing living inside a running database, usually with real data in it. If one engineer runs ALTER TABLE directly on production while another runs a slightly different version in staging, the two environments drift apart silently. Weeks later, a deploy fails in production because a column that exists in every developer’s laptop database was never actually added there.

Migrations solve this by treating schema changes the same way you’d treat application code: as ordered, reviewable, repeatable steps. Every environment — a fresh laptop checkout, a CI database, staging, production — reaches the same schema by replaying the same ordered list of scripts, not by someone remembering what they changed last Tuesday.

Migration files: up and down

Most migration tools structure each change as a pair of operations:

• Up — the forward change: create the table, add the column, backfill the new field.
• Down — the reverse: drop the table, remove the column, undo the backfill.

Writing the down migration is often more work than the up migration, and it’s tempting to skip. Resist that — a working rollback path is what lets you back out of a bad deploy without restoring from a backup. Some newer tools favor “expand and contract” instead of true rollbacks: you add the new shape, migrate data over, and only remove the old shape in a later, separate migration, so you’re never one command away from a destructive rollback.

How a migration tool tracks state

The tool itself needs to know which migrations have already run against a given database, so it doesn’t reapply them. It does this with a small bookkeeping table — often literally called schema_migrations — that stores the identifier (usually a timestamp or sequence number) of every migration that has been applied. On each run, the tool diffs the list of migration files on disk against the rows in that table and executes only the ones missing.

This is also why migration file names typically start with a timestamp or incrementing number: ordering matters. If migration 003 depends on a table created in 002, running them out of order breaks. Most teams treat migration order as append-only — once a migration has shipped to any shared environment, you don’t edit it; you write a new one.

Migrations as part of the deploy pipeline

In a typical CI/CD pipeline, migrations run as a distinct step before the new application code goes live — the schema has to support the new code before that code starts running queries against it. This ordering matters more than it looks: if you deploy code that expects a new status column before the migration that adds it has run, every request touching that table starts failing.

For zero-downtime deploys, the safe pattern is to make each migration backward-compatible with the previous version of the application code, not just the next one. Add a nullable column rather than a NOT NULL column with no default; add the new code path that reads it in a separate deploy; only make the column required and drop the old path once every instance is running the new code. This is the same discipline used for blue-green and canary deployments — never assume old and new versions won’t run side by side, even briefly.

Schema migrations vs data migrations

	Schema migration	Data migration
Changes	Table/column/index structure	Row values
Typical trigger	New feature, refactor	Backfill, cleanup, format change
Risk	Locking, blocking queries	Long-running, resource-heavy
Reversible?	Usually (drop what you added)	Often not (original values may be gone)
Example	ADD COLUMN status TEXT	Backfilling status for existing rows

Data migrations deserve extra caution because they’re often not cleanly reversible — once you’ve overwritten a column’s values, the “down” migration can’t reconstruct what was there before. For large tables, run data migrations in batches rather than a single transaction; an update that touches millions of rows at once can hold locks long enough to stall every other query, and a batch job that fails halfway is much easier to resume than a giant transaction that has to roll back entirely.

Common pitfalls

• Adding a NOT NULL column without a default on a large table can lock it for the duration of the rewrite, depending on the database engine.
• Running migrations automatically on app boot in a multi-instance deployment can cause several instances to race to apply the same migration simultaneously — use a dedicated migration step in the pipeline instead.
• Editing an already-applied migration file instead of writing a new one leaves environments that already ran the old version permanently out of sync with ones that haven’t.
• Skipping the down migration because “we’ll never roll back” — until the one time you need to, at 2 a.m., under a production incident.

Migration history also interacts with database isolation levels in subtle ways: a migration that adds a constraint can fail partway through if concurrent transactions are inserting rows that violate it, so it’s worth understanding what a database like PostgreSQL actually locks during a schema change, not just what the migration file says it does.

The takeaway

A database migration turns schema changes into ordered, version-controlled code instead of manual edits that drift between environments. Write both the up and down steps, keep migrations backward-compatible with the code that’s about to run alongside them, run them as an explicit pipeline step rather than on app boot, and treat data migrations — which are often irreversible — with more caution than structural ones.