@indiekit/pg-diff

PostgreSQL schema diff — compares two databases and generates migration SQL.

A pure-TypeScript alternative to Python's migra.

Features

Supported object types:

• Tables — columns, types, defaults, NOT NULL, generated columns, partitioned tables, unlogged tables
• Views — regular and materialized
• Indexes — including partial indexes and INCLUDE columns
• Constraints — PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY, EXCLUDE
• Enums — create, drop, and safe in-place modification
• Functions & Procedures — full definition comparison
• Triggers — create, drop, modify
• Sequences — with ownership tracking
• Schemas — create/drop
• Extensions — with optional version ignoring
• RLS Policies — row-level security policies
• Privileges — GRANT/REVOKE tracking

Install

npm install @indiekit/pg-diff

Quick Start

# Generate migration SQL
pg-diff postgresql://localhost/db_old postgresql://localhost/db_new

# Apply directly
pg-diff postgres://localhost/old postgres://localhost/new | psql postgres://localhost/old

CLI Usage

pg-diff <from_url> <to_url> [options]

Options

| Flag | Description | |------|-------------| | --json | Output as JSON (machine-readable) | | --safe | Omit all DROP statements | | --ignore-extension-versions | Ignore extension version differences | | --mcp | Start as MCP server (stdio) | | --help | Show help with examples |

Examples

# Plain SQL output (default)
pg-diff postgres://localhost/old postgres://localhost/new

# JSON output for scripting / agents
pg-diff --json postgres://localhost/old postgres://localhost/new

# Safe mode — no destructive changes
pg-diff --safe postgres://localhost/old postgres://localhost/new

Exit Codes

| Code | Meaning | |------|---------| | 0 | Success (or no differences) | | 1 | Error |

API

import { diff } from '@indiekit/pg-diff';

const result = await diff(
  'postgresql://localhost/db_old',
  'postgresql://localhost/db_new',
  { safe: true }
);

console.log(result.sql);           // Migration SQL string
console.log(result.statements);    // Individual SQL statements
console.log(result.summary);       // { added: [...], removed: [...], modified: [...] }

Lower-level API

import { inspectSchema, computeDiff } from '@indiekit/pg-diff';

const from = await inspectSchema('postgresql://localhost/db_old');
const to = await inspectSchema('postgresql://localhost/db_new');
const result = computeDiff(from, to, { safe: false });

Types

All types are exported:

import type { DiffResult, DiffOptions, SchemaObjects } from '@indiekit/pg-diff';

MCP Server

pg-diff exposes an MCP server for AI agent integration.

Start

pg-diff --mcp

Configuration

Add to your MCP client config (e.g. Claude Desktop):

{
  "mcpServers": {
    "pg-diff": {
      "command": "npx",
      "args": ["@indiekit/pg-diff", "--mcp"]
    }
  }
}

Tools

| Tool | Description | |------|-------------| | diff_schemas | Compare two databases, returns full JSON result (SQL + statements + summary) | | diff_summary | Compare two databases, returns human-readable summary |

Both tools accept fromUrl, toUrl, and optional safe parameter.

Safe Mode (--safe)

When --safe is enabled, all statements containing DROP are filtered out. This includes:

• DROP TABLE, DROP VIEW, DROP INDEX
• DROP COLUMN, DROP CONSTRAINT
• DROP FUNCTION, DROP TRIGGER
• Any other destructive operation

Use this for production migrations where you want to review destructive changes separately.

vs Python migra

| | pg-diff | migra | |---|---|---| | Language | TypeScript/Node.js | Python | | Install | npm install | pip install | | MCP Server | ✅ Built-in | ❌ | | JSON Output | ✅ --json | ❌ | | Safe Mode | ✅ --safe | ✅ --unsafe (inverted) | | API | ✅ ESM + CJS | ✅ Python | | Maintenance | Active | Unmaintained |

License

MIT