pg-schema-compare

Structurally compare two PostgreSQL database schemas, ignoring constraint and index naming differences.

Useful for verifying that a fresh migration produces the same schema as an existing database.

Why This Tool?

Most schema diff tools (e.g. migra, pgquarrel, pg-schema-diff) focus on generating migration SQL to bring one schema in line with another. This tool takes a different approach:

• Structure-only comparison -- compares what matters (tables, columns, indexes, foreign keys, constraints, enums, extensions) without generating migration output.
• Name-agnostic -- ignores index and constraint names entirely. Two databases with different naming conventions but identical structure are reported as equal.
• Normalization built-in -- handles common PostgreSQL quirks like sequence names, type casts, and default value formatting so you don't get false positives.
• CI-friendly -- simple exit codes (0 = identical, 1 = differences, 2 = error) make it easy to integrate into CI pipelines for migration drift detection.

Ideal for teams that want to answer one question: "Does running migrations from scratch produce the exact same schema as our existing database?"

Install

bun add -g pg-schema-compare

Or run directly with bunx:

bunx pg-schema-compare \
  --from postgres://user:pass@host:5432/db1 \
  --to postgres://user:pass@host:5432/db2

Usage

# Compare two databases
pg-schema-compare \
  --from postgres://user:pass@host:5432/db_existing \
  --to postgres://user:pass@host:5432/db_fresh

# Exclude specific tables
pg-schema-compare \
  --from postgres://user:pass@host:5432/db1 \
  --to postgres://user:pass@host:5432/db2 \
  --exclude migrations,logs

A full PostgreSQL connection string is required. Plain database names are not supported.

Options

| Option | Required | Description | | ----------- | -------- | --------------------------------------------------------- | | --from | Yes | Source database connection string | | --to | Yes | Target database connection string | | --exclude | No | Comma-separated list of tables to exclude from comparison |

What It Compares

| Category | Details | | ---------------------- | --------------------------------------------------------------------------------- | | Tables | Detects entire tables that exist in one database but not the other | | Columns | Data type, nullability, default value (with sequence and type cast normalization) | | Indexes | Normalized index definitions (index names are ignored) | | Foreign Keys | Foreign key constraint definitions (constraint names are ignored) | | Check Constraints | CHECK constraint definitions (NOT NULL checks are excluded) | | Unique Constraints | UNIQUE constraint definitions (constraint names are ignored) | | Enum Types | Custom PostgreSQL enum types and their values | | Extensions | Installed PostgreSQL extensions and their versions |

Normalization

The following normalizations are applied during comparison to avoid false positives:

• Sequences: nextval('any_seq_name'::regclass) -> nextval(autoincrement)
• Type casts: '0'::bigint -> 0, NULL::character varying -> NULL
• Index names: CREATE INDEX idx_name ON ... -> CREATE INDEX ON ...
• Constraint names: All constraint names are stripped; only definitions are compared
• NOT NULL checks: Internal CHECK ((col IS NOT NULL)) constraints are filtered out

Table-Level Awareness

When an entire table is missing from one database, it is reported as a single table-level diff instead of listing every column individually. Column-level diffs are only shown for tables that exist in both databases.

Exit Codes

| Code | Meaning | | ---- | --------------------------- | | 0 | Schemas are identical | | 1 | Differences found | | 2 | Connection or runtime error |

Programmatic API

import { fetchSchema, compareSchemas, formatDiffText } from 'pg-schema-compare'

const source = await fetchSchema('postgres://user:pass@localhost:5432/db1')
const target = await fetchSchema('postgres://user:pass@localhost:5432/db2')
const diff = compareSchemas(source, target)

// diff.tables           -> TableDiff[]
// diff.columns          -> ColumnDiff[]
// diff.indexes          -> IndexDiff[]
// diff.foreignKeys      -> ForeignKeyDiff[]
// diff.checkConstraints -> CheckConstraintDiff[]
// diff.uniqueConstraints -> UniqueConstraintDiff[]
// diff.enumTypes        -> EnumTypeDiff[]
// diff.extensions       -> ExtensionDiff[]

// Format as CLI output
const output = formatDiffText({
    diff,
    sourceName: 'db1',
    targetName: 'db2',
    source,
    target,
})

Development

# Install dependencies
bun install

# Run tests (requires PostgreSQL)
cp .env.test.example .env.test   # adjust PG_URL if needed
bun test

# Lint, format, type check
bun run check

# Auto-fix lint and format issues
bun run fix