Database Migrations

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

Every change is a migration — never alter production databases manually
Migrations are forward-only in production — rollbacks use new forward migrations
Schema and data migrations are separate — never mix DDL and DML in one migration
Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
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

-- Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- Column with default (Postgres 11+ is instant)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- NOT NULL without default (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

-- Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);

Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

Large Data Migrations

-- 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 $$;

-- Updates all rows in one transaction (locks table)
UPDATE users SET normalized_email = LOWER(email);

Prisma (TypeScript/Node.js)

Workflow

# 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

Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills):

# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index

-- 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);

Django (Python)

Data Migration

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),
    ]

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

Overview

Core Skills

Development Patterns

Language-Specific Skills

Advanced Skills

When to Activate

Core Principles

Migration Safety Checklist

PostgreSQL Patterns

Adding a Column Safely

Adding an Index Without Downtime

Renaming a Column (Zero-Downtime)

Large Data Migrations

Prisma (TypeScript/Node.js)

Workflow

Custom SQL Migration

Django (Python)

Data Migration

Zero-Downtime Migration Strategy

Timeline Example

Anti-Patterns

​When to Activate

​Core Principles

​Migration Safety Checklist

​PostgreSQL Patterns

​Adding a Column Safely

​Adding an Index Without Downtime

​Renaming a Column (Zero-Downtime)

​Large Data Migrations

​Prisma (TypeScript/Node.js)

​Workflow

​Custom SQL Migration

​Django (Python)

​Data Migration

​Zero-Downtime Migration Strategy

​Timeline Example

​Anti-Patterns

When to Activate

Core Principles

Migration Safety Checklist

PostgreSQL Patterns

Adding a Column Safely

Adding an Index Without Downtime

Renaming a Column (Zero-Downtime)

Large Data Migrations

Prisma (TypeScript/Node.js)

Workflow

Custom SQL Migration

Django (Python)

Data Migration

Zero-Downtime Migration Strategy

Timeline Example

Anti-Patterns