migration-helper

Skill: Migration Helper

Category: Dev Expert Version: 1.0.0 Used By: database-specialist, backend agents

Overview

Guide safe database and code migrations with zero-downtime strategies.

1. Migration Safety Rules

1. Always backup before migration
2. Test migration on staging first
3. Make migrations reversible
4. Deploy in small batches
5. Monitor during migration

2. Database Migration Patterns

Schema Changes

Zero-Downtime Pattern

Phase 1: Add new column (nullable)
Phase 2: Dual-write (write to both columns)
Phase 3: Backfill old data
Phase 4: Switch reads to new column
Phase 5: Remove old column writes
Phase 6: Drop old column

3. Migration Script Template

// migrations/20250128_add_user_status.ts
export async function up(db: Database) {
  // 1. Add column
  await db.query(`
    ALTER TABLE users
    ADD COLUMN status VARCHAR(20) DEFAULT 'active'
  `)

  // 2. Backfill in batches
  let lastId = 0
  while (true) {
    const rows = await db.query(`
      UPDATE users SET status = 'active'
      WHERE id > $1 AND status IS NULL
      LIMIT 1000
      RETURNING id
    `, [lastId])
    if (rows.length === 0) break
    lastId = rows[rows.length - 1].id
  }

  // 3. Add constraint
  await db.query(`
    ALTER TABLE users
    ALTER COLUMN status SET NOT NULL
  `)
}

export async function down(db: Database) {
  await db.query(`ALTER TABLE users DROP COLUMN status`)
}

4. Code Migration Strategies

Feature Flag Migration

// Phase 1: Behind flag
if (featureFlags.newPaymentSystem) {
  return newPaymentService.process(order)
}
return oldPaymentService.process(order)

// Phase 2: Gradually increase flag percentage
// Phase 3: Remove flag, delete old code

Strangler Fig Pattern

1. Identify bounded context to migrate
2. Create new implementation alongside old
3. Route traffic gradually to new
4. Remove old when new is stable

5. Data Migration Checklist

Before:

• Full backup created
• Migration tested on staging
• Rollback plan documented
• Monitoring alerts configured

During:

• Run in batches (not all at once)
• Monitor performance/errors
• Verify data integrity

After:

• Data validation queries
• Application smoke tests
• Monitor for 24-48 hours
• Document changes

6. Common Migration Scenarios

Adding Required Column

-- Wrong: Locks table, fails on existing rows
ALTER TABLE users ADD COLUMN email VARCHAR NOT NULL;

-- Right: Three-phase approach
ALTER TABLE users ADD COLUMN email VARCHAR;
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Large Table Index

-- Wrong: Blocks writes
CREATE INDEX idx_orders_user ON orders(user_id);

-- Right: Non-blocking
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

Type Conversion

-- Add new column
ALTER TABLE products ADD COLUMN price_cents INTEGER;

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

-- Switch application to use new column
-- Drop old column
ALTER TABLE products DROP COLUMN price;

7. Rollback Strategies

Best Practices

Do's

• Test migrations on copy of prod data
• Make migrations idempotent
• Batch large data updates
• Monitor during execution
• Keep rollback scripts ready

Don'ts

• Run untested migrations on prod
• Make irreversible changes without backup
• Migrate during peak traffic
• Skip staging environment
• Assume migrations are fast

Version: 1.0.0 | Last Updated: 2025-11-28