Database Migration and Schema Evolution Patterns

This comprehensive guide documents database migration patterns for modern applications, covering both traditional approaches and AI-assisted workflows with Claude Code. Whether you’re using TypeScript with popular ORMs (Prisma, TypeORM, Sequelize) or leveraging AI for intelligent automation, these patterns enable reliable schema evolution across SQL and NoSQL databases.

Table of Contents

1. Overview
2. Traditional Migration Patterns
3. AI-Assisted Migration with Claude Code
4. Best Practices
5. Real-World Examples

Overview {#overview}

Database migrations are critical for evolving your application’s data layer safely and reliably. This guide covers:

• Traditional Patterns: Automated generation, multi-database support, zero-downtime strategies
• AI-Assisted Patterns: Intelligent migration generation, context-aware evolution, automated testing
• Safety First: Rollback strategies, data validation, performance optimization
• Modern Workflows: CI/CD integration, cross-environment orchestration, monitoring

Traditional Migration Patterns {#traditional-patterns}

Automated Migration Generation

Modern ORMs provide powerful migration generation capabilities:

Prisma Auto-Generation

// prisma/schema.prisma
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
 
// migration-generator.ts
import { exec } from 'child_process';
import { promisify } from 'util';
 
const execAsync = promisify(exec);
 
export class PrismaMigrationGenerator {
  async generateMigration(name: string): Promise<void> {
    try {
      const { stdout } = await execAsync(
        `npx prisma migrate dev --name ${name} --create-only`
      );
      
      console.log('Migration generated:', stdout);
      await this.reviewMigration(name);
    } catch (error) {
      console.error('Migration generation failed:', error);
      throw error;
    }
  }
 
  private async reviewMigration(name: string): Promise<void> {
    // Implement automated checks for dangerous operations
  }
}

TypeORM Entity-Based Generation

// entities/User.entity.ts
import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn } from 'typeorm';
 
@Entity()
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;
 
  @Column({ unique: true })
  email: string;
 
  @Column({ nullable: true })
  name?: string;
 
  @CreateDateColumn()
  createdAt: Date;
}
 
// Generate migration based on entity changes
const migrationGenerator = new TypeORMMigrationGenerator(dataSource);
await migrationGenerator.generateMigration('add-user-entity');

Zero-Downtime Migration Strategies

Blue-Green Deployment Pattern

export class BlueGreenMigration {
  constructor(
    private blueDb: DatabaseConnection,
    private greenDb: DatabaseConnection,
    private healthChecker: HealthChecker
  ) {}
 
  async performMigration(): Promise<void> {
    // Step 1: Sync databases
    await this.syncDatabases();
    
    // Step 2: Apply migrations to green
    await this.applyMigrationsToGreen();
    
    // Step 3: Validate green database
    if (!await this.validateGreenDatabase()) {
      throw new Error('Green database validation failed');
    }
    
    // Step 4: Gradually shift traffic
    await this.shiftTraffic();
    
    // Step 5: Monitor and decide
    await this.monitorAndDecide();
  }
 
  private async shiftTraffic(): Promise<void> {
    const steps = [10, 25, 50, 75, 100];
    
    for (const percentage of steps) {
      await this.router.setGreenTrafficPercentage(percentage);
      await this.sleep(60000); // Wait 1 minute
      
      if (!await this.isHealthy()) {
        await this.router.setGreenTrafficPercentage(0);
        throw new Error(`Health check failed at ${percentage}%`);
      }
    }
  }
}

Dual Writing Pattern

export class DualWriteMigration {
  constructor(
    private oldDb: DatabaseConnection,
    private newDb: DatabaseConnection,
    private flagService: FeatureFlagService
  ) {}
 
  async write(data: any): Promise<void> {
    // Always write to old database
    await this.oldDb.write(data);
    
    // Conditionally write to new database
    if (await this.featureFlag.isEnabled()) {
      try {
        await this.newDb.write(data);
      } catch (error) {
        console.error('Dual write to new database failed:', error);
        // Log but don't fail the operation
      }
    }
  }
}

Data Validation and Integrity

export class SchemaValidator {
  private schemas: Map<string, Joi.Schema> = new Map();
 
  async validateTable(tableName: string, db: DatabaseConnection): Promise<ValidationResult> {
    const schema = this.schemas.get(tableName);
    const rows = await db.query(`SELECT * FROM ${tableName}`);
    const errors: ValidationError[] = [];
    
    for (const row of rows) {
      const { error } = schema.validate(row);
      if (error) {
        errors.push({
          rowId: row.id,
          errors: error.details.map(d => d.message)
        });
      }
    }
 
    return {
      tableName,
      totalRows: rows.length,
      validRows: rows.length - errors.length,
      errors
    };
  }
}

Performance Optimization

Batch Processing

export class BatchMigrationProcessor {
  private batchSize: number = 1000;
  private concurrency: number = 5;
 
  async processMigration(
    migration: BatchableMigration,
    db: DatabaseConnection
  ): Promise<void> {
    const totalRecords = await migration.getTotalRecords();
    const batches = Math.ceil(totalRecords / this.batchSize);
    
    const batchJobs = Array.from({ length: batches }, (_, i) => ({
      offset: i * this.batchSize,
      limit: this.batchSize,
      batchNumber: i + 1
    }));
    
    await this.processInBatches(batchJobs, async (job) => {
      await this.processBatch(migration, db, job);
    });
  }
}

Index-Aware Migration

export class IndexAwareMigration {
  async optimizeIndexOperations(
    operations: IndexOperation[],
    db: DatabaseConnection
  ): Promise<void> {
    const operationsByTable = this.groupByTable(operations);
    
    for (const [table, tableOperations] of operationsByTable.entries()) {
      // Drop indexes first
      const dropOps = tableOperations.filter(op => op.type === 'drop');
      for (const op of dropOps) {
        await this.dropIndex(op, db);
      }
      
      // Then create new indexes
      const createOps = tableOperations.filter(op => op.type === 'create');
      for (const op of createOps) {
        await this.createIndex(op, db);
      }
    }
  }
}

AI-Assisted Migration Patterns {#ai-assisted-patterns}

Intelligent Migration Generation with Claude Code

Claude Code transforms database migrations through AI-powered automation:

Context-Aware Schema Evolution

// Claude analyzes your entire project structure
async function evolveSchemaWithClaude() {
  // Claude analyzes models and usage patterns
  const analysis = await claude.analyzeSchema({
    models: './src/models',
    queries: './src/queries',
    performance: './logs/slow-queries.log'
  });
  
  // Generate optimized migration based on analysis
  const migration = await claude.generateMigration({
    analysis,
    strategy: 'zero-downtime',
    targetDatabase: 'postgresql'
  });
  
  return migration;
}

Automatic Migration from Natural Language

// Example: Claude generates migration from description
// User: "I added a 'status' field to the User model with enum values"
 
// Claude Code generates:
export class AddStatusToUsers1234567890 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    // Create enum type
    await queryRunner.query(`
      CREATE TYPE user_status_enum AS ENUM (
        'active', 'inactive', 'suspended', 'deleted'
      )
    `);
    
    // Add column with default
    await queryRunner.query(`
      ALTER TABLE "users"
      ADD COLUMN "status" user_status_enum DEFAULT 'active'
    `);
    
    // Backfill existing records
    await queryRunner.query(`
      UPDATE "users"
      SET "status" = 'active'
      WHERE "status" IS NULL
    `);
    
    // Make column required
    await queryRunner.query(`
      ALTER TABLE "users"
      ALTER COLUMN "status" SET NOT NULL
    `);
  }
  
  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "users" DROP COLUMN "status"`);
    await queryRunner.query(`DROP TYPE user_status_enum`);
  }
}

Multi-Database Orchestration

Claude Code excels at coordinating migrations across heterogeneous databases:

async function migrateHeterogeneousDatastores() {
  const migrations = await claude.generateMultiDatabaseMigrations({
    postgres: {
      schema: './schemas/postgres.sql',
      target: 'Add user preferences table'
    },
    mongodb: {
      schema: './schemas/mongodb.json',
      target: 'Add preferences subdocument to users'
    },
    redis: {
      schema: './schemas/redis-structures.yaml',
      target: 'Add user preference cache keys'
    }
  });
  
  // Execute with dependency awareness
  await claude.orchestrateMigrations(migrations, {
    strategy: 'dependency-aware',
    rollbackOnFailure: true
  });
}

Intelligent Data Transformation

async function transformDataWithClaude(request: DataTransformationRequest) {
  // Claude analyzes source and target schemas
  const transformationPlan = await claude.planDataTransformation(request);
  
  // Generate transformation code
  const transformer = await claude.generateTransformer({
    plan: transformationPlan,
    language: 'typescript',
    batchSize: 10000,
    errorHandling: 'continue-with-logging'
  });
  
  // Execute with progress tracking
  await claude.executeTransformation(transformer, {
    onProgress: (progress) => console.log(`Progress: ${progress.percentage}%`),
    onError: (error, record) => logError(error, record),
    parallel: true,
    maxConcurrency: 5
  });
}

Schema Drift Detection and Correction

class ClaudeSchemaDriftDetector {
  async detectAndCorrectDrift() {
    const environments = ['development', 'staging', 'production'];
    const schemas = await Promise.all(
      environments.map(env => this.getSchema(env))
    );
    
    // Claude identifies discrepancies
    const drift = await claude.analyzeSchemaDrift({
      schemas,
      baseline: 'production',
      ignorePatterns: ['temp_*', '*_backup']
    });
    
    // Generate correction migrations
    for (const env of drift.environmentsWithDrift) {
      const correction = await claude.generateDriftCorrection({
        environment: env,
        targetSchema: drift.baseline,
        safetyLevel: 'maximum',
        preserveData: true
      });
      
      await this.reviewAndApply(correction, env);
    }
  }
}

Best Practices {#best-practices}

1. Safety First Approach

class SafeMigrationExecutor {
  async executeMigration(migration: Migration) {
    // 1. Pre-flight checks
    await this.validateMigration(migration);
    
    // 2. Create backup point
    const backup = await this.createBackup();
    
    // 3. Test in transaction
    await this.testInTransaction(migration);
    
    // 4. Execute with monitoring
    const result = await this.executeWithMonitoring(migration);
    
    // 5. Validate results
    await this.validateResults(result);
    
    // 6. Clean up or rollback
    if (result.success) {
      await this.cleanupBackup(backup);
    } else {
      await this.rollbackToBackup(backup);
    }
  }
}

2. Comprehensive Testing

describe('Migration Tests', () => {
  test('should apply migration successfully', async () => {
    await migration.up();
    expect(await migration.validate()).toBe(true);
  });
 
  test('should rollback migration successfully', async () => {
    await migration.up();
    await migration.down();
    expect(finalSnapshot).toEqual(initialSnapshot);
  });
 
  test('should handle concurrent migrations', async () => {
    const promises = Array(5).fill(null).map(() => migration.up());
    const results = await Promise.allSettled(promises);
    const successful = results.filter(r => r.status === 'fulfilled');
    expect(successful.length).toBe(1);
  });
});

3. Documentation and Runbooks

// Generate comprehensive documentation with Claude
async function documentMigration(migration: Migration) {
  const documentation = await claude.generateMigrationDocs({
    migration,
    includeDecisions: true,
    includeRollbackPlan: true,
    includePerformanceTesting: true,
    format: 'markdown'
  });
  
  const runbook = await claude.generateRunbook({
    migration,
    environments: ['staging', 'production'],
    includeHealthChecks: true,
    includeRollbackTriggers: true
  });
  
  return { documentation, runbook };
}

4. CI/CD Integration

async function setupMigrationCI() {
  return {
    preCommit: async (files: string[]) => {
      const migrationFiles = files.filter(f => f.includes('migration'));
      
      for (const file of migrationFiles) {
        await claude.validateMigration(file, {
          checkReversibility: true,
          checkDataSafety: true,
          checkPerformance: true
        });
      }
    },
    
    preMerge: async (pr: PullRequest) => {
      const testResult = await claude.testAgainstProduction({
        migrations: pr.migrations,
        dataSubset: 'representative-sample',
        performanceBaseline: true
      });
      
      return testResult;
    }
  };
}

5. Monitoring and Observability

class MigrationMonitor {
  async monitorMigration(migration: Migration) {
    const metrics = {
      duration: 0,
      recordsProcessed: 0,
      errors: [],
      performance: {
        cpuUsage: [],
        memoryUsage: [],
        diskIO: []
      }
    };
    
    // Monitor during execution
    const monitoring = setInterval(async () => {
      metrics.performance.cpuUsage.push(await this.getCPUUsage());
      metrics.performance.memoryUsage.push(await this.getMemoryUsage());
      metrics.performance.diskIO.push(await this.getDiskIO());
    }, 1000);
    
    try {
      const start = Date.now();
      await migration.execute();
      metrics.duration = Date.now() - start;
    } finally {
      clearInterval(monitoring);
    }
    
    return metrics;
  }
}

Real-World Examples {#real-world-examples}

Supabase Migration Automation

async function automateSupabaseMigration() {
  // Claude reads your TypeScript models
  const models = await claude.analyzeTypeScriptModels('./src/models');
  
  // Generate Supabase migration
  const migration = await claude.generateSupabaseMigration({
    models,
    features: {
      rowLevelSecurity: true,
      realtime: ['users', 'posts'],
      indexes: 'auto-detect'
    }
  });
  
  // Apply with verification
  await claude.applySupabaseMigration(migration, {
    project: process.env.SUPABASE_PROJECT_ID,
    environment: 'staging',
    runTests: true
  });
}

Prisma Schema Evolution

async function evolvePrismaSchema() {
  // Claude analyzes your Prisma schema and usage
  const analysis = await claude.analyzePrismaUsage({
    schema: './prisma/schema.prisma',
    queries: './src/**/*.ts',
    performance: true
  });
  
  // Generate optimized schema
  const optimizedSchema = await claude.optimizePrismaSchema({
    current: analysis.schema,
    suggestions: await claude.suggestPrismaOptimizations(analysis),
    constraints: {
      backwardCompatible: true,
      preserveRelations: true
    }
  });
  
  // Generate migration with tests
  await claude.generatePrismaMigration({
    name: 'optimize-schema',
    schema: optimizedSchema,
    generateTests: true,
    generateRollback: true
  });
}

Legacy Database Modernization

async function modernizeLegacyDatabase() {
  // Claude analyzes legacy structure
  const legacyAnalysis = await claude.analyzeLegacyDatabase({
    connection: process.env.LEGACY_DB,
    includeData: true,
    sampleSize: 10000
  });
  
  // Generate modernization plan
  const plan = await claude.createModernizationPlan({
    analysis: legacyAnalysis,
    target: 'postgresql-15',
    improvements: [
      'normalize-denormalized-tables',
      'add-missing-foreign-keys',
      'optimize-data-types',
      'add-indexes-for-common-queries',
      'implement-soft-deletes'
    ]
  });
  
  // Execute modernization in phases
  for (const phase of plan.phases) {
    await claude.executeModernizationPhase(phase, {
      validateData: true,
      performanceTest: true,
      rollbackPoint: true
    });
  }
}

Key Takeaways

Traditional Patterns

• Always test migrations in isolated environments
• Implement rollback strategies for every migration
• Monitor performance during execution
• Ensure zero downtime with careful planning
• Version control everything including schemas

AI-Assisted Benefits

• Reduced error rate through AI validation
• Faster development with automated generation
• Better documentation automatically created
• Safer deployments with intelligent strategies
• Continuous optimization based on usage patterns

Combined Approach

The most effective strategy combines traditional engineering practices with AI assistance:

• Use AI for generation and validation
• Apply traditional patterns for execution and monitoring
• Leverage both for testing and documentation
• Maintain human oversight for critical decisions

Conclusion

Database migrations are critical operations that benefit from both proven engineering patterns and modern AI assistance. By combining traditional approaches with Claude Code’s intelligent automation, teams can evolve their databases more safely, quickly, and reliably than ever before.

Whether you’re performing simple schema changes or complex multi-database transformations, these patterns provide the foundation for successful migration strategies in production environments.

Resources

• Claude Code Documentation
• Prisma Migration Guide
• TypeORM Migrations
• Zero-Downtime Deployment