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;

A category has the same name here as in machine-readable output: the field name of the schema diff. The list comes from the comparison result rather than from the SQL, so a difference your dialect has no statement for is still named. The SQL section then reads Reconciling SQL: none. and standard error names the categories the planner could not turn into statements.

Under tables_modified the report gives the old and new value of each changed column, such as users.age: type: int8 -> int4.

A declared object the database read could not look at is named too. When the account may not read a catalog, the comparison cannot tell whether a declared object of that kind already exists, so it plans nothing for it. A MySQL account without access to the role tables is the usual case: a role the schema declares is withheld. The report lists each such object under Undecided, and standard error says why the read could not decide it. When nothing else differs, the report starts with No differences planned, but 1 declared object could not be decided: rather than No schema differences detected. ptah schema apply, ptah schema plan, ptah migrations plan and ptah migrations generate print the same warning on standard error, and none of them says the schema is synced while an object is undecided. The --json documents of schema plan and schema apply list the objects under undecided.

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. An undecided object counts too: a comparison that could not look is not proof that nothing drifted.

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. Expected output on standard output:

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

Findings:
- columns_added: 1 (warning)

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, as the next section shows.

These flags shape the check:

• --exclude and --include select what both sides hold, on schema compare too. See Selecting what a comparison holds.
• --ignore tables=audit_log excludes a table from the check, with its declared reference rows.
• --ignore-extension names a database extension the comparison leaves alone; see below.
• --format selects text, json (the findings, the inventory and the full structured diff, for tooling, under contract_version and command), or github-actions (workflow annotations).

A declared object the read could not check is graded as well. It is not a difference, so the report opens with No schema drift found, but 1 declared object could not be decided rather than claiming there is no drift. It lists the object under Undecided, and counts the objects as one undecided finding at warning severity. The default threshold fails on it, and --severity destructive counts only established destructive changes. A missing observation does not establish that a withheld operation is safe.

A MySQL or MariaDB event is named under not_compared and is not a finding; see Events.

The JSON report carries common-object limits in undecided.common and feature limits in undecided.features, with drift false and failed set by the threshold. Feature limits retain a structured subject and the provider’s reason; they can concern a whole namespace. Text summaries count these as comparison limits. github-actions annotates every limit. ptah schema serve counts and lists them the same way.

What each finding names

A finding is named for the kind of object it counts and for what applying the desired schema would do to it: <kind>_added counts the objects the database lacks, <kind>_removed the objects the desired schema does not declare, and <kind>_modified the objects both hold that differ. Every difference the comparison reports is counted under a category, so a report whose drift is true has at least one finding. Drift findings lists every category and the severity it carries.

How much each side holds

The findings say what differs. The JSON report’s inventory says how much of each kind of object either side holds, so a reader can tell three tables of forty differing from three of three. Against the schema of a database that lacks one declared table, holds one index the schema does not declare, and has one column in a shared table that the schema does not declare:

{
  "drift": true,
  "findings": [
    { "category": "columns_removed", "count": 1, "severity": "destructive" },
    { "category": "indexes_removed", "count": 1, "severity": "warning" },
    { "category": "tables_added", "count": 1, "severity": "safe" }
  ],
  "inventory": [
    { "kind": "columns", "desired": 2, "observed": 3 },
    { "kind": "indexes", "desired": 1, "observed": 2 },
    { "kind": "tables", "desired": 2, "observed": 1 }
  ]
}

desired is how many objects of the kind the desired schema declares and observed how many the database holds. kind is the stem of the kind’s findings, so tables joins tables_added and tables_removed without a second vocabulary. The entries carry counts and no names, and they are sorted by kind. A kind neither side holds has no entry; a kind only one side holds has a zero on the other side. A report that compared carries the field, empty when no kind has an entry, and an error report carries none.

The counts come from the comparison, so they describe the same objects the findings do:

• an object --exclude or --ignore leaves out, or one the comparison withholds as undecided, is counted on neither side;
• with no difference, each kind counts the same on both sides;
• columns are counted in the tables both sides hold, and enum values in the enum types both sides hold, so a table only one side holds counts under tables and its columns are not counted;
• an index or a constraint whose definition changed is dropped and created again, so it counts once on each side and not as one object both hold. A materialized view whose definition changed is dropped and created again too, but the comparison reports it as materialized_views_modified, so it counts as one object both sides hold.

Kind	What it counts
tables	Tables.
columns	Columns of the tables both sides hold.
indexes	Indexes the comparison compares, which leaves out an index a PRIMARY KEY, a UNIQUE constraint or a foreign key owns. An index renamed in place is one object.
constraints	CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY constraints. A primary key declared on its column is compared as part of the column and is not counted here.
enums	Enum types.
enum_values	Values of the enum types both sides hold.
functions	Functions and procedures, one per overload.
sequences	Standalone sequences. The sequence behind a SERIAL or identity column belongs to its column and is not counted.
views	Views.
materialized_views	Materialized views.
triggers	Triggers.
roles	The roles the desired schema declares. A role it does not declare is not compared, so the database side counts only the declared roles it holds.
role_memberships	Memberships of a role in another, on YDB, the target that compares them. The database side counts the declared memberships it holds, and every membership between two declared roles.
grants	Privileges, one per role, privilege and object, or column of an object. A declared ALL is one, however many privileges the database reports for it.
schemas	Databases, on a comparison of a whole MySQL or MariaDB server.

The database side of grants counts every privilege granted to a role the desired schema declares, and each privilege the desired schema grants another role that the database holds. A REVOKE the desired schema states for an object the plan creates counts on the database side too: the plan revokes what PostgreSQL grants the object when it is created, so the REVOKE names a privilege the database does not hold yet.

rls_policies and rls_enabled_tables count the policies and enablements the shared comparison pairs. A PostgreSQL report has neither: its feature owner counts policies under ptah.run/pgpolicy/policy-change and tables with row-level security on, by ENABLE or FORCE, under ptah.run/pgpolicy/table-state-change.

An object a feature owner compares, such as a PostgreSQL extension, policy or domain, is counted by its owner, which decides which objects on the two sides are the same object. Its entry is named for the change kind its findings carry after the colon, so ptah.run/pgextension/extension-change joins feature_changes:ptah.run/pgextension/extension-change:

{ "kind": "ptah.run/pgextension/extension-change", "desired": 2, "observed": 3 }

The owner counts as the comparison does. An object both sides hold counts on both, also when the plan drops it and creates it again. An object the owner leaves alone or cannot decide counts on neither, such as an extension --ignore-extension names, one a declared column’s type needs, or one the server installs in every database. An object on a table only one side holds counts with the table. A default privilege counts one entry per grantor, schema, object type and grantee, and a declaration the built-in privileges already satisfy counts on both sides.

An owner that does not count its objects has no entry, though its changes are still findings. A setting that stands for no object, such as a ClickHouse table’s TTL, has no entry either. A provider counts only with models.compare-counts.

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; the server refuses that while anything uses 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 and its structure. A database whose tables match and whose lookup values somebody edited by hand is drift, and the check exits 1. A row file outside the project is refused.

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.

A foreign key the schema declares and one the database holds are one object only when their identities agree, whether the schema declares the key on its column or on its table. A key the database holds under another name is in constraints_removed, and the declared key is in constraints_added.

A column-level UNIQUE is matched the same way on MySQL, MariaDB and PostgreSQL, by the name the server gives the column’s key. A key over the column that the database holds under another name is in constraints_removed, and the column’s change reads unique: false -> true.

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-config-exec. Repeated sources merge into one composite desired schema. See Composite desired schema for the merge rules.

Which schemas are read

These commands read the schemas ptah schema apply reads: the ones --schemas or schemas in ptah.yaml name, or else what the URL covers plus every schema the desired schema names. A PostgreSQL URL covers its pinned search_path, or every schema when it pins none, and the tables of a schema the desired schema does not describe are reported as removals, as apply would drop them.

Privileges: PostgreSQL, MySQL and MariaDB.

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.

A change an engine-specific setting owns, such as a ClickHouse TTL or a YDB coordination node’s settings, appears under feature_changes: at the top of changes for a standalone object, inside the table’s entry in tables_modified for a table setting, and inside the view’s entry in materialized_views_modified for a view setting such as a ClickHouse refresh schedule. Each entry names its subject and carries the change in an envelope. This is a ClickHouse TTL change, with each side of the payload trimmed to ttl:

{
  "subject": {
    "kind": "table",
    "schema": {"source": "ptah_test", "normalized": "ptah_test", "defaulted": true},
    "name": {"source": "events", "normalized": "events"}
  },
  "value": {
    "format": 1,
    "owner": "ptah.run/clickhouse",
    "kind": "ptah.run/clickhouse/table-change",
    "representation": "change",
    "version": 1,
    "definition": "sha256:<digest of the codec definition>",
    "payload": {
      "after": {"ttl": {"state": "explicit", "value": "at + INTERVAL 2 DAY"}},
      "before": {"ttl": "at + toIntervalDay(1)"}
    }
  }
}

subject is the object’s identity. kind names its family. catalog, schema, parent and name are the components the identity has, each with its spelling as written (source), its comparison form (normalized), whether the spelling was quoted, and whether the target’s default filled it in (defaulted). A routine adds its signature. A component or a field that is empty is left out.

owner names the provider that wrote payload, kind names what it holds, and version says in which form. definition is a digest of the codec definition, so it changes whenever that form does; it is elided above. A reader can rely on the fields of a kind it knows and skip the rest. The schema drift --format json report carries the same entries in its diff.

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 plans ALTER EXTENSION ... SET SCHEMA, after CREATE SCHEMA IF NOT EXISTS when the schema is new; an extension that is not relocatable exits 2 before SQL output.

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”. With --format json standard output then carries only contract_version, command and an error object, so no reader takes it for a database without drift. Why a command failed describes error.
• 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.