db-migrator

Database Migration Assistant

Trigger Conditions

Activate this skill when the user requests database migration, migration generation, schema changes, or database upgrades.

Workflow

Step 1: Identify the Framework

Scan the project files to determine the ORM/migration framework in use:

• alembic.ini / alembic/ → Alembic
• prisma/schema.prisma → Prisma
• pom.xml Contains flyway → Flyway
• manage.py + settings.py → Django
• Gemfile + db/migrate/ → Rails

Step 2: Analyze Schema Changes

• Compare the models definitions with the existing migration history
• Identify added/modified/deleted tables and fields
• Detect index, constraint, and foreign key changes

Step 3: Generate Migration Scripts

Generate the corresponding migration files based on the framework, and also generate rollback scripts.

Step 4: Risk Assessment

• Large table DDL changes (table lock risk)
• Data migration requirements
• Backward compatibility

Migration Templates

Alembic (Python)

"""add email column to users

Revision ID: abc123
"""
from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('users', sa.Column('email', sa.String(255), nullable=True))
    op.create_index('ix_users_email', 'users', ['email'], unique=True)

def downgrade():
    op.drop_index('ix_users_email')
    op.drop_column('users', 'email')

Flyway (Java)

-- V2__add_email_to_users.sql
ALTER TABLE users ADD COLUMN email VARCHAR(255);
CREATE UNIQUE INDEX ix_users_email ON users(email);

-- 回滚脚本 (单独文件 U2__rollback_add_email.sql)
-- 逆向操作

Prisma (Node.js)

// schema.prisma 变更
model User {
  id    Int     @id @default(autoincrement())
  name  String
  email String? @unique  // 新增字段
}

# 默认仅生成迁移，不执行数据库变更
npx prisma migrate dev --name add-email-to-users --create-only

After checking the generated SQL, connection target, and backup, have the user confirm execution:

# 开发环境应用迁移
npx prisma migrate dev

# 生产环境应用已审核并提交的迁移
npx prisma migrate deploy

Django (Python)

# users/migrations/0002_add_email.py
from django.db import migrations, models

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

    operations = [
        migrations.AddField(
            model_name='user',
            name='email',
            field=models.EmailField(unique=True, null=True),
        ),
    ]

unique=True Django has already generated the unique constraint and corresponding index; do not create duplicate indexes.

Rails (Ruby)

# db/migrate/20260617_add_email_to_users.rb
class AddEmailToUsers < ActiveRecord::Migration[7.0]
  def change
    add_column :users, :email, :string
    add_index :users, :email, unique: true
  end
end

rails db:migrate         # 执行迁移
rails db:rollback        # 回滚上一次
rails db:rollback STEP=3 # 回滚最近 3 次

Rollback Strategy

Scenario	Strategy
Add column	Remove the column directly
Drop column	Cannot be rolled back automatically; back up data in advance
Modify column type	Revert to the original type, beware of data loss risk
Add table	Remove the table
Data migration	Write a reverse migration script and keep a snapshot of the original data
Large table changes	Use online DDL tools to avoid table locks

Notes

• By default, only generate and review migrations; before execution, confirm the environment, database connection, backup, and maintenance window
• Provide a downgrade when the framework supports reversible migrations; when it does not, clearly specify a snapshot restore or forward-fix plan
• Large table changes require consideration of online DDL to avoid long table locks
• Handle data migration and schema changes separately
• Migrations should be version-recorded by the framework and safely retryable; do not assume version migrations can be repeated arbitrarily
• Validate in a staging environment before applying to production
• For changes involving dropping columns/tables, ensure the application code has already removed the related references