# SQL schema Use plain SQL DDL files as Ptah's desired schema. Source: https://docs.ptah.run/v0.25.0/schema/sql/ Use local SQL DDL files as a desired schema. Ptah parses every statement and refuses unsupported DDL. ## Write a schema file Create `schema.sql`: ```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: ```sql CREATE SCHEMA extensions; CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions VERSION '1.3'; ``` Render that PostgreSQL-specific file with the PostgreSQL dialect: ```bash ptah schema render --schema-file extensions.sql --dialect postgres ``` Expected output includes the schema precondition before the extension: ```sql 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](../../atlas/schema-commands/#a-schema-with-nothing-in-it) 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. See [PostgreSQL defaults](../../databases/postgresql/#how-a-declaration-is-compared-with-what-the-server-stored). ## Render it ```bash ptah schema render --schema-file schema.sql --dialect sqlite ``` Expected output includes: ```sql 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`](../export/#sources) except `hcl`. That includes the two documentation targets, so [a Markdown or HTML reference](../document/) can be generated from this file. Path confinement is shared by every `--schema-file` source; see [Schema file paths](../../reference/native-commands/#schema-file-paths). For PostgreSQL application roles, see [Conditional role bootstrap](../postgres-role-bootstrap/). ## 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](../../databases/clickhouse/#row-policies) for examples and supported clauses. ## 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: ```sql CREATE TABLE t (id int PRIMARY KEY, a int); GO CREATE TRIGGER trg ON t FOR INSERT, UPDATE AS SET NOCOUNT ON; UPDATE t SET a = a WHERE 1 = 0; GO ``` The 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](../../databases/sqlserver/#triggers). ## 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. ```sql 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](#--dialect-decides-how-the-file-is-read-not-only-how-it-is-written). 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 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`: ```sql 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. 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 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: ```sql 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 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](../../databases/postgresql/#constraint-enforcement-and-the-match-type) and [MySQL](../../databases/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](../../databases/postgresql/#unvalidated-constraints). 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: ```sql 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`, `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. `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. These operations are read: - `ADD COLUMN`, `ADD PRIMARY KEY` and `ADD CONSTRAINT` (`UNIQUE`, `CHECK`, `FOREIGN KEY`); - on PostgreSQL, `ADD [CONSTRAINT name] NOT NULL column`, which does what `SET NOT NULL` does and keeps the name. `NOT VALID` and `NO INHERIT` after the column are refused, because the model has no field for them; - `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. ## 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: ```sql 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](../../databases/postgresql/#a-security-option-the-declaration-leaves-out) a security option the view holds on: write `security_invoker = false` for that. [View options and storage parameters](../../databases/postgresql/#view-options-and-storage-parameters) says how they are compared and changed, and which engines take them. ## 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: ```sql 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 `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: ```sql 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: ```sql 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. `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; - 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 PostgreSQL, CockroachDB and YugabyteDB schema files declare row-level security with the statements a migration would run: ```sql 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](../../databases/postgresql/#row-level-security). `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. ## 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: ```sql 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](../../databases/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. ## 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](../yaml/), [HCL](../hcl/), or [Go annotations](../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: ```sql 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: ```text 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 them ``` `PTAH_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](../../operate/troubleshooting/#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](../../reference/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 SEQUENCE` or `CREATE MATERIALIZED VIEW ... IF NOT EXISTS` is 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_pkey` for the primary key of `t`, `t_a_key` for a `UNIQUE` column `a`, and `t_id_seq` for the sequence of a `serial` or identity column `id`. An identity column's `SEQUENCE NAME` names its sequence instead. - Keys of one `CREATE TABLE` that are equal build one index, the primary key's first: `id int PRIMARY KEY UNIQUE` holds `t_pkey` alone. `ALTER TABLE` folds no keys. - A name is free again once the server drops it. A `DROP TABLE` drops the indexes, keys and column sequences of its table, and a `DROP COLUMN` the column's own key and sequence. A sequence a default only draws from stays. - A `CREATE OR REPLACE` view, function, procedure or trigger replaces the definition of the object the server finds, `public.v` and `v` alike. 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 Everything a desired schema is for is the same for every source and lives on [Work with a desired schema](../work-with-a-source/). 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: ```sql -- 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: ```bash ptah schema diff \ --from old.sql \ --to schema.sql \ --dev-url "sqlite://dev?mode=memory" ``` Expected output includes: ```sql 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 - A change that a SQLite dev database cannot express as an in-place `ALTER` is refused loudly rather than turned into an incomplete diff. For example, adding a `NOT NULL` column to an existing table exits with `sqlite: 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: ```text read SQL schema file schema.sql:7:12: expected '(' after table name: expected Operator, got Identifier ``` Lines 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 lint` reports 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 and `DELIMITER` lines 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 reads `line 7, column 12: ...`. A refusal of what a statement declares, such as an `ALTER TABLE ... ADD COLUMN` for a column the table already has, comes after the statement was read, and names where that statement begins. The examples below leave out the leading `read 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: ```sql CREATE TABLE t (a integer, CHECK (a > 0) NO INHERIT); ``` ```text schema.sql:1:42: unexpected NO after a table element: expected ',' or ')' ``` - A constraint name on `DEFAULT` is refused. Ptah keeps a name on `NOT NULL`, `CHECK`, `REFERENCES`, `UNIQUE` and `PRIMARY KEY` where 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: ```sql CREATE TABLE t (b INTEGER CONSTRAINT c_x DEFAULT 1); ``` ```text schema.sql:1:42: named column constraint "c_x": Ptah has nowhere to keep a name on DEFAULT, and does not read one back from a database, so write the constraint without a name; a name is kept on NOT NULL, CHECK, REFERENCES, UNIQUE and PRIMARY KEY ``` Write `b INTEGER DEFAULT 1` instead. 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 KEY` and its column list is read under `--dialect mysql` and `--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: ```sql CREATE TABLE child (a INT, FOREIGN KEY zidx (a) REFERENCES parents (id)); ``` ```text 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) and declare the index "zidx" separately ``` A name written beside an explicit `CONSTRAINT` symbol is accepted and ignored, because both engines record the symbol for the backing index too. - `CONSTRAINT` without a name is read under `--dialect mysql` and `--dialect mariadb`, where the name is optional, and refused elsewhere. On those engines `CONSTRAINT FOREIGN KEY (a) REFERENCES parents (id)` is the same unnamed key as `FOREIGN KEY (a) REFERENCES parents (id)`, and it takes the same name. Every other dialect requires the name: ```sql CREATE TABLE child (a INT, CONSTRAINT FOREIGN KEY (a) REFERENCES parents (id)); ``` ```text schema.sql:1:28: CONSTRAINT is followed by FOREIGN, not by a name: postgres requires a name after CONSTRAINT, and only MySQL and MariaDB accept the keyword without one; name the constraint, or drop the CONSTRAINT keyword ``` The MySQL family accepts the form at table level before `PRIMARY KEY`, `UNIQUE`, `FOREIGN KEY` and `CHECK`. On a column, MySQL accepts it only before `CHECK` and MariaDB only before `REFERENCES`. The engine answers `ERROR 1064` to every other column spelling, and Ptah refuses it too. A `CONSTRAINT` in front of `KEY`, `INDEX`, `FULLTEXT` or `SPATIAL` is refused on every dialect, with a name or without one, because an index takes no `CONSTRAINT` keyword. - A named column constraint is read under `--dialect mysql` only before `CHECK`, and under `--dialect mariadb` only before `REFERENCES`. On a column, each engine takes `CONSTRAINT` before that one kind alone, with a name or without one. MySQL 8.4, MySQL 26.7 and MariaDB 11.8 answer `ERROR 1064` to every other kind, and Ptah refuses it too: ```sql CREATE TABLE t (a INT CONSTRAINT uq UNIQUE); ``` ```text schema.sql:1:23: CONSTRAINT uq is followed by UNIQUE: on a column, mysql accepts CONSTRAINT with a name only before CHECK, and answers ERROR 1064 (42000) to this; drop CONSTRAINT uq ``` Drop 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 postgres` reads a named `UNIQUE`, `PRIMARY KEY`, `CHECK`, `REFERENCES` and `NOT NULL` on a column. - A column-level `REFERENCES` clause 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-version` nor the version of the database it connects to reaches the reader, so either reading would be wrong on one of those lines: ```sql CREATE TABLE child (a INT REFERENCES parents (id)); ``` ```text schema.sql:1:27: a column-level REFERENCES clause: MySQL 8.4 builds nothing from the clause, while MySQL 9.7 and 26.7 build a foreign key and its index, and the SQL file is read without the server version, so Ptah refuses the clause rather than guess which schema it declares; write a table-level FOREIGN KEY clause, which every MySQL line builds ``` Write 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 mariadb` reads it unchanged. - A `REFERENCES` clause in the column definition of `ALTER TABLE ... MODIFY` is refused under `--dialect mysql` and `--dialect mariadb`, with `CONSTRAINT` in front or without it. MySQL 8.4, 9.7 and 26.7 and MariaDB 10.11, 11.8 and 12.3 each answer `ERROR 1064` to it, while `ADD COLUMN` takes the same clause. MariaDB takes no `CONSTRAINT` at all in a `MODIFY` column definition, so Ptah refuses a `CONSTRAINT ... CHECK` there too: ```sql ALTER TABLE c MODIFY a INT REFERENCES p (id); ``` ```text schema.sql:1:28: REFERENCES in ALTER TABLE ... MODIFY: mariadb takes no REFERENCES clause in a MODIFY column definition, and answers ERROR 1064 (42000) to one; add the key with ALTER TABLE ... ADD FOREIGN KEY ``` Add the key with `ALTER TABLE c ADD FOREIGN KEY (a) REFERENCES p (id)`. - `ALTER TABLE ... ADD KEY` adds a secondary index on MySQL and MariaDB, in every spelling the engines take: `ADD KEY`, `ADD INDEX`, `ADD SPATIAL KEY` and `ADD FULLTEXT KEY`, with a key part's prefix length and direction. A `UNIQUE` key stays a constraint, because it is a uniqueness guarantee rather than an index alone. On ClickHouse, `ADD INDEX` declares a data-skipping index, as the same keyword does inside a table body. - `ALTER TABLE ... ADD PRIMARY KEY` is read onto the table it names, with its prefix length and direction, exactly as the same key written inside the `CREATE TABLE` would be. A statement naming a table the document does not declare is refused rather than dropped: ```sql ALTER TABLE nosuch ADD PRIMARY KEY (a); ``` ```text the schema model has no place for this statement: ALTER TABLE nosuch ADD PRIMARY KEY names a table this schema does not declare ``` A 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 TABLE` operation 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 PROCEDURE` and `CREATE FUNCTION` it 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: ```sql CREATE PROCEDURE bump() SET @counter = @counter + 1; ``` ```text the schema model has no place for this statement: a mysql procedure whose body was kept as text rather than parsed, so nothing here can compare it: CREATE PROCEDURE bump() SET @counter = @counter + 1 ``` Write the body in a form Ptah reads — a `BEGIN ... END` block, or a `RETURN` — 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 NULL` is carried where the target **persists** it. The distinction is not whether the syntax parses: PostgreSQL 17 accepts `CONSTRAINT c_x NOT NULL` and stores nothing, while PostgreSQL 18 records one row per `NOT NULL` in `pg_constraint` with `contype = 'n'`, keyed to the column through `conkey`, and can drop, add and rename it by name. MySQL and MariaDB answer `ERROR 1064 (42000)` for the syntax outright, so `--dialect mysql` and `--dialect mariadb` refuse 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 - Combining SQL files with Go packages or other sources? [Composite desired schema](../composite/). - Planning versioned migrations from this file? [Generate migrations](../../versioned/generate/). - Using Atlas-style commands end to end? [Atlas compatibility overview](../../atlas/overview/).