database-migration-helper

What this skill does

# 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