Databases
Extension — not covered by the 1.0 stability promise (
database; the core)
A database block says how a system keeps its data: the tables, their keys,
and the relations between them. You write a table as you write a struct.
archec reads the same block four ways. archec check resolves every relation
from both sides and says what does not hold. archec gen --target sqlite
(or postgres, clickhouse, duckdb, mongodb) writes the schema and typed
access code. archec database migrate writes the migration from an earlier
version. The simulator keeps the database in memory, so a test case writes
rows, reads them, and proves the rules about the data.
This page builds a small shop: customers, products, orders and their lines.
The finished model is examples/shop/main.arche.
What you need#
Writing, checking, simulating and generating need nothing more than archec. No database is installed, and none is needed to run the tests.
The files archec writes are for the database you name. To run them you need
that database and its driver: sqlite3 comes with Python, the others are
pip install "psycopg[binary]", duckdb, clickhouse-connect or pymongo.
1. A table#
database Store "Shop database" {
table Customer "Customer" {
id: int64 = autoincrement key
email: string unique
name: string "Name"
joined: timestamp = now
}
}| You write | Meaning |
|---|---|
database Store |
A database. Code names it: Store.Customer.find(1). |
table Customer |
A table. Its fields are its columns. |
id: int64 |
A column, written as a struct's field: name: Type = default "Label" unit u range lo..hi. |
key |
This field is the table's key (its primary key). |
unique |
No two rows have the same value. |
index |
An index on the field, for the lookups by it. |
= autoincrement |
The database gives the next whole number. |
= now |
The time the row is written. The field is a timestamp. |
= uuid |
A new identifier. The field is a string. |
= 1, = "new", = Open |
Any other default is a value. |
optional string |
The field may be empty. Any other field always has a value. |
A column's type is a data type (bool, int8 to int64, uint8 to
uint64, float32, float64, string, bytes, timestamp, duration), a
type of the model, an enum, or a struct. A list is written string[].
For a key of several fields, write one line on the table. unique and index
take several fields the same way, and may be written more than once:
database Store {
table Stock {
warehouse: string
sku: string
count: int32 = 0
key warehouse, sku
index sku
}
}A table has one key. A table with none is an error, with a quick fix that
adds id: int64 = autoincrement key.
2. Relations#
A field whose type is another table is a relation.
enum OrderStatus { Open, Paid, Shipped }
database Store {
table Customer {
id: int64 = autoincrement key
orders: Order[]
}
table Order {
id: int64 = autoincrement key
customer: Customer on-delete cascade
status: OrderStatus = Open
}
}customer: Customer holds the customer's key. The table gets the column
customer_id: the field's name, then the name of the key it points at. This
is a foreign key.
orders: Order[] is the same relation seen from the customer. It holds no
column. You may leave it out: it is there so that code can ask for a
customer's orders.
| You write | Relation |
|---|---|
customer: Customer on Order |
Many orders to one customer. |
customer: optional Customer |
The same, and an order may have none. |
orders: Order[] on Customer |
The other side of it. |
user: User unique on Profile |
One to one: at most one profile per user. |
profile: optional Profile via user on User |
The other side of a one-to-one. |
tags: Tag[] with a join table |
Many to many. |
on-delete says what happens to the order when its customer is deleted:
restrict |
The delete is refused while an order points at the customer. This is the default. |
cascade |
The order is deleted too. |
set-null |
The order's customer is emptied. The field must be optional. |
no-action |
As restrict, checked at the end of the transaction where the database can. |
Which field is the other side#
archec finds the other side by itself when one field of the other table points
back. When two do, say which with via:
database Blog {
table User {
id: int64 = autoincrement key
written: Post[] via author
reviewed: Post[] via reviewer
}
table Post {
id: int64 = autoincrement key
author: User
reviewer: optional User on-delete set-null
}
}Without via, the error says so, and its quick fix writes the first one:
main.arche:4:5 error Post points at User 2 times (author, reviewer): say which one written is the other side of — `via author` [database-relation]
Many to many#
Many on both sides goes through a join table: a table with a field of each. Its key is usually the two together.
database Store {
table Product {
sku: string key
orders: Order[]
}
table Order {
id: int64 = autoincrement key
products: Product[] via OrderLine
}
table OrderLine {
order: Order on-delete cascade
product: Product
quantity: int32 = 1 range 1..100
key order, product
}
}key order, product names the relations: the key is the columns they hold,
order_id and product_sku. When one table joins the two, via may be left
out, as on orders: Order[].
A struct inside a row#
A field whose type is a struct is kept inside the row: a JSON column in
PostgreSQL, a STRUCT in DuckDB, a Tuple in ClickHouse, a sub-document in
MongoDB. A list of them is written Address[].
struct Address {
street: string
city: string
}
database Store {
table Customer {
id: int64 = autoincrement key
address: optional Address
}
}So the model chooses: a field of a struct's type is embedded, a field of a table's type is a reference to another row.
3. Rules on the data#
A field's range is a rule: the database refuses a value outside it. A
check line is a condition every row keeps, over its fields, written in the
action language:
database Store {
table Order {
id: int64 = autoincrement key
total: float64 unit EUR range 0..100000
discount: float64 = 0.0
check discount <= total
}
}A rule can say which requirement it satisfies. Write satisfies on the field,
on the table, or on the database:
requirement REQ-EMAIL "One account per email" {
text "Two customers shall not have the same email address."
}
database Store {
table Customer {
id: int64 = autoincrement key
email: string unique satisfies REQ-EMAIL
}
}archec status then shows the rule beside the requirement, with what proved
it (section 7).
4. Providers#
A provider is the database the layout is written for. A database names one or several:
database Store {
provider sqlite, postgres
table Customer {
id: int64 = autoincrement key
}
}archec ships five, each a plugin in lib/plugins/:
| Provider | Enforces | Does not | Writes |
|---|---|---|---|
sqlite |
keys, unique, foreign keys with on-delete, checks, required fields, autoincrement |
lists and structs (kept as JSON text); a constraint changed in place | schema.sql, Python (sqlite3), C (the sqlite3 C API) |
postgres |
all of it | schema.sql, Python (psycopg) |
|
duckdb |
keys, unique, foreign keys, checks, required fields, sequences | on-delete cascade and set-null; a constraint added to a table that exists |
schema.sql, Python (duckdb), TypeScript (@duckdb/node-api) |
clickhouse |
required fields, checks on the rows written | keys, unique, foreign keys | schema.sql, Python (clickhouse-connect) |
mongodb |
the key (_id), unique (a unique index), required fields, ranges and checks (the validator) |
references to another collection; transactions need a replica set | schema.js (mongosh), TypeScript (mongodb), Python (pymongo) |
archec database providers main.arche lists the providers the project sees,
what each enforces, and the options it takes.
What a provider does not enforce#
The model says a rule once. A provider that cannot keep it is told, where the rule is written:
main.arche:26:13 warning The relation Reading.device → Device: ClickHouse does not enforce it (it has no `foreign-keys`) — the model checks it in the simulator, the database will not [database-not-enforced]
Nothing is written into the schema for a rule the provider does not have. The simulator still checks it, and the evidence says who keeps the rule (section 7).
A provider's own options#
What only one database has is written in options <provider> { … }, on the
database, a table or a field. The provider declares each option, and
archec check holds the value to it.
database Telemetry {
provider clickhouse
table Reading {
device: string
at: timestamp = now options clickhouse { codec "Delta, ZSTD(1)" }
value: float64 options clickhouse { type "Decimal(12, 3)" }
key device, at
options clickhouse {
engine ReplacingMergeTree
engine-args at
partition-by "toYYYYMM(at)"
ttl "at + INTERVAL 90 DAY"
}
}
}main.arche:10:7 error 'engin' is not an option clickhouse takes on a table (engine, engine-args, order-by, …). Did you mean 'engine'? [database-option]
main.arche:10:14 error engine: "FastTree" is not one of "MergeTree", "ReplacingMergeTree", … [database-option]
Every provider takes type on a field: the column's type in that database,
when it is not the one the model's type maps to (Decimal(18, 4),
LowCardinality(String), HUGEINT, objectId).
ClickHouse. A table takes engine (MergeTree and its family, Log,
Memory), engine-args, order-by (the sorting key: the table's key when
not said), primary-key, partition-by, sample-by, ttl, settings and
materialized-view (a SELECT that fills the table: written as
CREATE MATERIALIZED VIEW <table>_mv TO <table>). A field takes type,
low-cardinality, enum16, codec, default, materialized, alias and
ttl. An optional field is Nullable, a list is Array, an enum is Enum8,
a struct is a named Tuple. options clickhouse { cluster "main" } on the
database writes ON CLUSTER.
DuckDB. Indexes are ART indexes: CREATE INDEX is written for each
index, but DuckDB recommends them for point lookups only, since its scans are
fast without one. A list is T[], a struct is a STRUCT, an enum is a
CREATE TYPE … AS ENUM, = autoincrement is a sequence.
MongoDB. A table is a collection. A key of one field is the document's
_id; options mongodb { object-id } on it makes it an ObjectId, which the
driver creates. A key of several fields is a unique index. A table takes
collection, additional-properties, validation-level,
validation-action, text and geo (a text index, 2dsphere indexes). A
field takes type, object-id, expire-after (a TTL index, in seconds) and
partial (its index covers only the documents that have the field). The
schema is a mongosh script, schema.js: one db.createCollection with its
$jsonSchema validator per table, then the createIndex calls.
5. The schema and the access code#
archec gen main.arche --target sqlite -o generated
generated/sqlite/Store/schema.sql
generated/sqlite/Store/store_db.py
generated/sqlite/Store/store_db.h
generated/sqlite/Store/store_db.c
schema.sql creates the tables, those another points at first:
CREATE TABLE "Order" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"customer_id" INTEGER NOT NULL,
"status" TEXT NOT NULL DEFAULT 'Open',
"total" REAL NOT NULL DEFAULT 0,
"placed" TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
CHECK ("status" IN ('Open', 'Paid', 'Shipped')),
CONSTRAINT "Order_check_1" CHECK ("total" >= 0),
FOREIGN KEY ("customer_id") REFERENCES "Customer" ("id") ON DELETE CASCADE
);
CREATE INDEX "Order_customer_id_placed_idx" ON "Order" ("customer_id", "placed");
The access code has a type per table and the same lookups in every language:
insert(row) |
A new row. A field left out takes its default. |
find(key…) |
The row with this key, or nothing. |
by_<field>(value) |
For each unique field: the row. For each index: the rows. |
where(filter), all(), count(filter) |
The rows whose fields equal the filter's. |
update(key…, changes) |
Changes the row with this key. |
delete(key…) |
Deletes the row with this key. |
<relation>(row) |
Follows a relation: customer(order), orders(customer), products(order). |
In Python:
import sqlite3
from store_db import Store
conn = sqlite3.connect("store.sqlite3")
conn.execute("PRAGMA foreign_keys = ON")
db = Store(conn)
ann = db.Customer.insert(email="ann@example.org", name="Ann")
order = db.Order.insert(customer_id=ann.id, total=79.0)
db.Customer.by_email("ann@example.org").id # 1
db.Order.customer(order).name # "Ann"
[o.total for o in db.Customer.orders(ann)] # [79.0]
In C, a row is a struct, and a list is given one row at a time:
sqlite3 *db;
store_open("store.sqlite3", &db);
store_customer_t ann = {0}, saved;
ann.email = "ann@example.org";
ann.name = "Ann";
store_customer_insert(db, &ann, &saved); /* 1, or -SQLITE_CONSTRAINT when refused */
store_customer_t found;
if (store_customer_by_email(db, "ann@example.org", &found) == 1) store_customer_free(&found);
A field that may be absent (optional, or with a default) has a has_<field>
beside it. Text a row holds is the row's own: store_customer_free gives it
back.
The generated code needs only the database's own driver. Nothing of it is built when archec is built or tested.
6. Migrations#
A migration is written from what changed between two versions of the model.
--since names the earlier one:
--since |
The earlier version |
|---|---|
v1.0 |
The baseline baselines/v1.0.json (archec baseline main.arche --name v1.0 keeps each database's layout). |
main~3, v0.4.0 |
A git revision: the project's folder as it was. |
old/main.arche |
Another file. |
archec database diff main.arche --since v1.0
database Store since baseline v1.0
+ Customer.phone is new (string, optional)
✗ Order.note is new (string) destructive — note is required and has no default: the rows there are have no value for it
✗ Order: customer_id, placed now unique destructive rebuilds the table — a unique constraint cannot be changed in place in sqlite: the table is made again and its rows copied
archec database migrate main.arche --since v1.0 --name add-phone -o generated
Each provider of the database writes its own file,
generated/<provider>/<Database>/migrations/add-phone.sql.
A destructive change is never applied unasked. A dropped table or column,
a type made narrower, a field newly required, a constraint the rows there may
break: each is written as a comment, marked DESTRUCTIVE with the reason, and
listed by the command. Read it, then run it yourself, or migrate again with
--allow-destructive to have it written as statements.
-- Order.total: float64 → float32
-- DESTRUCTIVE: float64 → float32 is narrower or another kind: values there may not fit. Not applied: read it, then run it yourself (or migrate with --allow-destructive).
-- ALTER TABLE "Order" ALTER COLUMN "total" TYPE REAL USING "total"::REAL;
A change that cannot be made in place rebuilds the table. SQLite and DuckDB
cannot add a constraint to a table that exists; ClickHouse cannot change an
engine, a sorting key or a partition. The migration then makes the table
again and copies its rows, under a REBUILD comment. For ClickHouse the
rebuild is always left as comments: it copies every row.
MongoDB. The migration is a mongosh script: collMod with the new
validator, then the indexes dropped and created. A change the documents there
must follow comes first, as a data step: an updateMany skeleton for a field
newly required or a type made narrower, $unset for a dropped field, and
$rename offered when a field was dropped and another of the same type added.
Each is marked DATA and left as a comment until you have written its value.
archec database migrate --check writes nothing and exits 1 when something
changed: for a pipeline that wants every change to come with its migration.
archec does not see a rename. A field renamed is one dropped and one added; the migration says so beside them, with the statement that renames instead.
7. Test cases on the simulator's database#
The simulator keeps each database in memory. It starts empty for every case. A case writes rows and reads them:
test-plan TP-SHOP "The shop's data" {
case TC-EMAIL "Two customers cannot share an email" {
verifies REQ-EMAIL
at 0ms do Store.Customer.insert({ email: "ann@example.org", name: "Ann" })
at 1ms do Store.Customer.insert({ email: "ann@example.org", name: "Another Ann" })
at 2ms expect Store.Customer.count() == 1
at 2ms expect Store.refused("unique") == 1
at 2ms expect Store.Customer.by_email("ann@example.org").name == "Ann"
}
}
A line names the database as code does: <Database>.<Table>.<what>(…), then a
field of what comes back. A do is one write: insert, update or delete.
An expect may mix what it reads of a database with the parts' variables:
expect Store.Order.count() == 1 and till.placed == 1.
Every write is checked against the layout: the key, each unique, each
relation, each check and range, each required field, each value's type.
A write that breaks a rule is refused. It is recorded, and the case goes
on. Store.refused() counts the writes refused so far: all of them, those of
one rule ("key", "unique", "foreign-key", "check", "required",
"type"), or those on one thing ("Customer.email").
A line is held to the layout when the model is checked:
main.arche:152:38 error Customer has no field 'mail' (id, email, name, address, joined). Did you mean 'email'? [database-line]
archec sim main.arche --test # the cases, on the simulator
archec test main.arche # the same, recorded as evidence
Data rules: 3 checked on the simulator's database
✓ Store: unique Customer.email → REQ-EMAIL — held on 6 write(s), 1 of them refused by it (…); checked by the model, and enforced by SQLite, PostgreSQL
archec test records two kinds of evidence. Each case is a record, as any
case is. Each rule that says satisfies is a record too: how many writes it
was checked on, how many it refused, and who keeps it outside the simulator.
For a provider that does not enforce the rule, the record says so:
checked by the model; not enforced by ClickHouse: there, only the model keeps it
archec status shows both beside the requirement. A table that changes makes
them out of date.
In a handler#
A handler names the database the same way. In TypeScript, the default:
component Checkout "Checkout" {
in orders: data receives PLACE_ORDER {
on-receive ```ts
const product = Store.Product.find(msg.sku)
if (!product) return
const order = Store.Order.insert({ customer_id: msg.customer, total: product.price * msg.quantity })
Store.OrderLine.insert({ order_id: order.id, product_sku: product.sku, quantity: msg.quantity })
```
}
}A row is as the database keeps it: a relation that holds a key is its column
(customer_id), and is followed by its name (Store.Order.customer(order)).
In a handler a refused write throws, as a driver does.
archec check holds a body to the layout: the table, what is called of it,
and the fields of a row written out in the call. archec stubs writes the
types into arche.d.ts, so the editor and tsc check the values too:
Store.Order.insert({ customer_id: 1, totl: 12 })
// ~~~~ 'totl' does not exist in type 'NewOrder'
Why a write was refused#
archec why main.arche --test TC-EMAIL "Store.Customer refused"
Store.Customer.insert refused: a Customer with email = ann@example.org is there already at 1.000 ms
← the rule: unique Customer.email main.arche:46
satisfies REQ-EMAIL "One account per email"
checked by the model, and enforced by SQLite, PostgreSQL
← the row in the way: Customer [1], written at 0.000 ms
asked by the test main.arche:155
← asked by the test main.arche:156
archec why … "Store.Order delete" says who asked for a write: the test, a
part handling a message, or a delete that cascaded, with the relation that
says so. archec why-not … "Store.Order insert" says why a write never
happened: it was tried and refused, with the rule, or nothing asked for it,
with the code that would have and whether that code ran.
Writing a provider#
A provider is a generator plugin (plugins,
generators) that says provides database. It is found and
approved as any plugin: in plugins/<name>/ beside the model, in a library of
kind plugin the project uses (its script runs once approved, with
archec plugin approve), in ARCHE_PLUGINS, or built in. archec has no line
about any one database: the five it ships are written this way, in
lib/plugins/.
A provider is two files. plugins/memdb/plugin.arche declares it:
generator memdb "MemDB" {
provides database
script "provider.ts"
capabilities primary-key, required
types { int64 "i64"; string "text"; bool "bit"; float64 "f64" }
option shard "Which shard the table is kept on" { on table; type int; rebuild }
option compress "How the column is packed" { on field; type string; values none, lz4 }
}
| You write | Meaning |
|---|---|
provides database |
The generator is a database provider. A database may name it. A number after it is the version of this contract it reads (1). |
capabilities |
What it enforces or holds: primary-key, unique, foreign-keys, on-delete, check, required, auto-increment, transactions, indexes, lists, embedded, alter-constraints. What a model uses and the provider does not list is reported (database-not-enforced). |
types { … } |
The model's types as it names them. A column of a type it does not name is an error, unless the field gives its own with the option type. |
option … { on table } |
An option written on a database, a table or a field, in options memdb { … }. Its type, values and default are checked as any generator option's. |
rebuild |
The option cannot change in place: a migration that changes it is flagged as rebuilding the table. |
archec reads these declarations to check a model. It does not run the provider's code for that.
plugins/memdb/provider.ts writes the files:
export default function generate(model, ctx) {
const files = {};
const types = model.provider.types;
for (const db of model.databases.filter((d) => d.providers.includes('memdb'))) {
if (model.migration) {
// `archec database migrate`: what changed since db.since
const lines = db.changes.map((c) => `${c.rebuild ? 'REBUILD' : c.destructive ? 'DANGER' : 'DO'} ${c.kind} ${c.table ?? ''}`);
for (const c of db.changes) if (c.destructive) ctx.warn(db.tables.find((t) => t.id === c.table) ?? db, c.text);
files[`${db.id}/migrations/${model.migration.name}.txt`] = lines.join('\n') + '\n';
continue;
}
// `archec gen --target memdb`: the schema
const out = [];
for (const t of db.tables) {
out.push(`table ${t.id} key(${t.key.join(',')})`);
for (const c of t.columns) out.push(` ${c.id} ${types[c.type] ?? '?'}${c.optional ? '?' : ''}`);
}
files[`${db.id}.memdb`] = out.join('\n') + '\n';
}
return { files };
}
The script reads the generator model. A model with a database has three more keys:
| Key | What it holds |
|---|---|
databases |
Each database, resolved: id, providers, options, enums, and its tables. A table has its columns (id, type, optional, list, default, key, unique, range, references for a foreign key's column, enum and values, struct and fields, options), its key, uniques, indexes, relations (to, kind, holds, columns, references, onDelete, inverse, through) and checks (text, sql, and tree for a database that is not SQL). |
provider |
What the provider declared: id, capabilities, types, options. |
migration |
Only for archec database migrate: name, since, allowDestructive. Each database then has previous (the earlier layout) and changes. |
A change has a kind (add-table, drop-table, add-column, drop-column,
alter-column, change-key, add-unique, drop-unique, add-index,
drop-index, add-relation, drop-relation, alter-relation, add-check,
drop-check, change-option, add-enum-value, drop-enum-value), the
table and column it is on, from and to, a text that says it, and two
flags archec sets: destructive (it loses data, or the rows there may not
fit) and rebuild (it cannot be done in place: an option marked rebuild, or
a constraint where the provider has no alter-constraints). What the script
reports with ctx.warn is listed by the command.
archec generator model main.arche prints the model, and
schemas/generator-model.v1.json describes it.
What is checked#
| Code | |
|---|---|
database-key |
a table with no key, two fields marked key, a key that is optional, a list or a struct |
database-relation |
a relation that does not resolve: nothing points back, two fields do and no via says which, on-delete on the side that holds no column, set-null on a field that is not optional |
database-field |
a key, unique or index line that names something with no column |
database-type |
a column of a type a database does not hold, or that a provider has no name for |
database-default |
= now on something other than a timestamp, = autoincrement on something other than an integer, = uuid on something other than a string, a value of another type |
database-check, database-range |
a check that cannot be read; a range on a field that is not a number |
database-duplicate, database-structure, database-name, database-index |
two things with one name; something in a database that is not a table; a database named as a type; an index written twice |
database-provider |
a provider the project does not see, or a provider's own declaration that cannot be read |
database-option |
an option the provider does not declare there, or a value it does not take |
database-not-enforced |
a rule a provider of the database does not enforce (a warning) |
database-line |
a test line, or a TypeScript body, that does not fit the layout |
database-migration |
a change that loses data or rebuilds a table |
archec explain database-relation says more about each.
In VS Code#
- Writing:
database,tableand a field's words are coloured and completed. Hovering over a relation says which table it points at, the column it holds and its other side. Ctrl+click on a relation's type goes to the table, onvia authorto the field, on a provider to its declaration. - Problems: the checks above, with their quick fixes (add a key, add
via, make a fieldoptional). - Commands: Arche: Show Database Layout draws the tables and their relations. Arche: Generate Database Schema… runs a provider. Arche: Write Database Migration… asks for the earlier version and writes the migration.
Limits#
- No query language. The access code looks rows up by key, by a unique field, by an index, by equal fields, and follows relations. A join, an aggregate or an ordering is yours to write in SQL.
- Handlers in TypeScript. The simulator's database answers TypeScript
bodies and test lines. A
```archebody and a Python body cannot name a database yet; Arche's own simulator does not have it, soarchec testruns a case that names a database on the browser simulator. - C access is SQLite's only. The other providers write Python, and DuckDB and MongoDB TypeScript too.
- No rename. A renamed field or table is read as one dropped and one added.
- One database per model version.
--sincecompares whole layouts; it does not keep a history of migrations, or say which were applied. - The simulator is not a database. It checks the model's rules on each write. It has no transactions, no concurrency and no SQL.
- Not read or written:
schema.prisma.