Database Migration

Database Migration Generator

Generate database migrations with rollback capability for schema changes, with built-in ToM verification for safe database operations.

When to Invoke

Auto-invoke when user mentions:

• "Create migration"
• "Add table"
• "Modify schema"
• "Change database"
• "Database migration for [change]"
• "Add column to [table]"
• "Rename [table/column]"

What This Does

1. Detects migration framework (Knex, Prisma, TypeORM, raw SQL)
2. Gathers migration requirements
3. Verifies understanding before generating (ToM checkpoint - critical for DB changes)
4. Generates migration file with timestamp
5. Creates schema change (up migration)
6. Creates rollback (down migration)
7. Validates migration safety
8. Shows migration summary

Execution Steps

Step 1: Detect Migration Framework

Check project for migration tool:

# Check for Knex
if [ -f "knexfile.js" ] || [ -f "knexfile.ts" ] || grep -q '"knex"' package.json 2>/dev/null; then
  echo "Knex detected"
fi

# Check for Prisma
if [ -f "prisma/schema.prisma" ]; then
  echo "Prisma detected"
fi

# Check for TypeORM
if [ -f "ormconfig.json" ] || [ -f "ormconfig.ts" ] || grep -q '"typeorm"' package.json 2>/dev/null; then
  echo "TypeORM detected"
fi

# Check for Drizzle
if grep -q '"drizzle-orm"' package.json 2>/dev/null; then
  echo "Drizzle detected"
fi

Framework detection result:

Detected: {FRAMEWORK}
Migration directory: {MIGRATION_PATH}
Naming convention: {CONVENTION}

If no framework detected:

⚠️  No migration framework detected

Options:
1. Generate raw SQL migrations
2. Set up Knex (recommended for flexibility)
3. Set up Prisma (recommended for type safety)

Your choice [1-3]:

Step 2: Gather Migration Requirements

Ask user for migration details:

Migration name: [e.g., add_user_verification_columns]
Change type:
  - create_table (new table)
  - add_column (add to existing table)
  - modify_column (change existing column)
  - drop_column (remove column)
  - rename (rename table or column)
  - add_index (create index)
  - add_constraint (foreign key, unique, etc.)

Target table: [e.g., users]

Schema details: [describe the changes]

Step 2.5: Verify Understanding (ToM Checkpoint - ALWAYS for DB) [EXECUTE]

CRITICAL: This step MUST ALWAYS be executed for database migrations. No exceptions.

Database migrations are high-stakes - ALWAYS verify before generating.

Display verification:

I understood you want:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Migration: {MIGRATION_NAME}
Framework: {FRAMEWORK} (detected)
Type: {CHANGE_TYPE}
Target: {TABLE_NAME}
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Schema Changes (UP):
{SCHEMA_CHANGE_PREVIEW}

Rollback (DOWN):
{ROLLBACK_PREVIEW}

⚠️  Database migrations affect production data
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Assumptions I'm making:
- Column types match existing conventions
- Indexes will use default naming
- No data migration needed (schema only)

Proceed with generation? [Y/n]

Never skip verification for database migrations - they can cause data loss.

Step 3: Generate Migration File

Based on detected framework:

Knex Migration

# Generate filename
TIMESTAMP=$(date +%Y%m%d%H%M%S)
FILENAME="${TIMESTAMP}_${MIGRATION_NAME}.ts"

# Create migration file
Write(
  file_path: "migrations/${FILENAME}",
  content: [knex migration template]
)

Knex template:

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  ${UP_MIGRATION}
}

export async function down(knex: Knex): Promise<void> {
  ${DOWN_MIGRATION}
}

Prisma Migration

# Prisma uses schema.prisma + migrate commands
# Update schema.prisma with new models/fields
# Then run: npx prisma migrate dev --name ${MIGRATION_NAME}

Show Prisma workflow:

Prisma detected - updating schema.prisma

1. I'll update prisma/schema.prisma with:
   ${SCHEMA_CHANGES}

2. Run migration:
   npx prisma migrate dev --name ${MIGRATION_NAME}

3. Generate client:
   npx prisma generate

TypeORM Migration

TIMESTAMP=$(date +%Y%m%d%H%M%S)
FILENAME="${TIMESTAMP}-${MIGRATION_NAME}.ts"

TypeORM template:

import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class ${MIGRATION_CLASS_NAME}${TIMESTAMP} implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    ${UP_MIGRATION}
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    ${DOWN_MIGRATION}
  }
}

Step 4: Generate Rollback Logic

Ensure every UP has a corresponding DOWN:

Warning for destructive operations:

⚠️  DROP COLUMN in DOWN migration will lose data!

Column: {COLUMN_NAME}
Type: {COLUMN_TYPE}

If this column has data, consider:
1. Backup data before migration
2. Add data migration step
3. Keep column but deprecate

Understood? [Y/n]

Step 5: Validate Migration Safety

Check for common issues:

Migration Safety Check:
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
✅ Rollback defined (can undo changes)
✅ No DROP TABLE without backup warning
✅ No ALTER on large tables without consideration
⚠️  Adding NOT NULL column - needs DEFAULT value
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Safety warnings to check:

• Adding NOT NULL without DEFAULT (will fail on existing rows)
• Dropping columns with data
• Renaming columns (may break application code)
• Adding UNIQUE constraint (may fail if duplicates exist)
• Large table alterations (may lock table)

Step 6: Show Migration Summary

Display completed migration:

✅ Migration Created: {MIGRATION_NAME}

File: {MIGRATION_PATH}/{FILENAME}
Framework: {FRAMEWORK}
Timestamp: {TIMESTAMP}

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Schema Changes:
┌─────────────────────────────────────────────┐
│ UP (Apply)                                  │
├─────────────────────────────────────────────┤
│ {UP_MIGRATION_SUMMARY}                      │
└─────────────────────────────────────────────┘

┌─────────────────────────────────────────────┐
│ DOWN (Rollback)                             │
├─────────────────────────────────────────────┤
│ {DOWN_MIGRATION_SUMMARY}                    │
└─────────────────────────────────────────────┘

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Run Migration:
{RUN_COMMAND}

Test Rollback:
{ROLLBACK_COMMAND}

Next Steps:
1. Review migration file
2. Test on development database
3. Run migration: {RUN_COMMAND}
4. Verify schema changes
5. Commit migration file

Schema Change Templates

Create Table (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('${TABLE_NAME}', (table) => {
    table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'));
    ${COLUMN_DEFINITIONS}
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('${TABLE_NAME}');
}

Add Column (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.${COLUMN_TYPE}('${COLUMN_NAME}')${MODIFIERS};
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.dropColumn('${COLUMN_NAME}');
  });
}

Add Index (Knex)

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.index(['${COLUMN_NAME}'], '${INDEX_NAME}');
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable('${TABLE_NAME}', (table) => {
    table.dropIndex(['${COLUMN_NAME}'], '${INDEX_NAME}');
  });
}

Framework-Specific Commands

Knex

# Run pending migrations
npx knex migrate:latest

# Rollback last batch
npx knex migrate:rollback

# Run specific migration
npx knex migrate:up ${MIGRATION_NAME}

# Check status
npx knex migrate:status

Prisma

# Create and apply migration
npx prisma migrate dev --name ${MIGRATION_NAME}

# Apply in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Check status
npx prisma migrate status

TypeORM

# Run pending migrations
npx typeorm migration:run

# Revert last migration
npx typeorm migration:revert

# Generate migration from entities
npx typeorm migration:generate -n ${MIGRATION_NAME}

# Show migrations
npx typeorm migration:show

Error Handling

Framework not detected:

⚠️  No migration framework detected in project

Please set up a migration framework first:
- Knex: npm install knex && npx knex init
- Prisma: npm install prisma && npx prisma init
- TypeORM: npm install typeorm && create ormconfig

Migration name conflict:

⚠️  Migration with similar name already exists

Existing: 20251209_add_users_table.ts
Requested: add_users_table

Options:
1. Use different name
2. Add version suffix (add_users_table_v2)
3. Check if existing migration is sufficient

Your choice [1-3]:

Validation failure:

❌ Migration validation failed

Issues:
- Column 'status' is NOT NULL but has no DEFAULT
- Table 'orders' doesn't exist (referenced in foreign key)

Fix these issues before generating migration.

Success Criteria

Migration is successful when:

• Migration file generated with unique timestamp
• Framework conventions followed
• UP migration creates/modifies schema correctly
• DOWN migration rolls back changes completely
• ToM verification passed (user confirmed understanding)
• Safety checks passed
• Commands shown for running migration

Best Practices

Naming Conventions

• create_users_table - for new tables
• add_email_to_users - for adding columns
• add_index_on_users_email - for indexes
• change_status_type_in_orders - for modifications

Safety

• Always test on development database first
• Backup production before running migrations
• Use transactions where supported
• Consider data migration for non-null columns

Code Review

• Review generated SQL before running
• Check rollback logic is complete
• Verify no data loss in DOWN migration
• Test full rollback cycle

Database migrations affect production data - ToM verification is mandatory for this skill 🗄️