Reference › Language reference › Databases

Reference › Language reference

Databases

A database's tables, their keys and the relations between them.

On this page: database · table

How a system keeps its data (the database extension, docs/database.md): a database, its tables, and what a table's field says beyond a struct's.

database#

A database's layout (the database extension, docs/database.md): its tables, their keys and the relations between them. archec check resolves every relation in both directions; archec gen --target <provider> writes the schema and the typed access code, archec database migrate the migration from an earlier version; the simulator keeps it in memory, and test cases write to it and read it (do Shop.Customer.insert({ … }), expect Shop.Customer.count() == 1).

  • Keywords: database
  • Set: databases
  • Where: top level
  • Syntax: database Id ["Label"] { [provider p, q] table Name { … } }
Property Value Meaning
description text Prose description. $…$ and $$…$$ are LaTeX math; a ```latex block is kept as LaTeX.
provider refs The databases it is written for: each a provider plugin (sqlite, postgres, clickhouse, duckdb, mongodb, or one of your own — archec plugin list). What a provider does not enforce (a foreign key in ClickHouse) is reported; the model still checks it in the simulator.
options any A provider's own options for this database: options postgres { schema "shop" }. The provider declares them (option schema { on database }).
satisfies list of requirement names Requirements this element satisfies (emitted to traces.yaml).
database Shop "Shop" {
  provider sqlite
  table Customer {
    id:     int64  = autoincrement key
    email:  string unique
    orders: Order[]
  }
  table Order {
    id:       int64     = autoincrement key
    customer: Customer  on-delete cascade
    placed:   timestamp = now
    index customer, placed
  }
}

table#

A table of a database: its fields are its columns, written as a struct's (name: Type = default "Label" unit u range lo..hi). A field whose type is another table is a relation: customer: Customer holds the other table's key (a foreign key), orders: Order[] is the same relation seen from the other side, and tags: Tag[] via PostTag goes through a join table. A field whose type is a struct is kept inside the row (a JSON column, a sub-document). key, unique and index on the table name several fields.

  • Keywords: table
  • Set: databases
  • Where: inside database
  • Syntax: table Name ["Label"] { name: [optional] Type [= default] [key] [unique] [index] [on-delete …] [via …] key a, b unique a, b index a, b check <condition> }
Property Value Meaning
description text Prose description. $…$ and $$…$$ are LaTeX math; a ```latex block is kept as LaTeX.
key refs The table's key when it is several fields together: key order, product. A relation named here stands for the columns it holds.
unique refs Fields that together are unique: unique customer, code. May be written several times.
index refs An index over these fields, in this order: index customer, placed. May be written several times.
check any A condition every row keeps, over its fields, in the action language: check quantity > 0 and price >= 0. A CHECK constraint where the provider has them; the simulator refuses a row that breaks it. May be written several times.
options any A provider's own options for this table: options clickhouse { engine ReplacingMergeTree; order-by device, at }. The provider declares them (option engine { on table }), and archec check holds the values to what it declares.
satisfies list of requirement names Requirements this element satisfies (emitted to traces.yaml).
table Order "Order" {
  id:       int64    = autoincrement key
  customer: Customer on-delete cascade
  quantity: int32    range 1..100
  check quantity > 0
}