A schema drift detector compares what your database should look like with what it actually looks like, then reports the differences. A migration generator goes one step further and turns those differences into candidate SQL. Both are small enough to build in Python. The hard part is deciding what the tool refuses to guess. This guide covers the design decisions that matter, using Alembic’s documented autogenerate behavior as a reference point and PostgreSQL’s own documentation for deployment limits. It is a design guide, not a report of benchmarks or production results.
The core idea: diff two snapshots, emit candidates
Whatever the implementation, the tool has three stages: read a target schema, read an actual schema, and compare them. Alembic is the best-documented example. It connects to a database, compares it with SQLAlchemy MetaData supplied as target_metadata, and writes candidate operations into a new revision file. Its documentation says candidates are reviewed and modified by hand before you proceed (Alembic: Auto Generating Migrations).
That framing should shape your tool. Generated output is a proposal, not a verdict that the migration is correct.
Choose your source of truth first
Everything else follows from what you compare against.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errors#1 Best Overall
- Application metadata (such as SQLAlchemy models): this is what Alembic does. The model is the intent, and the database is checked against it.
- A second database: useful for staging versus production drift. Both sides are introspected with the same code, so normalization problems affect both equally.
- A captured snapshot (for example a JSON file committed to the repo): good for detecting manual changes after a deploy, with no second database needed.
If you introspect both sides with the same function, you avoid a common class of false positives. Comparing a hand-written declaration against catalog output means you must reconcile spelling differences in types and defaults.
Decide what “schema” covers, and say so
Alembic’s scan inspects the default schema and, when configured, non-default schemas. It reads tables and their sub-objects through SQLAlchemy’s Inspector, and its documentation notes limitations around constraints (Alembic documentation). A lightweight tool will cover less, and that is acceptable if the scope is written down.
The documented detectable set in Alembic is a good floor for a first version (detection behavior and limitations):
Rank #2
- table additions and removals
- column additions and removals
- nullability changes
- basic index and named unique constraint changes
- basic foreign key changes
- column type changes (compared by default in current Alembic; server-default comparison is opt-in)
Functions, views, triggers, sequences, extensions, custom types, and privileges are separate object classes. If your tool does not read them, drift in them is invisible. Print the list of covered object types in every report so that a clean result is not mistaken for a complete one.
Reading the live schema
For a minimal version, information_schema.columns gives table, column, type, nullability, and default. Richer coverage (indexes, constraint definitions, exact type modifiers) requires the pg_catalog tables or helpers such as pg_get_indexdef and pg_get_constraintdef. The sketch below is illustrative, not a tested implementation.
import psycopg
COLUMNS_SQL = """
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = %s
ORDER BY table_name, ordinal_position
"""
def snapshot(conn, schema="public"):
tables = {}
with conn.cursor() as cur:
cur.execute(COLUMNS_SQL, (schema,))
for table, col, dtype, nullable, default in cur:
tables.setdefault(table, {})[col] = {
"type": dtype,
"nullable": nullable == "YES",
"default": default,
}
return tables
Represent the snapshot as plain dictionaries or dataclasses. That makes it serializable, diffable, and easy to unit test without a live server.
Diffing and emitting operations
Compare the two snapshots in a fixed order so output is deterministic: tables only in target, tables only in actual, then per-table column differences. Represent each difference as a typed operation object (add table, drop column, alter nullability) and render SQL from that object in a separate step. Keeping detection and rendering apart lets you offer a report-only mode for CI and an SQL-emitting mode for developers.
def diff(target, actual):
ops = []
for t in sorted(target.keys() - actual.keys()):
ops.append(("create_table", t, target[t]))
for t in sorted(actual.keys() - target.keys()):
ops.append(("drop_table", t, None))
for t in sorted(target.keys() & actual.keys()):
for c in sorted(target[t].keys() - actual[t].keys()):
ops.append(("add_column", t, c, target[t][c]))
for c in sorted(actual[t].keys() - target[t].keys()):
ops.append(("drop_column", t, c))
for c in sorted(target[t].keys() & actual[t].keys()):
if target[t][c] != actual[t][c]:
ops.append(("alter_column", t, c, target[t][c], actual[t][c]))
return ops
When rendering, quote identifiers properly (psycopg’s sql.Identifier exists for this) rather than concatenating strings, since table and column names come from data.
Handle the cases that cannot be guessed
Renames
A rename looks identical to a drop plus an add. Alembic reports table and column renames exactly that way, and its documentation says autogenerate is not intended to be perfect (Alembic docs). A drop followed by an add destroys the column’s data, so a generator should not silently convert it to a rename or leave it as drop and add without warning. Better options: flag any add/drop pair on the same table with a compatible type as a possible rename, or let the author declare renames explicitly in a small mapping file.
Destructive operations
Mark drop_table, drop_column, type narrowing, and SET NOT NULL as dangerous. Emit them commented out or behind a confirmation flag. SET NOT NULL fails if existing rows contain nulls, and a type change may need a USING expression that no diff can infer.
Defaults and types
PostgreSQL reports defaults and types in normalized forms that may not match how you wrote them (for example, a serial column surfaces as an integer with a sequence-based default). Alembic makes server-default comparison opt-in for this reason. Do the same, or normalize both sides carefully, and test against real catalog output.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Control the scope
An unfiltered diff proposes removing anything in the database that is not in the target. Alembic addresses this with include_schemas and include_name, which control which schemas and objects are inspected (Alembic documentation). Build the equivalent in from the start: an allow-list of schemas and an ignore pattern for tables owned by other tools, extensions, or the migration tracking table itself.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchUse it as a CI drift check
Report-only mode fits naturally in CI: exit non-zero when the operation list is non-empty. Alembic’s alembic check does this for SQLAlchemy projects. It runs the same comparison as revision autogeneration and fails if new operations would be generated (Alembic documentation). If your target is SQLAlchemy models, using it may be better than writing your own.
A passing check has limits. It means the comparison saw no differences in the objects it covers, not that every PostgreSQL object or semantic change was compared. Show that coverage list next to the result.
Logical replication needs its own schema path
If you replicate with PostgreSQL logical replication, remember that DDL is not replicated. The documentation suggests copying the initial schema with pg_dump --schema-only and then keeping later schema changes synchronized manually. It also notes that making additive changes on the subscriber first can avoid intermittent errors in some cases (PostgreSQL 17: Logical Replication Restrictions). A drift detector run against both publisher and subscriber is a natural fit, and it can warn when only one side has a pending change.
Design checklist
- State the source of truth and the object types covered in every run’s output.
- Use one introspection function for both sides where possible.
- Separate detection from SQL rendering.
- Sort everything so output is stable between runs.
- Treat renames and drops as warnings requiring a human decision.
- Filter by schema and name before diffing.
- Always present generated SQL as a candidate for review, and test it on a copy of the database before applying it.
What this guide does not establish
This article describes design choices and public documentation. It does not include benchmarks, a published implementation, or results from running such a tool against production databases. Details such as migration ordering, transaction handling, and supported PostgreSQL versions depend on the code you write and should be verified against your own databases.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
