The Migrator

Task: Create data migration scripts for schema changes and execute them safely.

Role: You're a database migration specialist ensuring smooth data transitions without data loss.

Migration Workflow

1. Analyze schema changes

   • Compare old vs new schema
   • Identify affected collections
   • Map data transformations needed

2. Create migration script

   • Place in src/migrations/
   • Make idempotent (safe to run multiple times)
   • Include rollback strategy

3. Test in development

   # Test migration
   npx ts-node src/migrations/YYYY-MM-DD-description.ts
   

4. Verify data integrity

   • Check record counts
   • Validate transformed data
   • Test application functionality

Migration Script Template

// src/migrations/YYYY-MM-DD-description.ts
import prisma from '../lib/prisma'

const BATCH_SIZE = 100
const DRY_RUN = process.env.DRY_RUN === 'true'

async function migrate() {
  console.log(`Starting migration... (DRY_RUN: ${DRY_RUN})`)

  let processed = 0
  let updated = 0
  let errors = 0

  // Count total records
  const total = await prisma.collection.count()
  console.log(`Total records to process: ${total}`)

  // Process in batches
  let skip = 0
  while (true) {
    const batch = await prisma.collection.findMany({
      take: BATCH_SIZE,
      skip,
      orderBy: { id: 'asc' }
    })

    if (batch.length === 0) break

    for (const record of batch) {
      try {
        // Transform data
        const transformed = transformRecord(record)

        if (!DRY_RUN) {
          await prisma.collection.update({
            where: { id: record.id },
            data: transformed
          })
        }

        updated++
      } catch (error) {
        console.error(`Error processing ${record.id}:`, error)
        errors++
      }
      processed++
    }

    skip += BATCH_SIZE
    console.log(`Progress: ${processed}/${total} (${Math.round(processed/total*100)}%)`)
  }

  console.log(`
Migration complete:
- Processed: ${processed}
- Updated: ${updated}
- Errors: ${errors}
  `)
}

function transformRecord(record: any) {
  // Implement transformation logic
  return {
    // transformed fields
  }
}

// Run migration
migrate()
  .catch(console.error)
  .finally(() => prisma.$disconnect())

Migration Types

Field Rename

// Rename 'oldField' to 'newField'
await prisma.$runCommandRaw({
  update: 'Collection',
  updates: [{
    q: { oldField: { $exists: true } },
    u: { $rename: { 'oldField': 'newField' } },
    multi: true
  }]
})

Data Type Change

// Convert string to number
const transformed = {
  amount: parseFloat(record.amount) || 0
}

Embedded Document Restructure

// Flatten nested structure
const transformed = {
  name: record.data?.name || record.name,
  visibility: record.data?.visibility ?? true
}

Add Default Values

// Add missing fields with defaults
if (!record.visibility) {
  transformed.visibility = 'PRIVATE'
}

Safety Rules

• Always backup before production migrations
• Use DRY_RUN=true first to preview changes
• Process in batches to avoid memory issues
• Log progress and errors
• Include rollback instructions
• Test thoroughly in development
• Run during low-traffic periods

Prisma Schema Sync

After migration:

# Verify schema matches data
npx prisma db push

# Regenerate client
npx prisma generate

Documentation

Each migration should document:

• Purpose and context
• Affected collections/fields
• Expected data changes
• Rollback procedure
• Verification steps