Database Migrations Best Practices: Change Your Schema Without Fear

Changing a database schema in production is one of those things that's routine right up until it isn't. A column rename takes down the app for a minute. An index build locks a table during peak traffic. Two developers' migrations collide. Most of these problems are avoidable with a few habits.

What migrations are

A migration is a versioned, ordered script that changes your database schema: create a table, add a column, add an index. Each one is committed to Git alongside the code that depends on it.

migrations/
  20260301_create_users.sql
  20260308_create_orders.sql
  20260315_add_status_to_orders.sql

A migration tool (built into Laravel, Django, Rails, Prisma, Drizzle, Knex, Flyway and many more) keeps a table in the database recording which migrations have run, and applies new ones in order.

The result: every environment (your laptop, a teammate's laptop, staging, production) can reach exactly the same schema with one command.

The rules that keep migrations safe

1. Never edit a migration that has already run elsewhere

Once a migration has run on another machine or in production, it's history. Editing it means environments silently diverge. Need a change? Write a new migration.

2. Every schema change goes through a migration

No "quick fix" in the production console. If it isn't in a migration, the next environment won't have it, and nobody will know why things differ.

3. Keep migrations small and focused

One logical change per migration. Small migrations are easier to review, quicker to run and easier to reason about when something fails.

4. Separate schema changes from big data changes

Backfilling millions of rows inside a schema migration can lock tables and time out deploys. Run large data updates as separate, batched scripts or background jobs. (Background jobs.)

5. Back up before risky migrations

Especially anything that drops or transforms data. And make sure you know how to restore. (Backup strategy.)

6. Test on realistic data

A migration that takes 50 milliseconds on your 100-row dev database might take 20 minutes on 50 million rows. Test heavy migrations against a copy of production-sized data.

Zero-downtime changes: expand and contract

During a deploy, old and new versions of your code often run at the same time. A migration that removes or renames something the old code uses will break it.

The fix is to split risky changes into backwards-compatible steps. Renaming name to full_name:

1. Expand: add the new column full_name (nullable). Deploy.
2. Dual-write: update the code to write to both columns. Deploy.
3. Backfill: copy existing name values into full_name, in batches.
4. Switch reads: update the code to read from full_name. Deploy.
5. Contract: stop writing name, then drop it in a later migration.

More steps, yes, but no moment where the running code and the schema disagree.

Operations that need extra care

• Adding an index on a big table: use your database's online option, such as CREATE INDEX CONCURRENTLY in PostgreSQL, so writes aren't blocked. (Indexing guide.)
• Adding a NOT NULL column: add it as nullable or with a default first, backfill, then add the constraint. (Recent PostgreSQL versions add a column with a constant default quickly, without rewriting the table.)
• Changing a column type: can rewrite the whole table. Often better done with expand and contract.
• Dropping columns or tables: make sure no running code references them, and keep a backup.
• Long-running locks: set a lock_timeout so a migration fails fast instead of queueing behind traffic and blocking everything.

Down migrations: useful, but not a safety net

Many tools support "down" migrations to reverse changes. They're handy in development. In production, reversing a migration that dropped data can't bring the data back. Rolling forward with a new fix is usually safer, and a tested backup is the real safety net.

Migrations in your workflow

• Run migrations automatically in CI against a fresh database, so broken migrations fail before merging. (GitHub Actions setup.)
• Run them as an explicit deploy step, before the new code starts serving traffic (for additive changes).
• Review migrations as carefully as application code. They're harder to undo.
• When two branches add migrations, check their order after merging.

FAQ

What is a database migration?

A versioned script that changes the database schema in a controlled, repeatable way, applied in order by a migration tool so every environment ends up with the same structure.

Should I edit an existing migration?

Only if it has never run anywhere except your own machine. Once it's been applied elsewhere, create a new migration instead.

How do I rename a column without downtime?

Use expand and contract: add the new column, write to both, backfill existing data, switch reads to the new column, then drop the old one in a later deploy.