Skip to content
PtahDocs
latest

Graphic preview

100%
Page type: how-to

Validate and format schema files

Check a desired schema for structural problems with ptah schema validate and keep HCL files canonically formatted with ptah schema fmt, both without a database.

Two native verbs check schema files before anything connects to a database. ptah schema validate reports structural problems in a desired schema, once per target dialect. ptah schema fmt rewrites HCL schema files into HashiCorp HCL’s canonical layout, or reports the ones that are not in it.

Use them as a pre-commit hook and as the first job of a pipeline. Neither one takes a --db-url, so both run on a machine with no database and finish in the time a linter takes.

Command Reads Answers
ptah schema validate the supported sources listed below Is this schema structurally sound for the dialects I target?
ptah schema fmt .hcl files on disk Are these files in canonical HCL layout?
Source Selector Source-specific limitation
SQL file --schema-file schema.sql Validates the Ptah DDL parser subset for the selected dialect.
YAML file --schema-file schema.yaml Validates Ptah YAML schema objects and documented dialect overrides.
HCL file --schema-file schema.hcl Validates the Atlas-compatible HCL subset plus Ptah extensions.
DBML file --schema-file schema.dbml DBML cannot express every Ptah object.
Go annotations --root-dir ./models Reads the native Go annotation model from a Go source tree.
OCI artifact --schema-file oci://registry.example/app:v1 Validates the canonical HCL stored in the artifact.
Composite source repeat --schema-file with compatible inputs Uses the union of the selected formats; conflicting definitions fail.

The source-support manifest records focused command evidence separately from accepted transport. A supported input that lacks a focused command test is not a claim that its format can express every Ptah object.

Prerequisites: an installed ptah binary (Install Ptah) and a desired schema as local files.

Save this as broken.yaml. It declares one table with two problems: an index over a column the table does not have, and a foreign key to a table nothing declares.

tables:
orders:
columns:
id:
type: SERIAL
primary: true
customer_id:
type: INTEGER
not_null: true
foreign: customers(id)
indexes:
idx_orders_status:
fields: [status]

Save this beside it as schema.hcl. It declares a sound schema in a layout that is not canonical:

schema "public" {}
table "customers" {
schema = schema.public
column "id" {
type = int
}
column "email" {
type = varchar(255)
null = false
}
}

--dialect is required, because a declaration valid for one target can be invalid for another:

Terminal window
ptah schema validate --schema-file broken.yaml --dialect postgres

Expected output includes:

postgres: index "idx_orders_status": names column "status", which table "orders" does not declare
postgres: schema: invalid foreign key: field "customer_id" references unknown table "customers"
2 structural problems

The run exits 1. Every line names the dialect it was found under, then the object, then what is wrong with it. A schema with nothing wrong prints nothing and exits 0, so a hook can read the status alone. Cancellation or failure to perform validation returns an error instead of schema findings. Those errors exit 2; they do not establish whether the schema is valid.

schema plan, schema apply, schema compare and schema drift check every index of the desired schema before comparing it with a database, and schema diff checks every side it reads from a document, --from as well as --to. An index whose table no declaration defines, or that names a column its table does not declare, gets the diagnostic schema validate prints, and those commands exit 2 without a plan, comparison or drift report. ptah-compat schema diff refuses the same documents and exits 1. The check reads the whole document: --exclude and --include choose what a run changes, and do not hide a fault in what it reads.

On a PostgreSQL URL that pins no search_path, those commands first place a table the schema names without a schema in the connection’s current schema (PostgreSQL), so the line names the table with that schema, as table "public.orders". schema diff of two documents places it in the dev database’s current schema. schema validate connects to no database, so it names the table as the source writes it.

A --to document of schema diff is also held to the target’s declaration rules, which schema plan applies to a desired schema: the types, features and keys the target accepts. These rules read the tables a run selects.

A --from document describes the state a plan starts from, so it is held to one of those rules: the referenced-key rule, which refuses a foreign key whose referenced columns the target’s referenced-key policy does not accept as a key. The rule applies to a --from document only on an engine whose server refuses such a key when it creates the table. The line starts with load --from schema:, and the command exits 2 (1 on ptah-compat). A fault of another rule in a --from document, such as ON DELETE SET NULL on a NOT NULL column, does not stop the diff and does not hide a key the rule refuses.

On such an engine the referenced-key rule reads each document whole, on both sides, as the index check does: the Atlas CLI creates each document on its dev database before it selects, so --exclude and --include do not hide the fault. With --dev-url, a --from document compared with a database or a migration directory is created on the dev database first, so that server judges it too.

SQLite creates such a table and, with foreign keys enforced, refuses the first write to it. A SQLite database can hold that state, so a --from document on SQLite is not held to the rule, as on the Atlas CLI. A --to document on SQLite is refused, where the Atlas CLI plans it: the plan would create a table no row can be written to.

Validate against every dialect you ship to

Section titled “Validate against every dialect you ship to”

--dialect is repeatable, and each value is checked separately with its own lines:

Terminal window
ptah schema validate --schema-file broken.yaml --dialect postgres --dialect mysql

Expected output includes:

postgres: index "idx_orders_status": names column "status", which table "orders" does not declare
postgres: schema: invalid foreign key: field "customer_id" references unknown table "customers"
mysql: index "idx_orders_status": names column "status", which table "orders" does not declare
mysql: schema: invalid foreign key: field "customer_id" references unknown table "customers"
4 structural problems

A problem that only one target has is reported under that target alone. A schema declaring a foreign key validates on postgres and fails on clickhouse with clickhouse: schema: clickhouse does not support foreign keys, because ClickHouse models none.

--root-dir reads Go annotations instead of a file, and --schema-file is repeatable. Naming both merges them into one composite desired schema. --server-version refines the capability set a dialect stands for, for example --dialect postgres --server-version 17.

Fail when a target would drop a declaration

Section titled “Fail when a target would drop a declaration”

Structural validation asks whether the schema is sound. It does not ask whether the target keeps everything the schema declares, and those are different questions: a table carrying ENGINE=InnoDB is sound, and PostgreSQL renders no table engine at all. --no-skipped adds the second question.

Terminal window
ptah schema validate --schema-file schema.sql --dialect postgres --no-skipped

Expected output includes:

postgres: column "users.id": auto-increment would be skipped; declare the column type as SERIAL or BIGSERIAL, or give it an identity clause with identity_generation
postgres: table "users": table option AUTO_INCREMENT=100 would be skipped; declare the start on the key column with identity_start
postgres: table "users": table option CHARSET=utf8mb4 would be skipped
postgres: table "users": table option COLLATE=utf8mb4_bin would be skipped
postgres: table "users": table option ENGINE=InnoDB would be skipped
5 problems

The first line is the one to read first. id INT NOT NULL AUTO_INCREMENT renders on PostgreSQL as "id" INT PRIMARY KEY NOT NULL, so the key generates nothing and every insert has to supply one. It is also what makes the line below it usable: moving the start onto identity_start is advice about a key this target was not generating until the column itself is fixed.

Each lost declaration is one line, so fixing some of them shortens the report rather than leaving it unchanged. A remedy is printed only where one works on that target. The run exits 1, the same status a structural problem carries, and a target that keeps every declaration prints nothing and exits 0.

The check reads what the renderer decided, not what it wrote in a comment. That matters because the targets are not equally talkative: the PostgreSQL family names a dropped table option on a skipped comment and SQLite, SQL Server and Oracle drop the same option without a word. A check built on the comment would report the quiet targets as the strict ones. ptah schema render reads the same decisions, names each one in a warning on stderr, and still exits 0:

warning: sqlite: table users: table option ENGINE=InnoDB was skipped (unsupported)

A render the target refuses outright fails this mode too, as a schema problem carrying the refusal.

The flag is opt-in because a schema written for several engines is expected to lose engine-specific declarations on the others. It reports only a declaration the target dropped on its own, so it stays quiet about:

  • an object a dialects= scope excludes from this target, which was never part of that target’s desired state;
  • a declaration a platform override replaced for this target, where the schema already says what this target gets;
  • a declaration made for one engine alone, such as a ClickHouse materialized view’s refresh schedule, which was never part of another target’s view.

The check reads a render of the create statements, so what it can see is what a CREATE carries: tables, columns and indexes. It covers what a renderer names as skipped, the table options a target cannot carry, every property a column declares, and an index’s condition, operator class, storage parameters, part order and uniqueness.

It also covers what a whole object declares: a table’s partitioning, a schema’s character set and collation, and a role’s comment.

Two cases are still outside it, and stokaro/ptah#2983 tracks them. The DROP path is one, because this check renders creates and never reaches a DROP. The other is an index’s type, which needs a rule for telling a normalized default from a discarded declaration before it can be reported at all: neither PostgreSQL nor the MySQL family writes USING BTREE, so a report built today would call a normalized default a loss.

A comment is reported wherever the target does not store it, including where the render writes it as a -- text line. SQLite and SQL Server keep none of a table, column or index comment. Oracle and ClickHouse keep the first two and have no clause for the third. The MySQL family and PostgreSQL keep all three.

A partial index reports its condition wherever the target drops it. That one is worth a gate on its own: the MySQL family and ClickHouse render the index over the whole table instead, so a unique index starts rejecting rows the author meant to allow.

A FULLTEXT parser is a platform property of the MySQL family, so another target leaves it out without a report, as it leaves out any other target’s properties. The rest of an index reports the same way:

Property Kept by Dropped by
Operator class the PostgreSQL family every other target
Storage parameters the PostgreSQL family every other target
Descending part every target but ClickHouse ClickHouse
UNIQUE every target but ClickHouse ClickHouse

ClickHouse builds a data-skipping index, which enforces nothing and is ordered by the table’s sorting key, so a unique index accepts every duplicate and a descending part is built ascending. The render writes a -- line about the first of those; the server stores none of it, which is why the report names it as well.

A covering index’s INCLUDE payload is the one property that is refused rather than reported. A target without the clause fails the render, and a plan that adds the payload to an existing index fails the same way, which is the louder answer, so nothing is dropped for a report to name.

Some declarations belong to a whole object rather than to a column or an index:

Property Kept by Dropped by
Table partitioning the PostgreSQL family SQLite, SQL Server, Oracle, ClickHouse
Unlogged table PostgreSQL every other built-in target
Schema character set and collation the MySQL family PostgreSQL, SQL Server, ClickHouse
Role comment PostgreSQL, YugabyteDB CockroachDB, the MySQL family, SQL Server, Oracle, ClickHouse

Losing the partitioning produces one ordinary table where a partitioned one was declared. The MySQL family refuses a partitioning instead of dropping it, from every source: Ptah does not model MySQL or MariaDB partitioning (stokaro/ptah#6357), and an existing table without partitions would otherwise compare as synced. CockroachDB refuses it too. SQLite and Oracle report a schema and a role as unsupported objects outright, so they name the loss already and add no second line about a property.

A generated key reports the values it loses, not the spelling. Every target writes a key some way, so AUTO_INCREMENT in place of GENERATED ALWAYS AS IDENTITY is not a finding; a declared identity_start that the target drops is. Two targets write no key at all for a column that declares one, and they report that on its own line: ClickHouse has no generated column, and PostgreSQL generates through a sequence-backed type or an identity clause and reads the flag nowhere, so a plain INTEGER marked auto-increment is a column whose values the caller has to supply.

The rest of a column’s properties are reported wherever the target drops them:

Property Kept by Dropped by
Collation the MySQL family every other target
NOT NULL constraint name PostgreSQL 18 and later the MySQL family, SQLite, SQL Server, Oracle, ClickHouse
Column UNIQUE every target but ClickHouse ClickHouse

Two of these are worth more than their storage. A dropped collation changes which values a comparison and a unique index treat as equal, and a dropped UNIQUE lets the database accept rows the author meant to exclude.

A column’s character set and ON UPDATE clause are not in the table. Every source binds them to MySQL and MariaDB, so another target leaves them out without a report; see column character set and ON UPDATE.

A NOT NULL constraint name is the one case where a target refuses instead of reporting. The PostgreSQL family accepts the syntax on a server that stores no such name and would lose it on the way in, so it fails the render rather than carrying on; every other target drops the name, keeps the constraint, and reports the loss. The name PostgreSQL 18 gives a NOT NULL nobody named, <table>_<column>_not_null, is not refused, because a server that keeps names gives the column the same one; see a NOT NULL name on PostgreSQL 17.

ptah schema fmt walks the paths it is given, or the current directory when it is given none, and rewrites every .hcl file whose layout is not canonical. It prints the files it changed:

Terminal window
ptah schema fmt .

Expected output includes:

schema.hcl

Only files whose content changed are printed, so a run that changes nothing prints nothing. The rewrite is HashiCorp HCL’s own canonical layout — indentation, alignment and spacing — and it changes no value the file declares. schema.hcl becomes:

schema "public" {}
table "customers" {
schema = schema.public
column "id" {
type = int
}
column "email" {
type = varchar(255)
null = false
}
}

--check rewrites nothing. It prints the files that are not canonically formatted and refuses:

Terminal window
ptah schema fmt --check .

Expected output includes:

schema.hcl
error: 1 file(s) are not canonically formatted; run `ptah schema fmt` to rewrite them

That run exits 2, not 1: an unformatted file is reported as a command failure rather than as an expected negative result. A pipeline step that treats any non-zero status as a failure needs no special handling; one that distinguishes 1 from 2 has to know this.

Message on stderr Cause Exit
error: --dialect is required: validation is per target, and a declaration valid for one dialect can be invalid for another schema validate with no --dialect. 2
error: schema fmt nosuch.hcl: stat nosuch.hcl: no such file or directory schema fmt given a path that does not exist. 2
error: 1 file(s) are not canonically formatted; run \ptah schema fmt` to rewrite them` schema fmt --check found files to rewrite. 2

See Exit codes for the contract these follow.

  • ptah schema validate checks structure, not renderability, unless --no-skipped is passed. Without it a declaration the renderer refuses for the same dialect validates cleanly: a SERIAL column validates against clickhouse and exits 0, while ptah schema render --dialect clickhouse over the same source exits 2 with clickhouse: SERIAL has no auto-increment equivalent. Either pass the flag or render as well as validate before trusting a target.
  • --no-skipped reports the declarations the render path names, the table options a target cannot carry, and the properties a target reads past on a column, an index or a whole object. Coverage is widened deliberately rather than assumed complete, and stokaro/ptah#2983 records what is left. It is not a portability verdict either: two targets that both keep every declaration can still store and compare the data differently.
  • ptah schema fmt reads .hcl files only. A YAML, SQL or DBML schema file in the same directory is left alone and not reported, so a formatting gate over a mixed directory covers the HCL half of it.
  • ptah schema fmt takes no --config and no --env. It works on paths, not on the schema sources a project configuration names.

Run ptah schema validate --help and ptah schema fmt --help for the flag sets with their environment variables. Native commands places both verbs in the tree, and Exit codes carries a row for each.