how-to

SQL schema

Use plain SQL DDL files as Ptah's desired schema.

Use SQL schema files when the desired schema is already written as local DDL (Data Definition Language). Ptah parses the file through its compatibility SQL parser; unsupported DDL fails explicitly instead of being skipped.

Write a schema file

Create schema.sql:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

PostgreSQL extension placement is preserved too. In a separate extensions.sql, Ptah accepts both the optional WITH spelling and the bare SCHEMA clause:

CREATE SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions VERSION '1.3';

Render that PostgreSQL-specific file with the PostgreSQL dialect:

ptah schema render --schema-file extensions.sql --dialect postgres

Expected output includes the schema precondition before the extension:

CREATE SCHEMA IF NOT EXISTS "extensions";

CREATE EXTENSION IF NOT EXISTS "pgcrypto" WITH SCHEMA "extensions" VERSION '1.3';

A column type may name its schema, quoted or not, the way pg_dump writes a type outside the search path: m app.mood, m "app"."Mood"[], g public.geometry(Point, 4326). A comparison matches it to the type the database reports by schema and name. A column moved to a type of the same name in another schema plans the change.

See PostgreSQL defaults.

Render it

ptah schema render --schema-file schema.sql --dialect sqlite

Expected output includes:

CREATE TABLE "users" (
  "id" INTEGER PRIMARY KEY,
  "email" TEXT NOT NULL
);

Rendering a SQL file back out proves the parser understood every statement, and can retarget the schema at another dialect. --schema-file is accepted wherever Ptah needs a desired schema: ptah schema render, ptah schema compare, ptah schema drift, the migration commands (ptah migrations plan / ptah migrations generate), and every target of ptah schema export except hcl. That includes the two documentation targets, so a Markdown or HTML reference can be generated from this file.

Path confinement is shared by every --schema-file source; see Schema file paths.

For PostgreSQL application roles, see Conditional role bootstrap.

Add a column after the table

A column can be added with ALTER TABLE ... ADD COLUMN in the same file, a schema directory, or an ordered atlas.hcl SQL src list. Columns follow declaration order.

CREATE TABLE users (id integer PRIMARY KEY);
ALTER TABLE users ADD COLUMN IF NOT EXISTS password_changed_at timestamptz;

IF NOT EXISTS makes the statement do nothing for a column the table already declares the same way. A column declared again is refused otherwise: without IF NOT EXISTS the server refuses it, and with it the server keeps the first declaration, so either reading would drop what the other one says.

Limit ON DELETE to some columns

PostgreSQL 15 and later take a column list after ON DELETE SET NULL and ON DELETE SET DEFAULT. The action then changes only the listed columns, so the other columns of a composite key may be NOT NULL:

CREATE TABLE parents (tenant integer, id integer, PRIMARY KEY (tenant, id));
CREATE TABLE children (
  id integer PRIMARY KEY,
  tenant integer NOT NULL,
  parent_id integer,
  FOREIGN KEY (tenant, parent_id) REFERENCES parents (tenant, id)
    ON DELETE SET NULL (parent_id)
);

Deleting a parent row sets parent_id to NULL and keeps tenant. The list is compared as a set of columns, and a list that names every column of the key means the same as no list.

A declaration the server would refuse, or would accept and then fail on when a parent row is deleted, is refused:

a NOT NULL column in the list, or a NOT NULL key column with no list;
a list after any action other than SET NULL or SET DEFAULT, or after ON UPDATE;
a column that is not part of the key. A column-level REFERENCES can list only its own column.

A target without the clause refuses the list instead of widening the action to every column: PostgreSQL before 15, YugabyteDB 2024.2, CockroachDB, and the other engines. The Go annotation and Atlas HCL exports refuse such a key for the same reason, because neither format can write the list.

Defer a check to the end of the transaction

A foreign key, a primary key, a UNIQUE and an EXCLUDE can put their check off. The clauses follow the constraint, on the table or on its column, in any order:

CREATE TABLE orders (id integer PRIMARY KEY);
CREATE TABLE lines (
  id integer PRIMARY KEY,
  order_id integer REFERENCES orders (id) DEFERRABLE INITIALLY DEFERRED,
  position integer UNIQUE DEFERRABLE,
  replaces integer,
  FOREIGN KEY (replaces) REFERENCES lines (id) INITIALLY DEFERRED
);

INITIALLY DEFERRED alone makes the constraint deferrable, as on PostgreSQL. The reader refuses what PostgreSQL 18.6 refuses: DEFERRABLE beside NOT DEFERRABLE, two timings, INITIALLY DEFERRED beside NOT DEFERRABLE, a clause after a CHECK, and a clause after a column that declares no key, as in a integer NOT NULL DEFERRABLE.

A deferrable key on a column is read as the table’s key over the column, as PostgreSQL reports it. A key that defers is a different key from one that does not, so changing the clause plans a drop and an add. A foreign key cannot reference a deferrable key; PostgreSQL refuses it.

A render for a target without the clause refuses the constraint rather than write one that checks at once:

Target	Foreign key	Primary key, `UNIQUE`
PostgreSQL	yes	yes, and `EXCLUDE`
Oracle	yes	yes
YugabyteDB	yes	no
SQLite	yes, `DEFERRABLE` first	no
CockroachDB, MySQL, MariaDB	no	no

The reader refuses the clauses on MySQL, MariaDB and CockroachDB, which refuses even NOT DEFERRABLE, and on SQLite after anything but a foreign key. The Atlas HCL and Go annotation exports refuse a deferrable key, because neither format can write one. The clauses PostgreSQL takes for the index behind a key, WITH (...) and USING INDEX TABLESPACE, are refused by name.

Clauses after a constraint or a key

These clauses after a table element change nothing the server builds, and the reader takes them:

ENFORCED after a CHECK, and after a foreign key on PostgreSQL. MySQL takes it after a CHECK only, and MariaDB not at all.
NOT VALID after a CHECK or a foreign key in a PostgreSQL CREATE TABLE, where the server records the constraint as validated.
MATCH SIMPLE after REFERENCES and its columns, before ON DELETE and ON UPDATE.
On MySQL and MariaDB, the index options VISIBLE, MariaDB’s NOT IGNORED, and USING BTREE or USING HASH after the parts of a key, the primary key included. A method written there is the one KEY k USING HASH (a) asks for, and the later clause wins.

The PostgreSQL and MySQL pages cover NOT ENFORCED, MATCH types, index COMMENT, INVISIBLE, IGNORED and KEY_BLOCK_SIZE [=] n. Primary keys keep COMMENT and KEY_BLOCK_SIZE too. NOT VALID after ALTER TABLE ... ADD is preserved.

The index options ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE have no model fields and are refused by name. The reader also refuses a clause the dialect’s server rejects, such as ENFORCED after a UNIQUE.

Change a table after creating it

A schema file is read as a script. An ALTER TABLE after the CREATE TABLE, in the same file, a later file of a schema directory or an imported file, changes the table the way the server would:

CREATE TABLE items (id integer NOT NULL, qty integer, note text, legacy text);
ALTER TABLE items ADD PRIMARY KEY (id);
ALTER TABLE items ALTER COLUMN qty SET DEFAULT 1, ALTER COLUMN qty SET NOT NULL;
ALTER TABLE items ALTER COLUMN note TYPE varchar(200);
ALTER TABLE items DROP COLUMN legacy;

DROP TABLE and DROP INDEX remove the object and what the server drops with it. A drop the server refuses is refused: an undeclared object without IF EXISTS, a table a foreign key or a PostgreSQL view still reads, the index behind a constraint, and the last index a MySQL or MariaDB foreign key needs.

On PostgreSQL, ALTER INDEX ... RENAME TO renames an index, and the constraint it backs. A column’s own UNIQUE becomes a named one. A name another relation holds is refused.

These operations are read:

ADD COLUMN, ADD PRIMARY KEY and ADD CONSTRAINT (UNIQUE, CHECK, FOREIGN KEY);
ALTER [COLUMN] ... SET DEFAULT, DROP DEFAULT, SET NOT NULL, DROP NOT NULL and [SET DATA] TYPE, with an optional USING;
DROP [COLUMN] [IF EXISTS] and RENAME [COLUMN] ... TO;
DROP CONSTRAINT [IF EXISTS] and RENAME CONSTRAINT, by the name the declaration gave the constraint or, for a column’s own UNIQUE, the name the server gives it;
the MySQL family’s MODIFY [COLUMN], DROP PRIMARY KEY, DROP FOREIGN KEY, DROP CHECK, DROP INDEX and RENAME INDEX, and SQL Server’s ALTER COLUMN with a new definition.

MODIFY keeps the column’s keys, as the server does. UNIQUE in it adds a key, so on a column with its own key it adds a second one, such as x_2 beside x.

Anything else is refused by name rather than read as nothing: an ALTER COLUMN action other than those above, RENAME TO, CASCADE, and an operation on a table the document does not declare. So is an operation the server would refuse:

a second primary key;
DROP NOT NULL or DROP COLUMN on a primary key column;
a missing column or constraint, or a name already held.

Dropping or renaming a column that an index, a constraint, an expression or another table’s foreign key still names is refused too, naming that object. PostgreSQL drops or follows the object with the column, but the schema file keeps its text. Drop the object first, or declare the column under its final name.

Comments

A comment can be set with a separate COMMENT ON, the way pg_dump writes one, in the same file or in a later file of a schema directory:

CREATE TABLE notes (id integer PRIMARY KEY, body text);
COMMENT ON TABLE notes IS 'what users wrote';
COMMENT ON COLUMN notes.body IS 'the text, as typed';

COMMENT ON is read in the forms pg_dump writes for the objects Ptah models:

COMMENT ON FUNCTION app.score(integer, text) IS 'ranks a note';
COMMENT ON MATERIALIZED VIEW app.totals IS 'refreshed nightly';
COMMENT ON TRIGGER stamp_notes ON app.notes IS 'sets updated_at';
COMMENT ON POLICY own_notes ON app.notes IS 'owners only';
COMMENT ON CONSTRAINT positive ON app.notes IS 'ids start at one';
COMMENT ON TYPE app.mood IS 'how a user feels';

That covers a table, a column, an index, a constraint, a schema, a role, a view, a materialized view, a sequence, a domain, a composite, range or enum type, an extension, a function, a procedure, a trigger and a policy, and the plan writes each of them to the database. A function or a procedure may be named with its argument list, with or without argument names, or by its name alone when the document declares one routine of that name. A trigger, a policy and a constraint are named ON their table.

A statement that cannot be applied is refused by name, with the file it came from:

a comment on an object the document does not declare, or on a trigger or a policy of another table;
a routine named by its name alone when the document declares more than one overload of it, or a function named as a procedure;
a comment on a domain’s constraint (ON DOMAIN) or on any other kind of object, such as a foreign table or a cast;
IS NULL.

A document may declare several overloads of a function or a procedure, as pg_dump writes them. Each is a routine of its own, told apart by its input argument types, so COMMENT ON FUNCTION app.f(integer, text) names one of them.

Row-level security

A PostgreSQL schema file declares row-level security with the statements a migration would run:

CREATE TABLE sites (id uuid PRIMARY KEY, tenant_id uuid NOT NULL);
ALTER TABLE sites ENABLE ROW LEVEL SECURITY;
ALTER TABLE sites FORCE ROW LEVEL SECURITY;
CREATE POLICY sites_tenant ON sites
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);
CREATE POLICY sites_scope ON sites AS RESTRICTIVE
  USING (current_setting('app.site_scope', true) = 'all');

FORCE binds the table’s owner to its policies; without it the owner reads and writes past every one of them. ENABLE and FORCE may come in either order. AS RESTRICTIVE narrows what the permissive policies admit, and AS PERMISSIVE is the default. Both flags are compared with the database and planned in both directions; PostgreSQL has the details.

Three spellings are refused rather than read. DISABLE ROW LEVEL SECURITY and NO FORCE ROW LEVEL SECURITY take a protection away, and a schema file says a table has none by not declaring it. A FORCE for a table the file never enables is refused too: PostgreSQL accepts it, and it changes nothing until the table enables row-level security.

API export metadata

SQL DDL cannot author Ptah’s export-only api_name, openapi_name, graphql_name, proto_name, api_type, or api_expose metadata. Exports work from a SQL schema, with public names, types, and exposure derived from the persistence schema. Use YAML, HCL, or Go annotations when the published contract must differ from database names and types.

`--dialect` decides how the file is read, not only how it is written

The dialect selects the tokenizer as well as the renderer: whether a backslash escapes inside a string, whether E'...' is an escape string, whether a string continues on the next line, whether --x without a space is a comment, and whether [name] is an identifier.

A file that the named engine would reject is rejected here. PostgreSQL runs with standard_conforming_strings on, so a backslash is an ordinary character and DEFAULT 'a\'b' is an unterminated string — PostgreSQL 18 answers unterminated bit string literal. Read with --dialect postgres, Ptah refuses it too. Read with --dialect mysql, where a backslash escapes, the same bytes are a valid default.

A PostgreSQL string constant is read in every spelling the server reads. With --dialect postgres, a comment, a column or domain default and an enum label may each be written as a dollar-quoted string, an E'...' escape string, a U&'...' Unicode escape string with an optional UESCAPE clause, or a string continued on the next line:

COMMENT ON COLUMN notes.body IS 'the text, '
  'as typed';
COMMENT ON TABLE notes IS $c$what users wrote, apostrophes and all$c$;

Each reads as the one string the server stores, so the file compares clean against a database it was applied to. The continuation follows PostgreSQL’s rule: only whitespace between the two strings, with at least one newline in it, and a -- comment counts as whitespace. Two strings on one line, or with a block comment between them, are refused, as PostgreSQL refuses them, and so is an escape the server rejects, such as U&'\0000'. With --dialect cockroachdb, yugabytedb or spanner, a comment, default or label written as a continued string or a U&'...' string is refused: CockroachDB applies a different continuation rule and has no U&'...' strings, and the other two have not been measured.

A version-guarded span is stepped over, and the one clause a schema needs is read out of it. mysqldump writes a full-text index’s parser as FULLTEXT KEY `ft` (`bio`) /*!50100 WITH PARSER `ngram` */, and that clause reaches the schema. The rest of a guard is not read: those spans hold version-conditional fragments Ptah does not model, and mariadb-dump opens every file with /*M!999999\- enable the sandbox mode */ — a guard no server executes, because no server is version 999999.

An unquoted name folds the way the engine folds it. PostgreSQL and YugabyteDB lower the ASCII letters of an unquoted name, so CREATE TABLE Docs (Id integer) declares the table docs with the column id, and a later ALTER TABLE DOCS or CREATE POLICY p ON docs names that table. CockroachDB lowers every letter of an unquoted name, past ASCII too. A quoted name keeps its case on all three. The rule covers every name the file writes: tables, columns, indexes, constraints, policies, roles, and the object a statement such as GRANT or CREATE INDEX names. Other dialects, and a read with no dialect, keep a name as written.

An ALTER TABLE reaches the table and column its dialect’s server would find for the names it writes. "Docs" and docs are two tables on PostgreSQL, and a file may declare both: each keeps its own columns, and ALTER TABLE docs changes docs. In a file that declares only "Docs", the same statement is refused, as PostgreSQL refuses it. ClickHouse compares names exactly too. SQLite ignores the case of ASCII letters, quoted or not, so ALTER TABLE docs reaches Docs there, and ärger does not reach Ärger. SQL Server, under its default case-insensitive collation, ignores case past ASCII as well. MySQL and MariaDB compare column names without case. A MySQL table name follows the server’s lower_case_table_names, which a schema file does not carry, so a spelling reaches a table that matches it exactly, or else the one table that differs from it only in case. A read with no dialect compares exactly.

Role names differ on CockroachDB, which lowers every role name, quoted or not: read with --dialect cockroachdb, "App_Reader" names the role app_reader, and "PUBLIC" is the PUBLIC keyword.

Omitting --dialect keeps a permissive reader. No dialect means no dialect’s rules, which is what lets one file mixing conventions be read at all. Name the dialect when the file belongs to one engine.

Use it

Everything a desired schema is for is the same for every source and lives on Work with a desired schema. For SQL the flag is --schema-file. What follows is specific to this source.

Split a schema across files

A SQL schema file can pull in other SQL files with an atlas:import comment, one per line:

-- schema/main.sql
-- atlas:import ./tables/orders.sql
-- atlas:import ./tables/users.sql

Point --schema-file at the entry point and the declarations merge in the order the file lists them. The entry point may declare objects of its own, and an imported file may import in turn. ptah-compat schema inspect writes this layout when its output goes through split, so an export reads back as the schema it was taken from.

Each path is relative to the file that writes it and must stay inside the entry point’s directory. An absolute path, a path that climbs out with .., a missing file, one that is not .sql, and a cycle are each refused by name.

Diff two SQL files locally

ptah schema diff compares local SQL files directly. With old.sql describing the deployed shape and schema.sql adding a pets table, a dev database replays both sides:

ptah schema diff \
  --from old.sql \
  --to schema.sql \
  --dev-url "sqlite://dev?mode=memory"