# Compare and drift See how a live database differs from the desired schema, and turn that difference into a check that gates pipelines. Source: https://docs.ptah.run/v0.25.0/direct/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](../../start/install/)). - 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: ```console mkdir ptah-drift cd ptah-drift ``` Save this as `schema.sql`: ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY ); ``` Apply it, so the database exists and matches the file: ```console ptah schema apply --db-url "sqlite://app.db" --schema-file schema.sql --auto-approve ``` Expected output on standard output: ```text 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: ```console ptah schema compare --schema-file schema.sql --db-url "sqlite://app.db" ``` Expected output on standard output: ```text === SCHEMA COMPARISON === No schema differences detected. ``` Now add a column to the table declaration in `schema.sql`: ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY, created_at TIMESTAMP ); ``` The comparison shows the change the database is missing: ```text === 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`](../apply/#undecided-objects). 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: ```console exits=1 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: ```text 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: ```console ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db" --severity destructive ``` Expected output on standard output: ```text 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](../../reference/native-commands/#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](#an-extension-the-schema-does-not-describe). - `--format` selects `text`, `json` (the findings, the [inventory](#how-much-each-side-holds) 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](../../databases/mysql/#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: `_added` counts the objects the database lacks, `_removed` the objects the desired schema does not declare, and `_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](../../reference/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: ```json { "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`: ```json { "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: ```bash illustration 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: ```yaml 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: ```hcl illustration // 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: ```go illustration //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](../../atlas/schema-commands/#the-document-says-what-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](../../versioned/reference-data/#declaring-managed-data). 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: ```json { "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](../../versioned/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: ```json { "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. ```json { "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: ```console ptah migrations plan --schema-file schema.sql --db-url "sqlite://app.db" ``` Expected output on standard output: ```text 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: ```console ptah schema apply --db-url "sqlite://app.db" --schema-file schema.sql --auto-approve ``` Expected output on standard output: ```text Schema apply completed successfully. ``` The check that failed at the start of this page now passes, and exits `0`: ```console ptah schema drift --schema-file schema.sql --db-url "sqlite://app.db" ``` Expected output on standard output: ```text No schema drift detected. ``` From here the workflows diverge: [Generate migrations](../../versioned/generate/) turns this plan into committed migration files, while [Apply directly](../apply/) 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](../../schema/composite/) 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](../../databases/postgresql/#what-the-connecting-role-needs), [MySQL and MariaDB](../../databases/mysql/#what-the-connecting-account-needs). ## 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`: ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY ); ``` and `new-schema.sql`: ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY, email TEXT ); ``` ```console ptah schema diff --from old-schema.sql --to new-schema.sql --dev-url "sqlite://diff-dev.db" ``` Expected output on standard output: ```text 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: ```json { "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](../../schema/lineage/). The two halves always agree. `changes` reports the comparison after [diff policy](../../reference/configuration/) 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`: ```json { "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:", "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](../../reference/exit-codes/#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](../../operate/troubleshooting/). ## Next steps - Apply the difference straight to the database: [Apply directly](../apply/). - Turn the difference into reviewed migration files: [Generate migrations](../../versioned/generate/). - Run the drift check on every pull request: [CI](../../testing/ci/).