SQL

SQL is part of the Atoll grammar. FROM, WHERE, SELECT, GET, SAVE, UPDATE, and DELETE are query expressions the compiler resolves, type checks, and renders into fixed prepared statements — not strings that a library assembles at run time.

The skeleton

Every working query needs three things in place: a schema declaration, a model declaration for each table, and a fallible function to run the query in. Postfix ? on the query propagates its QueryError.

schema shop

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

fn open_orders(wanted_customer: int): []Order ! QueryError {
    return FROM Order
        WHERE customer_id == wanted_customer
        ORDER BY id DESC?
}

fn main(): void {
    match open_orders(7) {
        Ok(rows) => println("${rows.len()} orders")
        Err(e) => println("query failed")
    }
}

Read that from the outside in:

schema shop names the logical database the models belong to. Routing that name to a real engine happens in atoll.toml; see Datasources.
model Order { ... } declares a persisted row shape. id, customer_id, and total are Atoll fields and database columns.
[]Order ! QueryError is the function’s type: a list of orders, or a query failure.
wanted_customer is a plain function parameter. Inside the query it becomes a bound prepared-statement parameter, never text spliced into SQL.
The ? after the last clause applies to the whole query expression.

Drop any one of those and the program does not compile. The most common mistake is forgetting that the enclosing function must be fallible:

schema shop

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

// `?` needs somewhere to send the error, and `[]Order` has no error channel.
fn all_orders(): []Order {
    return FROM Order?
}

Every query is a `Result`

A query expression evaluates to Result[T, QueryError]. ? is the usual way to unwrap it, but it is not required — you can name the Result and handle both channels yourself.

schema shop

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

// With `?`: the success type, and the error propagates.
fn unwrapped(): []Order ! QueryError {
    return FROM Order?
}

// Without `?`: the raw two-channel value, and this function cannot fail.
fn raw(): Result[[]Order, QueryError] {
    return FROM Order
}

fn count_or_zero(): int {
    match FROM Order {
        Ok(rows) => return rows.len()
        Err(e) => return 0
    }
}

fn main(): void {
    match unwrapped() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
    match raw() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
    println("${count_or_zero()}")
}

The success carrier depends on the verb, and the compiler picks it:

Query	Success type
`FROM Model`	`[]Model`
`FROM Model SELECT { .. }`	`[]Row` — an anonymous projection row
`ONE FROM Model SELECT { .. }`	`Row?` — projection required, see Reads
`GET Model(key)`	`Model?`
`GET Model(keys)`	`Map[Key, Model]`
`SAVE Model { .. }`	`Model`
`UPDATE` / `DELETE` without `RETURNING`	`int` rows affected

GET is the only read that hands back a whole Model?. A bare ONE FROM Model has no row encoding and does not compile at all; add a SELECT and you get an anonymous row, not a model.

schema shop

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

fn scan(): []Order ! QueryError { return FROM Order? }

fn one(order_id: int): Order? ! QueryError { return GET Order(order_id)? }

fn several(ids: []int): Map[int, Order] ! QueryError { return GET Order(ids)? }

fn totals(): []{ id: int, total: float } ! QueryError {
    return FROM Order SELECT { id, total }?
}

fn first_total(): { total: float }? ! QueryError {
    return ONE FROM Order ORDER BY total DESC SELECT { total }?
}

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

fn main(): void {
    match scan() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
    match one(1) { Ok(row) => println("got one") Err(e) => println("failed") }
    match several([1, 2]) { Ok(m) => println("${m.len()}") Err(e) => println("failed") }
    match totals() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
    match first_total() { Ok(row) => println("${row?.total ?? 0.0}") Err(e) => println("failed") }
    match purge() { Ok(n) => println("${n} removed") Err(e) => println("failed") }
}

Names resolve against the model

Inside a query, a bare identifier that matches a model field is a column; anything else resolves as an ordinary Atoll name and becomes a bound parameter. A typo is a compile error rather than a runtime no such column:

schema shop

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

fn f(): []Order ! QueryError {
    return FROM Order WHERE totl > 10.0?
}

That reports ATOLL3010: field 'totl' does not exist on model 'Order'. An unknown model name reports ATOLL3230 the same way.

When a parameter shares a spelling with a column, qualify the column with the model name so the two cannot be confused:

schema shop

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

fn above(total: float): []Order ! QueryError {
    return FROM Order WHERE Order.total > total?
}

fn main(): void {
    match above(9.99) {
        Ok(rows) => println("${rows.len()} above 9.99")
        Err(e) => println("failed")
    }
}

Writes

Mutating verbs are query expressions too, and they obey the same rules.

schema shop

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

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

fn cancel(order_id: int): int ! QueryError {
    return UPDATE Order SET cancelled = true WHERE id == order_id?
}

fn drop_cancelled(): int ! QueryError {
    return DELETE FROM Order WHERE cancelled?
}

fn main(): void {
    match record(1, 7, 42.0) { Ok(o) => println("saved ${o.id}") Err(e) => println("failed") }
    match cancel(1) { Ok(n) => println("${n} cancelled") Err(e) => println("failed") }
    match drop_cancelled() { Ok(n) => println("${n} purged") Err(e) => println("failed") }
}

SAVE returns the saved row; UPDATE and DELETE without RETURNING return the affected-row count as an int. Writes covers the full set.

Failure

QueryError is an ordinary Atoll error enum declared in the prelude. It has three variants — Engine, Encoding, and Backend — plus helpers that classify an engine failure portably, so you never have to parse a message.

schema shop

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

fn load(): []Order ! QueryError { return FROM Order? }

fn load_or_empty(): []Order {
    match load() {
        Ok(rows) => return rows
        Err(e) => {
            // A transient failure (deadlock, lock timeout, lost connection)
            // is worth retrying; a constraint violation never is.
            if e.is_retryable() { return [] }
            return []
        }
    }
}

fn main(): void {
    println("${load_or_empty().len()}")
}

is_retryable(), is_conflict(), is_constraint(), and is_not_found() group the portable SqlErrorCode classes. e.code() returns the class itself and e.native() returns the engine’s own code — a PostgreSQL SQLSTATE, a MySQL errno — for the long tail the portable taxonomy does not name.

Because a query is a Result, catch applies to it directly:

schema shop

model Order {
    @id
    id: int
}

fn ids_or_empty(): []Order {
    rows := FROM Order catch {
        _ => return []
    }
    return rows
}

fn main(): void {
    println("${ids_or_empty().len()}")
}

What is fixed and what is bound

The compiler renders one statement per query and registers it by hash. Table names, column names, operators, clause order, and SQL keywords are compile-time choices. Only values travel at run time, through the SQL ABI, as encoded parameters:

schema shop

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

// Renders once, as roughly:
//   SELECT id, customer_id, total FROM "order"
//   WHERE customer_id = ?1 AND total >= ?2 ORDER BY total DESC LIMIT ?3
fn top(customer: int, floor: float, n: int): []Order ! QueryError {
    return FROM Order
        WHERE customer_id == customer
        WHERE total >= floor
        ORDER BY total DESC
        LIMIT n?
}

fn main(): void {
    match top(7, 10.0, 5) {
        Ok(rows) => println("${rows.len()}")
        Err(e) => println("failed")
    }
}

No parameter can become a table name, a column name, or a keyword. Runtime ORDER BY direction looks like an exception but is not: the compiler pre-renders both statements and the guest selects one. See Filters.

A composed example

Two related models, an index, a named projection row, a parameterised scan, and explicit failure handling:

schema shop

model Customer {
    @id
    id: int
    @unique
    email: string
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
    cancelled: bool

    BELONGS TO customer: Customer VIA customer_id

    INDEX (customer_id, total DESC)
}

type OrderLine = { id: int, total: float }

fn recent_lines(customer: int, floor: float, n: int): []OrderLine ! QueryError {
    return FROM Order
        WHERE customer_id == customer
        WHERE cancelled == false
        WHERE total >= floor
        ORDER BY total DESC, id DESC
        LIMIT n
        SELECT { id, total }?
}

fn spend(customer: int): float {
    match recent_lines(customer, 0.0, 100) {
        Ok(lines) => {
            mut sum := 0.0
            for line in lines { sum = sum + line.total }
            return sum
        }
        Err(e) => return -1.0
    }
}

fn main(): void {
    println("${spend(7)}")
}

Guide map

The pages read in sidebar order, and each one builds on the last:

Models — fields, keys, decorators, indexes, relations.
Schemas — how a file claims its models.
Datasources — engines, routing, read-only policy.
Reads — FROM, ONE FROM, GET.
Filters — WHERE, ORDER BY, LIMIT, guards.
Projections — SELECT row shapes.
Joins — multi-model reads.
Aggregates — GROUP BY, HAVING, window functions.
Writes — SAVE, INSERT, UPDATE, DELETE, REMOVE.
Composition — reusable query fragments.
Frames — host-resident dataframes.
Advanced — engine-gated escape hatches.
Transactions — atomicity and route identity.

Transactions come last because a transaction block constrains every query form that precedes it: each operation inside one must resolve to the same datasource route.

Pages marked provisional describe surfaces whose engine, DDL, or runtime coverage is not yet uniform across every supported database. Where a form parses but does not lower to SQL, this guide says so and shows the supported alternative.