migration-generator

What this skill does

# 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
```sql
-- 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
```sql
-- 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)
```sql
-- 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
```sql
-- 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)
```sql
-- 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
```prisma
// 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")
}
```

```bash
# 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:**
```sql
-- 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
```typescript
// 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',