How I Built a Lightweight PostgreSQL Schema Drift Detector and Migration Generator in Python

Asad Ali Shah — Thu, 01 Oct 2026 08:15:09 +0000

Database migrations in staging almost always look clean.

Then somebody runs a manual hotfix query in production at 2 AM, and two weeks later your deployment pipeline fails because the schemas drifted silently.

Most teams only catch this after a table lock or a missing column crashes a live endpoint.

I wanted a clean tool that can run inside a lightweight GitHub Action runner or terminal without managing proprietary state files. So I built schemadrift - a fast, standalone PostgreSQL schema drift detector and safe migration generator.

schemadrift terminal demo

The Architecture: 0 Database Locks

Most existing tools require pulling in an entire ORM or running heavy binaries that acquire intrusive locks. schemadrift takes a different approach:

Introspection: Connects to your source and target PostgreSQL instances and queries pg_catalog and information_schema directly.
Pure Python Diff Engine: Compares tables, columns, data types, btree indexes, foreign key constraints, and enums into immutable dataclasses with 0 database round-trips.
Safe Dependency Ordering: Drops foreign keys first, drops deprecated indexes, alters modified columns, creates new tables, adds new columns, creates indexes, and re-attaches constraints.
Zero-Downtime Indexing: Includes native --concurrently flag support for CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY.
Interactive Audit Reports: Generates self-contained, responsive dark-mode HTML reports with 1-click SQL copy buttons.
Quick Start

Install via pip:

bash
pip install pg-schema-diff

Compare two databases and output migration SQL:

bash
schemadrift diff --source "postgres://user:pass@prod-host/db" --target "postgres://user:pass@staging-host/db" --output migration.sql

Generate an interactive dark-mode HTML audit report:

bash
schemadrift diff --source "postgres://user:pass@prod-host/db" --target "postgres://user:pass@staging-host/db" --format html --output drift-audit.html
GitHub Actions CI/CD Gate

schemadrift is officially published on the GitHub Marketplace. You can gate your pull requests and block breaking schema drift automatically:

yaml

name: Check Schema Drift uses: Asadshah7950/schemadrift@main with: source: ${{ secrets.PROD_DATABASE_URL }} target: ${{ secrets.STAGING_DATABASE_URL }} format: 'summary' fail-on-drift: 'true'

It automatically writes a rich diff table into $GITHUB_STEP_SUMMARY on every run.

Open Source & Community

The project is 100% open-source (MIT licensed) with 87 unit tests passing across Python 3.9 through 3.13.

If you work with PostgreSQL or find this tool useful, please consider dropping a star on the repository on GitHub:

GitHub: https://github.com/Asadshah7950/schemadrift
Marketplace: https://github.com/marketplace/actions/postgresql-schema-drift-detector

DEV Community: Asad Ali Shah

How I Built a Lightweight PostgreSQL Schema Drift Detector and Migration Generator in Python