Database migrations are where careful engineering pays off most. A bad migration in production locks your table, drops half your data, or corrupts records in a way that's invisible until users start filing bugs. In 2026, the tooling is excellent — the mistakes are still human. This guide covers the patterns that make migrations safe, fast, and reversible.
What changed in 2026
- Drizzle ORM is the dominant TypeScript migration tool, with a
drizzle-kit generate → drizzle-kit migrate workflow that tracks schema drift precisely.
- Alembic remains the standard for Python/Django projects; the
--autogenerate feature is reliable for detecting ORM model changes.
- Postgres 16/17 improved lock management; concurrent index builds are more reliable, and
SKIP LOCKED is widely used for batch backfills.
- Neon branching lets you test a migration on a copy of production data before running it — a major safety net for destructive changes.
Tool comparison
| Tool |
Language |
Migration tracking |
Auto-generate |
| Drizzle Kit |
TypeScript |
SQL files |
Yes (from schema) |
| Prisma Migrate |
TypeScript |
SQL + shadow DB |
Yes (from schema) |
| Alembic |
Python |
Python scripts |
Yes (from models) |
| Flyway |
Java/any SQL |
SQL files |
No |
| Liquibase |
Java/any SQL |
XML/YAML/SQL |
No |
For TypeScript projects, Drizzle Kit or Prisma Migrate. For Python, Alembic.
Basic migration anatomy (Alembic)
# migrations/versions/2026_06_02_add_status_to_posts.py
"""add status to posts"""
from alembic import op
import sqlalchemy as sa
revision = "3f9a1e2c8b4d"
down_revision = "1a2b3c4d5e6f"
branch_labels = None
depends_on = None
def upgrade() -> None:
op.add_column("posts", sa.Column("status", sa.String(20), nullable=True))
# Backfill after adding the column
op.execute("UPDATE posts SET status = 'published' WHERE status IS NULL")
op.alter_column("posts", "status", nullable=False)
def downgrade() -> None:
op.drop_column("posts", "status")
The zero-downtime column rename (expand-contract)
Never do this in one migration:
ALTER TABLE users RENAME COLUMN username TO display_name; -- breaks running app code
Instead, use three separate deploys:
Deploy 1 — expand: Add the new column, write to both.
ALTER TABLE users ADD COLUMN display_name TEXT;
-- App code writes to both `username` and `display_name`
Deploy 2 — contract: Backfill the new column, switch reads to the new column.
UPDATE users SET display_name = username WHERE display_name IS NULL;
-- App code now reads only `display_name`
Deploy 3 — drop: Remove the old column.
ALTER TABLE users DROP COLUMN username;
Adding a column with a default (safe pattern)
On Postgres 11+, adding a column with a constant default is instant (no table rewrite). On older versions, or with volatile defaults (NOW()), it rewrites the table:
-- Safe on Postgres 11+ (constant default)
ALTER TABLE orders ADD COLUMN is_archived BOOLEAN NOT NULL DEFAULT FALSE;
-- Risky on any version (volatile default)
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();
-- Instead: add nullable, backfill in batches, then set NOT NULL
For large tables with volatile defaults:
-- Step 1
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;
-- Step 2: batch backfill (outside migration, in a script)
UPDATE orders SET created_at = NOW() WHERE id BETWEEN 1 AND 10000;
-- ... repeat in batches
-- Step 3
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;
Creating an index without locking
-- Blocks all writes for the duration — avoid on large tables
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- Non-blocking: runs in the background
CREATE INDEX CONCURRENTLY idx_posts_user_id ON posts(user_id);
In Alembic:
op.create_index("idx_posts_user_id", "posts", ["user_id"], postgresql_concurrently=True)
Note: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Use with op.get_context().autocommit_block(): in Alembic or run the migration outside a transaction.
How to run migrations safely in production
- Test on a branch or staging with production-scale data before touching production.
- Run as a pre-deploy step, not inside app startup, so only one process runs it.
- Back up before destructive operations —
DROP COLUMN, DROP TABLE, type changes.
- Monitor lock waits with
pg_stat_activity and pg_locks during the migration run.
- Have a rollback plan — know which
downgrade() command to run and test it.
Common mistakes
No down migration. You'll need it at 2am when the deploy breaks something. Write it in the same commit as up.
DROP COLUMN without a data backup. Column data is gone immediately. Even if you can reverse the schema, the data is gone.
Long-running UPDATE in a migration. Updating millions of rows in one transaction holds locks for minutes. Batch it in a script.
Running migrations in parallel. Multiple app instances each running alembic upgrade head on startup causes race conditions. Use a migration lock or a dedicated step.
Autogenerating without reviewing. --autogenerate misses some changes (custom types, constraints without names) and occasionally generates spurious diffs. Always review the generated SQL.
What to skip
- Hand-editing migration files after they've been applied to any environment — the history diverges and future autogenerates break.
- Squashing migrations in production unless you have a dedicated maintenance window and have pruned all environments first.
nullable=False columns with no default on large tables in a single migration — that's a full table rewrite.
FAQ
How do I handle a migration that takes hours on a large table?
Break it into phases: add nullable column, backfill in batches with a script (outside Alembic/Drizzle), then set constraints. Each phase is a separate migration.
Should I use --autogenerate or write migrations by hand?
Use autogenerate as a starting point, then review the diff manually. It handles routine changes well; complex cases (renaming, custom constraints) need manual edits.
How do I test a migration before production?
Run it against a fresh restore of the production database on a staging server or Neon branch. Check query plans before and after for index usage regressions.
What is a shadow database (Prisma)?
Prisma creates a temporary empty DB to detect schema drift — it compares the shadow DB state to your migration history to catch edited migration files.
Where to go next
See How to set up Postgres locally in 2026, How to add full-text search in 2026, and How to seed test data in 2026.