---
name: database-migration-script-writer
description: "Writes a migration you can run on production with confidence: forward and rollback steps in your ORM or raw SQL, a preflight check on the data, and a note on the locks and downtime the change causes. Use when a schema change such as adding, renaming, or dropping a column, table, index, or foreign key needs a migration script with rollback for a specific database and ORM. Do not use for designing a schema from scratch, planning a multi-step refactor across many tables, or writing application queries. Related topics: coding, database, migration."
---

# Database Migration Script Writer

Writes a migration you can run on production with confidence: forward and rollback steps in your ORM or raw SQL, a preflight check on the data, and a note on the locks and downtime the change causes.

## When to use

Use this skill when a schema change such as adding, renaming, or dropping a column, table, index, or foreign key needs a migration script with rollback for a specific database and ORM. Do not use it for designing a schema from scratch, planning a multi-step refactor across many tables, or writing application queries.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Write the up migration in the tool's idiom and the database's dialect, noting every dialect-specific decision.
3. Split the change into non-locking steps for large tables: nullable add, batched backfill, then constraint.
4. Write the preflight validation query that must return zero rows when requested, with the action to take when it does not.
5. Write the down migration when requested and state exactly what it cannot restore.
6. Give the run order, expected locks and downtime, and the query that confirms the migration applied.

## Inputs

- **Database system** (choose one: PostgreSQL, MySQL, SQLite, MSSQL, MongoDB (schema migration), Other)
- **ORM or migration tool** (choose one: Raw SQL, Prisma, Drizzle, Knex.js, Alembic (Python), ActiveRecord (Rails), Flyway, Liquibase, None)
- **Migration type** (choose one: Add column, Rename column, Drop column, Add table, Drop table, Add index, Add foreign key, Change column type, Data transformation)
- **Table name** (free text, e.g. "The table being modified")
- **Change description** (longer free text)
- **Current schema (optional)** (longer free text)
- **Approximate row count** (free text, e.g. "2 million")
- **Include rollback (down migration)** (choose one: Yes, No)
- **Add data validation before migrating** (choose one: Yes, No)
- **Wrap in a transaction** (choose one: Yes, No)

## Output

Fenced code blocks for the up migration, the validation query when requested, and the down migration when requested, followed by a short section on run order, locking, downtime, and a confirmation query.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Database Migration Script Writer

Write a safe, reversible migration for this schema change.

- Database system: {{Database system}}
- ORM or migration tool: {{ORM or migration tool}}
- Migration type: {{Migration type}}
- Table name: {{Table name}}
- Change description: {{Change description}}
- Current schema (optional): {{Current schema (optional)}}
- Approximate row count: {{Approximate row count}}
- Include rollback (down migration): {{Include rollback (down migration)}}
- Add data validation before migrating: {{Add data validation before migrating}}
- Wrap in a transaction: {{Wrap in a transaction}}

Write the up migration in the chosen tool's idiom and the chosen database's dialect; where the dialect matters (adding a NOT NULL column with a default, renaming on MySQL versus PostgreSQL, CREATE INDEX CONCURRENTLY), say what you did and why. For large tables, split the change so no step holds a long lock: add the column nullable, backfill in batches, then add the constraint. When data validation is requested, write the query that must return zero rows before the migration runs and say what to do when it does not. When a rollback is requested, write a down migration that truly restores the previous schema, and state plainly when data cannot be recovered (a dropped column's values). Respect the transaction choice but warn when the database cannot run the statement inside one. Close with a run order, the expected lock and downtime, and the check to confirm the migration applied.

## Pitfalls

- Adding a NOT NULL column with a default in one statement on a large table, which rewrites every row under a lock on older database versions.
- Writing a down migration for a dropped column that recreates it empty and calling it a rollback; say the data is gone.
- Wrapping CREATE INDEX CONCURRENTLY or certain DDL in a transaction, which the database rejects or silently downgrades.
