Compare and drift

You have a desired schema and a live database, and you want to know how the two differ — as SQL you can read, or as a check that fails a pipeline when they diverge. ptah schema compare answers the first question; ptah schema drift answers the second. A third form, the plan-only run, shows the exact SQL that would reconcile the difference without executing anything.

Prerequisites:

• A ptah binary on your machine (Install Ptah).
• A desired schema, in any source Ptah reads. The examples use --schema-file schema.sql, so they require no Go toolchain.
• The URL of the database to check.

The examples start from a synced state: schema.sql describes exactly the one users table in a local SQLite database, sqlite://app.db. Substitute your own database URL throughout.

If you do not have that pair, build it here. Start in an empty directory:

mkdir ptah-drift
cd ptah-drift

Save this as schema.sql:

CREATE TABLE users (
    id INTEGER PRIMARY KEY
);

Apply it, so the database exists and matches the file:

ptah schema apply --db-url "sqlite://app.db" --schema-file schema.sql --auto-approve

Expected output on standard output:

Schema apply completed successfully.

Compare: the difference as SQL

ptah schema compare diffs the desired schema against the live database. It reports the difference twice: once as the categories of change the comparison found, and once as the SQL that reconciles them. In the synced starting state there is neither:

ptah schema compare --schema-file schema.sql --db-url "sqlite://app.db"

Expected output on standard output:

=== SCHEMA COMPARISON ===

No schema differences detected.

Now add a column to the table declaration in schema.sql:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    created_at TIMESTAMP
);

The comparison shows the change the database is missing:

=== SCHEMA COMPARISON ===

Differences detected (1 category):
  tables_modified (1): users

Reconciling SQL:
ALTER TABLE "users" ADD COLUMN "created_at" TIMESTAMP;

The category names are the field names of the schema diff, so a category carries the same spelling here that it does in machine-readable output. The list comes from the comparison result itself rather than from the SQL, which is what makes it complete: a difference your database dialect has no statement for is still named. When that happens the SQL section reads Reconciling SQL: none. and standard error names the categories the dialect’s planner could not turn into statements, so an empty statement list is never reported as agreement.

The command exits 0 whether or not differences exist; add --exit-code to exit 1 on a non-empty diff when a script needs the answer as a status code. Every category counts toward that check, including one whose SQL the dialect cannot produce.

Drift: the difference as a check

ptah schema drift evaluates the same difference as a pass/fail check with severity-classified findings. With the extra model column still in place:

ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db"

The command exits 1 because drift was found, which is the check working rather than a fault in it. Expected output on standard output:

Schema drift detected (highest severity: warning).
Failure threshold: all. Failing: true.

Findings:
- columns_added: 1 (warning)

The command exits 1 because drift was found. Findings describe what applying the desired schema would change on the database: columns_added means the desired schema has a column the database lacks, and columns_removed is classified destructive because applying would drop data.

--severity destructive lowers the threshold to data-risking drift only. The same findings print, and the check passes:

ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db" --severity destructive

Expected output on standard output:

Schema drift detected (highest severity: warning).
Failure threshold: destructive. Failing: false.
Database: sqlite://app.db

Findings:
- columns_added: 1 (warning)

Once the database gains the column — through a migration or a direct apply — the check passes at either threshold. The next section reconciles it and runs the check again.

Three more flags shape the check:

• --ignore excludes scopes from the check, for example --ignore tables=audit_log. An excluded table’s declared reference rows are excluded with it.
• --ignore-extension names a database extension the comparison leaves alone, and is repeatable. See An extension the schema does not describe.
• --format selects text, json (the findings plus the full structured diff, for tooling), or github-actions (workflow annotations).

An extension the schema does not describe

An extension a bootstrap step installs, and the desired schema deliberately does not declare, reads as removed: the comparison sees an object the database has and the declaration does not, and plans DROP EXTENSION. That statement is destructive, so --check-destructive fails on it and a drift gate reports it on every run.

Declaring the extension is not the way out. Declaring it hands Ptah the object, and that includes removing it; dropping one cascades into everything built on it, and CREATE EXTENSION IF NOT EXISTS has no safe reverse.

Name it instead, and the comparison neither creates nor drops it:

ptah schema drift --root-dir ./models --db-url "$DATABASE_URL" \
  --ignore-extension pg_trgm

The same flag is on ptah schema compare, ptah migrations plan and ptah migrations generate, and the list can live in ptah.yaml instead, which is where it belongs when every invocation would repeat it:

ignore_extensions:
  - pg_trgm

Both spellings add to Ptah’s own list rather than replacing it, so plpgsql stays ignored. A flag on the command line wins over the file.

Or say it in the schema itself

A serialized desired schema — HCL, or SQL — can carry the statement instead, as a directive in its leading comment header:

// ptah:not-described extension "pg_trgm"

schema "public" {}

A schema built from Go annotations says it with an annotation instead, because it is not a document and has no header to carry a directive:

//ptah:schema:notdescribed kind="extension" name="pg_trgm"
type _ struct{}

The two are neighboring statements rather than one. The directive and the annotation limit what the description claims, so a removal is withheld and a declaration is still honored, and the limit travels with the schema: push it to a registry and whoever pulls it reads the same one. The flag says this run manages the extension in neither direction, whatever either side says, which is what a reader who received their schema as third-party software needs and cannot get by editing it.

Dropping the name makes the directive cover every extension: // ptah:not-described extension. What a document says it does not describe is the wider subject; coverage governs schemas, roles and several other object kinds the same way.

Reference rows are part of the check

A desired schema that declares reference rows — a //ptah:schema:data annotation, or a data block in an HCL schema — is checked against the rows the database holds as well as against its structure. A database whose tables match and whose lookup values somebody edited by hand is drift, and the check exits 1 for it.

The report names the tables whose rows differ and how many rows moved. It carries no key, no column name and no value, so a pipeline may publish it where the row data itself must not go:

{
  "drift": true,
  "highest_severity": "destructive",
  "findings": [
    { "category": "data_rows_updated", "count": 1, "severity": "destructive" }
  ],
  "managed_data": {
    "tables": [
      { "table": "countries", "inserts": 0, "updates": 1, "deletes": 0 }
    ]
  }
}

The text report prints the same counts under a Managed data: heading, and --format github-actions writes one annotation per table.

Three findings carry the volume, and they set the severity the threshold reads:

Finding	Severity	What it means
data_rows_inserted	safe	The declaration holds a row the database does not. Writing it takes nothing away.
data_rows_updated	destructive	A live row holds a value the declaration does not. Applying overwrites it, and the value it held is not recoverable from the plan.
data_rows_deleted	destructive	The database holds a row the declaration no longer does. Applying removes it.

So --severity destructive passes a database that is only missing reference rows and fails one whose rows were edited or added to by hand.

A database that is behind on structure gets a defined answer rather than an error, because a check has to report rather than fall over, and the structural half of the report is what names what is missing:

• a declared table the database has not created yet is compared against no rows, so every declared row counts as an insert beside the tables_added finding;
• a column the declaration names and the table does not carry yet is left out of the row comparison, and the columns_added finding reports it. No live row holds a value for that column, so applying takes nothing away, and a run whose rows are otherwise in place stays clean at --severity destructive.

The comparison reads each declared table, so the role the check connects with needs SELECT on those tables as well as the catalog access the structural half needs. --ignore tables=... is what excludes a table from it, and it excludes the table from the structural half too.

Reference data declares the rows and reconciles them.

In the JSON document, a PostgreSQL row-level-security policy is reported by the table that owns it together with its name, because a policy name is scoped to its table and two tables may each carry one called tenant_isolation. Both diff.rls_policies_added and diff.rls_policies_removed hold objects:

{
  "diff": {
    "rls_policies_added": [
      { "policy_name": "tenant_isolation", "table_name": "zeta_orders" }
    ]
  }
}

rls_policies_added held bare policy-name strings in Ptah v0.2.0 and earlier — the v0.2.0 tag itself still declares RLSPoliciesAdded []string, so the object form has not appeared in a release yet. A consumer reading that field reads .policy_name now; nothing was removed.

A constraint carries an identity beside the name and table it is written with. The two are not the same answer: a description leaves a table unqualified where a catalog reports it with its schema, so one modified constraint arrives as widget on one side and public.widget on the other. The written spelling is what a statement and a diagnostic use; the identity is what says the two records are one object.

{
  "diff": {
    "constraints_removed": [
      {
        "name": "uq_widget_scope",
        "table_name": "public.widget",
        "type": "UNIQUE",
        "identity": { "schema": "public", "table": "widget", "name": "uq_widget_scope" }
      }
    ]
  }
}

The parts are kept separate rather than joined so a consumer never has to parse one back out, and they are already folded by the target’s rules — which is why identity.name can differ in case from name on MySQL and MariaDB, where the server resolves a constraint name case-insensitively.

The same pair identifies a policy everywhere else it is named: the plan resolves rls_policies_added, rls_policies_removed and rls_policies_modified by the owning table together with the policy name, and the table is matched under the target’s identifier rules, so orders and public.orders are one table. A reference the target schema cannot resolve is rejected rather than skipped.

Plan-only runs

When the drift check fails, the next question is what SQL would fix it. ptah migrations plan prints the reconciling migration SQL with a safety classification, without writing files or touching the database:

ptah migrations plan --schema-file schema.sql --db-url "sqlite://app.db"

Expected output on standard output:

Safety classification:
  #  severity      subject                  reason
  1  safe         users                    does not remove data or tighten constraints
=== MIGRATION SQL ===

ALTER TABLE "users" ADD COLUMN "created_at" TIMESTAMP;

Generated 1 migration statements.

Applying that plan is what closes the loop. A direct apply executes the same statement:

ptah schema apply --db-url "sqlite://app.db" --schema-file schema.sql --auto-approve

Expected output on standard output:

Schema apply completed successfully.

The check that failed at the start of this page now passes, and exits 0:

ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db"

Expected output on standard output:

No schema drift detected.

From here the workflows diverge: Generate migrations turns this plan into committed migration files, while Apply directly executes an equivalent plan straight against the database (ptah schema apply --dry-run is its plan-only form).

Schema sources

All three commands resolve the desired schema the same way: --schema-file adds SQL, YAML, HCL, DBML, or OCI sources; --root-dir scans Go annotations; and --schema-cmd runs an explicit external loader. A configured external_schema needs --config ... --allow-external-schema. Repeated sources merge into one composite desired schema. See Composite desired schema for the merge rules.

Diff two arbitrary schema states

schema compare and schema drift always pair desired sources with the live --db-url database. ptah schema diff generalizes the pair: each side is a schema file (repeatable), a database URL, or an Atlas-format migration directory, so CI can answer “do these two schema files differ?” or “does this migration directory converge to schema.hcl?” without a production database:

Save the two sides as old-schema.sql:

CREATE TABLE users (
    id INTEGER PRIMARY KEY
);

and new-schema.sql:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT
);

ptah schema diff --from old-schema.sql --to new-schema.sql --dev-url "sqlite://diff-dev.db"

Expected output on standard output:

ALTER TABLE "users" ADD COLUMN "email" TEXT;

The SQL dialect is pinned by --dev-url first, then by --from/--to database URLs; schema files alone still require --dev-url (a disposable database that is reset destructively). --format json emits a stable document, described below, and --schemas, --include, and --exclude scope both sides. Synced states print Schemas are synced, no changes to be made.

The JSON document

--format json answers one comparison two ways:

{
  "format_version": 1,
  "statements": [
    "CREATE TABLE \"orders\" (\n  \"id\" INTEGER PRIMARY KEY\n)",
    "ALTER TABLE \"users\" ADD COLUMN \"email\" TEXT"
  ],
  "changes": {
    "tables_added": ["orders"],
    "tables_modified": [
      {"table_name": "users", "columns_added": ["email"]}
    ]
  }
}

statements is the DDL a migration would run — what will happen. changes is the structural comparison those statements were planned from — what differs, with tables, columns, enums, indexes, constraints, views, triggers, roles and grants each under their own key. A check like “does this diff drop a column” reads a field; deciding it from statements means parsing DDL, and a DDL parser is wrong for every dialect it was not tested against. What that dropped column would break in a view is a separate question, answered by Trace view column lineage.

The two halves always agree. changes reports the comparison after diff policy has been applied, so a diff run with skip.drop_table shows neither a DROP TABLE statement nor the removed table.

Both keys are always present. An empty statements is a comparison that found nothing to do; changes is present and empty when the schemas are synced, so a CI check reads the same fields either way. Within changes, a collection with no members encodes as null or [] — read both as empty. format_version rises when a field’s meaning changes, not when one is added.

An explicit --include selection that matches neither side is invalid. The command prints no diff and exits 2 instead of reporting a synced schema. This is outcome-based: a matching top-level identifier that contains a dot remains selectable with a dotted spelling.

When the desired side is a live PostgreSQL database, a selected extension keeps its installation schema. Creating a non-default placement emits CREATE SCHEMA and CREATE EXTENSION ... WITH SCHEMA ...; an identical placement is synced, and extension drops remain supported. A placement change exits 2 before SQL output because Ptah does not yet plan ALTER EXTENSION ... SET SCHEMA.

Failure modes

• ptah schema drift exits 2 (not 1) when the check itself cannot run — an unreachable database, a bad URL, or an unparsable schema source — so pipelines can tell “drift found” from “check broken”.
• ptah schema compare without --exit-code never signals differences through its status code; scripts that forget the flag silently pass.

For symptoms beyond these, see Troubleshooting.

Next steps

• Apply the difference straight to the database: Apply directly.
• Turn the difference into reviewed migration files: Generate migrations.
• Run the drift check on every pull request: CI.