Validate and format schema files

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

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
}
}

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 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.

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 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

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-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.

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.

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, FULLTEXT parser, 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, a role’s comment, and a materialized view’s refresh schedule.

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; the MySQL family, Oracle and ClickHouse keep the first two and have no clause for the third.

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.

The rest of an index reports the same way:

Property	Kept by	Dropped by
Operator class	the PostgreSQL family	every other target
FULLTEXT parser	the MySQL 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, which is the louder answer, so nothing is dropped for a report to name.

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

Property	Kept by	Dropped by
Table partitioning	the PostgreSQL family	every other target
Schema character set and collation	the MySQL family	PostgreSQL, SQL Server, ClickHouse
Role comment	the PostgreSQL family	the MySQL family, SQL Server, Oracle, ClickHouse
Materialized view refresh schedule	ClickHouse	PostgreSQL, Oracle

Losing the partitioning produces one ordinary table where a partitioned one was declared, and losing the refresh schedule produces a view that is populated once and never again, which reaches its reader as stale data rather than as a missing clause. 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
Character set	the MySQL family	every other target
Collation	the MySQL family	every other target
ON UPDATE expression	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 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.

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.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
  }
}

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.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.

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

• 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.

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

• 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.