Migration Generator Skill

Create database migrations from model changes, schema diffs, and migration best practices.

Instructions

You are a database migration expert. When invoked:

1. Detect Schema Changes:

   • Compare current schema with desired state
   • Identify added/removed tables and columns
   • Detect modified column types and constraints
   • Find changed indexes and foreign keys

2. Generate Migration Files:

   • Create forward (up) and backward (down) migrations
   • Use ORM-specific migration format when applicable
   • Include data migrations when needed
   • Handle edge cases and potential data loss

3. Ensure Safety:

   • Prevent accidental data deletion
   • Add rollback capability
   • Include validation steps
   • Warn about breaking changes

4. Best Practices:

   • Make migrations atomic and reversible
   • Avoid destructive operations in production
   • Test migrations on staging first
   • Keep migrations small and focused

Supported Frameworks

• SQL: Raw SQL migrations (PostgreSQL, MySQL, SQLite)
• Node.js: Prisma, TypeORM, Sequelize, Knex.js
• Python: Alembic, Django migrations, SQLAlchemy
• Ruby: Rails Active Record Migrations
• Go: golang-migrate, goose
• PHP: Laravel migrations, Doctrine

Usage Examples

@migration-generator Add user email verification
@migration-generator --from-diff
@migration-generator --rollback
@migration-generator --data-migration
@migration-generator --zero-downtime

Raw SQL Migrations

PostgreSQL - Add Table

-- migrations/001_create_users_table.up.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  active BOOLEAN DEFAULT true NOT NULL,
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL
);

-- Create indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_active ON users(active) WHERE active = true;

-- Add comments
COMMENT ON TABLE users IS 'Application users';
COMMENT ON COLUMN users.email IS 'User email address (unique)';

-- migrations/001_create_users_table.down.sql
DROP TABLE IF EXISTS users CASCADE;

Add Column with Default Value

-- migrations/002_add_email_verified.up.sql
-- Step 1: Add column as nullable
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

-- Step 2: Set default value for existing rows
UPDATE users SET email_verified = false WHERE email_verified IS NULL;

-- Step 3: Make column NOT NULL
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

-- Step 4: Set default for future rows
ALTER TABLE users ALTER COLUMN email_verified SET DEFAULT false;

-- migrations/002_add_email_verified.down.sql
ALTER TABLE users DROP COLUMN email_verified;

Modify Column Type (Safe)

-- migrations/003_increase_email_length.up.sql
-- Safe: increasing varchar length
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(320);

-- migrations/003_increase_email_length.down.sql
-- Warning: May fail if data exceeds old limit
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

Add Foreign Key

-- migrations/004_create_orders.up.sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
  status VARCHAR(20) DEFAULT 'pending' NOT NULL,
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL,

  CONSTRAINT fk_orders_user_id
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE CASCADE
);

-- Indexes for foreign keys and common queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index for common query pattern
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- migrations/004_create_orders.down.sql
DROP TABLE IF EXISTS orders CASCADE;

Rename Column (Safe)

-- migrations/005_rename_password_column.up.sql
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN password_hash_new VARCHAR(255);

-- Step 2: Copy data
UPDATE users SET password_hash_new = password_hash;

-- Step 3: Make NOT NULL
ALTER TABLE users ALTER COLUMN password_hash_new SET NOT NULL;

-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN password_hash;

-- Step 5: Rename new column
ALTER TABLE users RENAME COLUMN password_hash_new TO password_hash;

-- migrations/005_rename_password_column.down.sql
-- Reversible using same pattern
ALTER TABLE users ADD COLUMN password_hash_old VARCHAR(255);
UPDATE users SET password_hash_old = password_hash;
ALTER TABLE users ALTER COLUMN password_hash_old SET NOT NULL;
ALTER TABLE users DROP COLUMN password_hash;
ALTER TABLE users RENAME COLUMN password_hash_old TO password_hash;

ORM Migration Examples

Prisma Migrations

// schema.prisma - Add new model
model User {
  id            Int       @id @default(autoincrement())
  email         String    @unique
  username      String    @unique
  passwordHash  String    @map("password_hash")
  active        Boolean   @default(true)
  emailVerified Boolean   @default(false) @map("email_verified")
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")

  orders  Order[]
  profile UserProfile?

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

model UserProfile {
  id        Int      @id @default(autoincrement())
  userId    Int      @unique @map("user_id")
  bio       String?  @db.Text
  avatarUrl String?  @map("avatar_url")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("user_profiles")
}

# Generate migration
npx prisma migrate dev --name add_user_profile

# Apply migration in production
npx prisma migrate deploy

# Reset database (development only!)
npx prisma migrate reset

Generated Migration:

-- CreateTable
CREATE TABLE "user_profiles" (
    "id" SERIAL NOT NULL,
    "user_id" INTEGER NOT NULL,
    "bio" TEXT,
    "avatar_url" TEXT,

    CONSTRAINT "user_profiles_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "user_profiles_user_id_key" ON "user_profiles"("user_id");

-- AddForeignKey
ALTER TABLE "user_profiles" ADD CONSTRAINT "user_profiles_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

TypeORM Migrations

// migration/1234567890123-CreateUser.ts
import { MigrationInterface, QueryRunner, Table, TableIndex } from 'typeorm';

export class CreateUser1234567890123 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',
            length: '255',
            isUnique: true,
            isNullable: false,
          },
          {
            name: 'username',
            type: 'varchar',
            length: '50',
            isUnique: true,
            isNullable: false,
          },
          {
            name: 'password_hash',
            type: 'varchar',
            length: '255',
            isNullable: false,
          },
          {
            name: 'active',
            type: 'boolean',
            default: true,
            isNullable: false,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
            isNullable: false,
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()',
            isNullable: false,
          },
        ],
      }),
      true,
    );

    // Create indexes
    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'idx_users_email',
        columnNames: ['email'],
      }),
    );

    await queryRunner.createIndex(
      'users',
      new TableIndex({
        name: 'idx_users_username',
        columnNames: ['username'],
      }),
    );
  }

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

// migration/1234567890124-AddForeignKey.ts
import { MigrationInterface, QueryRunner, Table, TableForeignKey } from 'typeorm';

export class AddOrdersForeignKey1234567890124 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'orders',
        columns: [
          {
            name: 'id',
            type: 'int',
            isPrimary: true,
            isGenerated: true,
            generationStrategy: 'increment',
          },
          {
            name: 'user_id',
            type: 'int',
            isNullable: false,
          },
          {
            name: 'total_amount',
            type: 'decimal',
            precision: 10,
            scale: 2,
            isNullable: false,
          },
          {
            name: 'status',
            type: 'varchar',
            length: '20',
            default: "'pending'",
            isNullable: false,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      }),
      true,
    );

    // Add foreign key
    await queryRunner.createForeignKey(
      'orders',
      new TableForeignKey({
        columnNames: ['user_id'],
        referencedColumnNames: ['id'],
        referencedTableName: 'users',
        onDelete: 'CASCADE',
      }),
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    const table = await queryRunner.getTable('orders');
    const foreignKey = table.foreignKeys.find(
      fk => fk.columnNames.indexOf('user_id') !== -1,
    );
    await queryRunner.dropForeignKey('orders', foreignKey);
    await queryRunner.dropTable('orders');
  }
}

# Generate migration
npx typeorm migration:generate -n AddUserProfile

# Run migrations
npx typeorm migration:run

# Revert last migration
npx typeorm migration:revert

Alembic (Python/SQLAlchemy)

# alembic/versions/001_create_users_table.py
"""create users table

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

"""
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 users table
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('email', sa.String(length=255), nullable=False),
        sa.Column('username', sa.String(length=50), nullable=False),
        sa.Column('password_hash', sa.String(length=255), nullable=False),
        sa.Column('active', sa.Boolean(), server_default='true', nullable=False),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('email'),
        sa.UniqueConstraint('username')
    )

    # Create indexes
    op.create_index('idx_users_email', 'users', ['email'])
    op.create_index('idx_users_username', 'users', ['username'])
    op.create_index(
        'idx_users_active',
        'users',
        ['active'],
        postgresql_where=sa.text('active = true')
    )

def downgrade():
    op.drop_table('users')

# alembic/versions/002_add_email_verified.py
"""add email_verified column

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

"""
from alembic import op
import sqlalchemy as sa

revision = '002'
down_revision = '001'
branch_labels = None
depends_on = None

def upgrade():
    # Add column as nullable first
    op.add_column('users', sa.Column('email_verified', sa.Boolean(), nullable=True))

    # Set default value for existing rows
    op.execute('UPDATE users SET email_verified = false WHERE email_verified IS NULL')

    # Make column NOT NULL
    op.alter_column('users', 'email_verified', nullable=False, server_default='false')

def downgrade():
    op.drop_column('users', 'email_verified')

# Generate migration
alembic revision --autogenerate -m "add user profile"

# Run migrations
alembic upgrade head

# Rollback one migration
alembic downgrade -1

# Rollback to specific version
alembic downgrade 001

Django Migrations

# app/migrations/0001_initial.py
from django.db import migrations, models

class Migration(migrations.Migration):
    initial = True
    dependencies = []

    operations = [
        migrations.CreateModel(
            name='User',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True)),
                ('email', models.EmailField(max_length=255, unique=True)),
                ('username', models.CharField(max_length=50, unique=True)),
                ('password_hash', models.CharField(max_length=255)),
                ('active', models.BooleanField(default=True)),
                ('created_at', models.DateTimeField(auto_now_add=True)),
                ('updated_at', models.DateTimeField(auto_now=True)),
            ],
            options={
                'db_table': 'users',
            },
        ),
        migrations.AddIndex(
            model_name='user',
            index=models.Index(fields=['email'], name='idx_users_email'),
        ),
        migrations.AddIndex(
            model_name='user',
            index=models.Index(fields=['username'], name='idx_users_username'),
        ),
    ]

# app/migrations/0002_add_user_profile.py
from django.db import migrations, models
import django.db.models.deletion

class Migration(migrations.Migration):
    dependencies = [
        ('app', '0001_initial'),
    ]

    operations = [
        migrations.CreateModel(
            name='UserProfile',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True)),
                ('bio', models.TextField(blank=True, null=True)),
                ('avatar_url', models.URLField(blank=True, null=True)),
                ('user', models.OneToOneField(
                    on_delete=django.db.models.deletion.CASCADE,
                    to='app.user',
                    related_name='profile'
                )),
            ],
            options={
                'db_table': 'user_profiles',
            },
        ),
    ]

# Generate migrations
python manage.py makemigrations

# Apply migrations
python manage.py migrate

# Rollback to specific migration
python manage.py migrate app 0001

# Show migration status
python manage.py showmigrations

Data Migrations

Backfill Data (PostgreSQL)

-- migrations/006_backfill_user_roles.up.sql
-- Add role column
ALTER TABLE users ADD COLUMN role VARCHAR(20);

-- Backfill existing users with default role
UPDATE users SET role = 'member' WHERE role IS NULL;

-- Make NOT NULL after backfill
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';

-- Add check constraint
ALTER TABLE users ADD CONSTRAINT chk_users_role
  CHECK (role IN ('admin', 'member', 'guest'));

-- migrations/006_backfill_user_roles.down.sql
ALTER TABLE users DROP COLUMN role;

Complex Data Migration (Node.js/TypeORM)

// migration/1234567890125-MigrateUserData.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class MigrateUserData1234567890125 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    // Get all users
    const users = await queryRunner.query('SELECT id, full_name FROM users');

    // Split full_name into first_name and last_name
    for (const user of users) {
      const parts = user.full_name?.split(' ') || ['', ''];
      const firstName = parts[0] || '';
      const lastName = parts.slice(1).join(' ') || '';

      await queryRunner.query(
        'UPDATE users SET first_name = $1, last_name = $2 WHERE id = $3',
        [firstName, lastName, user.id],
      );
    }

    // Drop old column
    await queryRunner.query('ALTER TABLE users DROP COLUMN full_name');
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    // Add back full_name column
    await queryRunner.query('ALTER TABLE users ADD COLUMN full_name VARCHAR(255)');

    // Reconstruct full_name
    await queryRunner.query(
      `UPDATE users SET full_name = first_name || ' ' || last_name`,
    );

    // Drop first_name and last_name
    await queryRunner.query('ALTER TABLE users DROP COLUMN first_name');
    await queryRunner.query('ALTER TABLE users DROP COLUMN last_name');
  }
}

Data Migration with Python/Alembic

# alembic/versions/003_migrate_prices.py
"""migrate prices to cents

Revision ID: 003
Revises: 002
Create Date: 2024-01-03 12:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

revision = '003'
down_revision = '002'

def upgrade():
    # Add new column
    op.add_column('products', sa.Column('price_cents', sa.Integer(), nullable=True))

    # Migrate data: convert decimal to cents
    op.execute('''
        UPDATE products
        SET price_cents = CAST(price * 100 AS INTEGER)
    ''')

    # Make NOT NULL after migration
    op.alter_column('products', 'price_cents', nullable=False)

    # Drop old column
    op.drop_column('products', 'price')

    # Rename new column
    op.alter_column('products', 'price_cents', new_column_name='price')

def downgrade():
    # Add back decimal column
    op.add_column('products', sa.Column('price_decimal', sa.Numeric(10, 2), nullable=True))

    # Convert back to decimal
    op.execute('''
        UPDATE products
        SET price_decimal = price / 100.0
    ''')

    op.alter_column('products', 'price_decimal', nullable=False)
    op.drop_column('products', 'price')
    op.alter_column('products', 'price_decimal', new_column_name='price')

Zero-Downtime Migrations

Adding NOT NULL Column

-- Migration 1: Add column as nullable
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Deploy application code that writes to phone column

-- Migration 2: Backfill existing data
UPDATE users SET phone = 'UNKNOWN' WHERE phone IS NULL;

-- Migration 3: Make column NOT NULL
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users ALTER COLUMN phone SET DEFAULT 'UNKNOWN';

Renaming Column (Zero Downtime)

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- Phase 2: Deploy app code that writes to both columns

-- Phase 3: Backfill data
UPDATE users SET email_address = email WHERE email_address IS NULL;

-- Phase 4: Deploy app code that reads from new column

-- Phase 5: Drop old column
ALTER TABLE users DROP COLUMN email;

-- Phase 6: Rename new column (optional)
ALTER TABLE users RENAME COLUMN email_address TO email;

Removing Column (Safe)

-- Phase 1: Deploy code that doesn't use the column

-- Phase 2: Remove NOT NULL constraint (make safe to rollback)
ALTER TABLE users ALTER COLUMN deprecated_field DROP NOT NULL;

-- Phase 3: Wait and verify no issues

-- Phase 4: Drop the column
ALTER TABLE users DROP COLUMN deprecated_field;

Common Patterns

Add Enum Column

-- Create enum type (PostgreSQL)
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');

-- Add column with enum type
ALTER TABLE users ADD COLUMN status user_status DEFAULT 'active' NOT NULL;

-- Rollback
ALTER TABLE users DROP COLUMN status;
DROP TYPE user_status;

Add JSON Column

-- PostgreSQL
ALTER TABLE users ADD COLUMN metadata JSONB DEFAULT '{}' NOT NULL;
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- MySQL
ALTER TABLE users ADD COLUMN metadata JSON;

Add Full-Text Search

-- PostgreSQL
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Create generated column
UPDATE products SET search_vector =
  to_tsvector('english', name || ' ' || description);

-- Create GIN index for fast searching
CREATE INDEX idx_products_search ON products USING GIN(search_vector);

-- Trigger to keep search_vector updated
CREATE TRIGGER products_search_update
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', name, description);

Best Practices

DO ✓

• Make migrations reversible - Always implement down migration
• Test on staging first - Never run untested migrations in production
• Keep migrations small - One logical change per migration
• Use transactions - Ensure atomicity (when DB supports it)
• Backup before migration - Always have a rollback plan
• Add indexes concurrently - Use CONCURRENTLY in PostgreSQL to avoid locks
• Version control migrations - Commit migrations with code changes
• Document breaking changes - Add comments for complex migrations
• Use batch updates - For large data migrations, process in chunks

DON'T ✗

• Never modify committed migrations - Create new migration instead
• **Don't use SELECT *** - Specify columns in data migrations
• Avoid long-running migrations - Break into smaller steps
• Don't assume data state - Validate before transforming
• Never skip migrations - Run in order
• Don't ignore warnings - Address deprecation notices
• Avoid circular dependencies - Keep migration order clean
• Don't forget indexes - Especially on foreign keys

Migration Checklist

## Pre-Migration Checklist

- [ ] Migration tested on local database
- [ ] Migration tested on staging environment
- [ ] Database backup created
- [ ] Migration is reversible (down migration works)
- [ ] Reviewed for potential data loss
- [ ] Checked for long-running operations
- [ ] Foreign key constraints validated
- [ ] Indexes added for new columns
- [ ] Performance impact assessed
- [ ] Team notified of migration schedule

## Post-Migration Checklist

- [ ] Migration completed successfully
- [ ] Application logs checked for errors
- [ ] Database performance monitored
- [ ] Rollback plan tested (if needed)
- [ ] Documentation updated
- [ ] Migration marked as applied in version control

Troubleshooting

Migration Failed Mid-Way

-- Check migration status
SELECT * FROM schema_migrations;

-- Manual rollback if transaction failed
BEGIN;
-- Run down migration manually
ROLLBACK;

-- Or mark as not applied
DELETE FROM schema_migrations WHERE version = '20240101120000';

Large Table Migration

-- Use batch processing for large updates
DO $$
DECLARE
  batch_size INTEGER := 1000;
  offset_val INTEGER := 0;
  rows_updated INTEGER;
BEGIN
  LOOP
    UPDATE users
    SET email_verified = false
    WHERE id IN (
      SELECT id FROM users
      WHERE email_verified IS NULL
      ORDER BY id
      LIMIT batch_size
      OFFSET offset_val
    );

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    offset_val := offset_val + batch_size;
    COMMIT;
    RAISE NOTICE 'Updated % rows', offset_val;
  END LOOP;
END $$;

Notes

• Always test migrations in non-production environment first
• Use database transactions when possible
• Keep migrations in version control
• Document complex migrations
• Consider zero-downtime strategies for production
• Monitor database performance during migrations
• Have rollback plan ready
• Use ORM migration tools when available for type safety