pgdrift

Snapshot your Postgres schema. Diff two snapshots. Know exactly what changed.

pgdrift is a small CLI that captures a point-in-time snapshot of a Postgres schema and compares any two snapshots to tell you what drifted — categorized as BREAKING, ADDITIVE, INFORMATIONAL, or SCHEMA-LEVEL.

It's useful anywhere a schema can change outside your migration chain: hotfix column renames, a DBA dropping an index during an incident, a migration that ran on one environment but not another.

---

Quick start

pipx install pgdrift          # or: pip install pgdrift

# Capture the current schema
pgdrift snapshot --url postgresql://user:pass@localhost/mydb

# Compare two snapshots
pgdrift diff snapshots/snapshot_20250115_100000.json snapshots/snapshot_20250115_143000.json

# Watch for drift on an interval (exits 1 on BREAKING changes)
pgdrift watch --url $DATABASE_URL --baseline snapshots/baseline.json --interval 60

# Pretty-print a snapshot
pgdrift report snapshots/baseline.json

# Run a demo against acme-parts-cloud (docker compose up required)
pgdrift demo

--url falls back to the DATABASE_URL environment variable on all commands.

---

Diff categories

Every detected change is classified before it's shown:

Category	Color	Examples
BREAKING	red	Dropped column or table, incompatible type change or varchar narrowing, nullable → NOT NULL, new NOT NULL column with no default, dropped unique/check constraint, removed enum value
ADDITIVE	green	New table, new nullable column, new column with a default, varchar widening, NOT NULL relaxed to nullable, new index, new enum value
INFORMATIONAL	yellow	Default value changed, index dropped, same-named index definition changed
SCHEMA-LEVEL	blue	New or dropped enum type

The judgment is opinionated. If your team disagrees with a classification, open an issue.

---

Snapshot format

Snapshots are plain JSON files — human-readable, diff-able with git diff, and safe to commit. Keys are deterministically sorted so two snapshots of an unchanged schema produce identical output.

{
  "format_version": 1,
  "captured_at": "2025-01-15T10:00:00+00:00",
  "db_version": "PostgreSQL 16.2 on x86_64-pc-linux-gnu",
  "connection": "postgresql://pgdrift@localhost:5432/acme",
  "tables": {
    "public.parts": {
      "columns": [...],
      "primary_key": ["id"],
      "unique_constraints": {"uq_parts_part_number": ["part_number"]},
      "check_constraints": {},
      "foreign_keys": [],
      "indexes": [...]
    }
  },
  "enums": {
    "part_status": ["active", "obsolete", "superseded"]
  }
}

Passwords are stripped from the stored connection field before the file is written.

---

Machine-readable output for CI

pgdrift diff baseline.json current.json --format json | jq '.summary'

diff exits with code 1 if any BREAKING changes are present — suitable for a CI gate.

---

Using in CI

- name: Check for schema drift
  run: |
    pgdrift diff snapshots/pre-migration.json snapshots/post-migration.json \
      --format json > drift.json
    cat drift.json | jq '.summary'

Or use pgdrift diff --breaking-only to fail only on BREAKING changes and suppress the rest.

---

Stack

Python 3.12 · psycopg 3 · click · rich · pytest · ruff · GitHub Actions

No ORM. Schema data comes directly from information_schema and pg_catalog, so there are no migration files to maintain and no framework-specific assumptions.

---

Demo: acme-parts-cloud integration

pgdrift demo connects to acme-parts-cloud, takes a baseline snapshot, applies its packaged mutation script (four schema changes covering all diff categories), and shows the full diff report. Requires acme-parts-cloud running locally:

# In the acme-parts-cloud directory
docker compose up -d

# Back in pgdrift
pgdrift demo

---

Design notes

Why not just diff pg_dump --schema-only output? You can, but raw DDL diffs are noisy — comment changes, whitespace, and statement ordering make them hard to read and impossible to categorize programmatically. pgdrift gives you structured data you can act on.

Why psycopg 3 and not psycopg2? psycopg 3 is the current maintained branch. The two are not drop-in compatible; pgdrift targets 3.x and says so explicitly so there's no version-mismatch confusion.

Why plain JSON and not a database for snapshot storage? Snapshots are meant to be committed alongside code and reviewed in pull requests. A file you can git diff is more useful than a record in a database you have to query. If you want centralized schema tracking, that's a different tool (Atlas, SchemaHero) and a different problem.

---

All data is synthetic

The demo corpus and fixture files use acme-parts-cloud, a fully synthetic dataset. No real company names, part numbers, or supplier data appear anywhere in this repository.

---

License

MIT — see LICENSE.