db-migrate

What this skill does

# DB Migrate

> **Cross-Platform AI Agent Skill**
> This skill works with any AI agent platform that supports the skills.sh standard.

Create, validate, and run database migrations across any framework. Auto-detects the migration tool from project files.

## Anti-Hallucination Guidelines

**CRITICAL**: Migration operations can be irreversible in production:
1. **Detect before acting** — Always scan for marker files before running any command
2. **Never invent commands** — Only use commands listed in the detection table or discovered in project docs
3. **Warn on destructive operations** — Always flag DROP, TRUNCATE, or irreversible schema changes
4. **Dry-run first** — Use `--dry-run` mode when supported by the framework
5. **Read existing migrations** — Check naming conventions before creating new ones

## Workflow

### Phase 1: Detect Framework

Scan for marker files to identify the migration framework:

```bash
# Check for common marker files
ls alembic.ini 2>/dev/null
ls prisma/schema.prisma 2>/dev/null
ls knexfile.* 2>/dev/null
ls flyway.conf 2>/dev/null
ls db/migrate/ 2>/dev/null
ls manage.py 2>/dev/null
ls atlas.hcl 2>/dev/null
```

Also check:
- `package.json` for `knex`, `db-migrate`, `sequelize`, `typeorm` dependencies
- `pyproject.toml` / `requirements.txt` for `alembic`, `sqlalchemy`
- `Gemfile` for `activerecord`

If multiple frameworks detected, ask user which one to use. If none detected, ask user to specify.

Refer to [references/framework-commands.md](references/framework-commands.md) for the full detection table and per-framework commands.

### Phase 2: Parse Arguments

Determine operation mode from `$ARGUMENTS`:

- `create` (or no argument): Create a new migration file
- `status`: Show pending and applied migrations
- `validate`: Check rollback scripts, naming conventions, index coverage

If `--name <name>` provided, use it for migration name. Otherwise ask for a descriptive name.
If `--dry-run` provided, show what would be created/run without executing.

### Phase 3: Execute Operation

#### create — New Migration

1. **Check naming conventions** in existing migrations (Glob `migrations/`, `db/migrate/`) — infer pattern
2. **Generate timestamp prefix**: `date +%Y%m%d%H%M%S`
3. **Run framework-native CLI** to create the migration file
4. **Verify file was created** — read and display it to the user
5. **If autogenerate is available** (Alembic, Prisma): generate from schema diff

After creation, remind user to:
- Review the generated migration for correctness
- Add rollback/down script if not auto-generated
- Add indexes for any new foreign keys

#### status — Pending Migrations

Run the framework's status command and display:
- Applied migrations (with timestamps)
- Pending migrations (not yet applied)
- Current database schema version

#### validate — Migration Quality Check

Check the following:
1. **Rollback exists**: Every `up` migration has a `down` script (warn if missing)
2. **Naming convention**: All files follow the project's timestamp + description pattern
3. **Foreign key indexes**: Grep for `REFERENCES` or foreign key declarations without corresponding `CREATE INDEX`
4. **Destructive operations**: Grep for `DROP TABLE`, `DROP COLUMN`, `TRUNCATE` — flag each one
5. **Data migrations vs schema migrations**: Warn if both data and schema changes exist in one file

### Phase 4: Safety Checks

Always run these before finalizing a `create` operation:

**Destructive operation detection**:
```bash
grep -iE "DROP TABLE|DROP COLUMN|TRUNCATE|DELETE FROM" <migration_file>
```
If found: warn user, require explicit confirmation to proceed.

**Missing index check**:
```bash
grep -iE "REFERENCES|foreign_key|FK_" <migration_file>
```
If foreign keys present, check for corresponding index creation.

**Rollback verification**:
- Check if the migration has both up and down sections
- For autogenerated migrations (Prisma, Alembic): verify the shadow database check passes

### Phase 5: Report

Display a summary of what was created or checked:
- File path of new migration
- Migration name and timestamp
- Any warnings (destructive ops, missing rollbacks, missing indexes)
- Next steps (apply with `<command>`, rollback with `<command>`)

## Argument Parsing

- `create`: Create a new migration (default if no subcommand given)
- `status`: Show migration status
- `validate`: Validate existing migrations
- `--name <name>`: Migration description for the filename
- `--dry-run`: Show what would happen without executing

## Important Notes

- **Always review auto-generated migrations** before applying — they may include unintended changes
- **Rollback scripts are required** for production safety; warn loudly if missing
- **Never run migrations directly against production** without a backup
- **Prisma shadow database**: Prisma dev migrations use a shadow DB; ensure `DATABASE_URL` is not production
- **Transaction wrapping**: Ensure migrations run in transactions for atomicity

## Examples

```bash
# Create a new migration
/db-migrate create --name add_users_table

# Check pending migrations
/db-migrate status

# Validate migration quality
/db-migrate validate

# Create without executing (preview)
/db-migrate create --name drop_legacy_column --dry-run
```