Migration scripts are how your database schema evolves without losing data or taking down the app. In 2026, every serious project uses a migration tool — the question is which one and how to structure the migrations. Done right, migrations are boring and invisible; done wrong, they cause production incidents, data loss, or hours of downtime.
What changed in 2026
- Drizzle Kit matured into a first-class migrations tool for TypeScript projects using the Drizzle ORM.
- Prisma Migrate added
--create-only mode by default in most workflows, making it easier to review generated SQL before applying.
- Zero-downtime patterns are standard — background column additions, dual-write patterns, and blue-green deploys are well-documented and expected.
- TypeScript migration scripts are common — tools like
tsx make it easy to write migrations in TS alongside your application code.
Migration tools compared
| Tool |
Best for |
Language |
Approach |
| Prisma Migrate |
Prisma ORM users |
TypeScript |
Schema-first, auto-generates SQL |
| Drizzle Kit |
Drizzle ORM users |
TypeScript |
Schema-first, generates SQL |
| node-pg-migrate |
Custom Postgres, full SQL control |
JS/TS |
SQL or JS up/down functions |
| Flyway |
Java, enterprise, polyglot |
SQL |
SQL versioned files |
| Liquibase |
Enterprise, changesets |
SQL/XML/YAML |
Changeset tracking |
| golang-migrate |
Go projects |
SQL |
Versioned SQL files |
Basic structure: up and down
// migrations/0012_add_user_role.ts
import type { Kysely } from 'kysely';
export async function up(db: Kysely<unknown>) {
await db.schema
.alterTable('users')
.addColumn('role', 'varchar(50)', (col) => col.notNull().defaultTo('member'))
.execute();
}
export async function down(db: Kysely<unknown>) {
await db.schema
.alterTable('users')
.dropColumn('role')
.execute();
}
Always write the down function even if you never plan to use it — it forces you to think through the reversal.
Prisma Migrate workflow
# Generate a migration from schema changes
npx prisma migrate dev --name add_user_role
# Apply in production (no schema drift, no interactive prompts)
npx prisma migrate deploy
Prisma generates a SQL file in prisma/migrations/. Review it before applying in production — never blindly run auto-generated DDL on prod.
Zero-downtime column addition (Postgres)
Adding a non-null column with no default requires a full table rewrite in older Postgres. In Postgres 11+, adding a column with a DEFAULT is instant (stored in catalog, not rewritten):
-- Fast in Postgres 11+ (no table rewrite)
ALTER TABLE orders ADD COLUMN status varchar(50) NOT NULL DEFAULT 'pending';
-- If you need to backfill existing rows differently, do it in a second migration:
UPDATE orders SET status = 'legacy' WHERE created_at < '2024-01-01';
For very large tables (> 10M rows), run the UPDATE in batches:
DO $
DECLARE batch_size INT := 10000;
DECLARE last_id BIGINT := 0;
BEGIN
LOOP
UPDATE orders
SET status = 'legacy'
WHERE id > last_id AND id <= last_id + batch_size AND created_at < '2024-01-01';
EXIT WHEN NOT FOUND;
last_id := last_id + batch_size;
PERFORM pg_sleep(0.05); -- yield briefly between batches
END LOOP;
END $;
Rename a column safely (3-step deploy)
Renaming a column atomically breaks running application code. The safe pattern:
- Add the new column; update app to write to both old and new.
- Backfill the new column from the old.
- Drop the old column once all deploys no longer read it.
Each step is a separate migration and deploy.
How to start
- Choose a migration tool that matches your ORM/stack.
- Write
up and down for every migration, commit them to version control.
- Test against a production-size database clone before running in production.
- Apply in a transaction where possible — DDL is transactional in Postgres.
- Lock down who can run
migrate deploy in production (CI/CD only, not laptops).
Common mistakes
Editing an applied migration. Once a migration is applied somewhere (even dev), treat it as immutable. Change it by writing a new migration.
Running data migrations inline with schema migrations. Large backfills hold an exclusive lock or run for minutes. Separate them: DDL first, data backfill as a background job or separate migration with batching.
Not testing rollback. "I have a down function" is not the same as "I have tested it." Run the rollback in staging before you need it in production.
Ignoring lock wait timeouts. In Postgres, ALTER TABLE takes an ACCESS EXCLUSIVE lock. On a live table with active queries, set lock_timeout = '5s' to fail fast rather than hang.
What to skip
- Direct production DDL from a developer laptop — run migrations from CI/CD only; human-run migrations are a reliability risk.
DROP TABLE or DROP COLUMN without a retention period — keep old columns alive for at least one deploy cycle in case you need to roll back.- One giant migration file — split unrelated changes into separate files; smaller migrations are easier to reason about and roll back.
FAQ
Should migrations run automatically on app start?
For small projects: acceptable. For production: no — run migrations as a separate CI/CD step before the new app version boots, so you can catch failures early.
How do I handle migrations across multiple microservices sharing a DB?
Each service should own and apply migrations only for its own tables. Shared tables need a coordination process — usually a designated service or a separate migration repo.
What is a squash migration?
Collapsing many old migrations into one to reduce startup time. Do this only on tables where all envs are in sync; never squash unapplied migrations.
How do I migrate data between two incompatible schemas?
Write a data migration script with dual-write: write to old and new schema simultaneously, backfill, verify counts, then cut over reads. See how to seed test data in 2026 for patterns on populating a migrated schema.
Where to go next