Database Migration Helper

Database Migration Helper Skill

Expert at creating safe, reversible database migrations across different frameworks and tools.

When to Activate

• "create database migration for [change]"
• "generate migration to add [table/column]"
• "write data migration for [transformation]"

Prisma Migrations

// prisma/schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  slug        String    @unique
  content     String    @db.Text
  published   Boolean   @default(false)
  authorId    Int
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  publishedAt DateTime?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  @@index([slug])
  @@index([authorId, publishedAt])
  @@map("posts")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

# Create migration
npx prisma migrate dev --name add_user_role

# Apply migrations
npx prisma migrate deploy

# Reset database (development only)
npx prisma migrate reset

# Create migration without applying
npx prisma migrate dev --create-only

TypeORM Migrations

// migrations/1234567890-AddUserRole.ts
import { MigrationInterface, QueryRunner, TableColumn } from 'typeorm';

export class AddUserRole1234567890 implements MigrationInterface {
  name = 'AddUserRole1234567890';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Add role column
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'role',
        type: 'enum',
        enum: ['user', 'admin', 'moderator'],
        default: "'user'",
      })
    );

    // Create index
    await queryRunner.createIndex(
      'users',
      new Index({
        name: 'IDX_USERS_ROLE',
        columnNames: ['role'],
      })
    );

    // Data migration - set existing users to 'user' role
    await queryRunner.query(
      `UPDATE users SET role = 'user' WHERE role IS NULL`
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Remove index
    await queryRunner.dropIndex('users', 'IDX_USERS_ROLE');

    // Remove column
    await queryRunner.dropColumn('users', 'role');
  }
}

# Generate migration from entity changes
npm run typeorm migration:generate -- -n AddUserRole

# Create empty migration
npm run typeorm migration:create -- -n DataMigration

# Run migrations
npm run typeorm migration:run

# Revert last migration
npm run typeorm migration:revert

Alembic (Python) Migrations

# alembic/versions/001_add_user_role.py
"""add user role

Revision ID: 001
Revises:
Create Date: 2024-01-01 12:00:00

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers
revision = '001'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # Create enum type
    role_enum = postgresql.ENUM('user', 'admin', 'moderator', name='role')
    role_enum.create(op.get_bind())

    # Add column
    op.add_column(
        'users',
        sa.Column('role', role_enum, nullable=False, server_default='user')
    )

    # Create index
    op.create_index('ix_users_role', 'users', ['role'])

    # Data migration
    op.execute("""
        UPDATE users
        SET role = 'admin'
        WHERE email IN (SELECT email FROM admin_emails)
    """)


def downgrade():
    # Remove index
    op.drop_index('ix_users_role', table_name='users')

    # Remove column
    op.drop_column('users', 'role')

    # Drop enum
    op.execute('DROP TYPE role')

# Create migration
alembic revision -m "add user role"

# Auto-generate migration from models
alembic revision --autogenerate -m "add user role"

# Run migrations
alembic upgrade head

# Rollback one migration
alembic downgrade -1

# Show current version
alembic current

Sequelize Migrations (Node.js)

// migrations/20240101120000-add-user-role.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    // Add column
    await queryInterface.addColumn('users', 'role', {
      type: Sequelize.ENUM('user', 'admin', 'moderator'),
      allowNull: false,
      defaultValue: 'user',
    });

    // Add index
    await queryInterface.addIndex('users', ['role'], {
      name: 'users_role_idx',
    });

    // Data migration using raw SQL
    await queryInterface.sequelize.query(`
      UPDATE users
      SET role = 'admin'
      WHERE is_admin = true
    `);

    // Remove old column
    await queryInterface.removeColumn('users', 'is_admin');
  },

  down: async (queryInterface, Sequelize) => {
    // Re-add old column
    await queryInterface.addColumn('users', 'is_admin', {
      type: Sequelize.BOOLEAN,
      defaultValue: false,
    });

    // Reverse data migration
    await queryInterface.sequelize.query(`
      UPDATE users
      SET is_admin = true
      WHERE role = 'admin'
    `);

    // Remove index
    await queryInterface.removeIndex('users', 'users_role_idx');

    // Remove column and enum
    await queryInterface.removeColumn('users', 'role');
    await queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_users_role"');
  },
};

Raw SQL Migration Template

-- Up Migration
-- migrations/001_add_user_role_up.sql

-- Add enum type (PostgreSQL)
CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator');

-- Add column
ALTER TABLE users ADD COLUMN role user_role NOT NULL DEFAULT 'user';

-- Create index
CREATE INDEX idx_users_role ON users(role);

-- Data migration
UPDATE users SET role = 'admin' WHERE id IN (1, 2, 3);

-- Down Migration
-- migrations/001_add_user_role_down.sql

-- Remove index
DROP INDEX IF EXISTS idx_users_role;

-- Remove column
ALTER TABLE users DROP COLUMN IF EXISTS role;

-- Drop type
DROP TYPE IF EXISTS user_role;

Complex Data Migration Example

// Data transformation migration
export class MigrateUserData1234567890 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // 1. Create new table structure
    await queryRunner.query(`
      CREATE TABLE users_new (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) UNIQUE NOT NULL,
        profile JSONB NOT NULL,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `);

    // 2. Migrate data with transformation
    await queryRunner.query(`
      INSERT INTO users_new (id, email, profile, created_at)
      SELECT
        id,
        email,
        jsonb_build_object(
          'firstName', first_name,
          'lastName', last_name,
          'phone', phone,
          'address', jsonb_build_object(
            'street', address_street,
            'city', address_city,
            'zip', address_zip
          )
        ) as profile,
        created_at
      FROM users_old
    `);

    // 3. Drop old table
    await queryRunner.query(`DROP TABLE users_old`);

    // 4. Rename new table
    await queryRunner.query(`ALTER TABLE users_new RENAME TO users`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Reverse migration
    // ... implementation
  }
}

Best Practices

• Always include both up and down migrations
• Test migrations on copy of production data
• Use transactions for data migrations
• Add indexes after data insertion for large tables
• Version control all migrations
• Never modify existing migrations after deployment
• Use descriptive migration names
• Add comments explaining complex migrations
• Test rollback procedures
• Back up database before major migrations
• Use batching for large data migrations
• Monitor migration execution time
• Handle NULL values properly
• Validate data after migration

Output Checklist

• ✅ Migration file created
• ✅ Up migration implemented
• ✅ Down migration implemented
• ✅ Indexes added
• ✅ Data migration (if needed)
• ✅ Constraints added
• ✅ Tested on sample data
• 📝 Migration notes documented