# @indiekitai/pg-safe-migrate

**Catch unsafe PostgreSQL migrations before they reach production.**

The JS/TypeScript equivalent of Ruby's [`strong_migrations`](https://github.com/ankane/strong_migrations) (⭐ 4k). Zero other JS alternatives exist.

```bash
npx @indiekitai/pg-safe-migrate check ./migrations/001_add_index.sql
```

```
Checking: migrations/001_add_index.sql

  ✗ [DANGER] Create Index Without Concurrently (line 1)
    CREATE INDEX idx_users_email ON users (email);
    ↳ CREATE INDEX without CONCURRENTLY locks the table for reads and writes during index creation.
    ↳ Fix: Use CREATE INDEX CONCURRENTLY to build the index without locking.

Summary: 1 danger, 0 warnings, 0 info — NOT SAFE
```

## Install

```bash
npm install -g @indiekitai/pg-safe-migrate
# or use directly:
npx @indiekitai/pg-safe-migrate check migration.sql
```

## Usage

```bash
# Check a single migration file
pg-safe-migrate check migration.sql

# Check all .sql files in a directory
pg-safe-migrate check ./migrations/

# JSON output (for CI pipelines)
pg-safe-migrate check migration.sql --json

# List all available checks
pg-safe-migrate list-checks
```

**Exit codes:** `0` = safe, `1` = DANGER found, `2` = WARNING found (no dangers)

## CI Integration

```yaml
# GitHub Actions
- name: Check migrations
  run: npx @indiekitai/pg-safe-migrate check ./migrations/ --json
```

## All 15 Checks

### 🔴 DANGER — Will cause downtime or data loss

| Check | Issue | Safe Alternative |
|-------|-------|-----------------|
| `create_index_without_concurrently` | Locks table during index build | `CREATE INDEX CONCURRENTLY` |
| `add_column_not_null_without_default` | Table rewrite in older PG | Add nullable, backfill, add NOT NULL |
| `alter_column_type` | Rewrites entire table | New column → backfill → rename |
| `add_foreign_key_without_not_valid` | Full scan + lock | Add `NOT VALID`, then `VALIDATE CONSTRAINT` |
| `add_unique_constraint` | Full scan + lock | `CREATE UNIQUE INDEX CONCURRENTLY` → `USING INDEX` |
| `set_not_null` | Full table scan | `CHECK CONSTRAINT NOT VALID` pattern |
| `drop_table` | Irreversible | Remove app code first |
| `truncate_table` | ACCESS EXCLUSIVE lock | Batched DELETE |

### 🟡 WARNING — Risky, may cause issues

| Check | Issue | Safe Alternative |
|-------|-------|-----------------|
| `rename_column` | Breaks ORM caching | Dual-write pattern |
| `rename_table` | Breaks all queries | Transitional view |
| `drop_column` | ORM attribute caching | Ignore in app code first |
| `add_check_constraint_without_not_valid` | Full table scan | Add `NOT VALID`, validate later |

### 🔵 INFO — Best practices

| Check | Issue |
|-------|-------|
| `multiple_operations` | >3 ALTER TABLE statements, hard to roll back |
| `backfill_without_where` | UPDATE without WHERE can timeout |
| `index_on_many_columns` | 4+ column index has diminishing returns |

## MCP Server (for Claude / Cursor)

After installing globally (`npm install -g @indiekitai/pg-safe-migrate`):

```json
{
  "mcpServers": {
    "pg-safe-migrate": {
      "command": "pg-safe-migrate-mcp"
    }
  }
}
```

Or without global install:

```json
{
  "mcpServers": {
    "pg-safe-migrate": {
      "command": "npx",
      "args": ["--package", "@indiekitai/pg-safe-migrate", "pg-safe-migrate-mcp"]
    }
  }
}
```

Tools available:
- `check_migration(sql)` — check SQL string for issues
- `list_checks()` — list all checks
- `explain_check(checkName)` — explain a specific check

## Programmatic Usage

```typescript
import { checkMigration } from "@indiekitai/pg-safe-migrate";

const result = checkMigration(`
  CREATE INDEX idx_users_email ON users (email);
`);

console.log(result.safe);           // false
console.log(result.summary);        // { danger: 1, warning: 0, info: 0 }
console.log(result.issues[0].check); // "create_index_without_concurrently"
```

## Why This Exists

Ruby has `strong_migrations`. Python has nothing. JavaScript has nothing. Until now.

If you're running PostgreSQL with Node.js (Prisma, Drizzle, node-pg-migrate, raw SQL), this tool gives you the same safety net Rails developers have had for years.

---

Part of the [IndieKit](https://indiekit.ai) PostgreSQL toolchain:
[pg-inspect](https://github.com/indiekitai/pg-inspect) · [pg-diff](https://github.com/indiekitai/pg-diff) · [pg-top](https://github.com/indiekitai/pg-top) · **pg-safe-migrate** · [pg-dash](https://github.com/indiekitai/pg-dash)