A database migration is a small, reviewable file, and also one of the few changes in a codebase that can take production down if it goes wrong in a way a code review alone won't catch — the gap between "correct SQL" and "safe to run against a live database" is where most migration incidents happen.
A database migration is a version-controlled, incremental change to a database schema — adding a column, creating an index, altering a constraint — applied in order and tracked so every environment's schema stays in sync with the application code that depends on it. Nearly every ORM and framework ships migration tooling (Prisma Migrate, Drizzle Kit, Rails migrations, Django migrations) because manually keeping schemas in sync across environments by hand doesn't scale past a single developer.
Why Migrations Matter (and When to Skip Them)
Without versioned migrations, schema drift between development, staging, and production environments becomes inevitable — someone runs an ALTER TABLE directly against production to fix an urgent issue, and now no other environment matches it. Migrations make schema changes reproducible, reviewable, and auditable, the same benefits version control brings to application code.
Skip formal migration tooling only for genuinely throwaway prototypes with no real data and no team beyond yourself — the moment a second person or a persistent environment enters the picture, informal schema changes become a liability.
Getting Started with Migrations
A typical migration file (Drizzle Kit style), generated from a schema diff:
-- 0003_add_user_preferences.sql
ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);
Applying migrations as part of a deploy pipeline:
npx drizzle-kit generate # diff schema, produce migration file
npx drizzle-kit migrate # apply pending migrations
Core Migration Concepts Every Developer Should Know
Migrations should be additive-first for zero-downtime deploys. Adding a nullable column is safe to deploy before the application code that uses it; dropping a column the old application code still reads is not. The standard safe sequence is: add the new column → deploy code that writes to both old and new → backfill → deploy code that reads only the new column → drop the old column, each as a separate migration.
-- step 1: additive, safe to run before code deploy
ALTER TABLE users ADD COLUMN email_normalized TEXT;
-- step 4 (later, after backfill and code cutover): safe to drop
ALTER TABLE users DROP COLUMN email_legacy;
Large table changes need lock-awareness. Some schema changes (adding a column with a non-null default, adding certain indexes) can lock a table for the duration of the change on large tables. Fix: use CREATE INDEX CONCURRENTLY in Postgres for index creation without a blocking lock, and be deliberate about default-value backfills on large tables.
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);
Migrations should be idempotent or at least safely re-runnable in your tooling's tracking system. Your migration tool tracks which migrations have already applied (usually via a metadata table) — trust that tracking rather than manually running SQL directly against production outside the migration system, which desyncs the tracked state from reality.
Rollback strategy needs to exist before you need it. Not every migration is cleanly reversible (a dropped column with its data is gone), so plan for forward-fix migrations as the realistic rollback strategy for destructive changes, and reserve true down-migrations for genuinely reversible changes.
Common Migration Mistakes and How to Fix Them
Mistake 1: combining a schema change with the application code deploy that depends on it, as one atomic event. If the migration runs slowly or fails partway on a large table, the application deploy is now blocked or inconsistent with the database state. Fix: separate migration deploys from application code deploys where the change isn't trivially fast, using the additive-first pattern.
Mistake 2: running migrations manually against production instead of through CI/CD. Manual runs are error-prone and skip whatever review/audit process your pipeline enforces. Fix: run migrations as an automated, reviewed step in your deploy pipeline, never as an ad-hoc manual command against production.
Mistake 3: not testing migrations against production-scale data before running them for real. A migration that's instant on a development database with a hundred rows can lock a production table with a hundred million rows for minutes. Fix: test migration timing against a realistic data volume (a staging replica of production, or at minimum a synthetic large dataset) before deploying anything touching a large table.
When Should You Use a Migration Tool vs. Manual Schema Management?
Always use a migration tool for anything beyond a single-developer throwaway prototype — the reproducibility, review trail, and team coordination benefits are worth the setup cost almost immediately. The only real question is which tool fits your stack (Prisma Migrate, Drizzle Kit, or your framework's built-in system), not whether to use one at all.
Migrations in Production
Build the additive-first, multi-step pattern into your team's default workflow for any schema change touching a column or table already in production use — treating every schema change as potentially needing this pattern, rather than deciding case by case under time pressure, prevents most migration-related incidents. Also keep migrations small and focused; a single migration doing five unrelated things is harder to review, harder to debug if something goes wrong, and harder to roll forward from cleanly.
Before running any migration against production, ask whether it's safe to run before, during, and after the corresponding code deploy independently — if the answer isn't clearly yes for all three, the migration needs to be split into safer steps.