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
}