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.
Starting state
Section titled “Starting state”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.publiccolumn "id" {type = int}column "email" { type = varchar(255) null = false}}Validate against one dialect
Section titled “Validate against one dialect”--dialect is required, because a declaration valid for one target can be
invalid for another:
ptah schema validate --schema-file broken.yaml --dialect postgresExpected output includes:
postgres: index "idx_orders_status": names column "status", which table "orders" does not declarepostgres: schema: invalid foreign key: field "customer_id" references unknown table "customers"2 structural problemsThe 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:
ptah schema validate --schema-file broken.yaml --dialect postgres --dialect mysqlExpected output includes:
postgres: index "idx_orders_status": names column "status", which table "orders" does not declarepostgres: schema: invalid foreign key: field "customer_id" references unknown table "customers"mysql: index "idx_orders_status": names column "status", which table "orders" does not declaremysql: schema: invalid foreign key: field "customer_id" references unknown table "customers"4 structural problemsA 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.
ptah schema validate --schema-file schema.sql --dialect postgres --no-skippedExpected 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_generationpostgres: table "users": table option AUTO_INCREMENT=100 would be skipped; declare the start on the key column with identity_startpostgres: table "users": table option CHARSET=utf8mb4 would be skippedpostgres: table "users": table option COLLATE=utf8mb4_bin would be skippedpostgres: table "users": table option ENGINE=InnoDB would be skipped5 problemsThe 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
refreshschedule, 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.
Format HCL schema files
Section titled “Format HCL schema files”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:
ptah schema fmt .Expected output includes:
schema.hclOnly 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 }}Gate a pipeline on formatting
Section titled “Gate a pipeline on formatting”--check rewrites nothing. It prints the files that are not canonically
formatted and refuses:
ptah schema fmt --check .Expected output includes:
schema.hclerror: 1 file(s) are not canonically formatted; run `ptah schema fmt` to rewrite themThat 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.
Failure modes
Section titled “Failure modes”| 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.
Limitations
Section titled “Limitations”ptah schema validatechecks structure, not renderability, unless--no-skippedis passed. Without it a declaration the renderer refuses for the same dialect validates cleanly: aSERIALcolumn validates againstclickhouseand exits0, whileptah schema render --dialect clickhouseover the same source exits2withclickhouse: SERIAL has no auto-increment equivalent. Either pass the flag or render as well as validate before trusting a target.--no-skippedreports 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, andstokaro/ptah#2983records 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 fmtreads.hclfiles 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 fmttakes no--configand no--env. It works on paths, not on the schema sources a project configuration names.
Exact reference
Section titled “Exact reference”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.
Next steps
Section titled “Next steps”- Ready to see the SQL the schema renders to? Work with a schema source.
- Want the same check against a live database? Compare and drift.
- Adding these to a pipeline? Continuous integration.