Database Migration Patterns

--- name: database-migrations description: > Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate). Use when planning or implementing database schema changes. metadata: origin: ECC --- # Database Migration Patterns Safe, reversible database schema changes for production systems. ## When to Activate - Creating or altering database tables - Adding/removing columns or indexes - Running data migrations (backfill, transform) - Planning zero-downtime schema changes - Setting up migration tooling for a new project ## Core Principles 1. Every change is a migration : never alter production databases manually 2. Migrations are forward-only in production : rollbacks use new forward migrations 3. Schema and data migrations are separate : never mix DDL and DML in one migration 4. Test migrations against production-sized data : a migration that works on 100 rows may lock on 10M 5. Migrations are immutable once deployed : never edit a migration that has run in production ## Migration Safety Checklist Before applying any migration: - [ ] Migration has both UP and DOWN (or is explicitly marked irreversible) - [ ] No full table locks on large tables (use concurrent operations) - [ ] New columns have defaults or are nullable (never add NOT NULL without default) - [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables) - [ ] Data backfill is a separate migration from schema change - [ ] Tested against a copy of production data - [ ] Rollback plan documented ## PostgreSQL Patterns ### Adding a Column Safely ``sql -- GOOD: Nullable column, no lock ALTER TABLE users ADD COLUMN avatar_url TEXT; -- GOOD: Column with default (Postgres 11+ is instant, no rewrite) ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true; -- BAD: NOT NULL without default on existing table (requires full rewrite) ALTER TABLE users ADD COLUMN role TEXT NOT NULL; -- This locks the table and rewrites every row ` ### Adding an Index Without Downtime `sql -- BAD: Blocks writes on large tables CREATE INDEX idx_users_email ON users (email); -- GOOD: Non-blocking, allows concurrent writes CREATE INDEX CONCURRENTLY idx_users_email ON users (email); -- Note: CONCURRENTLY cannot run inside a transaction block -- Most migration tools need special handling for this ` ### Renaming a Column (Zero-Downtime) Never rename directly in production. Use the expand-contract pattern: `sql -- Step 1: Add new column (migration 001) ALTER TABLE users ADD COLUMN display_name TEXT; -- Step 2: Backfill data (migration 002, data migration) UPDATE users SET display_name = username WHERE display_name IS NULL; -- Step 3: Update application code to read/write both columns -- Deploy application changes -- Step 4: Stop writing to old column, drop it (migration 003) ALTER TABLE users DROP COLUMN username; ` ### Removing a Column Safely `sql -- Step 1: Remove all application references to the column -- Step 2: Deploy application without the column reference -- Step 3: Drop column in next migration ALTER TABLE orders DROP COLUMN legacy_status; -- For Django: use SeparateDatabaseAndState to remove from model -- without generating DROP COLUMN (then drop in next migration) ` ### Large Data Migrations `sql -- BAD: Updates all rows in one transaction (locks table) UPDATE users SET normalized_email = LOWER(email); -- GOOD: Batch update with progress DO $$ DECLARE batch_size INT := 10000; rows_updated INT; BEGIN LOOP UPDATE users SET normalized_email = LOWER(email) WHERE id IN ( SELECT id FROM users WHERE normalized_email IS NULL LIMIT batch_size FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS rows_updated = ROW_COUNT; RAISE NOTICE 'Updated % rows', rows_updated; EXIT WHEN rows_updated = 0; COMMIT; END LOOP; END $$; ` ## Prisma (TypeScript/Node.js) ### Workflow `bash # Create migration from schema changes npx prisma migrate dev --name add_user_avatar # Apply pending migrations in production npx prisma migrate deploy # Reset database (dev only) npx prisma migrate reset # Generate client after schema changes npx prisma generate ` ### Schema Example `prisma model User { id String @id @default(cuid()) email String @unique name String? avatarUrl String? @map("avatar_url") createdAt DateTime @default(now()) @map("created_at") updatedAt DateTime @updatedAt @map("updated_at") orders Order[] @@map("users") @@index([email]) } ` ### Custom SQL Migration For operations Prisma cannot express (concurrent indexes, data backfills): `bash # Create empty migration, then edit the SQL manually npx prisma migrate dev --create-only --name add_email_index ` ``sql -- migrations/20240115addemail_index/migration.sql -- Prisma cannot g