PostgreSQL#

[tool.model.postgres] emits a .sql file of creation DDL for the model: tables, the types and domains those tables need, and the constraints the model declares.

[tool.model.postgres]
out = "schema/catalog.sql"

This is a fourth output language, not a Go feature. It generates the schema and nothing else. The Go postgres feature generates code that reads and writes those tables, and the two are enabled separately — see Go and PostgreSQL.

The output targets PostgreSQL 14 or newer.

Which structs become tables#

Only those marked top_level. Every other struct in the model is either embedded in one of them or unused by the schema.

type: struct
display: Order
desc: A persisted order
top_level: true
fields:
  order_id:
    display: Order ID
    desc: The stable order identifier
    type: integer
    required: true
    primary_key: true

A persisted struct needs at least one field with primary_key: true, and every key field must also be required: true. Several marked fields form one composite primary key. A referenced struct can be a key too, when all of its flattened components are required. A list or map cannot be part of a key, because it is stored in its own table. No part of a key, including a field of a struct it holds, can be a secret or a password: a key names its row in the clear, and a password’s stored hash is salted afresh each time it is made. A key marker on a struct that is not top_level is an error.

Unique fields#

A field with unique: true gets a unique index, named <table>_<column>_key, so no two rows hold the same value in it:

login:
  display: Login
  desc: Who logs in.
  type: string
  required: true
  unique: true
CREATE UNIQUE INDEX account_login_key ON account (login);

The index is on the columns a key on the field would be: a datetime’s instant and zone, a rooted path’s resolved form. An optional field’s NULLs never conflict, so any number of rows may leave it out. Inside an embedded struct the field is unique across the table, as its column is; in a list or map item it is unique among the items of one row’s list or map, its index leading with the parent key. A violation is a unique violation, which the Go REST handlers answer with 409 Conflict.

Only a field stored in one column, or beside its zone or resolved path, can be unique. A list, map, file or password cannot be, nor can a struct or union field; mark the fields in it instead. A primary-key field is unique already, and marking it is an error.

Types#

Model

PostgreSQL

boolean

boolean

string, path

text (a path as text[])

integer, decimal

numeric

estimate

double precision

time

time without time zone

datetime

timestamp with time zone, and its zone in <column>_zone text; see Datetimes

date

date

isbn

isbn13, from the isn extension

uuid

uuid, with a CHECK of its version

url

text, the resolved absolute URL

nerd_font_glyph

text, the glyph’s full name

password

text, the Argon2id hash in the PHC string format

secret

bytea, the plaintext; see Passwords and secrets

file

four columns: the content as bytea, and its metadata; see Files

constant

the base type’s column, with a CHECK that it equals the constant

ref

the target’s key type, with a foreign key; see Foreign keys

list, map

a child table, one row per item; see Lists and maps

enum (string)

a PostgreSQL enum

enum (integer)

a named domain over numeric

An integer enum, and a generated integer union tag, become domains rather than enums because PostgreSQL enums are string-valued. Requiredness stays on the table column, not on the reusable domain.

Descriptions become comments. Every generated table, column, type, and domain carries its model desc as a COMMENT ON.

A field type that declares a required extension emits it once, as CREATE EXTENSION IF NOT EXISTS.

isbn is the one that does. The isn extension ships with PostgreSQL’s contrib modules, but creating it needs a role allowed to do so. Its isbn13 checks the check digit on input and compares a book’s ISBN-10 and ISBN-13 equal, so a UNIQUE or primary key on it catches one book entered two ways. It writes the ISBN back hyphenated, as 978-0-306-40615-7, and the generated Go reads that as the compact ISBN-13. An extension’s type has a different OID in each database, so the generated RegisterTypes looks isbn13 up by name on each connection.

The schema also needs pg_trgm when a declared query matches a field by LIKE or ILIKE, for the trigram index it gets. pg_trgm is a trusted extension, so the database’s owner can create it without being a superuser.

Passwords and secrets#

A password column holds the Argon2id hash, never the plaintext. The generated Go hashes a plaintext when it stores the row, and reads the hash back into a secret.Password whose Check tells whether a password matches.

A secret column holds the plaintext, which the generated Go reads straight into guarded memory. Being the value itself, it can be unique and compared, as an API key looked up by its value is. Anyone who can read the table can read it, so keep it from the disk by encrypting the database’s storage.

Neither column is searched.

Embedded structs#

A direct struct reference is flattened into the table, its path joined with _:

CREATE TABLE record (
    id numeric NOT NULL,
    address_city text,
    address_postal_code text,
    ...
);

Embedding creates no foreign key; a ref does. An optional embedded value is absent when all of its columns are NULL, which is why those columns are nullable even when the inner fields are required.

Unions#

A union becomes a tag column plus one prefixed column group per variant. Each variant of a stored union must hold a struct, whose fields become its group:

    pet_kind pettag,
    pet_cat_lives numeric,
    pet_dog_good boolean,

Check constraints require the active branch’s columns and keep every inactive branch NULL, so the tag and the data cannot disagree.

Lists and maps#

A list or map is not a column. Each one gets a child table, with one row per item. The items are real columns, so you can index them, make them UNIQUE, or point a foreign key at them.

CREATE TABLE record_labels (
    record_id numeric NOT NULL,
    labels_ordinal integer NOT NULL,
    value text NOT NULL,
    CONSTRAINT record_labels_pkey PRIMARY KEY (record_id, labels_ordinal),
    CONSTRAINT record_labels_parent_fkey FOREIGN KEY (
        record_id
    ) REFERENCES record (id) ON DELETE CASCADE ON UPDATE CASCADE
);

The child table’s name is the parent table’s name and the field’s path, joined with _. A list in an embedded struct, such as address.lines, is record_address_lines. Its columns are:

  1. The parent row’s key. The columns of the root table’s key get the root table’s name as a prefix (record_id), so an item field named id cannot collide with them.

  2. The item’s position. For a list, this is <path>_ordinal, the item’s index from 0. For a map, it is <path>_key, the map key. The key of a map must be stored in one column, so it cannot be a struct, a union, a list, or a map.

  3. The item. A scalar item is one column, value. A struct or union item is flattened into columns, in the same way as an embedded struct or a union in a table. Its checks become CHECK constraints on the child table.

The table’s primary key is the parent key plus the position. Its foreign key to the parent row cascades, so deleting a row deletes its items, and changing its key moves them.

Nesting. A list or map in an item gets a table of its own, one level down. That table’s key starts with every position above it. For a list of lists, the outer table holds only the positions (grid_ordinal), so an empty inner list still keeps its place. The inner table adds grid_values_ordinal and value.

Model

Tables

addresses: list<Address>, where Address.lines is a list

record_addresses, record_addresses_lines

grid: list<list<integer>>

record_grid, record_grid_values

bins: map<string, list<string>>

record_bins, record_bins_values

Length rules. A CHECK constraint cannot count the rows of another table. So a list or map that is required, or that has a min_length or max_length, gets a constraint trigger that counts its items. The trigger is on the child table and on its parent, and it is DEFERRABLE INITIALLY DEFERRED. Thus it runs at commit, after all of the items are written, and a violation is a check_violation (SQLSTATE 23514) that names the trigger:

ERROR:  record holds 1 items, violating record_labels_length

An empty list or map is the same as an absent one. Thus a minimum length only applies to a list that has items, and min_length: 1 has an effect only on a required list. A required list in an optional struct or a union branch is required only when that struct or branch is present.

Constraints#

Model constraints become named CHECK constraints on the table:

    CONSTRAINT record_id_min CHECK (id >= 1),
    CONSTRAINT record_address_city_min_len CHECK (
        (address_city IS NULL OR (char_length(address_city) >= 1))
    ),

A nullable column’s check is guarded with IS NULL OR, so absence is not a violation — requiredness is NOT NULL’s job, and the two are kept separate.

String lengths count Unicode code points, matching the other three languages.

A time or datetime window is user-interface metadata and is never a database constraint.

Paths#

A path column is text[], holding strings.Split of the text form. The leading empty element of an absolute path is what records that it is absolute, so nothing is escaped and no marker is invented:

path

stored

/a/b

{"","a","b"}

a/b

{"a","b"}

/

{"",""}

""

{""}

Ordering is why it is an array and not text. PostgreSQL compares arrays element-wise, so a directory sorts immediately before its own contents and a subtree stays contiguous. Plain text compares bytes, and a sibling named a-1 or a b sorts between /d/a and its child /d/a/z, because - and the space are below /.

The column pins COLLATE "C" so that ordering is byte-wise. Under a locale-aware collation punctuation is ignored at the primary level and the guarantee would not hold.

Querying one directory’s subtree is a range scan over an ordered index; in Go, Path.DescendantRange builds the bounds. See Path.

Rooted paths#

A path field with a root gets a second column, <column>_resolved, beside the one holding the value as written. It holds the absolute path the value resolves to in its root, as if chrooted into it: always starting with /, with relative_to applied and . and .. gone. So a file of a.epub relative to /volumes stores {"a.epub"} and {"","volumes","a.epub"}.

The generated Go code fills it on every write, by calling the root’s opener and resolving the value there, so a row cannot be written while its root cannot be opened. An opener that takes a context.Context gets the one the operation was called with. It is never read back: the value comes from the written column. A field’s resolved column is recomputed whenever its row is written, so changing the field a path is relative to and saving the row updates it. When a rooted key resolves to a different path after the filesystem changes, reading the row and writing it again moves the row to the new key (see Stored keys).

When the field is the primary key, the key is on the resolved column. Two spellings of one file, such as a.epub and covers/../a.epub, are then one key, and a second insert of the same file fails. Get<Name> and Get<Name>ForUpdate take the resolved path, not the value as written, and so does a ref to the struct: its column references the resolved column. In a struct-valued key, the rooted paths in the struct that Get takes are also resolved paths.

Every rooted path gets the two columns, wherever it is stored:

  • A path field of a struct stored in a table, including one flattened into it.

  • A list item or map value: the child table has value and value_resolved.

  • A map key: the child table has <path>_key and <path>_key_resolved, and its primary key is on <path>_key_resolved. Two keys of one map that name the same file, such as a.epub and ../volumes/a.epub, are two keys in memory, but the second one fails the write. The tables of the map’s items reference the resolved column. The map is read back with its keys as written, in the order of their resolved paths.

A list or map resolves its paths through the field that holds it, so an item or key follows the field’s relative_to.

A path with no root is a pure path. It has no filesystem to resolve in, so it gets no resolved column, and no resolution or clean-up happens: . and .. segments are kept, and /a/./b and /a/b are different values. As a primary key, such a path is keyed on the value as written, so two spellings of one path are two rows, and a ref to it must hold the same spelling.

Files#

A file field avatar is four columns:

Column

Type

Holds

avatar

bytea

The content

avatar_type

text

The media type it was sniffed as

avatar_size

bigint

Its length in bytes

avatar_sha256

bytea

Its SHA-256

All four are NULL when the field is unset, and a CHECK keeps them set together. Others keep the size the content’s length (octet_length), the digest 32 bytes, and the size and type within the field’s max_size and content_types.

A large value is stored out of line by TOAST, so a row with a file is no slower to read for it, as long as the content is left out. The generated Go reads and writes the row without the content column: the metadata is part of the row, and the content is read only by Open<Field> and the file’s GET, and written only by Set<Field> and its POST. An update of the row keeps the content while the digest it writes is the one stored, and sets it to NULL otherwise. So clearing the metadata clears the content, and a write that gives the file another digest, without its content, fails the check rather than leaving the content unmatched.

Do not keep large media here: PostgreSQL reads a bytea whole. A path names a file kept outside the database.

Datetimes#

A timestamp with time zone holds only the instant: PostgreSQL converts it to UTC and does not keep the zone it was written in. So a datetime gets a second column, <column>_zone, holding the name of its zone as the wire form writes it in brackets: an IANA name such as America/New_York, UTC, or an offset such as -04:00. The generated Go code writes it from the value’s time.Location and reads the value back in it.

The zone column is NOT NULL when the instant is, and a CHECK named <column>_zone_paired holds it NULL exactly when the instant is NULL.

A datetime key is its instant and its zone. A primary key on a datetime is on both columns, so 2026-10-06T21:00:00-04:00[America/New_York] and the same instant in Europe/London are two rows, and Get<Name> finds a row only by the zone it was stored in. A map keyed by datetimes is keyed the same way, though in memory an mdl.Map holds one key per instant, so it never writes two keys that differ only by zone. Its keys are read back in instant order.

A ref to a struct keyed by a datetime stores the key the same way, in <ref> and <ref>_zone, and its foreign key is on the two together. A reference therefore names the zone its target is stored in, and a target moved to another zone moves the references declared with on_update: cascade with it.

Every datetime gets the two columns, wherever it is stored: a field, a list item or map value (value and value_zone), and a map key (<map>_key and <map>_key_zone). So does every such reference. A child table inherits a datetime key as both columns, such as <table>_<column> and <table>_<column>_zone.

Upgrading a table from before zone columns#

The generated DDL only creates tables. A table created before datetimes kept their zone has no zone column, so add one for each datetime column, give the existing rows UTC, and add the check:

ALTER TABLE meeting ADD COLUMN starts_at_zone text;
UPDATE meeting SET starts_at_zone = 'UTC' WHERE starts_at IS NOT NULL;
ALTER TABLE meeting ADD CONSTRAINT meeting_starts_at_zone_paired
    CHECK ((starts_at IS NULL) = (starts_at_zone IS NULL));

Make the column NOT NULL too when the datetime is required. Then create model_datetime_words from the new DDL and upgrade the table’s search (see Upgrading a table from before item search), since it now searches its datetimes.

A datetime key, and a reference to one, needs more: the zone column joins the key. Drop the foreign keys that reference the table, replace its primary key with one on both columns, add the zone column to each table that references it or inherits its key, filled with UTC, and add the foreign keys back on both columns, as the new DDL declares them.

What is not supported#

Generation fails with an error, rather than emitting DDL that would accept the first row and reject the next:

  • Recursive reference cycles in a persisted shape.

  • A top_level struct as a list or map item. It is a table of its own. Store its key, and a reference to it, in place of the struct.

  • A list or map in a primary key.

  • A map key that is not one column: a struct, a union, a list, or a map.

Identifiers, flattened column names included, must fit PostgreSQL’s 63-byte limit and may not collide.