Database Migration Manager

Detect. Generate. Migrate. Roll back. Zero downtime.

Phase 1: Detect Migration Tool

Scan the project to identify the ORM/migration tool in use. Check in this order:

If no tool is detected, ask the user which they want to use. If starting fresh, recommend Drizzle for TypeScript projects, Alembic for Python, Django migrations for Django apps.

Read the existing migration history to understand current schema state before doing anything.

Phase 2: Determine the Operation

Classify what the user needs:

If the user's request implies multiple operations (e.g., "migrate production safely"), chain them: backup -> diff -> generate -> run.

Phase 3: Execute by Tool

Prisma

CODEBLOCK0

Prisma rollback: Prisma has no built-in rollback command. To roll back:

1. 1. Create a new migration that reverses the changes
2. Or restore from backup
3. For failed migrations: INLINECODE15

Prisma seed setup: Ensure package.json has:
CODEBLOCK1

Drizzle

CODEBLOCK2

Drizzle rollback: Drizzle generates SQL files. To roll back:

1. 1. Write a new migration that reverses changes
2. Or delete the migration file and re-push (dev only)
3. For production: always use generated SQL files, never INLINECODE17

Drizzle seed: Create a seed.ts file and run with tsx drizzle/seed.ts or add a script to package.json.

Knex

CODEBLOCK3

Knex migration template:
CODEBLOCK4

TypeORM

CODEBLOCK5

Alembic (Python/SQLAlchemy)

CODEBLOCK6

Django

CODEBLOCK7

Raw SQL

When no ORM is detected and the project uses raw SQL:

1. 1. Create a migrations/ directory if it doesn't exist
2. Name files with timestamp prefix: INLINECODE22
3. Each migration file should have -- UP and -- DOWN sections
4. Track applied migrations in a _migrations table

Raw SQL migration template:
CODEBLOCK8

Migration tracking table:
CODEBLOCK9

Phase 4: Database Backup

Always back up before running migrations in production. Offer to back up before any destructive operation.

Postgres

MySQL

SQLite

Phase 5: Schema Diff Between Environments

Compare schemas to detect drift between environments.

Prisma

Drizzle

Generic SQL Diff (Postgres)

Generic SQL Diff (MySQL)

For richer diffs, use migra (Postgres):
CODEBLOCK17

Phase 6: Seed Data

When creating seed scripts, follow these principles:

1. 1. Idempotent: Seeds must be safe to run multiple times (use upserts or check-before-insert)
2. Environment-aware: Different seed data for dev vs. staging vs. production
3. Realistic: Use realistic data shapes, not lorem ipsum
4. Referential integrity: Insert in dependency order (users before posts, etc.)
5. Deterministic: Use fixed IDs or consistent generation for reproducibility

Seed Script Structure

CODEBLOCK18

Python Seed Pattern (Alembic/Django)

CODEBLOCK19

CODEBLOCK20

Phase 7: Zero-Downtime Migrations

Use the expand-contract pattern for any schema change that could break running application code. This is mandatory for production deployments with rolling updates or blue-green deploys.

When to Use Zero-Downtime Patterns

The Expand-Contract Pattern

Step 1: Expand (Deploy migration + new code that writes to both)
CODEBLOCK21

Step 2: Backfill (Run as background job, batch processing)
CODEBLOCK22

Step 3: Contract (Deploy code that only reads from new column)
CODEBLOCK23

Safe Index Creation

CODEBLOCK24

Safe Foreign Key Addition (Postgres)

CODEBLOCK25

Rename Column Pattern

Never rename directly. Instead:

1. 1. Add new column
2. Deploy code that writes to both old and new
3. Backfill new column from old
4. Deploy code that reads from new column only
5. Drop old column after bake period

Multi-Deploy Migration Checklist

For any breaking schema change, split across multiple deploys:

• - [ ] Deploy 1: Add new column/table (expand)
• [ ] Deploy 2: Update writes to populate both old and new
• [ ] Deploy 3: Backfill existing data
• [ ] Deploy 4: Switch reads to new column/table
• [ ] Deploy 5: Stop writing to old column
• [ ] Deploy 6: Drop old column/table (contract) — after bake period

Safety Rules

1. 1. Never run migrate reset, push, or db push in production — these are dev-only commands
2. Always back up before migrating production — offer this proactively
3. Never drop columns or tables without confirming with the user — even if the schema change implies it
4. Review generated SQL before applying — auto-generated migrations can be destructive
5. Test migrations on a staging copy of production data — operations that take milliseconds on dev can lock for minutes on large tables
6. Monitor during production migrations — watch replication lag, CPU, lock contention
7. Keep migrations small and focused — one concern per migration file
8. Never skip the down/rollback function — every up needs a corresponding down
9. Use transactions where supported — Postgres DDL is transactional, MySQL is not (most DDL auto-commits)
10. Check for active connections before dropping — INLINECODE30

Error Recovery

If a migration fails mid-way:

1. 1. Check migration status — which migrations applied, which failed
2. Do NOT re-run blindly — understand what partially applied
3. For Prisma: npx prisma migrate resolve --rolled-back <name> to mark as rolled back
4. For Alembic: alembic stamp <last_good_revision> to reset tracking
5. For Django: Fix the issue, then python3 manage.py migrate again (Django tracks per-migration)
6. For Knex: Check knex_migrations table, manually remove failed entry if needed
7. Restore from backup if the database is in an inconsistent state

Environment Variable Detection

Read database connection from the project's environment:

Check .env, .env.local, .env.development, and .env.production for these values. Never log or display connection strings — they contain credentials.