Database migrations are critical for evolving your schema while maintaining data integrity. This guide covers strategies for safe, reversible schema changes.
Expand and Contract Pattern#
For renaming columns safely:
1-- Step 1: Add new column
2ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
3
4-- Step 2: Backfill data
5UPDATE users SET full_name = name;
6
7-- Step 3: Deploy app that writes to both
8-- Step 4: Deploy app that reads from new column
9-- Step 5: Remove old column
10ALTER TABLE users DROP COLUMN name;Adding NOT NULL Columns#
1-- Step 1: Add nullable
2ALTER TABLE users ADD COLUMN status VARCHAR(20);
3
4-- Step 2: Backfill
5UPDATE users SET status = 'active' WHERE status IS NULL;
6
7-- Step 3: Add constraint
8ALTER TABLE users ALTER COLUMN status SET NOT NULL;Non-Blocking Index Creation#
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);Prisma Migrations#
npx prisma migrate dev --name add_status_column
npx prisma migrate deploy # ProductionKeep migrations small, reversible, and test with production-like data.