Database Migration

Database Migration

Master database schema and data migrations across ORMs (Sequelize, TypeORM, Prisma), including rollback strategies and zero-downtime deployments.

When to Use This Skill

• Migrating between different ORMs
• Performing schema transformations
• Moving data between databases
• Implementing rollback procedures
• Zero-downtime deployments
• Database version upgrades
• Data model refactoring

ORM Migrations

Sequelize Migrations

// migrations/20231201-create-users.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      email: {
        type: Sequelize.STRING,
        unique: true,
        allowNull: false,
      },
      createdAt: Sequelize.DATE,
      updatedAt: Sequelize.DATE,
    })
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('users')
  },
}

// Run: npx sequelize-cli db:migrate
// Rollback: npx sequelize-cli db:migrate:undo

TypeORM Migrations

// migrations/1701234567-CreateUsers.ts
import { MigrationInterface, QueryRunner, Table } from 'typeorm'

export class CreateUsers1701234567 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'CURRENT_TIMESTAMP',
          },
        ],
      })
    )
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('users')
  }
}

// Run: npm run typeorm migration:run
// Rollback: npm run typeorm migration:revert

Prisma Migrations

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  createdAt DateTime @default(now())
}

// Generate migration: npx prisma migrate dev --name create_users
// Apply: npx prisma migrate deploy

Schema Transformations

Adding Columns with Defaults

// Safe migration: add column with default
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('users', 'status', {
      type: Sequelize.STRING,
      defaultValue: 'active',
      allowNull: false,
    })
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'status')
  },
}

Renaming Columns (Zero Downtime)

// Step 1: Add new column
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('users', 'full_name', {
      type: Sequelize.STRING,
    })

    // Copy data from old column
    await queryInterface.sequelize.query('UPDATE users SET full_name = name')
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'full_name')
  },
}

// Step 2: Update application to use new column

// Step 3: Remove old column
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'name')
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('users', 'name', {
      type: Sequelize.STRING,
    })
  },
}

Changing Column Types

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // For large tables, use multi-step approach

    // 1. Add new column
    await queryInterface.addColumn('users', 'age_new', {
      type: Sequelize.INTEGER,
    })

    // 2. Copy and transform data
    await queryInterface.sequelize.query(`
      UPDATE users
      SET age_new = CAST(age AS INTEGER)
      WHERE age IS NOT NULL
    `)

    // 3. Drop old column
    await queryInterface.removeColumn('users', 'age')

    // 4. Rename new column
    await queryInterface.renameColumn('users', 'age_new', 'age')
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.changeColumn('users', 'age', {
      type: Sequelize.STRING,
    })
  },
}

Data Transformations

Complex Data Migration

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Get all records
    const [users] = await queryInterface.sequelize.query('SELECT id, address_string FROM users')

    // Transform each record
    for (const user of users) {
      const addressParts = user.address_string.split(',')

      await queryInterface.sequelize.query(
        `UPDATE users
         SET street = :street,
             city = :city,
             state = :state
         WHERE id = :id`,
        {
          replacements: {
            id: user.id,
            street: addressParts[0]?.trim(),
            city: addressParts[1]?.trim(),
            state: addressParts[2]?.trim(),
          },
        }
      )
    }

    // Drop old column
    await queryInterface.removeColumn('users', 'address_string')
  },

  down: async (queryInterface, Sequelize) => {
    // Reconstruct original column
    await queryInterface.addColumn('users', 'address_string', {
      type: Sequelize.STRING,
    })

    await queryInterface.sequelize.query(`
      UPDATE users
      SET address_string = CONCAT(street, ', ', city, ', ', state)
    `)

    await queryInterface.removeColumn('users', 'street')
    await queryInterface.removeColumn('users', 'city')
    await queryInterface.removeColumn('users', 'state')
  },
}

Rollback Strategies

Transaction-Based Migrations

module.exports = {
  up: async (queryInterface, Sequelize) => {
    const transaction = await queryInterface.sequelize.transaction()

    try {
      await queryInterface.addColumn(
        'users',
        'verified',
        { type: Sequelize.BOOLEAN, defaultValue: false },
        { transaction }
      )

      await queryInterface.sequelize.query('UPDATE users SET verified = true WHERE email_verified_at IS NOT NULL', {
        transaction,
      })

      await transaction.commit()
    } catch (error) {
      await transaction.rollback()
      throw error
    }
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'verified')
  },
}

Checkpoint-Based Rollback

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Create backup table
    await queryInterface.sequelize.query('CREATE TABLE users_backup AS SELECT * FROM users')

    try {
      // Perform migration
      await queryInterface.addColumn('users', 'new_field', {
        type: Sequelize.STRING,
      })

      // Verify migration
      const [result] = await queryInterface.sequelize.query(
        'SELECT COUNT(*) as count FROM users WHERE new_field IS NULL'
      )

      if (result[0].count > 0) {
        throw new Error('Migration verification failed')
      }

      // Drop backup
      await queryInterface.dropTable('users_backup')
    } catch (error) {
      // Restore from backup
      await queryInterface.sequelize.query('DROP TABLE users')
      await queryInterface.sequelize.query('CREATE TABLE users AS SELECT * FROM users_backup')
      await queryInterface.dropTable('users_backup')
      throw error
    }
  },
}

Zero-Downtime Migrations

Blue-Green Deployment Strategy

// Phase 1: Make changes backward compatible
module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Add new column (both old and new code can work)
    await queryInterface.addColumn('users', 'email_new', {
      type: Sequelize.STRING,
    })
  },
}

// Phase 2: Deploy code that writes to both columns

// Phase 3: Backfill data
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.sequelize.query(`
      UPDATE users
      SET email_new = email
      WHERE email_new IS NULL
    `)
  },
}

// Phase 4: Deploy code that reads from new column

// Phase 5: Remove old column
module.exports = {
  up: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'email')
  },
}

Cross-Database Migrations

PostgreSQL to MySQL

// Handle differences
module.exports = {
  up: async (queryInterface, Sequelize) => {
    const dialectName = queryInterface.sequelize.getDialect()

    if (dialectName === 'mysql') {
      await queryInterface.createTable('users', {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        data: {
          type: Sequelize.JSON, // MySQL JSON type
        },
      })
    } else if (dialectName === 'postgres') {
      await queryInterface.createTable('users', {
        id: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          autoIncrement: true,
        },
        data: {
          type: Sequelize.JSONB, // PostgreSQL JSONB type
        },
      })
    }
  },
}

Resources

• references/orm-switching.md: ORM migration guides
• references/schema-migration.md: Schema transformation patterns
• references/data-transformation.md: Data migration scripts
• references/rollback-strategies.md: Rollback procedures
• assets/schema-migration-template.sql: SQL migration templates
• assets/data-migration-script.py: Data migration utilities
• scripts/test-migration.sh: Migration testing script

Best Practices

1. Always Provide Rollback: Every up() needs a down()
2. Test Migrations: Test on staging first
3. Use Transactions: Atomic migrations when possible
4. Backup First: Always backup before migration
5. Small Changes: Break into small, incremental steps
6. Monitor: Watch for errors during deployment
7. Document: Explain why and how
8. Idempotent: Migrations should be rerunnable

Common Pitfalls

• Not testing rollback procedures
• Making breaking changes without downtime strategy
• Forgetting to handle NULL values
• Not considering index performance
• Ignoring foreign key constraints
• Migrating too much data at once