database-migrations

# 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/20240115_add_email_index/migration.sql -- Prisma cannot generate CONCURRENTLY, so we write it manually CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email); ``` ## Drizzle (TypeScript/Node.js) ### Workflow ```bash # Generate migration from schema changes npx drizzle-kit generate # Apply migrations npx drizzle-kit migrate # Push schema directly (dev only, no migration file) npx drizzle-kit push ``` ### Schema Example ```typescript import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core"; export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), email: text("email").notNull().unique(), name: text("name"), isActive: boolean("is_active").notNull().default(true), createdAt: timestamp("created_at").notNull().defaultNow(), updatedAt: timestamp("updated_at").notNull().defaultNow(), }); ``` ## Kysely (TypeScript/Node.js) ### Workflow (kysely-ctl) ```bash # Initialize config file (kysely.config.ts) kysely init # Create a new migration file kysely migrate make add_user_avatar # Apply all pending migrations kysely migrate latest # Rollback last migration kysely migrate down # Show migration status kysely migrate list ``` ### Migration File ```typescript // migrations/2024_01_15_001_create_user_profile.ts import { type Kysely, sql } from 'kysely' // IMPORTANT: Always use Kysely<any>, not your typed DB interface. // Migrations are frozen in time and must not depend on current schema types. export async function up(db: Kysely<any>): Promise<void> { await db.schema .createTable('user_profile') .addColumn('id', 'serial', (col) => col.primaryKey()) .addColumn('email', 'varchar(255)', (col) => col.notNull().unique()) .addColumn('avatar_url', 'text') .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull() ) .execute() await db.schema .createIndex('idx_user_profile_avatar') .on('user_profile') .column('avatar_url') .execute() } export async function down(db: Kysely<any>): Promise<void> { await db.schema.dropTable('user_profile').execute() } ``` ### Programmatic Migrator ```typescript import { Migrator, FileMigrationProvider } from 'kysely' import { promises as fs } from 'fs' import * as path from 'path' // ESM only — CJS can use __dirname directly import { fileURLToPath } from 'url' const migrationFolder = path.join( path.dirname(fileURLToPath(import.meta.url)), './migrations', ) // `db` is your Kysely<any> database instance const migrator = new Migrator({ db, provider: new FileMigrationProvider({ fs, path, migrationFolder, }), // WARNING: Only enable in development. Disables timestamp-ordering // validation, which can cause schema drift between environments. // allowUnorderedMigrations: true, }) const { error, results } = await migrator.migrateToLatest() results?.forEach((it) => { if (it.status === 'Success') { console.log(`migration "${it.migrationName}" executed successfully`) } else if (it.status === 'Error') { console.error(`failed to execute migration "${it.migrationName}"`) } }) if (error) { console.error('migration failed', error) process.exit(1) } ``` ## Django (Python) ### Workflow ```bash # Generate migration from model changes python manage.py makemigrations # Apply migrations python manage.py migrate # Show migration status python manage.py showmigrations # Generate empty migration for custom SQL python manage.py makemigrations --empty app_name -n description ``` ### Data Migration ```python from django.db import migrations def backfill_display_names(apps, schema_editor): User = apps.get_model("accounts", "User") batch_size = 5000 users = User.objects.filter(display_name="") while users.exists(): batch = list(users[:batch_size]) for user in batch: user.display_name = user.username User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size) def reverse_backfill(apps, schema_editor): pass # Data migration, no reverse needed class Migration(migrations.Migration): dependencies = [("accounts", "0015_add_display_name")] operations = [ migrations.RunPython(backfill_display_names, reverse_backfill), ] ``` ### SeparateDatabaseAndState Remove a column from the Django model without dropping it from the database immediately: ```python class Migration(migrations.Migration): operations = [ migrations.SeparateDatabaseAndState( state_operations=[ migrations.RemoveField(model_name="user", name="legacy_field"), ], database_operations=[], # Don't touch the DB yet ), ] ``` ## golang-migrate (Go) ### Workflow ```bash # Create migration pair migrate create -ext sql -dir migrations -seq add_user_avatar # Apply all pending migrations migrate -path migrations -database "$DATABASE_URL" up # Rollback last migration migrate -path migrations -database "$DATABASE_URL" down 1 # Force version (fix dirty state) migrate -path migrations -database "$DATABASE_URL" force VERSION ``` ### Migration Files ```sql -- migrations/000003_add_user_avatar.up.sql ALTER TABLE users ADD COLUMN avatar_url TEXT; CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL; -- migrations/000003_add_user_avatar.down.sql DROP INDEX IF EXISTS idx_users_avatar; ALTER TABLE users DROP COLUMN IF EXISTS avatar_url; ``` ## Zero-Downtime Migration Strategy For critical production changes, follow the expand-contract pattern: ``` Phase 1: EXPAND - Add new column/table (nullable or with default) - Deploy: app writes to BOTH old and new - Backfill existing data Phase 2: MIGRATE - Deploy: app reads from NEW, writes to BOTH - Verify data consistency Phase 3: CONTRACT - Deploy: app only uses NEW - Drop old column/table in separate migration ``` ### Timeline Example ``` Day 1: Migration adds new_status column (nullable) Day 1: Deploy app v2 — writes to both status and new_status Day 2: Run backfill migration for existing rows Day 3: Deploy app v3 — reads from new_status only Day 7: Migration drops old status column ``` ## Anti-Patterns | Anti-Pattern | Why It Fails | Better Approach | |-------------|-------------|-----------------| | Manual SQL in production | No audit trail, unrepeatable | Always use migration files | | Editing deployed migrations | Causes drift between environments | Create new migration instead | | NOT NULL without default | Locks table, rewrites all rows | Add nullable, backfill, then add constraint | | Inline index on large table | Blocks writes during build | CREATE INDEX CONCURRENTLY | | Schema + data in one migration | Hard to rollback, long transactions | Separate migrations | | Dropping column before removing code | Application errors on missing column | Remove code first, drop column next deploy |