SQL schema
Use plain SQL DDL files as Ptah's desired schema.
Use local SQL DDL files as a desired schema. Ptah parses every statement and refuses unsupported DDL.
Write a schema file
Section titled “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 postgresExpected 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';On PostgreSQL, CockroachDB, YugabyteDB and SQL Server, a plan creates a schema the file declares and the database lacks, whether or not anything is in it, and a rollback drops it. A plan never drops a schema the file does not mention. Atlas schema commands has the measured table.
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.
A PostgreSQL column can declare bit varying(8) in a SQL schema file.
varying is read as part of the column type.
PostgreSQL also accepts a table-level NOT NULL constraint that names its
column. Ptah attaches it to that column even when the constraint comes first:
CREATE TABLE accounts ( CONSTRAINT account_id_required NOT NULL id, id bigint);The constraint name is kept when the target is PostgreSQL 18 or newer. Ptah
refuses NO INHERIT and NOT VALID on this form because the schema model does
not represent them.
See PostgreSQL defaults.
Render it
Section titled “Render it”ptah schema render --schema-file schema.sql --dialect sqliteExpected output includes:
CREATE TABLE "users" ( "id" INTEGER PRIMARY KEY, "email" TEXT NOT NULL);--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.
Declare a ClickHouse row policy
Section titled “Declare a ClickHouse row policy”With --dialect clickhouse, CREATE ROW POLICY declares a policy and
ALTER ROW POLICY amends an earlier declaration, preserving omitted clauses.
DROP ROW POLICY is migration syntax and is refused in a desired schema.
See ClickHouse row policies for
examples and supported clauses.
Declare a SQL Server trigger
Section titled “Declare a SQL Server trigger”Read as SQL Server, a trigger names its table before its timing, in the T-SQL
order that ptah db read also prints:
CREATE TABLE t (id int PRIMARY KEY, a int);GOCREATE TRIGGER trg ON t FOR INSERT, UPDATE ASSET NOCOUNT ON;UPDATE t SET a = a WHERE 1 = 0;GOThe body runs to the end of its batch: to GO, to the END that closes a
BEGIN ... END body, or to the end of the file. FOR is read as AFTER, and
CREATE OR ALTER TRIGGER replaces a trigger. A header clause that Ptah does
not model is refused by name. See
SQL Server triggers.
Add a column after the table
Section titled “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 skips the statement for a column the table already has, as
the server skips it: the first declaration stands, whatever type, default or
constraint the second one writes. PostgreSQL and CockroachDB skip the whole
subcommand, its keys and sequence included. This is the rule CREATE TABLE ... IF NOT EXISTS follows, described under
--dialect decides how the file is read.
Without IF NOT EXISTS, a column declared again is refused, as the server
refuses it.
On MariaDB a restated column that adds a key is refused: a REFERENCES
clause, or a UNIQUE or PRIMARY KEY the column does not have. MariaDB skips
the column and still adds the key, which a restatement cannot say. Declare the
key with ALTER TABLE ... ADD FOREIGN KEY, ADD UNIQUE or ADD PRIMARY KEY.
Limit ON DELETE to some columns
Section titled “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 NULLcolumn in the list, or aNOT NULLkey column with no list; - a list after any action other than
SET NULLorSET DEFAULT, or afterON UPDATE; - a column that is not part of the key. A column-level
REFERENCEScan 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. Go annotations preserve on_delete_columns. Atlas HCL export
refuses a narrowed list; it can omit a list covering the whole key.
A change replaces the key in a transaction, preserving its declared settings and comment. Reordering or repeating columns produces no migration.
Defer a check to the end of the transaction
Section titled “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.
ALTER TABLE ... ALTER CONSTRAINT name [NOT] DEFERRABLE [INITIALLY DEFERRED | IMMEDIATE] changes the timing of a foreign key declared earlier in the same
file, as Ent’s schema export writes it. A property the statement does not name
keeps its value, and NOT DEFERRABLE clears the timing. The name has to belong
to a foreign key; the reader refuses any other constraint, as PostgreSQL does.
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.
On PostgreSQL, ALTER TABLE ... ADD [CONSTRAINT name] UNIQUE | PRIMARY KEY USING INDEX index makes a key of a unique index the schema declares on the
table, as the server does: the key takes the index’s columns, INCLUDE
columns and NULLS NOT DISTINCT, and the index is renamed to the constraint’s
name, or keeps its own where the statement names none. An index the server
does not make a key of is refused with the server’s reason, and so is one with
an operator class, storage parameters or a comment, which a key does not keep.
CREATE TABLE and the other engines refuse the clause.
Clauses after a constraint or a key
Section titled “Clauses after a constraint or a key”These clauses after a table element change nothing the server builds, and the reader takes them:
ENFORCEDafter aCHECK, and after a foreign key on PostgreSQL. MySQL takes it after aCHECKonly, and MariaDB not at all.NOT VALIDafter aCHECKor a foreign key in a PostgreSQLCREATE TABLE, where the server records the constraint as validated.MATCH SIMPLEafterREFERENCESand its columns, beforeON DELETEandON UPDATE.- On MySQL and MariaDB, the index options
VISIBLE, MariaDB’sNOT IGNORED, andUSING BTREEorUSING HASHafter the parts of a key, the primary key included. A method written there is the oneKEY 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
Section titled “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;A statement finds a table with or without the engine’s default schema: on
PostgreSQL, ALTER TABLE public.items changes the table CREATE TABLE items
created, and DROP TABLE items drops one created as public.items. A
database URL that selects another schema by search_path creates a table
written without a schema in that schema, so a file read for such a run spells
each table one way.
CREATE INDEX ... ON finds a table or a materialized view by the same rule, in
the schema the name spells. A bare name means the default schema: public on
PostgreSQL, YugabyteDB and CockroachDB, dbo on SQL Server, and main on
SQLite. On MySQL and MariaDB it means the database the connection selects,
which the file does not name, so app.items and items are two tables there,
as they are to ALTER TABLE. A name that only a relation of another schema or
database carries is refused on every engine. PostgreSQL refuses
CREATE INDEX ON items beside only app.items with
relation "items" does not exist.
DROP TABLE, DROP INDEX, DROP VIEW and DROP MATERIALIZED VIEW 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, a view another view reads there, the index behind a
constraint, and the last index a MySQL or MariaDB foreign key needs.
On ClickHouse, DROP TABLE also removes a declared view or materialized view.
The SQL schema reader removes that view from the desired schema.
DROP TRIGGER, DROP FUNCTION, DROP PROCEDURE, DROP SEQUENCE and DROP TYPE
are refused by name: the model does not follow them, and stepping over one would
keep an object the script removes. TRUNCATE, LOCK, SAVEPOINT and the other
statements that change no schema object change nothing the read returns.
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. DROP INDEX and ALTER INDEX find an index of a table or of
a materialized view in the schema the name spells, and a bare name means the
default schema, so DROP INDEX app.items_idx and DROP INDEX items_idx name
two indexes.
These operations are read:
ADD COLUMN,ADD PRIMARY KEYandADD CONSTRAINT(UNIQUE,CHECK,FOREIGN KEY);- on PostgreSQL,
ADD [CONSTRAINT name] NOT NULL column, which does whatSET NOT NULLdoes and keeps the name.NOT VALIDandNO INHERITafter the column are refused, because the model has no field for them; ALTER [COLUMN] ... SET DEFAULT,DROP DEFAULT,SET NOT NULL,DROP NOT NULLand[SET DATA] TYPE, with an optionalUSING;DROP [COLUMN] [IF EXISTS]andRENAME [COLUMN] ... TO;DROP CONSTRAINT [IF EXISTS]andRENAME CONSTRAINT, by the name the declaration gave the constraint or, for a column’s ownUNIQUE, the name the server gives it.RENAME CONSTRAINTalso reads a column’s namedNOT NULL; useALTER COLUMN ... DROP NOT NULLto remove it;- the MySQL family’s
MODIFY [COLUMN],DROP PRIMARY KEY,DROP FOREIGN KEY,DROP CHECK,DROP INDEXandRENAME INDEX, and SQL Server’sALTER COLUMNwith a new definition.
On PostgreSQL, a plan that adds a column with its own primary key writes the
column’s NOT NULL and the ADD PRIMARY KEY as separate clauses of one
ALTER TABLE statement. An explicit name on the column’s NOT NULL stays
with that column.
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 NULLorDROP COLUMNon 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.
Declare view options
Section titled “Declare view options”The WITH list before AS declares a PostgreSQL view’s options and a
materialized view’s storage parameters, in the spellings pg_dump writes and
the bare ones alike:
CREATE VIEW order_report WITH (security_invoker = 'true') AS SELECT id, amount FROM orders;CREATE MATERIALIZED VIEW order_totals WITH (fillfactor = 70) AS SELECT sum(amount) AS total FROM orders;The plan removes a storage parameter or a false option the database holds and
the file leaves out, and refuses to
remove
a security option the view holds on: write security_invoker = false for that.
View options and storage
parameters says
how they are compared and changed, and which engines take them.
ALTER VIEW order_report OWNER TO reporting after the view declares its owner,
and ALTER MATERIALIZED VIEW does the same for a materialized view. A view no
such statement names keeps whatever owner it has; see View and materialized
view owners.
Declare a check option
Section titled “Declare a check option”A view that ends in WITH CHECK OPTION declares a check option, as
with_check does in Go annotations and YAML, and the level the clause names
is kept:
CREATE VIEW positive_orders AS SELECT id, amount FROM orders WHERE amount > 0 WITH LOCAL CHECK OPTION;WITH LOCAL CHECK OPTION checks a row written through the view against the
view’s own condition. WITH CHECK OPTION and WITH CASCADED CHECK OPTION
check it against the conditions of the views it reads as well. Go annotations
and YAML write the level in with_check: true or cascaded, local, or
false. HCL writes check_option = LOCAL or check_option = CASCADED.
The clause is not part of the view’s query. PostgreSQL, MySQL and MariaDB
store it apart from the query, in information_schema.views.check_option, and
the comparison reads it from there. A view whose level differs from the
declared one is replaced. The plan writes WITH LOCAL CHECK OPTION for a
LOCAL view and WITH CHECK OPTION for a CASCADED one. The words inside a
string literal, a quoted name, a comment or a subquery stay part of the query.
SQL Server and Oracle have no LOCAL level, so the plan refuses a LOCAL
view there by name rather than create it with the stricter CASCADED check.
Comments
Section titled “Comments”COMMENT ON CONSTRAINT ... ON table stays executable when parsed SQL is rendered.
COMMENT ON sets a declared object’s comment in the same file or a later
schema file:
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';Ptah plans comments for tables, columns, indexes, constraints, schemas, roles,
views, materialized views, sequences, domains, composite types, ranges, enums,
extensions, functions, procedures, triggers, and policies. Functions and
procedures accept argument lists with optional argument names, or a bare name
when the document declares one overload. Triggers, policies, and constraints
are named ON their table. An index is named in the schema of its table or
materialized view, and a
bare name is an index of the default schema, so COMMENT ON INDEX app.notes_idx
and COMMENT ON INDEX notes_idx name two indexes. A SET search_path in the
file does not change which schema a bare name means. CockroachDB names an index
within its table, and two tables of one schema may each have an index of one
name. COMMENT ON INDEX notes@notes_idx names the index of one table there.
IS NULL clears a declared object’s comment; 'NULL' sets the text NULL.
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;
- on CockroachDB, an index named without its table when more than one table of the schema has an index of that name, which the server refuses as ambiguous;
- a comment on a domain’s constraint (
ON DOMAIN) or on any other kind of object, such as a foreign table or a cast.
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.
PostgreSQL functions retain LEAKPROOF, STRICT and PARALLEL through
conversion and export. Complete declarations without these clauses request
defaults. Property changes use CREATE OR REPLACE FUNCTION.
Row-level security
Section titled “Row-level security”PostgreSQL, CockroachDB and YugabyteDB schema files declare 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 subjects the table owner to policies. It requires ENABLE in the same
file and table lifetime, in either order. DROP TABLE ends that lifetime;
recreating the table restores its defaults. DISABLE ROW LEVEL SECURITY and
NO FORCE ROW LEVEL SECURITY are refused; omit protection to declare its absence.
AS RESTRICTIVE narrows permissive policies; AS PERMISSIVE is the default.
Both table flags are compared and planned in both directions. See
PostgreSQL.
COMMENT ON POLICY name ON table IS 'text' sets a declared policy’s comment;
IS NULL clears it. The declaration must precede the comment, including across
SQL files.
Role settings
Section titled “Role settings”A PostgreSQL schema file gives a role its configuration defaults with the statements that set them:
CREATE ROLE app_reader LOGIN;ALTER ROLE app_reader SET statement_timeout = '30s';ALTER ROLE app_reader SET search_path TO "$user", public;ALTER ROLE app_reader RESET work_mem;RESET and SET ... TO DEFAULT take a setting away; a setting the file does
not name is left as the server holds it. Statements about one setting fold in
file order, and each needs its role declared before it. ALTER ROLE ... IN DATABASE, ALTER ROLE ALL, SET ... FROM CURRENT and RESET ALL are refused.
See Role settings.
Declare a partition
Section titled “Declare a partition”On PostgreSQL and YugabyteDB, CREATE TABLE ... PARTITION OF declares a partition of
a table with PARTITION BY, whichever order the file writes them in:
CREATE TABLE events (id bigint NOT NULL, day date NOT NULL) PARTITION BY RANGE (day);CREATE TABLE events_2026 PARTITION OF events FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');CREATE TABLE events_rest PARTITION OF events DEFAULT;The partition takes its columns from its parent, so it declares none. ALTER TABLE parent ATTACH PARTITION partition bound, which pg_dump writes after the partition’s
own CREATE TABLE, declares the same partition. PostgreSQL table
partitions has the bound forms, how a
bound is compared, how a dump reads, and the forms that are refused. Every other
target refuses the statement by name.
Start from a pg_dump file
Section titled “Start from a pg_dump file”On PostgreSQL, the reader takes the ownership statements pg_dump --schema-only
writes after each object it dumps:
ALTER SCHEMA app OWNER TO postgres;ALTER TABLE app.t OWNER TO postgres;ALTER FUNCTION app.f(integer) OWNER TO postgres;ALTER VIEW app.v OWNER TO postgres;The reader takes ALTER ... OWNER TO for a table, schema, type, domain,
function, procedure, sequence, view or materialized view, and a function or
procedure may carry its argument list. After a view or a materialized view the
statement declares the view’s owner, as it does in any SQL file; see View and
materialized view owners.
After any other object it declares nothing: the role is not a role of the
schema, and the statement plans no CREATE ROLE, GRANT or OWNER TO.
So a plain dump plans as the same dump written with --no-owner does, plus the
owner of each view it holds. A dump names the roles of the server it was taken
from: create them on the target, or write the dump with --no-owner to leave
the owner of its views unmanaged. Another action on one of these kinds, such as
ALTER SCHEMA ... RENAME TO, and a role missing after OWNER TO are refused by
name. CockroachDB and YugabyteDB take the statement for a view and a
materialized view only, and every other target refuses it, because its server
has no OWNER TO.
Serial and identity columns
Section titled “Serial and identity columns”pg_dump writes a serial column as four statements, and the reader reads
them as the one column id serial declares:
CREATE TABLE public.orders (id integer NOT NULL);CREATE SEQUENCE public.orders_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;ALTER SEQUENCE public.orders_id_seq OWNED BY public.orders.id;ALTER TABLE ONLY public.orders ALTER COLUMN id SET DEFAULT nextval('public.orders_id_seq'::regclass);The sequence is part of the column, as a read of the database reports it, so
a plan from the dump creates no sequence the database already holds. The
reader folds a sequence into its column only where serial builds it as the
dump states it:
- the column is
NOT NULLor in the primary key, its type issmallint,integerorbigint, and its default isnextvalof the sequence it owns; - the sequence has the column’s type, the name
<table>_<column>_seq, the optionsserialgives it and no comment.
Any other sequence stays a sequence of its own, with its owner and its
options: one whose increment was changed after the table was created, one with
another name, or one with a comment. ALTER SEQUENCE ... OWNED BY NONE takes
the owner away, and any other action of ALTER SEQUENCE is refused by name. On
CockroachDB and YugabyteDB, the reader keeps the sequence as a sequence the
column owns, and a plan against a database that holds it does not create it
again.
CREATE SEQUENCE ... OWNED BY names the owner as ALTER SEQUENCE ... OWNED BY
does: table.column, or schema.table.column where the schema is the
sequence’s own. Either form reads as the column a read of the database
reports, so OWNED BY public.orders.id is the column id of orders. The
table and the column are found as an ALTER TABLE finds them, and the reader
refuses what PostgreSQL refuses: a table or a column the file has not declared
by then, a column written without its table, and a column of a table in
another schema. CockroachDB accepts a table in another schema, and the reader
refuses it there too. A column a database name qualifies,
db.public.orders.id, is refused: PostgreSQL takes it only for the database it
is connected to, and a schema file does not name one.
The owner follows its column as the server’s does. A DROP TABLE or a DROP COLUMN drops the sequences the column owns, and a RENAME COLUMN keeps them
owned by the column under its new name. A sequence that OWNED BY NONE
released, or that a later ALTER SEQUENCE ... OWNED BY gave to another table,
stays.
An identity column is the column, NOT NULL, and an ALTER TABLE ... ALTER COLUMN ... ADD GENERATED ... AS IDENTITY after it. The reader reads the
statement as the same clause written on the column, with the generation and
the options as the dump writes them. As for the clause on the column, a plan
compares the generation with the database and not the options. It refuses the statement where the server
refuses it: on a column that is an identity already, one with a default, a
nullable one outside the primary key, and one of a type other than smallint,
integer or bigint. CockroachDB reads the statement too; YugabyteDB refuses
it.
ALTER TABLE ... REPLICA IDENTITY, which pg_dump writes for a table whose
replica identity is not the default, is refused by name. Ptah does not model a
table’s replica identity, and pg_dump has no option that leaves the
statement out. Delete the statement from the dump, and set the replica
identity in a migration where a database needs it.
API export metadata
Section titled “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
Section titled “--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 column default with an operator is read as the engine reads it.
PostgreSQL, CockroachDB, YugabyteDB, SQL Server, Oracle and ClickHouse take
DEFAULT 5 - 2, and the default ends where the column’s next clause begins.
MySQL, MariaDB and SQLite take an expression default only in parentheses,
DEFAULT (5 - 2), and answer a syntax error to an operator outside them,
+ included. Read with one of those dialects, the file is refused, and the
refusal names the parenthesized form:
schema.sql:3:19: expected default value: 5 - 2 is an operator expression outside parentheses: write DEFAULT (5 - 2); MySQL 26.10 takes an expression default only in parentheses and answers ERROR 1064 (42000) to an operator outside themA comment inside such a default separates its tokens, as whitespace does, and
is not kept, so DEFAULT 5 - 2 -- the answer on a line of its own still ends
before the next column. After an operator, PostgreSQL, CockroachDB and
YugabyteDB read IS [NOT] DISTINCT FROM but no other IS test, and no AND
or OR, unless the whole default is in parentheses; SQL Server reads no
predicate in a default at all. Ptah refuses those defaults where the server
does. Oracle and ClickHouse read DEFAULT 1 + 2 IS NOT NULL as one
expression, with the NOT NULL belonging to the test.
The shifts << and >> are read on PostgreSQL, CockroachDB, YugabyteDB and
SQL Server, and refused on Oracle and ClickHouse, which have no shift operator.
== is read only on ClickHouse, which stores it as =. DEFAULT NOT NULL
declares the default NOT NULL on Oracle and ClickHouse, which is NULL, and
leaves the column nullable; every other engine answers a syntax error, and Ptah
refuses it. A number written beside a number type, as in DEFAULT -1 on an
INT column, is written as a number on every engine but ClickHouse, which
casts a quoted one. Beside a text type it is quoted, so DEFAULT 007 on a
VARCHAR column is the string '007'.
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
or yugabytedb, 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 YugabyteDB has not been measured.
On PostgreSQL, CockroachDB and YugabyteDB a block comment may hold another, as
/* a /* b */ c */, and reads as one comment to its own */, as the server
reads it. The same holds for a migration body, which migrations up splits into
statements by these rules. Every other dialect ends a comment at the first */.
A version-guarded span is stepped over, except among a key’s options, where
it is read as options. mysqldump writes KEY `k` (`a`) /*!80000 INVISIBLE */ and FULLTEXT KEY `ft` (`bio`) /*!50100 WITH PARSER `ngram` */, and
both reach the schema. Other guards 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.
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.
A name past the limit is cut where the server cuts it. PostgreSQL and
YugabyteDB keep the first 63 bytes of a longer name, quoted or not, ending on a
character boundary, and print a notice. The reader records the object under the
same name, so render, compare and planning all use the name the database holds:
a foreign key that Ent’s schema export names in 64 bytes is the key the server
holds, and an ALTER TABLE that names it by its first 63 bytes reaches it.
CockroachDB keeps a long name whole, and so does the reader there.
Every command that reads the file reports the names it cuts in one warning, on
ptah and ptah-compat alike. One name is reported as itself, such as
warning: schema file schema.sql: foreign key "…" in table "public.orders" is 69 bytes; the server keeps its first 63: "…". Several are one line with how
many, counted once each however often the run reads them. A PRIMARY KEY or
UNIQUE constraint counts once, although the server stores its index under the
same name:
warning: 47 names are longer than 63 bytes; the server keeps their first 63 bytes, and they are read that way; PTAH_LOG_LEVEL=debug lists themPTAH_LOG_LEVEL=debug lists each name written and kept, and
PTAH_LOG_FORMAT=json lists them as records with written, stored, kind,
scope, source and limit fields, followed by the summary as a record with
category and count. The summary record is written at any log level, as the
text line is. The strict compatibility
profile refuses both variables. See
Warnings about many objects.
Two different names cut to one are refused in the namespace where the server
stores them as one. PostgreSQL keeps one namespace per schema for tables, views,
materialized views, sequences and indexes, including the index that a
PRIMARY KEY, UNIQUE or EXCLUDE constraint builds under its own name.
Constraint, column and trigger names are unique per table, so two tables may
each hold a foreign key of the same cut name. A function or procedure name is
unique per schema together with its argument types. The refusal names both declarations and the
name they share. It holds under IF NOT EXISTS and OR REPLACE too, where the
server skips the second statement or lets it replace the first: the file names
two objects, and the server would keep one of them without an error. Ptah is
stricter than the server here.
Lint reports the same names without a database. ptah sql lint reports each
cut name as DDL003, a warning, and each cut name the server refuses as already
in use as DDL004, an error, at the line and column where the name is written.
ptah migrations lint reports them as PG401P and PG402P at the line of the
statement, and follows the names across the directory in the order its
versions apply. A guarded statement whose cut name lands on a name in use is
reported as DDL003 or PG401P, with a note that the server keeps one of the
two objects, because the server accepts the statement. Both commands read
.ptah-lint.yaml, so a project can promote DDL003 to an error or disable it
like any other rule; see Lint rules.
The same namespaces decide a name written twice. A table created again without
IF NOT EXISTS, a second index of one name, an index, view or sequence named
like a table, or a constraint added under a name the table already uses is
refused, as PostgreSQL refuses it with already exists. Two PRIMARY KEY or
UNIQUE constraints that one CREATE TABLE writes identically, name and
definition alike, are read as one, as the server keeps one. A schema directory
refuses an object that a later file declares again in its own words.
An extension one file creates twice without IF NOT EXISTS is refused on
PostgreSQL and YugabyteDB, which refuse the second CREATE EXTENSION with
extension "pgcrypto" already exists. CockroachDB runs the second statement,
so a file read for it declares the extension once.
On PostgreSQL and YugabyteDB, a guard on a name written twice is read the way the server runs it, in one file or across the files of a schema directory, so a database built from the file and the file itself compare equal:
- A
CREATE TABLE,CREATE INDEX,CREATE SEQUENCEorCREATE MATERIALIZED VIEW ... IF NOT EXISTSis skipped whole when its name is taken in the namespace the server checks. The first declaration stands with nothing of the second, not its columns, keys or options. - Another kind of object can hold the name, such as a view of the table’s
name, and so can a name the server derives:
t_pkeyfor the primary key oft,t_a_keyfor aUNIQUEcolumna, andt_id_seqfor the sequence of aserialor identity columnid. An identity column’sSEQUENCE NAMEnames its sequence instead. - Keys of one
CREATE TABLEthat are equal build one index, the primary key’s first:id int PRIMARY KEY UNIQUEholdst_pkeyalone.ALTER TABLEfolds no keys. - A name is free again once the server drops it. A
DROP TABLEdrops the indexes, keys and column sequences of its table, and aDROP COLUMNthe column’s own key and sequence. A sequence a default only draws from stays. - A
CREATE OR REPLACEview, function, procedure or trigger replaces the definition of the object the server finds,public.vandvalike. The last definition counts, and a comment on the first one stays.
On other engines the reader skips a CREATE TABLE or CREATE SEQUENCE ... IF NOT EXISTS whose name the file already declares. On CockroachDB a table, a
view, a materialized view or a sequence of the schema holds the name, because
they share one namespace there. An index does not, because CockroachDB names an
index per table. On the other engines only a table or a sequence of the
statement’s own kind holds it. MySQL, MariaDB and SQL Server hold one routine
per name, so a CREATE OR REPLACE or CREATE OR ALTER replaces the routine of
its name, whatever arguments either takes. MariaDB drops a routine’s comment on
OR REPLACE, so only the replacement’s comment stays there.
A CREATE EXTENSION IF NOT EXISTS of an extension a statement before it
declared is skipped whole, as PostgreSQL skips it with a notice, in one file
or in a later file of a schema directory. The extension stays in the schema
and at the version the first statement gave it, and a COMMENT ON EXTENSION
after the repeat comments on it. Without the guard, a second declaration that
differs from the first is refused.
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
Section titled “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
Section titled “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.sqlPoint --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
Section titled “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"Expected output includes:
CREATE TABLE "pets" ( "id" INTEGER PRIMARY KEY, "name" TEXT NOT NULL, "user_id" INTEGER NOT NULL CONSTRAINT "fk_pets_user_id" REFERENCES "users" ("id"));Failure modes
Section titled “Failure modes”-
A change that a SQLite dev database cannot express as an in-place
ALTERis refused loudly rather than turned into an incomplete diff. For example, adding aNOT NULLcolumn to an existing table exits withsqlite: adding column email to table users requires a table rebuild plan. -
A refusal names the file, line and column where the reader stopped, in the form an editor and a terminal open:
read SQL schema file schema.sql:7:12: expected '(' after table name: expected Operator, got IdentifierLines count from 1 and end at LF, CRLF or a lone CR. Columns count from 1 in characters, so a multi-byte character is one column and a tab is one column, which is what
ptah sql lintreports for the same file. A refusal at the end of the file names the place after its last character, which is column 1 of the next line when the file ends with a line ending. Both numbers belong to the file as written, with its comments, multi-line strings andDELIMITERlines counted, and a schema directory or an import names the file that holds the statement. Text read from a command rather than a file has no file to name, and readsline 7, column 12: .... A refusal of what a statement declares, such as anALTER TABLE ... ADD COLUMNfor a column the table already has, comes after the statement was read, and names where that statement begins. The examples below leave out the leadingread SQL schema file. -
Unsupported DDL constructs fail with a parse error naming the statement. Treat the error as a compatibility gap and check the conformance reports.
-
A table element ends at a comma or at the closing parenthesis. A clause the reader does not take after a column or constraint is refused by its first word rather than read as another column:
CREATE TABLE t (a integer, CHECK (a > 0) NO INHERIT);schema.sql:1:42: unexpected NO after a table element: expected ',' or ')' -
A constraint name on
DEFAULTis refused. Ptah keeps a name onNOT NULL,CHECK,REFERENCES,UNIQUEandPRIMARY KEYwhere the dialect’s grammar takes one; the last two are read as the table constraint they describe, which is the level a name lives at. A default has no such level and no engine Ptah supports records one:CREATE TABLE t (b INTEGER CONSTRAINT c_x DEFAULT 1);schema.sql:1:42: named column constraint "c_x": Ptah has nowhere to keep aname on DEFAULT, and does not read one back from a database, so write theconstraint without a name; a name is kept on NOT NULL, CHECK, REFERENCES,UNIQUE and PRIMARY KEYWrite
b INTEGER DEFAULT 1instead. A name Ptah accepts and cannot read back would make every later comparison report a difference no apply can settle. -
An index name between
FOREIGN KEYand its column list is read under--dialect mysqland--dialect mariadb, and refused elsewhere. On MySQL it names the index the server builds for the key, which is not built where another index covers the key and gives way to a later index that begins with its columns. On MariaDB it is the key’s own name, and the key’s index takes it. No other engine has the syntax:CREATE TABLE child (a INT, FOREIGN KEY zidx (a) REFERENCES parents (id));schema.sql:1:40: an index name after FOREIGN KEY is the MySQL family's alone;postgres has no such syntax, so write the key as FOREIGN KEY (columns) anddeclare the index "zidx" separatelyA name written beside an explicit
CONSTRAINTsymbol is accepted and ignored, because both engines record the symbol for the backing index too. -
CONSTRAINTwithout a name is read under--dialect mysqland--dialect mariadb, where the name is optional, and refused elsewhere. On those enginesCONSTRAINT FOREIGN KEY (a) REFERENCES parents (id)is the same unnamed key asFOREIGN KEY (a) REFERENCES parents (id), and it takes the same name. Every other dialect requires the name:CREATE TABLE child (a INT, CONSTRAINT FOREIGN KEY (a) REFERENCES parents (id));schema.sql:1:28: CONSTRAINT is followed by FOREIGN, not by a name: postgresrequires a name after CONSTRAINT, and only MySQL and MariaDB accept thekeyword without one; name the constraint, or drop the CONSTRAINT keywordThe MySQL family accepts the form at table level before
PRIMARY KEY,UNIQUE,FOREIGN KEYandCHECK. On a column, MySQL accepts it only beforeCHECKand MariaDB only beforeREFERENCES. The engine answersERROR 1064to every other column spelling, and Ptah refuses it too. ACONSTRAINTin front ofKEY,INDEX,FULLTEXTorSPATIALis refused on every dialect, with a name or without one, because an index takes noCONSTRAINTkeyword. -
A named column constraint is read under
--dialect mysqlonly beforeCHECK, and under--dialect mariadbonly beforeREFERENCES. On a column, each engine takesCONSTRAINTbefore that one kind alone, with a name or without one. MySQL 8.4, MySQL 26.7 and MariaDB 11.8 answerERROR 1064to every other kind, and Ptah refuses it too:CREATE TABLE t (a INT CONSTRAINT uq UNIQUE);schema.sql:1:23: CONSTRAINT uq is followed by UNIQUE: on a column, mysqlaccepts CONSTRAINT with a name only before CHECK, and answers ERROR 1064(42000) to this; drop CONSTRAINT uqDrop the name, or declare the constraint at table level to keep it, as in
CONSTRAINT uq UNIQUE (a). PostgreSQL takes a name before every column constraint, so--dialect postgresreads a namedUNIQUE,PRIMARY KEY,CHECK,REFERENCESandNOT NULLon a column. -
A column-level
REFERENCESclause is refused under--dialect mysql. What MySQL builds from it depends on the server version: MySQL 8.4 accepts the syntax and builds nothing, while MySQL 9.7 and 26.7 build a foreign key and its index. Ptah reads a SQL file with its dialect alone: neither--server-versionnor the version of the database it connects to reaches the reader, so either reading would be wrong on one of those lines:CREATE TABLE child (a INT REFERENCES parents (id));schema.sql:1:27: a column-level REFERENCES clause: MySQL 8.4 builds nothing fromthe clause, while MySQL 9.7 and 26.7 build a foreign key and its index, andthe SQL file is read without the server version, so Ptah refuses the clauserather than guess which schema it declares; write a table-level FOREIGN KEYclause, which every MySQL line buildsWrite the relationship as a table-level
FOREIGN KEY (a) REFERENCES parents (id)instead. MariaDB enforces the column-level spelling and builds a backing index for it, so--dialect mariadbreads it unchanged. -
A
REFERENCESclause in the column definition ofALTER TABLE ... MODIFYis refused under--dialect mysqland--dialect mariadb, withCONSTRAINTin front or without it. MySQL 8.4, 9.7 and 26.7 and MariaDB 10.11, 11.8 and 12.3 each answerERROR 1064to it, whileADD COLUMNtakes the same clause. MariaDB takes noCONSTRAINTat all in aMODIFYcolumn definition, so Ptah refuses aCONSTRAINT ... CHECKthere too:ALTER TABLE c MODIFY a INT REFERENCES p (id);schema.sql:1:28: REFERENCES in ALTER TABLE ... MODIFY: mariadb takes noREFERENCES clause in a MODIFY column definition, and answers ERROR 1064(42000) to one; add the key with ALTER TABLE ... ADD FOREIGN KEYAdd the key with
ALTER TABLE c ADD FOREIGN KEY (a) REFERENCES p (id). -
ALTER TABLE ... ADD KEYadds a secondary index on MySQL and MariaDB, in every spelling the engines take:ADD KEY,ADD INDEX,ADD SPATIAL KEYandADD FULLTEXT KEY, with a key part’s prefix length and direction. AUNIQUEkey stays a constraint, because it is a uniqueness guarantee rather than an index alone. On ClickHouse,ADD INDEXdeclares a data-skipping index, as the same keyword does inside a table body. -
ALTER TABLE ... ADD PRIMARY KEYis read onto the table it names, with its prefix length and direction, exactly as the same key written inside theCREATE TABLEwould be. A statement naming a table the document does not declare is refused rather than dropped:ALTER TABLE nosuch ADD PRIMARY KEY (a);the schema model has no place for this statement: ALTER TABLE nosuch ADDPRIMARY KEY names a table this schema does not declareA primary key has nowhere to live without its table, and the document is not one any engine would run either. Declare the table first, in the same file or in an earlier one, or drop the statement. Every other
ALTER TABLEoperation is refused the same way. -
A routine whose body Ptah did not parse is refused rather than dropped. The parser understands the outer boundary of every
CREATE PROCEDUREandCREATE FUNCTIONit accepts; where it cannot model the body, it keeps the text — and text nothing read cannot be compared, so it has no place in a desired schema:CREATE PROCEDURE bump() SET @counter = @counter + 1;the schema model has no place for this statement: a mysql procedure whosebody was kept as text rather than parsed, so nothing here can compare it:CREATE PROCEDURE bump() SET @counter = @counter + 1Write the body in a form Ptah reads — a
BEGIN ... ENDblock, or aRETURN— or keep the routine out of the desired schema and manage it separately. Carried silently, the routine would be missing from the desired schema: a comparison against a database that has it reports no difference, and a migration against one that does not plans it out of existence. -
A constraint name on
NOT NULLis carried where the target persists it. The distinction is not whether the syntax parses: PostgreSQL 17 acceptsCONSTRAINT c_x NOT NULLand stores nothing, while PostgreSQL 18 records one row perNOT NULLinpg_constraintwithcontype = 'n', keyed to the column throughconkey, and can drop, add and rename it by name. MySQL and MariaDB answerERROR 1064 (42000)for the syntax outright, so--dialect mysqland--dialect mariadbrefuse it when the file is read. Elsewhere the name is gated on the target’s measured capability, and a target that cannot keep it refuses the declaration rather than silently dropping the name.
Next steps
Section titled “Next steps”- Combining SQL files with Go packages or other sources? Composite desired schema.
- Planning versioned migrations from this file? Generate migrations.
- Using Atlas-style commands end to end? Atlas compatibility overview.