Database Migrations

What is it?

Once an application is live, its schema rarely stays frozen — you'll add a column, create a new table, rename something. Doing that by hand (logging into production and typing ALTER TABLE yourself) is risky: it's easy to forget a step, apply changes in the wrong order, or have your development database drift out of sync with what's actually running in production.

A migration is a small script that describes one specific schema change — "add a phone_number column to users" — saved as a file, checked into version control alongside your application code, and numbered or timestamped so migrations always run in a known, repeatable order. A migration tool keeps track of which migrations have already been applied to a given database, so running it again only applies the new ones.

Explain like I'm 10

Manually editing a live schema is like renovating a house without ever writing down what you changed — nobody else can reliably reproduce it. Migrations are like a numbered set of renovation blueprints: blueprint 1 adds the garage, blueprint 2 adds a window, and any house (any environment — your laptop, staging, production) built by following the blueprints in order ends up identical.

Examples

A migration file adding a column

-- migrations/0007_add_phone_number_to_users.sql

ALTER TABLE users
ADD COLUMN phone_number TEXT;

This one file describes exactly one schema change. Its filename encodes an order (0007), so a migration tool knows it should run after migration 0006 and before 0008.

A migration with an explicit rollback (using an ORM's migration tool, conceptually)

// migrations/0008_create_reviews_table.js
exports.up = function (schema) {
  schema.createTable("reviews", (t) => {
    t.integer("id").primaryKey();
    t.integer("product_id").references("products.id");
    t.text("body");
  });
};

exports.down = function (schema) {
  schema.dropTable("reviews");
};

Many migration tools pair each change (up) with its exact opposite (down), so a migration can be undone cleanly if it needs to be rolled back.

The same idea with Alembic (SQLAlchemy's migration tool)

# migrations/versions/a1b2c3_add_phone_number.py

def upgrade():
    op.add_column("users", sa.Column("phone_number", sa.String()))

def downgrade():
    op.drop_column("users", "phone_number")

Alembic is the migration tool most commonly paired with SQLAlchemy/FastAPI — upgrade() and downgrade() are exactly the up/down pair from the JavaScript example, just Python syntax; running alembic upgrade head applies every migration not yet recorded, in order.

How it works

A migration tool keeps a small table inside the database itself (often literally called schema_migrations) recording which migration files have already been run. When you run the tool, it compares that record against the migration files that exist, and applies only the ones not yet marked as applied — in order.

Because migrations are just files, they travel with your codebase through version control: every developer, and every environment (development, staging, production), can run the exact same sequence of migrations and end up with an identical schema, instead of drifting apart from manual changes.

Why does it exist?

Manual schema changes don't scale past a single person working on a single database. The moment there's more than one developer, more than one environment, or a need to deploy schema changes alongside code changes in a repeatable way, you need changes to be recorded, ordered, and re-runnable — which is exactly what migrations provide.

When to use it

Use migrations for essentially every schema change on any application with more than one environment or more than one contributor — adding columns, creating tables, changing constraints, backfilling data as part of a schema change.

When not to use it

For a true one-off, throwaway local prototype with a single developer and no shared environments, the overhead of formal migration files may not be worth it yet — though it's worth adopting them as soon as the project is shared with anyone else or deployed anywhere.

Common mistakes

• Editing a migration file after it's already been applied elsewhere, instead of writing a new migration — this leaves environments that already ran the old version out of sync.

• Making a schema change directly against production to 'fix it quickly,' bypassing migrations entirely and causing drift from what the migration history says the schema should be.

• Writing a migration that changes both schema and large amounts of data in one long-running step, risking locking the table for a long time in production.

Practice exercises

1. Easy:

   Write a migration that adds a boolean is_active column, defaulting to true, to a users table.

2. Medium:

   Write a migration that creates a new tags table and a join table linking tags to posts (a many-to-many relationship).

3. Hard:

   Explain why editing an already-applied migration file, instead of writing a new one, can cause a team's databases to drift out of sync with each other.

Interview questions

What is a database migration?

A versioned script describing one specific schema change, saved as a file and tracked in a known order, so it can be applied consistently and repeatably across every environment.

What do the `up` (or `upgrade`) and `down` (or `downgrade`) parts of a migration represent?

up/upgrade applies the change going forward; down/downgrade is meant to be its exact opposite, so the same migration file can both apply the change and cleanly undo it later.

How does a migration tool decide which migrations still need to run, and in what order?

It keeps a table inside the database (often literally schema_migrations) recording which migration files have already been applied, compares that against the files that exist, and runs only the unapplied ones in filename order.

Why shouldn't you edit an already-applied migration file instead of writing a new one?

Environments that already ran the original version won't see the edit, so their schema silently drifts out of sync with any environment that runs the edited file fresh.

Concretely, what breaks when a team edits an applied migration instead of adding a new one?

A developer whose database already recorded that migration as applied keeps the old schema forever, while anyone running migrations from scratch gets the edited version — and the migration tracking table can't detect this, since it only records that a migration ran, not what it currently contains.

Why is a down migration risky specifically when the up migration dropped a column or table?

The down migration can recreate the empty structure, but it can't recover the data that existed in it — running down after an up that dropped something looks like a clean rollback while the data itself is already permanently gone.

What does 'zero-downtime migration' mean, and why can an ordinary schema change threaten uptime?

It means never having a moment where the running application code and the current schema are incompatible with each other; a change like renaming or dropping a column the live code still reads can break every request the instant it runs.

Describe the 'expand-contract' pattern for renaming a column without downtime.

Expand: add the new column (nullable) and have the app write to both old and new columns, then backfill existing rows into the new one; once every app instance is confirmed reading the new column, contract: drop the old column in a later, separate migration.

Why can adding a `NOT NULL` column with no default be riskier than adding a nullable one?

The database needs a value for every existing row the instant the constraint takes effect; with no default supplied there's nothing to put there, so the statement is rejected outright (or, where the engine must backfill every row to satisfy it, can hold a long lock rewriting the whole table), while a nullable column simply stores NULL for existing rows untouched.

Why does a plain `CREATE INDEX` risk blocking writes on a large production table, and what's Postgres's alternative?

Building the index normally takes a lock that blocks writes to the table for the whole build; CREATE INDEX CONCURRENTLY builds it without that exclusive lock, at the cost of taking longer and needing two passes over the table.

Why can't `CREATE INDEX CONCURRENTLY` run inside the same transaction as other migration statements?

Postgres requires it to run outside a transaction block, since it internally uses multiple transactions to build the index safely — a migration tool has to run it as its own separate, non-transactional step.

What's the danger of renaming a column in one atomic migration during a rolling deploy?

While old and new application instances run side by side during the rollout, the old instances still expect the original column name — the instant the rename runs, every one of them starts failing.

What is a 'backward-compatible' migration, and why does it matter for rolling deploys?

One that leaves the schema working for both the code currently running and the code about to be deployed at the same time, so neither the outgoing nor incoming instances break during the window both are live.

Why do migration tools enforce a strict run order instead of letting migrations run in any order?

Later migrations routinely assume earlier ones already ran — a migration adding a foreign key assumes the referenced table already exists — so applying them out of order can fail or corrupt the schema.

What happens when two developers on separate branches each create a migration numbered `0012`?

A naming collision that makes it ambiguous which one should run first once the branches merge — teams either resolve it manually at merge/review time or avoid it by using timestamp-based filenames instead of small sequential integers.

Why do many teams prefer timestamp-based migration filenames over small sequential integers?

Two developers working in parallel are very unlikely to create a migration in the same millisecond, while two people independently picking the next small integer (e.g. both choosing 0012) collide easily.

What's the difference between a schema migration and a data migration, and why is mixing them risky?

A schema migration changes structure (tables/columns); a data migration changes or backfills the actual row values. Mixing both in one migration — e.g. adding a column, then updating millions of rows in the same step — risks one long-running statement holding a lock for its entire duration, instead of a fast schema change followed by a batched backfill off the critical path.

Why batch a large backfill into many small updates instead of one large `UPDATE`?

A single huge update can hold locks and generate a large amount of transaction log in one go and, if interrupted partway, has to restart from scratch; batching keeps each transaction small, releases locks between batches, and preserves progress if it's interrupted.

What does it mean for a migration to run inside a transaction, and why doesn't every database support that fully for schema changes?

Wrapping a migration's statements in a transaction means a failure partway rolls back the whole thing cleanly instead of leaving the schema half-changed; Postgres supports this for most DDL, but some databases (e.g. MySQL) implicitly commit DDL statements as they run, so a mid-migration failure there can leave the schema partially applied.

If a migration fails partway through on a database without transactional DDL, what state is left, and what has to happen next?

Some statements before the failure point are already committed while the rest never ran, leaving a schema that matches neither the old nor the new version — it has to be diagnosed and fixed manually (often with a corrective migration), since simply re-running the same file may error again on the part that already succeeded.

Why do teams sometimes 'squash' old migrations into one baseline file, and what's lost by doing so?

A long migration history becomes slow to replay from scratch (e.g. building a fresh test database) and cluttered; squashing consolidates it into one current-schema file, at the cost of destroying the fine-grained history of exactly how the schema evolved, one change at a time.

What's the risk of applying an autogenerated migration without reviewing it first?

Autogeneration diffs the ORM's model definitions against the database and infers the change, but it can miss what it can't detect or generate something unsafe for production scale — reviewing the actual generated SQL before running it is essential.

Why might autogeneration turn a column rename into 'drop old column, add new column' instead of an actual rename?

It typically diffs the old and new schema by name and has no way to know a dropped field and an added field were meant to be the same column rather than two unrelated changes — generating the drop+add it can detect would delete that column's existing data if applied as-is.

Why run migrations as part of CI/CD instead of manually per environment?

It guarantees every environment — a fresh CI database, staging, production — applies migrations consistently in the same automated step as the code deploy, instead of depending on someone remembering to run them by hand, which is exactly the drift migrations exist to prevent.

Should a migration that adds a column run before or after deploying code that reads it? What about one dropping a column old code still reads?

Adding a column a new code version needs should run before that code deploys, so the column already exists; dropping a column old code still reads should run only after every instance is confirmed to be on code that no longer needs it — reversing either order is what breaks a rolling deploy.

What's a seed/fixture script, and why is it usually kept separate from schema migrations?

It inserts default or sample data (an initial admin user, reference lookup rows); it's kept separate because it's about populating data for a particular environment, not describing a structural change every environment must apply identically.

Why is it good practice for a migration to be safe to re-run, even though the tool already tracks what's applied?

It guards against edge cases like the tracking table getting out of sync or a migration being manually re-attempted, so a retry doesn't error out or corrupt the schema — e.g. guarding a table creation with CREATE TABLE IF NOT EXISTS.

What does `alembic upgrade head` actually do?

It applies every migration not yet recorded as run, in order, up through the most recent one ('head') in the migration chain.

How does Alembic typically chain migrations together, and how does that differ from relying on a filename's number?

Each migration records the revision id of the one before it, forming a linked chain, rather than depending purely on a filename's sequential number — so ordering stays correct even when migrations are merged from different branches out of numeric sequence.

Why does `op.drop_column` in a downgrade function lose data, and when is that an acceptable tradeoff?

Dropping the column deletes every value stored in it for every row; that's acceptable when a rollback happens shortly after a failed deploy, before the column has been meaningfully populated — not once it's been in real use.

What's the difference between rolling back a migration and writing a new migration that reverses an earlier one?

Rolling back runs the recorded down script for that specific migration and un-marks it as applied; writing a new forward migration instead applies a brand-new step and keeps the history moving in one direction — many teams prefer the latter once a migration has already reached production, since it avoids requiring every environment to support running rollbacks.

How would you change a large, actively-written-to table's column type without significant downtime?

Use the expand-contract approach: add a new column of the target type, dual-write to both, backfill existing rows in batches, switch reads over to the new column, then drop the old column in a later migration — instead of a single blocking type-conversion statement that rewrites the whole table at once.

Why test a migration's down/rollback path, not just its up path?

An untested down migration might not actually restore the previous schema, or might error outright — a problem that only becomes visible during a real rollback under pressure, exactly when you can least afford it to fail.