migration-diff skill

# migration-diff skill

## When to use

Use this skill when the user wants to:

- Understand what schema changes were made across a series of SQL migration files
- Compare the schema state at two different migration versions
- Visualize the full diff timeline of all migrations in a project
- Find when a specific column or table was added, removed, or changed

## Prerequisites

- Node.js 20+
- A directory of `.sql` migration files with a numeric or timestamp prefix

## Quick start

```
npx migration-diff-tool
```

Dashboard: http://127.0.0.1:7703

Or via CLI:

```
migration-diff add ~/projects/my-app/db/migrations --dialect postgres
migration-diff scan ~/projects/my-app/db/migrations
```

## CLI reference

| Command | Description |
|---------|-------------|
| `migration-diff add <dir>` | Add a migration directory as a project |
| `migration-diff list` | List all tracked projects |
| `migration-diff scan <dir>` | Scan and display summary of schema changes |
| `migration-diff diff <dir>` | Show diff between two versions |
| `migration-diff show <dir>` | Show schema state at a specific version |
| `migration-diff serve` | Start the dashboard server |

## Flags - add

| Flag | Default | Description |
|------|---------|-------------|
| `--name` | derived from path | Project display name |
| `--dialect` | `postgres` | SQL dialect: `postgres`, `mysql`, `sqlite` |

## Flags - diff

| Flag | Default | Description |
|------|---------|-------------|
| `--from` | first migration | Source version (migration filename prefix) |
| `--to` | last migration | Target version (migration filename prefix) |
| `--json` | - | Output machine-readable JSON |
| `--table` | all | Filter diff to a specific table name |

## Flags - show

| Flag | Default | Description |
|------|---------|-------------|
| `--version` | last | Migration version to inspect |
| `--table` | all | Filter to a specific table |
| `--json` | - | Machine-readable JSON output |

## Migration file naming

Files are sorted by their prefix. Supported prefix formats:

| Format | Example | Sort key |
|--------|---------|----------|
| Timestamp | `20240115_create_users.sql` | `20240115` |
| Numeric | `001_create_users.sql` | `001` |
| Timestamp+time | `20240115120000_create_users.sql` | `20240115120000` |

Files without a recognized prefix are sorted lexicographically.

## Change types

| Type | Meaning |
|------|---------|
| `table_added` | Table created by this migration |
| `table_removed` | Table dropped by this migration |
| `column_added` | Column added to existing table |
| `column_removed` | Column dropped from table |
| `column_type_changed` | Column type string changed |
| `column_nullable_changed` | NOT NULL constraint added or removed |
| `column_default_changed` | Default value added, removed, or changed |

## Diff output (CLI)

```
$ migration-diff diff ~/projects/my-app/db/migrations --from 20240201 --to 20240701

diff: 20240201_add_user_bio -> 20240701_add_user_timestamps
10 changes: +1 table, +7 columns, 1 type change, -1 table

TABLE CHANGES
  + comments (added)
  - legacy_tokens (removed)

COLUMN CHANGES (users)
  ~ email: VARCHAR(255) -> TEXT (type changed)
  + last_login_at: TIMESTAMP nullable
  + deleted_at: TIMESTAMP nullable

COLUMN CHANGES (posts)
  + status: TEXT NOT NULL DEFAULT 'draft'
  + published_at: TIMESTAMP nullable

COLUMN CHANGES (comments)
  + id: UUID NOT NULL PRIMARY KEY
  + body: TEXT NOT NULL
  + created_at: TIMESTAMP NOT NULL DEFAULT NOW()
```

## Environment variables

| Variable | Default | Description |
|----------|---------|-------------|
| `MIGDIFF_PORT` | `7703` | Dashboard server port |
| `MIGDIFF_DATA_DIR` | `~/.migration-diff` | Directory for SQLite cache |
| `MIGDIFF_LOG_LEVEL` | `info` | Log level |
| `MIGDIFF_MAX_FILES` | `2000` | Maximum migration files per project |

## Limitations

- Only DDL is tracked: CREATE TABLE, ALTER TABLE, DROP TABLE.
- DML statements (INSERT, UPDATE, DELETE) are skipped.
- Indexes, triggers, views, functions, and stored procedures are not tracked.
- The parser uses regex matching, not a full SQL grammar. Complex inline expressions (subqueries in DEFAULT clauses, computed columns) may not parse correctly.
- RENAME TABLE is not supported. Use DROP + CREATE as a workaround.

## Troubleshooting

| Symptom | Resolution |
|---------|------------|
| Table shows as "never created" | A migration file may be missing, or the CREATE TABLE is in a file outside the scanned directory |
| Column type shows incorrectly | Complex type expressions (e.g. `VARCHAR(255) COLLATE utf8_bin`) may be partially parsed. View the SQL source to verify |
| Migrations out of order | Ensure filenames have a consistent numeric or timestamp prefix. Check with `migration-diff list` |
| Too many parse warnings | Triggers, indexes, and DML generate warnings. This is expected - only DDL is tracked |
| Cache stale after file changes | Run `migration-diff scan <dir>` or click Rescan in the dashboard |