schemadrift

Detect schema drift between PostgreSQL databases and generate safe, ordered migration SQL — from the command line.

Features

• 🔍 Schema inspection — Introspects live PostgreSQL databases via psycopg2 (tables, columns, indexes, foreign keys, enums)
• 🔄 Drift detection — Pure-Python diff engine with zero database dependency for the comparison step
• 📝 Safe SQL generation — Produces BEGIN/COMMIT-wrapped migration scripts in the correct dependency order (drop FKs first, create tables before adding columns, etc.)
• 🖥️ Rich CLI — Beautiful terminal output powered by Rich
• 📦 Multiple output formats — SQL, JSON, or human-readable summary
• ✅ 95%+ unit test coverage — All core logic tested without a live database

Installation

pip install schemadrift

Or install from source:

git clone https://github.com/Asadshah7950/schemadrift.git
cd schemadrift
pip install -e '.[dev]'

Quick Start

Python API

from schemadrift.inspector import SchemaInspector
from schemadrift.differ import SchemaDiffer
from schemadrift.generator import MigrationGenerator

# Introspect both databases
source = SchemaInspector("postgres://user:pass@source-host/mydb").snapshot()
target = SchemaInspector("postgres://user:pass@target-host/mydb").snapshot()

# Compute the diff
diff = SchemaDiffer(source, target).diff()

# Generate migration SQL
sql = MigrationGenerator(diff).generate()
print(sql)

Output example

BEGIN;

-- Drop foreign key: fk_orders_user
ALTER TABLE "orders" DROP CONSTRAINT "fk_orders_user";

-- Add table: payments
CREATE TABLE "payments" (
    "id" integer NOT NULL,
    "amount" numeric NOT NULL,
    PRIMARY KEY ("id")
);

-- Add column: users.phone
ALTER TABLE "users" ADD COLUMN "phone" text;

-- Add foreign key: fk_orders_user
ALTER TABLE "orders" ADD CONSTRAINT "fk_orders_user"
  FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE NO ACTION;

COMMIT;

CLI Usage

diff — Compare two schemas

# Print migration SQL to stdout
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db"

# Save to a file
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --output migration.sql

# JSON output
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --format json

# Human-readable summary
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --format summary

# GitHub Actions / PR Markdown report
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --format markdown >> $GITHUB_STEP_SUMMARY

# CI/CD Gate: Fail pipeline (exit code 1) if schema drift is detected
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --fail-on-drift

# Rollback / down migration (revert target back to source)
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --direction down \
  --output rollback.sql

# Non-transactional execution (omit BEGIN / COMMIT)
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --no-transaction

# Zero-downtime index management (CREATE / DROP INDEX CONCURRENTLY)
schemadrift diff \
  --source "postgres://user:pass@source-host/db" \
  --target "postgres://user:pass@target-host/db" \
  --concurrently

inspect — Print a schema overview

schemadrift inspect --dsn "postgres://user:pass@host/db"

Output:

              Schema Summary
┌──────────────┬─────────┬─────────┐
│ Table        │ Columns │ Indexes │
├──────────────┼─────────┼─────────┤
│ orders       │       6 │       3 │
│ payments     │       4 │       1 │
│ users        │       8 │       4 │
└──────────────┴─────────┴─────────┘
Foreign keys: 2

Architecture

Contributing

Contributions, bug reports, and feature requests are welcome! See CONTRIBUTING.md for development setup instructions.

License

MIT © 2024 Asad Shah