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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
four columns: the content as |
|
the base type’s column, with a |
|
the target’s key type, with a foreign key; see Foreign keys |
|
a child table, one row per item; see Lists and maps |
|
a PostgreSQL enum |
|
a named domain over |
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:
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 namedidcannot collide with them.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.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 becomeCHECKconstraints 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 |
|---|---|
|
|
|
|
|
|
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 |
|---|---|
|
|
|
|
|
|
|
|
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
valueandvalue_resolved.A map key: the child table has
<path>_keyand<path>_key_resolved, and its primary key is on<path>_key_resolved. Two keys of one map that name the same file, such asa.epuband../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 |
|---|---|---|
|
|
The content |
|
|
The media type it was sniffed as |
|
|
Its length in bytes |
|
|
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.
Full-text search#
Every top_level struct’s table is searched by the text of its own columns
and of its lists’ and maps’ items:
field type |
searched as |
|---|---|
|
the stored text; a glyph is its full name, such as |
|
both the hyphenated ISBN-13 and its compact form |
|
its lower-case hyphenated hex |
|
its segments, as words |
|
the ISO date, the month’s name and abbreviation, the day, the year, and the weekday’s name and abbreviation |
|
the words of the date it falls on in its own zone, as a |
an enum |
its value’s |
Fields of embedded structs and union variants are searched too, since they
are columns of the table. So are the items of its lists and maps, at any
depth, and a map’s keys, each as its type is searched. Numbers, times,
booleans, constants, and refs are not searched, and neither is a rooted
path’s resolved column, which would add the root’s own directory to every
row, nor a datetime’s zone column. Nor is a field that sets a read perm,
or anything it holds (see Field perms): which rows a search
matches would tell a request what the field holds. A table with none of these
fields, in itself or its items, gets no search.
searchable: false leaves a field out of the search while it stays readable
and queryable, for text that only makes searches noisy. On a struct, union,
list or map field it leaves out everything the field holds:
fields:
description:
display: Description
desc: The long description shown on the card.
type: string
searchable: false
The text is stored in the table’s model_search column, a tsvector with a
GIN index named <table>_search_index. Triggers keep it current, whoever
writes the row:
<table>_search, before every insert and update of the row, computes it from the row and the items it holds.<child>_search_insert,_updateand_delete, after each statement on a child table holding searched text, touch the rows whose items changed, so the first trigger computes their text again.
Writing an item updates its row, so the row’s own UPDATE triggers fire for
it too. Every word of a query must match, but it may match in the row or in
any of its items: emma pencil finds a shelf holding the book Emma and a
box of pencils.
The search uses one text search configuration, simple unless
[tool.model.postgres] sets search-config (see Configuration). simple
lower-cases words and nothing else, which suits names, ISBNs and paths. A
language’s configuration, such as english, also stems words and drops its
stop words, so running finds run. The configuration is part of the stored
text, so a query must use the same one; the generated Go code does (see
Operations). To query it by hand, match model_search:
SELECT * FROM record
WHERE model_search @@ websearch_to_tsquery('simple'::regconfig, 'emma');
These functions are generated for the triggers:
model_path_words(text[])joins a path’s segments with spaces.model_date_words(date)spells a date out, so2024-02-29,february 29,feb 2024andthursdayall find February 29, 2024. The names are always English, whatever the search configuration orlc_time.model_datetime_words(timestamp with time zone, text)spells out the date an instant falls on in a zone, withmodel_date_words. A New York meeting at 21:00 on October 6 is found byoctober 6, though in UTC it is already the 7th. PostgreSQL must know the zone’s name: one its time zone database lacks fails the write.<enum>_display(<enum>)maps each value of an enum to its display. It is generated from the model, so after you change a display, replace the function from the new DDL and recompute the tables that search the enum withUPDATE <table> SET model_search = model_search. Until then the old display is what they hold.
Upgrading a table from before item search#
A table created before its items were searched has an index on an
expression, not a model_search column. Drop the index, add the column,
create the table’s search functions and triggers from the new DDL, fill the
column, and index it:
DROP INDEX record_search_index;
ALTER TABLE record ADD COLUMN model_search tsvector NOT NULL DEFAULT '';
-- CREATE FUNCTION record_search() ... and every CREATE TRIGGER for it,
-- and each child table's <child>_search function and triggers, as the
-- new DDL declares them.
UPDATE record SET model_search = model_search;
CREATE INDEX record_search_index ON record USING gin (model_search);
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_levelstruct 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.