Database migration mastery with Prisma, Drizzle, Knex, TypeORM, Alembic, Flyway, and golang-migrate. Use when user asks to "create a migration", "update database schema", "add a column", "remove a column", "rename a table", "set up Prisma", "rollback migration", "write SQL migration", "set up Drizzle", "zero-downtime migration", "backfill data", "squash migrations", "seed database", "migrate production", or any database schema change tasks.
Database migration mastery with Prisma, Drizzle, Knex, TypeORM, Alembic, Flyway, and golang-migrate. Use when user asks to "create a migration", "update database schema", "add a column", "remove a column", "rename a table", "set up Prisma", "rollback migration", "write SQL migration", "set up Drizzle", "zero-downtime migration", "backfill data", "squash migrations", "seed database", "migrate production", or any database schema change tasks.
SQL Migrations
Migration Fundamentals
A migration is a versioned, incremental change to a database schema. Migrations run in order and track which have been applied via a metadata table (e.g., schema_migrations, _prisma_migrations).
Up/Down: The up migration applies the change. The down migration reverses it. Not all changes are reversible (dropping a column with data destroys it).
Versioned vs Repeatable: Versioned migrations run once in order (001, 002, ...). Repeatable migrations (Flyway R__ prefix) re-run whenever their checksum changes -- useful for views, functions, and stored procedures.
Idempotent migrations: Use IF NOT EXISTS / IF EXISTS guards so a migration can be re-run safely without erroring on already-applied state:
CREATE TABLE IF NOTEXISTS users (...);
ALTER TABLE users ADDCOLUMN IF NOTEXISTS role TEXT;
DROP INDEX IF EXISTS idx_users_email;
Tool-Specific Patterns
Prisma
npm install prisma @prisma/client
npx prisma init
Commands:
npx prisma migrate dev --name add_users_table # create + apply migration (dev)
npx prisma migrate deploy # apply pending migrations (production)
npx prisma migrate reset # drop + recreate + seed (destructive)
npx prisma migrate status # check migration status
npx prisma generate # regenerate client
npx prisma db push # push schema without migration file (prototyping)
npx prisma db seed # run seed script
npx prisma studio # open database GUI
Drizzle
npm install drizzle-orm drizzle-kit
Commands:
npx drizzle-kit generate # generate migration from schema diff
npx drizzle-kit migrate # apply migrations
npx drizzle-kit push # push schema directly (prototyping)
npx drizzle-kit studio # open Drizzle Studio
npx drizzle-kit drop # drop a migration
alembic init alembic # initialize
alembic revision --autogenerate -m "add users table"
alembic upgrade head# apply all
alembic downgrade -1 # undo last
alembic history# list migrations
alembic current # show current revision
Flyway (Java / JVM)
flyway migrate # apply pending
flyway info # show status
flyway validate # verify applied match local
flyway repair # fix metadata table
flyway clean # drop all objects (destructive)# Naming: V1__Create_users.sql, V2__Add_email_index.sql# Repeatable: R__Create_views.sql (re-runs when checksum changes)
golang-migrate
migrate create -ext sql -dir db/migrations -seq add_users_table
migrate -path db/migrations -database "$DB_URL" up
migrate -path db/migrations -database "$DB_URL" down 1
migrate -path db/migrations -database "$DB_URL" force 3 # fix dirty state
Writing Safe Migrations (Zero-Downtime)
Adding a Column
Never add a NOT NULL column without a default to a table that has existing rows. The safe sequence:
Add column as nullable: ALTER TABLE users ADD COLUMN role TEXT;
Deploy code that writes the new column.
Backfill existing rows: UPDATE users SET role = 'user' WHERE role IS NULL;
Add the constraint: ALTER TABLE users ALTER COLUMN role SET NOT NULL;
Removing a Column
Never drop a column that code still reads. The safe sequence:
Stop reading the column in application code. Deploy.
Stop writing the column. Deploy.
Drop the column: ALTER TABLE users DROP COLUMN legacy_field;
Renaming a Column or Table
Renaming breaks existing queries instantly. The safe sequence:
Add the new column. Deploy code that writes to both old and new.
Backfill new column from old column.
Switch reads to new column. Deploy.
Stop writing old column. Deploy.
Drop old column.
For tables, the same expand-migrate-contract pattern applies. Alternatively, create a view with the old name during transition.
Index Creation
On Postgres, CREATE INDEX locks the table for writes. Use CONCURRENTLY:
-- Safe: does not block writes (Postgres only, cannot run inside a transaction)CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Drop safely tooDROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
In migration tools that wrap each file in a transaction, you must disable the transaction for that specific migration or run the index creation separately.
Adding Constraints
-- Add foreign key without locking (Postgres)ALTER TABLE posts ADD CONSTRAINT fk_posts_author
FOREIGN KEY (author_id) REFERENCES users(id) NOT VALID;
ALTER TABLE posts VALIDATE CONSTRAINT fk_posts_author;
-- Add check constraint without locking (Postgres 12+)ALTER TABLE users ADD CONSTRAINT chk_role
CHECK (role IN ('admin', 'user', 'moderator')) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT chk_role;
Data Migrations vs Schema Migrations
Keep them separate. Schema migrations change structure (DDL). Data migrations change content (DML). Mixing them causes problems:
Schema migrations should be fast and reversible. Data migrations on large tables are slow.
Schema rollbacks cannot un-delete data.
Data migrations may need batching; schema migrations do not.
-- Schema migration: 005_add_status_column.sqlALTER TABLE orders ADDCOLUMN status TEXT;
-- Data migration: 006_backfill_status.sql (separate file)UPDATE orders SET status ='completed'WHERE completed_at ISNOT NULL;
UPDATE orders SET status ='pending'WHERE completed_at ISNULL;
Large Table Migrations
For tables with millions of rows, a single ALTER TABLE or UPDATE can lock the table or run for hours.
Batched updates: Process rows in chunks to avoid long locks and transaction log bloat:
-- Backfill in batches of 10,000
DO $$
DECLARE batch_size INT :=10000;
BEGIN
LOOP
UPDATE orders SET status ='pending'WHERE id IN (SELECT id FROM orders WHERE status ISNULL LIMIT batch_size);
EXIT WHENNOT FOUND;
COMMIT;
END LOOP;
END $$;
Online schema change tools (MySQL): pt-online-schema-change (Percona) and gh-ost (GitHub) create a shadow table, copy data, replay binlog changes, then swap. Use these for any DDL on large MySQL tables in production.
When migration count grows unwieldy (100+ files), squash them:
Dump the current schema: pg_dump --schema-only > baseline.sql
Delete all existing migration files.
Create a single baseline migration from the dump.
Mark it as applied in the migrations table without running it.
All future migrations build from this baseline.
Prisma: npx prisma migrate diff can generate a diff between two states. Drizzle and Knex do not have built-in squash -- do it manually.
Rollback Strategies and Limitations
Always write down migrations but understand their limits:
Dropping a column is irreversible (data is gone). The down migration can recreate the column but not the data.
Data migrations cannot be meaningfully reversed if the old value was overwritten.
Rollbacks in production are risky. Prefer forward-fixing: deploy a new migration that undoes the change.
-- Down migration: 003_add_role.down.sqlALTER TABLE users DROPCOLUMN IF EXISTS role;
Migration Locking and Concurrent Deploys
Most migration tools use advisory locks to prevent two processes from running migrations simultaneously. If your deployment runs multiple instances:
Prisma and Flyway acquire locks automatically.
Knex, golang-migrate: only one instance should run migrations (use a deploy step, not application startup).
If a migration crashes mid-run and leaves a lock, you may need to manually clear the lock or use migrate force (golang-migrate) / flyway repair.
Run migrations in a dedicated CI/CD step, not at application boot.
Testing Migrations
Test against a production-like dataset, not an empty database. Schema changes that work on empty tables may lock or fail on tables with millions of rows.
Run up then down then up again to verify reversibility.
Use a copy of production data (anonymized) in staging.
Check migration speed: if a migration takes > 1 second on staging, it will take longer in production. Plan accordingly.
Seeding Data
Seeds populate the database with initial or test data. Keep seeds idempotent.
-- Idempotent seedINSERT INTO roles (name) VALUES ('admin'), ('user'), ('moderator')
ON CONFLICT (name) DO NOTHING;
Run migrations against a test database before merging.
Verify no pending migrations exist after running.
Run the application test suite after migrations.
In production deploys, run migrations before deploying new application code (if the migration is backward-compatible) or after (if old code must stop using removed columns first).
-- Example up migrationCREATE TABLE IF NOTEXISTS users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULLUNIQUE,
name VARCHAR(255),
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMPDEFAULT NOW(),
updated_at TIMESTAMPDEFAULT NOW()
);
-- Example down migrationDROPTABLE IF EXISTS users;
Quick Reference
1. One change per migration -- easier to review, rollback, and debug
2. Always write down migrations for reversibility
3. Never edit an applied migration -- create a new one
4. Test migrations against production-like data
5. Wrap multi-statement migrations in transactions (except CONCURRENTLY)
6. Separate schema migrations from data migrations
7. Backfill data in a separate migration from the schema change
8. Run migrations in CI/CD, not at application boot
9. Back up the database before running production migrations
