Database Migration Helper

This skill helps you create database migration files that follow your project's ORM conventions and naming patterns.

When to Use This Skill

• User requests to create a database migration
• Adding new tables or columns to the database
• Modifying existing database schema
• Creating indexes, constraints, or relationships
• User mentions "migration", "schema change", or "database update"

Instructions

1. Detect the ORM/Migration Tool

First, identify which ORM or migration tool the project uses:

• Prisma: Look for

  prisma/schema.prisma

  or

  @prisma/client

  in package.json
• Sequelize: Look for

  .sequelizerc

  or

  sequelize-cli

  in package.json
• Knex: Look for

  knexfile.js

  or

  knex

  in package.json
• TypeORM: Look for

  ormconfig.json

  or

  typeorm

  in package.json
• Alembic (Python): Look for

  alembic.ini

  or

  alembic/

  directory
• Django: Look for

  manage.py

  and Django migrations in

  */migrations/

• Active Record (Rails): Look for

  db/migrate/

  directory
• Flyway: Look for

  flyway.conf

  or

  db/migration/

• Liquibase: Look for

  liquibase.properties

  or changelog files

Use Glob to search for these indicator files.

2. Examine Existing Migrations

Read existing migration files to understand:

• Naming conventions (timestamp format, description format)
• Directory structure
• Migration file format (SQL, JavaScript, TypeScript, Python, etc.)
• Coding patterns (up/down functions, forwards/rollback, etc.)

Use Grep to find recent migrations: look in common directories like:

• prisma/migrations/

• db/migrate/

• migrations/

  or

  database/migrations/

• alembic/versions/

3. Generate Migration File

Based on the detected ORM, create an appropriate migration file:

Prisma

• Run

  npx prisma migrate dev --name <description>

  OR
• Manually create migration SQL in

  prisma/migrations/<timestamp>_<name>/migration.sql

Sequelize

• Generate:

  npx sequelize-cli migration:generate --name <description>

• Then fill in the up/down functions with the schema changes

Knex

• Generate:

  npx knex migrate:make <description>

• Fill in exports.up and exports.down functions

TypeORM

• Generate:

  npm run typeorm migration:create src/migrations/<Name>

• Implement up() and down() methods

Alembic

• Generate:

  alembic revision -m "<description>"

• Fill in upgrade() and downgrade() functions

Django

• Run:

  python manage.py makemigrations

• Or manually create migration in

  <app>/migrations/

Rails

• Generate:

  rails generate migration <ClassName>

• Fill in the change method (or up/down for complex migrations)

4. Follow Naming Conventions

Use consistent, descriptive names:

• Good:

  add_user_email_index

  ,

  create_products_table

  ,

  add_payment_status_to_orders

• Bad:

  migration1

  ,

  update

  ,

  fix

Format based on project patterns:

• Timestamp prefix:

  20231215120000_add_email_to_users

• Sequential:

  001_create_users

  ,

  002_add_indexes

5. Include Both Up and Down/Rollback

Always provide both directions when supported:

• Up/Upgrade/Forward: Apply the schema change
• Down/Downgrade/Rollback: Revert the schema change

For ORMs that use reversible operations (Rails, some Sequelize), a single

change

method may be sufficient.

6. Migration Content Guidelines

Creating Tables:

• Define all columns with appropriate types
• Set NOT NULL constraints where appropriate
• Add primary keys
• Include timestamps (created_at, updated_at) if project uses them
• Add foreign keys and indexes in the same migration or separate if project prefers

Altering Tables:

• Be specific:

  ADD COLUMN

  ,

  DROP COLUMN

  ,

  MODIFY COLUMN

• Handle existing data appropriately (defaults, backfills)
• Consider backwards compatibility

Adding Indexes:

• Name indexes clearly:

  idx_users_email

  ,

  idx_orders_user_id_created_at

• Use appropriate index types (B-tree, Hash, GIN, etc.)
• Consider partial indexes for large tables

Data Migrations:

• Separate schema migrations from data migrations if possible
• Be cautious with large datasets (batch operations)
• Test rollback with realistic data volumes

7. Validate Migration Safety

Before finalizing, check:

• Reversibility: Can the migration be rolled back?
• Data loss: Will any data be lost? Warn the user!
• Downtime: Will this lock tables? Consider online migrations for large tables
• Dependencies: Are there dependent migrations that must run first?

8. Testing Recommendations

Suggest to the user:

• Run migration on a development database first
• Test rollback functionality
• For production: test on a staging environment
• Review generated SQL (for ORMs that auto-generate)

ORM-Specific Templates

Reference the templates in

templates/

directory:

• prisma-migration.sql

  - Prisma migration example

• sequelize-migration.js

  - Sequelize migration example

• knex-migration.js

  - Knex migration example

• typeorm-migration.ts

  - TypeORM migration example

• alembic-migration.py

  - Alembic migration example

• rails-migration.rb

  - Rails migration example

Best Practices

1. One purpose per migration: Don't mix unrelated changes
2. Descriptive names: Names should explain what the migration does
3. Timestamps: Use the ORM's timestamp format for ordering
4. Idempotent when possible: Safe to run multiple times
5. Test rollbacks: Ensure down/rollback works correctly
6. Document complex logic: Add comments for non-obvious operations
7. Batch large operations: For data migrations affecting many rows
8. Use transactions: Wrap operations in transactions when supported

Supporting Files

• templates/

  : Migration templates for various ORMs

• reference.md

  : Naming conventions and migration patterns