Skip to content
Atoll Book

Writes

Save, upsert, update, delete, and remove SQL-backed rows with checked statements.

Updated View as Markdown

Writes are query expressions like reads: checked against the model, rendered into a prepared statement, and typed Result[T, QueryError]. No value a write carries is ever spliced into SQL text.

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

fn record(new_id: int, customer: int, amount: float): Order ! QueryError {
    return SAVE Order {
        id: new_id,
        customer_id: customer,
        total: amount,
        status: "new",
    }?
}

fn main(): void {
    match record(1, 42, 19.5) {
        Ok(order) => println("saved order ${order.id} (${order.status})")
        Err(e) => println("write failed")
    }
}

SAVE returns the saved model, so record hands back an Order. Other verbs return other things, and picking the right one is mostly about picking the success value you want to reason about.

Which verb, and what it gives back

Form Success value
SAVE Model { ... } the saved Model
SAVE Model FROM rows []Model
UPSERT Model ON keys { ... } affected-row int
UPDATE / DELETE FROM without RETURNING affected-row int
REMOVE Model(key) bool — whether a row existed
REMOVE Model(keys) affected-row int
INSERT INTO Model FROM rows affected-row int
any write ... RETURNING { ... } a list of projected rows
single-key REMOVE ... RETURNING { ... } one optional projected row

These are not interchangeable spellings. Swapping SAVE for a plain insert turns a duplicate key from an error into an update. Swapping a single-key REMOVE for a predicate DELETE FROM changes the result from “did that row exist” into “how many rows went away”, and widens what can be deleted.

Save

SAVE upserts one complete model by primary key, and returns the stored row:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

fn store(new_id: int, customer: int, amount: float): Order ! QueryError {
    return SAVE Order {
        id: new_id,
        customer_id: customer,
        total: amount,
        status: "new",
    }?
}

fn main(): void {
    match store(1, 42, 19.5) {
        Ok(saved) => println("stored order ${saved.id} with status ${saved.status}")
        Err(e) => println("write failed")
    }
}

The value block is a record literal, so fields are comma-separated and a bare name is shorthand for name: name:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

fn store(id: int, customer_id: int, total: float, status: string): Order ! QueryError {
    return SAVE Order { id, customer_id, total, status }?
}

fn main(): void {
    match store(1, 42, 19.5, "new") {
        Ok(saved) => println("stored ${saved.id}")
        Err(e) => println("write failed")
    }
}

The batch form takes a list of models and upserts each one, returning the saved rows:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn store_all(orders: []Order): []Order ! QueryError {
    return SAVE Order FROM orders?
}

fn main(): void {
    mut batch: []Order
    batch.add(Order { id: 1, customer_id: 42, total: 19.5 })
    batch.add(Order { id: 2, customer_id: 42, total: 3.25 })
    match store_all(batch) {
        Ok(saved) => println("stored ${saved.len()} orders")
        Err(e) => println("write failed")
    }
}

The batch path executes a prepared single-row statement repeatedly. It handles all-scalar model rows; nullable-column batch binding and some relationship-bearing shapes are still incomplete.

Nullable columns

A value block may omit a nullable column, or bind it from an Option-typed local. Both write SQL NULL when there is no value:

schema shop

model Order {
    @id
    id: int
    total: float
    note: string?
}

// The column simply is not mentioned.
fn without_note(new_id: int, amount: float): Order ! QueryError {
    return SAVE Order { id: new_id, total: amount }?
}

// The column is bound from a `string?` — `None` and `Some` both work.
fn with_note(new_id: int, amount: float, note: string?): Order ! QueryError {
    return SAVE Order { id: new_id, total: amount, note }?
}

fn main(): void {
    match without_note(1, 19.5) { Ok(o) => println("${o.id}") Err(e) => println("failed") }
    match with_note(2, 3.25, None) { Ok(o) => println("${o.id}") Err(e) => println("failed") }
    match with_note(3, 8.0, "gift wrap") { Ok(o) => println("${o.id}") Err(e) => println("failed") }
}

What does not work is spelling the literal None in the value block. That shape has no plan, so the whole statement fails to lower:

schema shop

model Order {
    @id
    id: int
    total: float
    note: string?
}

fn broken(new_id: int): Order ! QueryError {
    return SAVE Order { id: new_id, total: 1.0, note: None }?
}

That is ATOLL3244. Omit the field, or route it through an Option local as with_note does.

Update

UPDATE changes every row matching its predicate and reports how many it touched:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    status: string
}

fn cancel_all(customer: int): int ! QueryError {
    return UPDATE Order
        SET status = "cancelled"
        WHERE customer_id == customer?
}

fn main(): void {
    match cancel_all(42) {
        Ok(n) => println("cancelled ${n} orders")
        Err(e) => println("update failed")
    }
}

One SET clause per column; repeat the keyword to change several:

schema shop

model Order {
    @id
    id: int
    total: float
    status: string
    note: string
}

fn settle(wanted: int, amount: float, note: string): int ! QueryError {
    return UPDATE Order
        SET status = "settled"
        SET total = amount
        SET note = note
        WHERE id == wanted?
}

fn main(): void {
    match settle(1, 19.5, "paid by card") {
        Ok(n) => println("settled ${n} orders")
        Err(e) => println("update failed")
    }
}

The assigned value must be a bare local, a parameter, or a literal — nothing else. Reading the old row in the assignment is not supported, so the familiar SET count = count + 1 does not lower:

schema shop

model Order {
    @id
    id: int
    retries: int
}

fn broken(wanted: int): int ! QueryError {
    return UPDATE Order
        SET retries = retries + 1
        WHERE id == wanted?
}

That reports ATOLL3244. The same restriction bites Atoll-side arithmetic: SET total = amount * 2.0 is rejected even though amount is a parameter, because the right-hand side is an expression rather than a bare name. Compute the value into a local first:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn double_price(wanted: int, amount: float): int ! QueryError {
    doubled := amount * 2.0
    return UPDATE Order
        SET total = doubled
        WHERE id == wanted?
}

fn main(): void {
    match double_price(1, 19.5) {
        Ok(n) => println("repriced ${n} orders")
        Err(e) => println("update failed")
    }
}

For a genuine read-modify-write, read the row, compute in Atoll, and write it back with a predicate that pins the version you read — a compare-and-set, which is safer than a blind increment anyway.

RETURNING swaps the affected-row count for a list of projected rows:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    status: string
}

fn cancel_and_report(
    customer: int,
): []{ id: int, status: string } ! QueryError {
    return UPDATE Order
        SET status = "cancelled"
        WHERE customer_id == customer
        RETURNING { id, status }?
}

fn main(): void {
    match cancel_and_report(42) {
        Ok(rows) => {
            for row in rows {
                println("order ${row.id} is now ${row.status}")
            }
        }
        Err(e) => println("update failed")
    }
}

The affected count is a signal

An update that matches nothing is a success with count 0, not a QueryError — the database ran the statement and found no row. Put the state you expect into the predicate and treat 0 as the conflict:

schema shop

model Order {
    @id
    id: int
    status: string
}

error OrderError { Conflict { id: int } }

fn advance(wanted: int, expected: string, next: string): void ! OrderError {
    n := (
        UPDATE Order
            SET status = next
            WHERE id == wanted && status == expected
    ) catch {
        _ => error Conflict { id: wanted }
    }
    if n == 0 {
        error Conflict { id: wanted }
    }
}

fn main(): void {
    match advance(1, "open", "settled") {
        Ok(_) => println("advanced")
        Err(Conflict { id }) => println("order ${id} was not in the expected state")
    }
}

The WHERE status == expected clause makes the prior state part of the write, so two concurrent callers cannot both advance the same order.

A clause expression must be a plain local, a parameter, or a literal. A field access such as order.id is not lowered — bind it to a local first:

schema shop

model Order {
    @id
    id: int
    status: string
}

fn settle(order: Order): int ! QueryError {
    wanted := order.id
    return UPDATE Order
        SET status = "settled"
        WHERE id == wanted && status == "open"?
}

fn main(): void {
    order := Order { id: 1, status: "open" }
    match settle(order) {
        Ok(n) => println("settled ${n} rows")
        Err(e) => println("update failed")
    }
}

Delete and remove

DELETE FROM is predicate-based and can remove many rows:

schema shop

model Order {
    @id
    id: int
    total: float
    status: string
}

fn purge_empty(): int ! QueryError {
    return DELETE FROM Order WHERE total == 0.0?
}

fn purge_all(): int ! QueryError {
    return DELETE FROM Order?
}

fn main(): void {
    match purge_empty() {
        Ok(n) => println("removed ${n} zero-value orders")
        Err(e) => println("delete failed")
    }
    match purge_all() {
        Ok(n) => println("removed everything: ${n} rows")
        Err(e) => println("delete failed")
    }
}

REMOVE deletes by primary key instead, and its result makes absence explicit:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn drop_one(wanted: int): bool ! QueryError {
    return REMOVE Order(wanted)?
}

fn drop_many(ids: []int): int ! QueryError {
    return REMOVE Order(ids)?
}

fn main(): void {
    match drop_one(1) {
        Ok(existed) => println("row 1 existed: ${existed}")
        Err(e) => println("remove failed")
    }
    mut ids: []int
    ids.add(2)
    ids.add(3)
    match drop_many(ids) {
        Ok(n) => println("removed ${n} rows")
        Err(e) => println("remove failed")
    }
}

drop_one returns false when no such row existed — absence is a value, not an error. With RETURNING, the single-key form yields an optional row and the batch form a list:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn drop_and_report(wanted: int): { id: int, total: float }? ! QueryError {
    return REMOVE Order(wanted) RETURNING { id, total }?
}

fn drop_batch_and_report(ids: []int): float ! QueryError {
    mut refunded := 0.0
    for r in REMOVE Order(ids) RETURNING { id, total }? {
        refunded = refunded + r.total
    }
    return refunded
}

fn main(): void {
    match drop_and_report(1) {
        Ok(Some(r)) => println("removed ${r.id} worth ${r.total}")
        Ok(None) => println("nothing to remove")
        Err(e) => println("remove failed")
    }
    mut ids: []int
    ids.add(2)
    ids.add(3)
    match drop_batch_and_report(ids) {
        Ok(total) => println("refunded ${total}")
        Err(e) => println("remove failed")
    }
}

Composite keys are supplied positionally, in declaration order:

schema stats

model DailyStat {
    @id
    day: int
    @id
    route_id: int
    total: int
}

fn drop_stat(day: int, route_id: int): { total: int }? ! QueryError {
    return REMOVE DailyStat(day, route_id) RETURNING { total }?
}

fn main(): void {
    match drop_stat(20260729, 7) {
        Ok(Some(row)) => println("dropped a stat of ${row.total}")
        Ok(None) => println("no such stat")
        Err(e) => println("remove failed")
    }
}

Upsert

UPSERT names its conflict target explicitly, which is what you want when the uniqueness that matters is not the primary key:

schema stats

model DailyStat {
    @id
    day: int
    @id
    route_id: int
    total: int
}

fn record(day: int, route_id: int, total: int): int ! QueryError {
    return UPSERT DailyStat ON day, route_id {
        day,
        route_id,
        total,
    }?
}

fn main(): void {
    match record(20260729, 7, 148) {
        Ok(n) => println("upsert touched ${n} rows")
        Err(e) => println("upsert failed")
    }
}

The conflict columns must name model columns and must also appear in the value block. The compiler renders the PostgreSQL/SQLite ON CONFLICT ... DO UPDATE form and the MySQL ON DUPLICATE KEY UPDATE form from the same plan.

RETURNING gives a list, because a general write terminal can affect more than one row:

schema stats

model DailyStat {
    @id
    day: int
    @id
    route_id: int
    total: int
}

fn record(day: int, route_id: int, total: int): []{ total: int } ! QueryError {
    return UPSERT DailyStat ON day, route_id { day, route_id, total }
        RETURNING { total }?
}

fn main(): void {
    match record(20260729, 7, 148) {
        Ok(rows) => {
            for row in rows {
                println("stat is now ${row.total}")
            }
        }
        Err(e) => println("upsert failed")
    }
}

Batch insert

INSERT INTO Model FROM rows inserts a list and returns the affected-row count:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn import_orders(orders: []Order): int ! QueryError {
    return INSERT INTO Order FROM orders?
}

fn main(): void {
    mut batch: []Order
    batch.add(Order { id: 1, customer_id: 42, total: 19.5 })
    batch.add(Order { id: 2, customer_id: 42, total: 3.25 })
    match import_orders(batch) {
        Ok(n) => println("inserted ${n} rows")
        Err(e) => println("insert failed")
    }
}

This FROM form is the only insert shape that lowers. Two neighbouring spellings parse and type-check but have no plan, so they are compile errors — not runtime gaps.

The single-row INSERT INTO Model VALUES { ... } is one of them:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn broken(new_id: int): Order ! QueryError {
    return INSERT INTO Order VALUES { id: new_id, total: 1.0 }?
}

So is BATCH SIZE, even on the batch form that otherwise compiles:

schema shop

model Order {
    @id
    id: int
    total: float
}

fn broken(orders: []Order): int ! QueryError {
    return INSERT INTO Order FROM orders BATCH SIZE 100?
}

Both report ATOLL3244. Drop the BATCH SIZE clause; and for a single row, reach for SAVE when an existing row should be replaced, or UPSERT when you want to state the conflict key yourself.

Handling failure

Every write is Result[T, QueryError]. ? propagates it; catch converts it into your own error type. QueryError classifies itself, so a caller can tell a data conflict from a transient one:

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

error PlaceError { Duplicate, Unavailable }

fn place(new_id: int, customer: int, amount: float): Order ! PlaceError {
    return SAVE Order {
        id: new_id,
        customer_id: customer,
        total: amount,
        status: "new",
    } catch {
        e if e.is_constraint() => error Duplicate
        _ => error Unavailable
    }
}

fn main(): void {
    match place(1, 42, 19.5) {
        Ok(order) => println("placed ${order.id}")
        Err(Duplicate) => println("that order id is taken")
        Err(Unavailable) => println("store unavailable")
    }
}

is_constraint() means the caller supplied data the database refused. is_retryable() means the failure looked transient — a deadlock, a lock timeout, a lost connection. is_conflict() and is_not_found() cover the other coarse classes.

Retryability says the failure may be transient. It does not say the operation is safe to run twice:

Write shape Repeating it
set a column to a fixed value idempotent
SAVE/UPSERT the same complete row idempotent, if no trigger says otherwise
append a log row, or increment a counter duplicates the effect
insert with a generated key creates a second logical row
REMOVE by stable key second attempt reports absence

When several statements establish one invariant, retry from the transaction boundary rather than replaying the last statement. See Transactions.

Safety gates

The compiler refuses to emit a statement it cannot lower rather than dropping the clause it does not understand. LIMIT on a write, GROUP BY on a write, joins on a write, conditional SET clauses, and the incomplete batch forms all diagnose at compile time. Engine gates apply too: a DataFusion route is read-only, MySQL rejects RETURNING, and a datasource configured with readOnly = true rejects every write before it executes.

A single statement is atomic on its own. Several statements are not, unless you put them in a transaction block.

A worked example

schema shop

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
}

error CheckoutError {
    Duplicate { id: int }
    NotOpen { id: int }
    Unavailable
}

/// Create an order, then settle it — with the previous state pinned into the
/// update so a concurrent settle cannot double-count.
fn checkout(new_id: int, customer: int, amount: float): Order ! CheckoutError {
    created := SAVE Order {
        id: new_id,
        customer_id: customer,
        total: amount,
        status: "open",
    } catch {
        e if e.is_constraint() => error Duplicate { id: new_id }
        _ => error Unavailable
    }

    wanted := created.id
    settled := (
        UPDATE Order
            SET status = "settled"
            WHERE id == wanted && status == "open"
            RETURNING { id, total, status }
    ) catch {
        _ => error Unavailable
    }

    if settled.len() == 0 {
        error NotOpen { id: wanted }
    }
    return created
}

fn main(): void {
    match checkout(1, 42, 19.5) {
        Ok(order) => println("checked out ${order.id}")
        Err(Duplicate { id }) => println("order ${id} already exists")
        Err(NotOpen { id }) => println("order ${id} was not open")
        Err(Unavailable) => println("store unavailable")
    }
}
Navigation

Type to search…

↑↓ navigate↵ selectEsc close