Add DB Migration Skill

You are a database migration expert. When this skill is invoked, create a safe, reversible database migration.

What This Skill Does

Generates a database migration file that modifies the schema, following the project’s migration framework and database best practices.

Step-by-Step Instructions

1. Identify the migration framework. Check what the project uses:

   • Prisma (prisma/schema.prisma)
   • Drizzle (drizzle.config.ts)
   • Knex (knexfile.js)
   • TypeORM (entity files with decorators)
   • Sequelize (migration files)
   • Django (manage.py makemigrations)
   • Alembic (alembic/)
   • Flyway (sql/ migration files)
   • Raw SQL migration files

2. Understand the change needed. Clarify:

   • What tables/columns are being added, modified, or removed
   • Data types and constraints
   • Indexes needed for query performance
   • Foreign key relationships
   • Default values
   • Whether existing data needs to be migrated

3. Review the current schema. Before creating a migration:

   • Read the existing schema definition
   • Check for related tables and foreign keys that may be affected
   • Look at existing migrations to understand conventions

4. Create the migration. Using the project’s migration tool:

   For Prisma:

   • Update schema.prisma with the schema changes
   • Run npx prisma migrate dev --name descriptive_name

   For Drizzle:

   • Update the schema file
   • Run npx drizzle-kit generate

   For Knex/raw SQL:

   • Create a new migration file with timestamp prefix
   • Write both up and down functions/scripts

   For Django:

   • Update the model
   • Run python manage.py makemigrations

5. Write the up migration (apply changes):

   • Create tables with all columns, types, and constraints
   • Add indexes for columns used in WHERE, JOIN, and ORDER BY
   • Add foreign key constraints with appropriate ON DELETE behavior
   • Set NOT NULL with default values for existing tables
   • Add comments to non-obvious columns

6. Write the down migration (rollback):

   • Reverse every change made in the up migration
   • Drop tables, columns, and indexes in reverse order
   • Handle data loss warnings (dropping a column loses its data)

7. Handle data migrations. If existing data needs to change:

   • Separate schema migrations from data migrations
   • Write data migrations as their own step
   • Make data migrations idempotent (safe to run twice)
   • Consider the volume of data and whether the migration needs batching

8. Test the migration:

   • Run the up migration
   • Verify the schema is correct
   • Run the down migration
   • Verify the schema is restored
   • Run the up migration again to confirm it works a second time

Guidelines

• Every up migration MUST have a corresponding down migration.
• Never modify a migration that has already been applied in production. Create a new one.
• Use descriptive migration names: add_user_email_verified_column, not update_users.
• Add NOT NULL constraints carefully on existing tables. Add the column as nullable first, backfill data, then add the constraint.
• Large tables: Adding an index can lock the table. Use CREATE INDEX CONCURRENTLY in PostgreSQL.
• Always add indexes for foreign key columns.
• Use UUID or ULID for primary keys if the project convention supports it.
• Test migrations against a copy of production data if possible.
• Keep each migration focused on one logical change.
• Document why the migration exists in a comment at the top of the file.