Database Migration

Database Migration

Safe patterns for evolving database schemas in production.

Migration Principles

1. Backward compatible - New code works with old schema 2. Reversible - Can rollback if needed 3. Tested - Verify on staging before production 4. Incremental - Small changes, not big-bang 5. Zero downtime - No service interruption

Safe Migration Pattern

Phase 1: Add New (Compatible)

-- Add new column (nullable initially)
ALTER TABLE users ADD COLUMN full_name VARCHAR(255) NULL;

-- Deploy new code that writes to both old and new
UPDATE users SET full_name = CONCAT(first_name, ' ', last_name);

Phase 2: Migrate Data

-- Backfill existing data
UPDATE users
SET full_name = CONCAT(first_name, ' ', last_name)
WHERE full_name IS NULL;

Phase 3: Make Required

-- Make column required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Phase 4: Remove Old (After New Code Deployed)

-- Remove old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

Common Migrations

Adding Index

-- Create index concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Renaming Column

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Phase 2: Copy data
UPDATE users SET email_address = email;

-- Phase 3: Drop old column (after deploy)
ALTER TABLE users DROP COLUMN email;

Changing Column Type

-- Phase 1: Add new column with new type
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- Phase 2: Migrate data
UPDATE products SET price_cents = CAST(price * 100 AS INTEGER);

-- Phase 3: Drop old column
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

Adding Foreign Key

-- Add column first
ALTER TABLE orders ADD COLUMN user_id INTEGER NULL;

-- Populate data
UPDATE orders SET user_id = (
    SELECT id FROM users WHERE users.email = orders.user_email
);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(id);

Migration Tools

Python (Alembic)

# Generate migration
alembic revision --autogenerate -m "add user full_name"

# Apply migration
alembic upgrade head

# Rollback
alembic downgrade -1

JavaScript (Knex)

// Create migration
knex migrate:make add_full_name

// Apply migrations
knex migrate:latest

// Rollback
knex migrate:rollback

Rails

# Generate migration
rails generate migration AddFullNameToUsers full_name:string

# Run migrations
rails db:migrate

# Rollback
rails db:rollback

Testing Migrations

def test_migration_forward_backward():
    # Apply migration
    apply_migration("add_full_name")

    # Verify schema
    assert column_exists("users", "full_name")

    # Rollback
    rollback_migration()

    # Verify rollback
    assert not column_exists("users", "full_name")

Dangerous Operations

❌ Avoid in Production

-- Locks table for long time
ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL;

-- Can't rollback
DROP TABLE old_users;

-- Breaks existing code immediately
ALTER TABLE users DROP COLUMN email;

✅ Safe Alternatives

-- Add as nullable first
ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL;

-- Rename instead of drop
ALTER TABLE old_users RENAME TO archived_users;

-- Keep old column until new code deployed
-- (multi-phase approach)

Rollback Strategy

-- Every migration needs DOWN
-- UP
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- DOWN
ALTER TABLE users DROP COLUMN full_name;

Decision Support

Quick Decision Guide

Making a schema change?

• Breaking change (drops/modifies data) → Multi-phase migration (expand-contract)
• Additive change (new columns/tables) → Single-phase migration
• Large table (millions of rows) → Use CONCURRENTLY for indexes

Need zero downtime?

• Schema change → Expand-contract pattern (5 phases)
• Data migration (< 10k rows) → Synchronous in-migration
• Data migration (> 1M rows) → Background worker pattern

Planning rollback?

• Added new schema only → Simple DOWN migration
• Modified/removed schema → Multi-phase rollback or fix forward
• Cannot lose data → Point-in-time recovery (PITR)

Choosing migration tool?

• Python/Django → Django Migrations
• Python/SQLAlchemy → Alembic
• Node.js/TypeScript → Prisma Migrate or Knex.js
• Enterprise/multi-language → Flyway or Liquibase

→ See [references/decision-trees.md](./references/decision-trees.md) for comprehensive decision frameworks

Troubleshooting

Common Issues Quick Reference

Migration failed halfway → Check database state, fix forward with repair migration

Schema drift detected → Use autogenerate to create reconciliation migration

Cannot rollback (no downgrade) → Create reverse migration or fix forward

Foreign key violation → Clean data before adding constraint, or add as NOT VALID

Migration locks table too long → Use CONCURRENTLY, add columns in phases, batch updates

Circular dependency → Create merge migration or reorder dependencies

→ See [references/troubleshooting.md](./references/troubleshooting.md) for detailed solutions with examples

Navigation

Detailed References

• [🌳 Decision Trees](./references/decision-trees.md) - Schema migration strategies, zero-downtime patterns, rollback strategies, migration tool selection, and data migration approaches. Load when planning migrations or choosing strategies.

• [🔧 Troubleshooting](./references/troubleshooting.md) - Failed migration recovery, schema drift detection, migration conflicts, rollback failures, data integrity issues, and performance problems. Load when debugging migration issues.

Remember

• Test migrations on copy of production data
• Have rollback plan ready
• Monitor during deployment
• Communicate with team about schema changes
• Keep migrations small and focused

Database Migration Decision Trees

This guide helps you make critical database migration decisions through clear decision trees and selection criteria.

Table of Contents

• Schema Migration Strategy
• Zero-Downtime Deployment Patterns
• Rollback Strategy Selection
• Migration Tool Choice
• Data Migration Approach

---

Schema Migration Strategy

Decision Tree

Making a schema change?
│
├─ Breaking change (drops data or incompatible)?
│   │
│   ├─ YES → Multi-phase migration required
│   │   │
│   │   ├─ Phase 1: Add new (nullable)
│   │   ├─ Phase 2: Dual-write old + new
│   │   ├─ Phase 3: Backfill data
│   │   ├─ Phase 4: Migrate reads to new
│   │   ├─ Phase 5: Remove old
│   │   │
│   │   ✅ Zero downtime maintained
│   │   ✅ Can rollback at each phase
│   │   ✅ Production-safe
│   │
│   └─ NO → Additive change?
│       │
│       ├─ YES → Single-phase migration
│       │   ✅ Add columns as nullable initially
│       │   ✅ Set defaults after data loaded
│       │   ✅ Safe and simple
│       │
│       └─ NO → Data-only change (no schema)?
│           └─ Use data migration script
│               ✅ No schema changes
│               ✅ Easier rollback
│
└─ Creating new table/index?
    │
    ├─ Large table (millions of rows)?
    │   └─ Use CONCURRENTLY for indexes
    │       ✅ No table locks (PostgreSQL)
    │       ⚠️  Takes longer but zero downtime
    │
    └─ Small table?
        └─ Standard CREATE
            ✅ Fast and simple

When to Use Multi-Phase Migrations

✅ Use multi-phase when:

• Renaming columns or tables
• Changing column types
• Removing columns or tables
• Adding NOT NULL constraints
• Changing foreign key relationships

Example: Renaming a column

-- ❌ Bad: Breaking change in one step
ALTER TABLE users RENAME COLUMN email TO email_address;
-- Breaks running application immediately!

-- ✅ Good: Multi-phase approach
-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255) NULL;

-- Phase 2: Deploy code that writes to both columns
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Phase 3: Migrate reads to new column (code deploy)

-- Phase 4: Remove old column (after new code deployed)
ALTER TABLE users DROP COLUMN email;

When to Use Single-Phase Migrations

✅ Use single-phase when:

• Adding new nullable columns
• Adding new tables
• Adding indexes (use CONCURRENTLY)
• Adding new foreign keys to nullable columns

-- ✅ Safe: Additive changes
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Can safely deploy with running application

---

Zero-Downtime Deployment Patterns

Decision Tree

Need zero-downtime deployment?
│
├─ Schema change required?
│   │
│   ├─ YES → Expand-Contract pattern
│   │   │
│   │   ├─ Step 1: EXPAND schema (add new)
│   │   ├─ Step 2: Deploy NEW code (dual-write)
│   │   ├─ Step 3: MIGRATE data (backfill)
│   │   ├─ Step 4: Deploy NEW code (read from new)
│   │   ├─ Step 5: CONTRACT schema (remove old)
│   │   │
│   │   ✅ Zero downtime guaranteed
│   │   ✅ Rollback at each step
│   │
│   └─ NO → Code-only deployment?
│       └─ Standard rolling deployment
│           ✅ Simple and fast
│
├─ Data migration required?
│   │
│   ├─ Large dataset (millions of rows)?
│   │   └─ Background worker pattern
│   │       ✅ Batch processing
│   │       ✅ No blocking
│   │       ✅ Progress tracking
│   │
│   └─ Small dataset?
│       └─ Synchronous migration
│           ✅ Simple and fast
│
└─ Index creation?
    │
    ├─ PostgreSQL?
    │   └─ CREATE INDEX CONCURRENTLY
    │       ✅ No locks
    │       ⚠️  Slower than regular CREATE
    │
    └─ MySQL?
        └─ ALGORITHM=INPLACE, LOCK=NONE (5.6+)
            ✅ Online DDL

Expand-Contract Pattern (Best Practice)

Phase 1: EXPAND

-- Add new schema elements
ALTER TABLE products ADD COLUMN price_cents INTEGER NULL;

Phase 2: Deploy Code (Dual Write)

# Application writes to BOTH old and new
product.price = Decimal("19.99")
product.price_cents = 1999  # New field
product.save()

Phase 3: MIGRATE Existing Data

-- Backfill in batches
UPDATE products
SET price_cents = CAST(price * 100 AS INTEGER)
WHERE price_cents IS NULL
LIMIT 1000;
-- Repeat until all migrated

Phase 4: Deploy Code (Read New)

# Application now reads from new field
price = Decimal(product.price_cents) / 100

Phase 5: CONTRACT

-- Remove old schema
ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_cents TO price;

Blue-Green Deployment Pattern

✅ Use blue-green when:

• Complete database swap needed
• Testing production-like data required
• Instant rollback critical

┌─────────────────┐      ┌─────────────────┐
│   Blue (Live)   │      │  Green (New)    │
│                 │      │                 │
│  App v1         │      │  App v2         │
│  DB Schema v1   │      │  DB Schema v2   │
└────────┬────────┘      └────────┬────────┘
         │                        │
         │  1. Replicate data     │
         │  2. Apply migrations   │
         │  3. Test green         │
         │  4. Switch traffic ────▶
         │                        │
         │  5. Monitor            │
         │  6. Rollback if needed │

---

Rollback Strategy Selection

Decision Tree

Planning rollback strategy?
│
├─ Migration added new schema only?
│   │
│   ├─ YES → Simple rollback
│   │   │
│   │   └─ Run DOWN migration
│   │       ✅ Removes new additions
│   │       ✅ Safe (no data loss)
│   │
│   └─ NO → Migration modified/removed schema?
│       │
│       └─ Complex rollback needed
│           │
│           ├─ Data was migrated?
│           │   └─ Restore from backup + replay transactions
│           │       ⚠️  Complex, test thoroughly
│           │
│           └─ No data migration yet?
│               └─ Reverse migration safe
│                   ✅ Schema-only rollback
│
├─ Can tolerate data loss?
│   │
│   ├─ YES → Snapshot rollback
│   │   └─ Restore from backup
│   │       ⚠️  Loses recent data
│   │
│   └─ NO → Point-in-time recovery required
│       └─ Use transaction log replay
│           ✅ No data loss
│           ⚠️  Complex setup
│
└─ How fast must rollback be?
    │
    ├─ Immediate (< 1 minute)?
    │   └─ Blue-green deployment
    │       ✅ Traffic switch only
    │       ✅ Instant rollback
    │
    └─ Can wait (5-30 minutes)?
        └─ Reverse migration
            ✅ Standard approach

Rollback Strategies

Strategy 1: Reverse Migration (Default)

# Alembic example
def upgrade():
    op.add_column('users', sa.Column('full_name', sa.String(255)))

def downgrade():
    op.drop_column('users', 'full_name')

When to use:

• Additive changes only
• No data migration performed
• Testing/staging environments

Strategy 2: Multi-Phase Rollback

Migration in progress:
  Phase 1: ✅ Add new column       → Rollback: Drop new column
  Phase 2: ✅ Dual-write deployed  → Rollback: Deploy old code
  Phase 3: 🔄 Backfilling data     → Rollback: Stop backfill, drop new column
  Phase 4: ❌ Not started          → Cannot rollback beyond this point safely

Rule: Can only rollback to phases already completed

Strategy 3: Snapshot Rollback

# Take snapshot before migration
pg_dump -Fc mydb > backup_before_migration.dump

# If rollback needed
pg_restore -d mydb backup_before_migration.dump

When to use:

• Development/staging only
• Data loss acceptable
• Need fast rollback

⚠️ Warning: Loses all data created after snapshot

Strategy 4: Point-in-Time Recovery (Production)

-- PostgreSQL PITR
-- 1. Enable WAL archiving
archive_mode = on
archive_command = 'cp %p /archive/%f'

-- 2. Take base backup
pg_basebackup -D /backup/base

-- 3. If rollback needed
# Restore base backup
# Replay WAL up to migration point
recovery_target_time = '2025-12-03 10:00:00'

When to use:

• Production databases
• Zero data loss required
• Have WAL archiving setup

---

Migration Tool Choice

Decision Tree

Choosing migration tool for project?
│
├─ What's your tech stack?
│   │
│   ├─ Python (Django)?
│   │   └─ Django Migrations (built-in)
│   │       ✅ Integrated with ORM
│   │       ✅ Auto-generates migrations
│   │       ✅ Schema + data migrations
│   │
│   ├─ Python (other frameworks)?
│   │   └─ Alembic
│   │       ✅ Works with SQLAlchemy
│   │       ✅ Auto-detection
│   │       ✅ Flask, FastAPI compatible
│   │
│   ├─ Node.js / TypeScript?
│   │   │
│   │   ├─ Using Prisma ORM?
│   │   │   └─ Prisma Migrate
│   │   │       ✅ Type-safe
│   │   │       ✅ Schema as code
│   │   │
│   │   ├─ Using Drizzle ORM?
│   │   │   └─ Drizzle Kit
│   │   │       ✅ TypeScript-first
│   │   │       ✅ Zero dependencies
│   │   │
│   │   └─ Framework-agnostic?
│   │       └─ Knex.js or node-pg-migrate
│   │           ✅ Simple and flexible
│   │           ✅ No ORM required
│   │
│   ├─ Ruby (Rails)?
│   │   └─ ActiveRecord Migrations (built-in)
│   │       ✅ Integrated with Rails
│   │       ✅ DSL for schema changes
│   │
│   ├─ Go?
│   │   └─ golang-migrate or goose
│   │       ✅ Fast and simple
│   │       ✅ No ORM dependency
│   │
│   └─ Language-agnostic?
│       └─ Flyway or Liquibase
│           ✅ Works with any language
│           ✅ Enterprise features
│           ✅ Team tracking
│
└─ Database-specific needs?
    │
    ├─ PostgreSQL-only?
    │   └─ Sqitch or dbmate
    │       ✅ PostgreSQL optimized
    │
    └─ Multi-database support?
        └─ Flyway or Liquibase
            ✅ Supports 20+ databases

Comparison Matrix

When to Use Each Tool

Django Migrations

# Auto-generate from models
python manage.py makemigrations

# Apply migrations
python manage.py migrate

✅ Use when: Building Django applications with ORM

Alembic (SQLAlchemy)

# Auto-generate from models
alembic revision --autogenerate -m "add user table"

# Apply
alembic upgrade head

✅ Use when: Flask, FastAPI, or any Python with SQLAlchemy

Prisma Migrate

# Generate migration from schema
npx prisma migrate dev --name add_user_table

# Apply to production
npx prisma migrate deploy

✅ Use when: TypeScript projects with type-safe database access

Flyway (Enterprise)

-- migrations/V1__create_users.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

flyway migrate

✅ Use when: Multi-language teams, enterprise requirements

---

Data Migration Approach

Decision Tree

Migrating data between schemas?
│
├─ How much data?
│   │
│   ├─ Small (< 10,000 rows)?
│   │   └─ Synchronous in-migration approach
│   │       ✅ Simple UPDATE statement
│   │       ✅ Runs during migration
│   │       ✅ Fast enough
│   │
│   ├─ Medium (10k - 1M rows)?
│   │   └─ Batched migration approach
│   │       ✅ Process in chunks (1000 rows)
│   │       ✅ Avoid long locks
│   │       ✅ Resumable if interrupted
│   │
│   └─ Large (> 1M rows)?
│       └─ Background worker approach
│           ✅ Process asynchronously
│           ✅ Track progress
│           ✅ Zero downtime
│
├─ Complex transformations needed?
│   │
│   ├─ YES → Application-level migration
│   │   │
│   │   └─ Use background jobs
│   │       ✅ Business logic in code
│   │       ✅ Easy to test
│   │       ✅ Can retry failures
│   │
│   └─ NO → SQL-level migration
│       └─ Direct UPDATE/INSERT
│           ✅ Faster
│           ✅ Less code
│
└─ Can tolerate temporary inconsistency?
    │
    ├─ YES → Lazy migration
    │   │
    │   └─ Migrate on read/write
    │       ✅ Zero downtime
    │       ✅ Gradual migration
    │       ✅ No batch jobs needed
    │
    └─ NO → Eager migration required
        └─ Backfill before switching reads
            ✅ Consistent data
            ⚠️  Requires coordination

Data Migration Patterns

Pattern 1: Synchronous (Small Data)

-- Simple one-shot migration
UPDATE products
SET price_cents = CAST(price * 100 AS INTEGER)
WHERE price_cents IS NULL;

When to use:

• < 10,000 rows
• Fast transformation
• No locks concern

Pattern 2: Batched (Medium Data)

def migrate_in_batches(batch_size=1000):
    while True:
        updated = db.execute("""
            UPDATE products
            SET price_cents = CAST(price * 100 AS INTEGER)
            WHERE price_cents IS NULL
            LIMIT :batch_size
        """, batch_size=batch_size)

        if updated == 0:
            break  # All migrated

        time.sleep(0.1)  # Breather between batches

When to use:

• 10k - 1M rows
• Want to avoid long locks
• Need progress tracking

Pattern 3: Background Worker (Large Data)

# Celery task example
@celery.task
def backfill_price_cents():
    products = Product.objects.filter(price_cents__isnull=True)[:1000]

    for product in products:
        product.price_cents = int(product.price * 100)
        product.save()

    if products.count() == 1000:
        # More to process, schedule next batch
        backfill_price_cents.apply_async(countdown=1)

When to use:

• > 1M rows
• Complex transformations
• Need monitoring/retry

Pattern 4: Lazy Migration (Zero Downtime)

class Product(models.Model):
    price = models.DecimalField()
    price_cents = models.IntegerField(null=True)

    def save(self, *args, **kwargs):
        # Migrate on write
        if self.price_cents is None:
            self.price_cents = int(self.price * 100)
        super().save(*args, **kwargs)

    @property
    def current_price_cents(self):
        # Migrate on read
        if self.price_cents is None:
            self.price_cents = int(self.price * 100)
            self.save()
        return self.price_cents

When to use:

• True zero downtime needed
• Gradual migration acceptable
• No batch processing infrastructure

---

Migration Checklist

Before executing migrations, verify:

1. Backup Taken → Full database backup exists 2. Tested on Staging → Migration tested on production-like data 3. Rollback Plan → Know how to reverse the migration 4. Monitoring Ready → Can detect issues quickly 5. Team Notified → Coordinate with team on timing 6. Load Tested → Tested with production-scale data 7. Read-Only Mode → Can enable read-only if issues 8. Runbook Created → Step-by-step execution guide

---

Related References

• [Troubleshooting](./troubleshooting.md) - Common migration failures and recovery procedures
• [Zero-Downtime Patterns](./zero-downtime-patterns.md) - Detailed zero-downtime deployment strategies (coming soon)
• [Tool Guides](./tool-guides.md) - Framework-specific migration tool guides (coming soon)

Database Migration Troubleshooting Guide

Comprehensive troubleshooting guide for database migration failures, recovery procedures, and common issues.

Table of Contents

• Failed Migrations Recovery
• Schema Drift Detection
• Migration Conflicts Resolution
• Rollback Failures
• Data Integrity Issues
• Performance Problems

---

Failed Migrations Recovery

Issue: Migration failed halfway through

Problem:

$ alembic upgrade head
...
sqlalchemy.exc.OperationalError: (psycopg2.errors.UndefinedColumn)
column "email" does not exist

Migration failed at version abc123

Diagnosis:

• Migration script has errors
• Database state is inconsistent
• Previous migration didn't complete

Solutions:

Solution 1: Check migration state

# Check current database version
alembic current

# Check migration history
alembic history

Solution 2: Fix forward (preferred)

# Create a repair migration
alembic revision -m "repair_failed_migration"

# In the upgrade() function, handle the incomplete state
def upgrade():
    # Check if column exists before adding
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    columns = [col['name'] for col in inspector.get_columns('users')]

    if 'email' not in columns:
        op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade():
    pass  # No-op since we're repairing

Solution 3: Manual database repair

-- Identify what state the database is in
SELECT column_name FROM information_schema.columns
WHERE table_name = 'users';

-- Manually complete the migration
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Mark migration as complete
-- (Framework-specific, for Alembic:)
UPDATE alembic_version SET version_num = 'abc123';

Solution 4: Rollback and retry

# Roll back to last known good state
alembic downgrade -1

# Fix the migration script
# Edit migrations/abc123_add_email.py

# Retry migration
alembic upgrade head

---

Issue: Migration table is corrupted

Problem:

$ python manage.py migrate
django.db.utils.ProgrammingError:
relation "django_migrations" does not exist

Diagnosis:

• Migration tracking table deleted or corrupted
• Database was restored from old backup
• Manual database changes

Solutions:

Solution 1: Recreate migration table (Django)

# In Django shell
from django.db import connection
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE django_migrations (
        id SERIAL PRIMARY KEY,
        app VARCHAR(255) NOT NULL,
        name VARCHAR(255) NOT NULL,
        applied TIMESTAMP NOT NULL
    )
""")

Solution 2: Fake migrations to current state (Django)

# Mark all migrations as applied without running them
python manage.py migrate --fake

# Or fake specific migration
python manage.py migrate myapp 0005 --fake

Solution 3: Recreate from scratch

# DANGER: Only in development
# Drop migration table
# Recreate and re-run all migrations
python manage.py migrate --run-syncdb

---

Issue: Circular migration dependency

Problem:

$ alembic upgrade head
sqlalchemy.exc.CircularDependencyError:
Circular dependency detected between migrations

Diagnosis:

• Migration A depends on B
• Migration B depends on A
• Merge conflicts in migrations

Solutions:

Solution 1: Identify the circular dependency

# Visualize migration graph (Alembic)
alembic history --verbose

# Or use Django
python manage.py showmigrations --plan

Solution 2: Merge migrations

# Create a merge migration (Django)
python manage.py makemigrations --merge

# Edit the merge migration to resolve conflicts
# migrations/0006_merge.py
dependencies = [
    ('myapp', '0004_auto_20251201'),
    ('myapp', '0005_auto_20251202'),
]

Solution 3: Reorder migrations

# Edit migration file dependencies
# In migrations/0005_add_field.py
dependencies = [
    ('myapp', '0004_previous'),  # Changed from 0005
]

---

Schema Drift Detection

Issue: Database schema doesn't match ORM models

Problem: Production database has columns not in migrations, or migrations have been applied incorrectly.

Diagnosis:

• Manual database changes
• Migrations applied out of order
• Migrations missing or skipped

Solutions:

Solution 1: Generate diff (Alembic)

# Auto-generate migration based on model changes
alembic revision --autogenerate -m "fix_schema_drift"

# Review the generated migration carefully!
# It will show differences between DB and models

Solution 2: Schema inspection (Django)

# Compare actual DB schema with expected
from django.core.management import call_command
from django.db import connection

# Get actual schema
cursor = connection.cursor()
cursor.execute("""
    SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = 'myapp_user'
""")
actual_columns = cursor.fetchall()

# Get expected schema from models
from myapp.models import User
expected_fields = User._meta.get_fields()

# Compare and log differences

Solution 3: Use database migration tools

# PostgreSQL: Compare schemas
pg_dump -s production_db > prod_schema.sql
pg_dump -s staging_db > staging_schema.sql
diff prod_schema.sql staging_schema.sql

# MySQL: Use pt-table-checksum (Percona Toolkit)
pt-table-checksum --databases mydb

Solution 4: Create reconciliation migration

# Alembic
def upgrade():
    # Add missing columns
    op.add_column('users', sa.Column('phone', sa.String(20)))

    # Remove extra columns
    op.drop_column('users', 'deprecated_field')

    # Fix column types
    op.alter_column('users', 'age',
                    existing_type=sa.String(),
                    type_=sa.Integer())

---

Issue: Migration history out of sync

Problem:

$ alembic upgrade head
Target database is not up to date.

Diagnosis:

• Migrations applied manually
• Migration table manually edited
• Different environments have different history

Solutions:

Solution 1: Stamp database to specific version

# Alembic: Mark database as being at specific version
alembic stamp head

# Or stamp to specific revision
alembic stamp abc123

Solution 2: Fake migrations (Django)

# Mark migration as applied without running
python manage.py migrate myapp 0005 --fake

# Mark all as applied
python manage.py migrate --fake

Solution 3: Reset migration history (DANGEROUS)

# Only in development/testing!
# Delete migration tracking
DELETE FROM alembic_version;

# Re-stamp to current
alembic stamp head

---

Migration Conflicts Resolution

Issue: Multiple developers created same migration number

Problem:

migrations/
  0004_add_email.py (Developer A)
  0004_add_phone.py (Developer B)  ❌ Conflict!

Diagnosis:

• Two branches created migrations simultaneously
• Migration numbers collide

Solutions:

Solution 1: Rename migration (Django)

# Developer B renames their migration
mv 0004_add_phone.py 0005_add_phone.py

# Update dependencies in file
# migrations/0005_add_phone.py
dependencies = [
    ('myapp', '0004_add_email'),  # Changed from 0003
]

Solution 2: Create merge migration

# Django auto-detects and creates merge migration
python manage.py makemigrations --merge

Solution 3: Squash migrations (after resolution)

# Combine multiple migrations into one (Django)
python manage.py squashmigrations myapp 0001 0005

---

Issue: Migration depends on deleted migration

Problem:

dependencies = [
    ('myapp', '0003_old_migration'),  # This was deleted!
]

Diagnosis:

• Migration file deleted but referenced
• Branch merged without updating dependencies

Solutions:

Solution 1: Update dependency

# Edit migration file
dependencies = [
    ('myapp', '0002_previous_migration'),  # Updated
]

Solution 2: Recreate deleted migration

# Create empty migration with same number
# migrations/0003_old_migration.py
def upgrade():
    pass

def downgrade():
    pass

---

Rollback Failures

Issue: Cannot rollback migration (no downgrade)

Problem:

$ alembic downgrade -1
NotImplementedError: downgrade() not implemented

Diagnosis:

• Migration has no downgrade() function
• Irreversible operation (data deletion)

Solutions:

Solution 1: Write downgrade function

# migrations/abc123_add_column.py
def upgrade():
    op.add_column('users', sa.Column('email', sa.String(255)))

def downgrade():
    # Add the missing downgrade
    op.drop_column('users', 'email')

Solution 2: Create reverse migration

# Create new migration that reverses changes
alembic revision -m "reverse_abc123"

def upgrade():
    # Reverse the previous migration
    op.drop_column('users', 'email')

def downgrade():
    # Allow rolling back this rollback
    op.add_column('users', sa.Column('email', sa.String(255)))

Solution 3: Manual rollback

-- Manually reverse the changes
ALTER TABLE users DROP COLUMN email;

-- Update migration table
UPDATE alembic_version SET version_num = 'previous_version';

---

Issue: Rollback loses data

Problem: Rolling back migration would delete production data.

Diagnosis:

• Migration added column with data
• Rollback would drop column
• No data backup

Solutions:

Solution 1: Export data before rollback

-- Create backup table
CREATE TABLE users_email_backup AS
SELECT id, email FROM users;

-- Now safe to rollback
-- migrations/downgrade.py
def downgrade():
    op.drop_column('users', 'email')

Solution 2: Modify downgrade to preserve data

def downgrade():
    # Don't drop column, just mark as deprecated
    op.alter_column('users', 'email',
                    existing_type=sa.String(255),
                    comment='DEPRECATED: Will be removed in next version')

Solution 3: Don't rollback - fix forward

# Create new migration that fixes the issue
# migrations/abc456_fix_issue.py
def upgrade():
    # Fix the problem without rolling back
    op.alter_column('users', 'email', type_=sa.String(320))

---

Data Integrity Issues

Issue: Foreign key constraint violation during migration

Problem:

$ alembic upgrade head
IntegrityError: foreign key constraint "fk_orders_user" violated
Key (user_id)=(123) is not present in table "users"

Diagnosis:

• Data inconsistency exists
• Orphaned records
• Migration adds FK on dirty data

Solutions:

Solution 1: Clean data before adding constraint

def upgrade():
    # Delete orphaned records first
    op.execute("""
        DELETE FROM orders
        WHERE user_id NOT IN (SELECT id FROM users)
    """)

    # Now safe to add foreign key
    op.create_foreign_key(
        'fk_orders_user',
        'orders', 'users',
        ['user_id'], ['id']
    )

Solution 2: Set orphaned FKs to NULL

def upgrade():
    # Add column as nullable first
    op.add_column('orders', sa.Column('user_id', sa.Integer(), nullable=True))

    # Update valid references
    op.execute("""
        UPDATE orders o
        SET user_id = old_user_id
        FROM users u
        WHERE o.old_user_id = u.id
    """)

    # Handle orphans (set to NULL or default user)
    op.execute("""
        UPDATE orders
        SET user_id = NULL
        WHERE old_user_id NOT IN (SELECT id FROM users)
    """)

    # Now add foreign key
    op.create_foreign_key('fk_orders_user', 'orders', 'users', ['user_id'], ['id'])

Solution 3: Add constraint as NOT VALID (PostgreSQL)

-- Add constraint without validating existing data
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

-- Validate in separate transaction (can be done later)
ALTER TABLE orders
VALIDATE CONSTRAINT fk_orders_user;

---

Issue: Unique constraint violation during data migration

Problem:

IntegrityError: duplicate key value violates unique constraint "users_email_key"

Diagnosis:

• Data has duplicates
• Migration adds unique constraint
• Need to deduplicate first

Solutions:

Solution 1: Identify and remove duplicates

def upgrade():
    # Find duplicates
    op.execute("""
        WITH duplicates AS (
            SELECT email, MIN(id) as keep_id
            FROM users
            GROUP BY email
            HAVING COUNT(*) > 1
        )
        DELETE FROM users
        WHERE id NOT IN (SELECT keep_id FROM duplicates)
    """)

    # Now add unique constraint
    op.create_unique_constraint('uq_users_email', 'users', ['email'])

Solution 2: Merge duplicate records

def upgrade():
    # Merge duplicates (keep oldest, update references)
    op.execute("""
        WITH duplicates AS (
            SELECT email, array_agg(id ORDER BY created_at) as ids
            FROM users
            GROUP BY email
            HAVING COUNT(*) > 1
        )
        UPDATE orders
        SET user_id = d.ids[1]  -- Keep oldest
        FROM duplicates d, users u
        WHERE orders.user_id = ANY(d.ids[2:])
        AND u.email = d.email
    """)

    # Delete merged records
    op.execute("""
        WITH duplicates AS (
            SELECT email, array_agg(id ORDER BY created_at) as ids
            FROM users
            GROUP BY email
            HAVING COUNT(*) > 1
        )
        DELETE FROM users
        WHERE id IN (
            SELECT unnest(ids[2:]) FROM duplicates
        )
    """)

    # Add constraint
    op.create_unique_constraint('uq_users_email', 'users', ['email'])

Solution 3: Suffix duplicates

def upgrade():
    # Add suffix to duplicate emails
    op.execute("""
        WITH ranked AS (
            SELECT id, email,
                   ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
            FROM users
        )
        UPDATE users u
        SET email = u.email || '_' || r.rn
        FROM ranked r
        WHERE u.id = r.id AND r.rn > 1
    """)

    # Now add unique constraint
    op.create_unique_constraint('uq_users_email', 'users', ['email'])

---

Performance Problems

Issue: Migration locks table for too long

Problem:

$ alembic upgrade head
# ... hangs for 10 minutes ...
# Production writes are blocked!

Diagnosis:

• Adding NOT NULL to large table
• Creating index without CONCURRENTLY
• Altering column type on large table

Solutions:

Solution 1: Use CONCURRENTLY for indexes (PostgreSQL)

def upgrade():
    # Create index without locking
    op.execute("""
        CREATE INDEX CONCURRENTLY idx_users_email ON users(email)
    """)

# Note: Cannot use in transaction
# Alembic: Set transaction_per_migration = False

Solution 2: Add column in phases

# Phase 1: Add as nullable
def upgrade():
    op.add_column('users', sa.Column('email', sa.String(255), nullable=True))

# Phase 2 (separate migration): Make NOT NULL
def upgrade():
    # Backfill first
    op.execute("UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL")

    # Then add constraint
    op.alter_column('users', 'email', nullable=False)

Solution 3: Use batched updates

def upgrade():
    # Process in batches to avoid long locks
    batch_size = 1000
    while True:
        result = op.execute(f"""
            WITH batch AS (
                SELECT id FROM users
                WHERE email IS NULL
                LIMIT {batch_size}
            )
            UPDATE users u
            SET email = 'unknown@example.com'
            FROM batch b
            WHERE u.id = b.id
        """)

        if result.rowcount == 0:
            break

---

Issue: Migration takes too long on large table

Problem:

# Migration running for 2 hours on 100M row table
ALTER TABLE events ADD COLUMN processed BOOLEAN DEFAULT FALSE;

Diagnosis:

• Large table
• Expensive default value calculation
• Full table rewrite required

Solutions:

Solution 1: Add without default, set later

def upgrade():
    # Add column without default (instant in PostgreSQL 11+)
    op.add_column('events', sa.Column('processed', sa.Boolean(), nullable=True))

    # Set default for future rows
    op.alter_column('events', 'processed', server_default=sa.false())

    # Backfill in batches (asynchronously)
    # Use background worker to set existing rows

Solution 2: Use background worker for backfill

# In migration
def upgrade():
    op.add_column('events', sa.Column('processed', sa.Boolean(), nullable=True))

# Separately, run background job
@celery.task
def backfill_processed_flag():
    batch_size = 10000
    while True:
        updated = db.execute("""
            UPDATE events
            SET processed = FALSE
            WHERE processed IS NULL
            LIMIT :batch_size
        """, batch_size=batch_size)

        if updated == 0:
            break

Solution 3: Partition table first (PostgreSQL)

-- For very large tables, partition before migration
CREATE TABLE events_new (LIKE events INCLUDING ALL)
PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events_new
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Migrate data in chunks
INSERT INTO events_new SELECT * FROM events WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

-- Swap tables
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;

---

Migration Checklist for Troubleshooting

When migration fails:

1. Don't panic → Most migrations can be fixed forward 2. Check database state → What actually exists in the database? 3. Check migration state → What does migration tool think state is? 4. Read error messages → They usually tell you exactly what's wrong 5. Test on copy first → Never experiment on production 6. Have backup ready → Always have rollback option 7. Document the fix → Help future you and teammates

---

Related References

• [Decision Trees](./decision-trees.md) - Make better migration strategy decisions
• [Zero-Downtime Patterns](./zero-downtime-patterns.md) - Prevent issues with proper patterns (coming soon)
• [Rollback Procedures](./rollback-procedures.md) - Detailed rollback strategies (coming soon)