# 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.11.4/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; ``` 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. 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, which is the check working rather than a fault in it. Expected output on standard output: ```text 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: ```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. 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](#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). 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` does not, because nothing the read could not see is a change that would lose data. The JSON report carries the objects in `undecided`, with `drift` false and `failed` set by the threshold, and `github-actions` annotates each one. `ptah schema serve` counts and lists them the same way. ### 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: ```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 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: ```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-external-schema`. Repeated sources merge into one composite desired schema. See [Composite desired schema](../../schema/composite/) 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`: ```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. 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](../../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/).