Migration Strategies

Migration Strategies Skill

This skill covers database migration patterns and best practices.

When to Use

Use this skill when:

• Making schema changes
• Deploying database updates
• Handling data migrations
• Planning rollback strategies

Core Principle

FORWARD-ONLY, REVERSIBLE - Migrations should be forward-only in production but designed to be logically reversible.

Migration Types

Schema Migrations

• Add/remove tables
• Add/remove columns
• Modify column types
• Add/remove indexes
• Add/remove constraints

Data Migrations

• Backfill data
• Transform existing data
• Migrate between schemas

Safe Migration Patterns

Adding Columns

-- Safe: Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Safe: Add column with default
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

-- Unsafe: Add NOT NULL without default
-- ALTER TABLE users ADD COLUMN required_field TEXT NOT NULL; -- DON'T DO THIS

Adding Non-Null Columns

// Step 1: Add nullable column
await prisma.$executeRaw`ALTER TABLE users ADD COLUMN bio TEXT`;

// Step 2: Backfill data
await prisma.$executeRaw`UPDATE users SET bio = '' WHERE bio IS NULL`;

// Step 3: Add NOT NULL constraint
await prisma.$executeRaw`ALTER TABLE users ALTER COLUMN bio SET NOT NULL`;

Renaming Columns (Zero Downtime)

// Step 1: Add new column
await db.schema.alterTable('users').addColumn('full_name', 'text');

// Step 2: Backfill data
await db.update(users).set({ fullName: sql`${users.name}` });

// Step 3: Update application to use new column
// Deploy code that writes to both columns

// Step 4: Stop writing to old column
// Deploy code that only uses new column

// Step 5: Remove old column (separate migration)
await db.schema.alterTable('users').dropColumn('name');

Adding Indexes (Non-Blocking)

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

-- Don't do this in production:
-- CREATE INDEX users_email_idx ON users(email); -- Blocks table

Removing Columns

// Step 1: Stop reading the column in code
// Deploy code that doesn't read the column

// Step 2: Stop writing the column in code
// Deploy code that doesn't write the column

// Step 3: Remove the column
await db.schema.alterTable('users').dropColumn('old_column');

Prisma Migration Workflow

Development

# Create and apply migration
npx prisma migrate dev --name add_phone_to_users

# Reset database (destructive)
npx prisma migrate reset

# Apply without generating
npx prisma db push

Production

# Apply pending migrations
npx prisma migrate deploy

# Check migration status
npx prisma migrate status

Migration File

-- prisma/migrations/20240101_add_phone/migration.sql
-- CreateTable
ALTER TABLE "users" ADD COLUMN "phone" TEXT;

-- CreateIndex
CREATE INDEX "users_phone_idx" ON "users"("phone");

Drizzle Migration Workflow

Generate Migration

npx drizzle-kit generate:pg

Apply Migration

// src/db/migrate.ts
import { migrate } from 'drizzle-orm/postgres-js/migrator';

await migrate(db, { migrationsFolder: './drizzle' });

Data Migration Pattern

// migrations/backfill-user-slugs.ts
import { db } from '../src/db';
import { users } from '../src/db/schema';
import { isNull } from 'drizzle-orm';
import slugify from 'slugify';

const BATCH_SIZE = 1000;

async function backfillSlugs(): Promise<void> {
  let processed = 0;

  while (true) {
    const batch = await db
      .select({ id: users.id, name: users.name })
      .from(users)
      .where(isNull(users.slug))
      .limit(BATCH_SIZE);

    if (batch.length === 0) break;

    await db.transaction(async (tx) => {
      for (const user of batch) {
        const slug = slugify(user.name, { lower: true });
        await tx.update(users)
          .set({ slug })
          .where(eq(users.id, user.id));
      }
    });

    processed += batch.length;
    console.log(`Processed ${processed} users`);
  }

  console.log(`Backfill complete. Total: ${processed}`);
}

backfillSlugs().catch(console.error);

Rollback Strategies

With Prisma

# Prisma doesn't have built-in rollback
# Instead, create a new migration that reverses changes

# Mark migration as rolled back (doesn't undo changes)
npx prisma migrate resolve --rolled-back 20240101_add_phone

Manual Rollback

// migrations/rollback-20240101.ts
import { db } from '../src/db';

async function rollback(): Promise<void> {
  await db.transaction(async (tx) => {
    // Reverse the changes
    await tx.schema.alterTable('users').dropColumn('phone');
  });

  // Update migration table
  await db.delete(migrations)
    .where(eq(migrations.name, '20240101_add_phone'));
}

rollback().catch(console.error);

Zero-Downtime Migration Checklist

1. Backward compatible - Old code works with new schema
2. Forward compatible - New code works with old schema
3. Non-blocking - Use CONCURRENTLY for indexes
4. Batched - Process large data in batches
5. Idempotent - Safe to run multiple times
6. Monitored - Watch for locks and performance

Migration Testing

// migrations/__tests__/add-phone.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { migrate } from '../add-phone';
import { rollback } from '../rollback-add-phone';
import { setupTestDb, teardownTestDb } from '../../tests/utils';

describe('add-phone migration', () => {
  beforeAll(async () => {
    await setupTestDb();
  });

  afterAll(async () => {
    await teardownTestDb();
  });

  it('adds phone column', async () => {
    await migrate();

    const columns = await db.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'phone'
    `);

    expect(columns.length).toBe(1);
  });

  it('rollback removes phone column', async () => {
    await rollback();

    const columns = await db.query(`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'users' AND column_name = 'phone'
    `);

    expect(columns.length).toBe(0);
  });
});

CI/CD Integration

# .github/workflows/migrate.yml
name: Database Migration

on:
  push:
    branches: [main]
    paths:
      - 'prisma/migrations/**'

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: '22'

      - run: npm ci

      - name: Run migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

Best Practices

1. One change per migration - Easier to debug and rollback
2. Test migrations - Run against production-like data
3. Backup before migrating - Always have a restore point
4. Monitor locks - Watch for blocking queries
5. Schedule large migrations - During low-traffic periods
6. Document migrations - Explain why, not just what

Notes

• Never modify existing migrations after deployment
• Large data migrations should be batched
• Use transactions for consistency
• Always test rollback procedures