Advanced | Atoll Book

Use checked SQL subqueries, sets, CTEs, windows, search, vectors, and row locking.

The integrated query AST covers advanced SQL without falling back to raw strings. Every form is checked against the engine of the datasource that serves the model’s schema — a feature the engine cannot run is rejected at compile time with ATOLL3232 rather than rendered and discovered at runtime.

Reading the engine gates on this page

A file with no atoll.toml above it compiles against the default engine, SQLite. Several forms below — row locking, full-text search, vector search — are unsupported there, so their examples appear as failing blocks showing the exact diagnostic. The syntax is correct; the engine is what rejects it. Point the schema at a Postgres datasource and the same source compiles.

Feature	Postgres / AlloyDB	MySQL	SQLite	Turso	DataFusion
subqueries, sets, non-recursive CTEs	yes	yes	yes	yes	yes
`WITH RECURSIVE`	yes	yes	yes	no	yes
window functions with frames	yes	yes	yes	no	yes
`FOR UPDATE` / `SKIP LOCKED`	yes	yes	no	no	no
full-text search (`USING fts`)	yes	no	no	yes	no
vector search (`@sql.vector`)	yes	no	no	yes	no

Subqueries

An IN subquery must project exactly one column. It sees the outer scope, so a qualified outer column correlates:

schema seaport

model Customer {
    @id
    id: int
    name: string
    active: bool
}

model Booking {
    @id
    id: int
    customer_id: int
    nights: int
    price: int
}

fn bookings_for_active_customers(): []Booking ! QueryError {
    return FROM Booking
        WHERE customer_id in (
            FROM Customer
            WHERE active
            SELECT { id }
        )?
}

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

exists(...) asks only whether a row is present and needs no projection; ! negates it. A parenthesized single-column read used where a value is expected is a scalar subquery:

schema seaport

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

model Booking {
    @id
    id: int
    customer_id: int
    price: int
}

fn customers_with_bookings(): []Customer ! QueryError {
    return FROM Customer
        WHERE exists(FROM Booking WHERE booking.customer_id == customer.id)?
}

fn customers_without_bookings(): []Customer ! QueryError {
    return FROM Customer
        WHERE !exists(FROM Booking WHERE booking.customer_id == customer.id)?
}

fn above_average_price(): []Booking ! QueryError {
    return FROM Booking
        WHERE price > (FROM Booking SELECT { avg_price: avg(price) })?
}

fn main(): void {
    match above_average_price() {
        Ok(rows) => println("${rows.len()} expensive bookings")
        Err(e) => println("read failed")
    }
}

A scalar operand that projects more than one column is rejected rather than silently truncated:

schema seaport

model Booking {
    @id
    id: int
    price: int
}

fn wrong(): []Booking ! QueryError {
    // error[ATOLL3243]: a subquery used here must return exactly one column
    return FROM Booking
        WHERE price > (FROM Booking SELECT { id, price })?
}

Set operations

UNION, UNION ALL, INTERSECT, and EXCEPT combine reads whose projections agree in arity and column type. INTERSECT binds tighter than UNION, so A UNION B INTERSECT C means A UNION (B INTERSECT C).

schema seaport

model Booking {
    @id
    id: int
    total: int
}

model Quote {
    @id
    id: int
    total: int
}

fn all_ids(): int ! QueryError {
    distinct_ids := FROM Booking SELECT { id } UNION FROM Quote SELECT { id }?
    every_id := FROM Booking SELECT { id } UNION ALL FROM Quote SELECT { id }?
    both := FROM Booking SELECT { id } INTERSECT FROM Quote SELECT { id }?
    only_bookings := FROM Booking SELECT { id } EXCEPT FROM Quote SELECT { id }?
    return distinct_ids.len() + every_id.len() + both.len() + only_bookings.len()
}

fn main(): void {
    match all_ids() {
        Ok(n) => println("${n} rows across four set operations")
        Err(e) => println("read failed")
    }
}

UNION removes duplicates; UNION ALL preserves them. A set expression still has one checked result-row type, and SELECT DISTINCT on a branch applies to that branch alone.

Common table expressions

WITH binds one or more reads for the query that follows. Each binding is visible to the main query and to later bindings in the same WITH list, and each is checked with the same clause and engine gates as a top-level query.

schema seaport

model Customer {
    @id
    id: int
    name: string
    active: bool
}

model Booking {
    @id
    id: int
    customer_id: int
    price: int
}

fn active_customer_spend(): []{ id: int, price: int } ! QueryError {
    return WITH active_ids AS (
        FROM Customer WHERE active SELECT { id }
    )
    FROM Booking
        WHERE customer_id in (FROM active_ids SELECT { id })
        SELECT { id, price }?
}

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

WITH RECURSIVE puts the CTE’s own name in scope inside its body, so an anchor branch can be extended by a recursive branch that joins the CTE:

schema seaport

model Booking {
    @id
    id: int
    parent_id: int
    total: int
}

fn amendment_chain(root: int): []{ bid: int, parent: int } ! QueryError {
    return WITH RECURSIVE chain AS (
        FROM Booking WHERE id == root SELECT { bid: id, parent: parent_id }
        UNION ALL
        FROM Booking JOIN chain ON Booking.parent_id == chain.bid
            SELECT { bid: Booking.id, parent: Booking.parent_id }
    )
    FROM chain SELECT { bid, parent }?
}

fn main(): void {
    match amendment_chain(1) {
        Ok(rows) => println("chain of ${rows.len()}")
        Err(e) => println("read failed")
    }
}

Without the RECURSIVE keyword a CTE cannot see itself — only earlier bindings are in scope. A recursive CTE is not a runtime collection and not a way to issue several statements; it is one rendered query. Turso 0.7 does not accept the keyword, so a recursive CTE against a Turso datasource is ATOLL3232.

Window functions

A window function is a projection item carrying an OVER clause with optional partition keys and ordering. Projection fields are comma-separated, exactly like a record literal:

schema seaport

model Booking {
    @id
    id: int
    customer_id: int
    price: int
}

fn ranked(): []{ id: int, rank: int, best: int } ! QueryError {
    return FROM Booking
        SELECT {
            id,
            rank: row_number() OVER (
                PARTITION BY customer_id
                ORDER BY price DESC
            ),
            best: dense_rank() OVER (ORDER BY price DESC)
        }?
}

fn main(): void {
    match ranked() {
        Ok(rows) => {
            for row in rows {
                println("booking ${row.id}: #${row.rank} for its customer")
            }
        }
        Err(e) => println("read failed")
    }
}

Ranking (row_number, rank, dense_rank, ntile, percent_rank, cume_dist), offset (lead, lag), value (first_value, last_value, nth_value), and the aggregates (sum, count, avg, min, max) are all windowable. A frame narrows which rows the aggregate sees — ROWS counts physical rows, RANGE groups peers by the ordering value:

schema seaport

model Booking {
    @id
    id: int
    customer_id: int
    price: int
}

fn running_totals(): []{ id: int, running: int, window: float } ! QueryError {
    return FROM Booking
        SELECT {
            id,
            running: sum(price) OVER (
                ORDER BY id
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ),
            window: avg(price) OVER (
                PARTITION BY customer_id
                ORDER BY id
                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
            )
        }?
}

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

An offset or bucket argument must be an integer literal — a bound local cannot be rendered as a constant. A plain scalar function under OVER is not a window function and is reported, not silently dropped:

schema seaport

model Booking {
    @id
    id: int
    title: string
    price: int
}

fn wrong(): int ! QueryError {
    // error[ATOLL3240]: an OVER clause requires a window function or an aggregate
    rows := FROM Booking
        SELECT { x: lower(title) OVER (ORDER BY price) }?
    return rows.len()
}

Turso 0.7 implements only row_number() and the aggregate windows, and no frames at all; every other engine on the matrix takes the full surface.

Conditional queries

When engines need different whole-query shapes, branch at compile time. The datasource engine of the queried model selects the first matching branch; the other branches are parsed but not type-checked, so a clause the current engine rejects can live safely in a branch that engine will never take.

schema seaport

model Booking {
    @id
    id: int
    archived: bool
    notes: string
}

fn live_bookings(): []{ id: int } ! QueryError {
    return when Postgres {
        FROM Booking WHERE archived == false SELECT { id }
    } when MySQL {
        FROM Booking WHERE archived == false SELECT { id }
    } else {
        FROM Booking WHERE archived == false SELECT { id }
    }?
}

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

else is optional, but compilation fails when no branch matches and there is no fallback — the compiler will not guess a shape:

schema seaport

model Booking {
    @id
    id: int
    archived: bool
}

fn live_bookings(): int ! QueryError {
    // error[ATOLL3232]: no `when` branch for the Sqlite engine, and no `else` fallback
    rows := when Postgres {
        FROM Booking WHERE archived == false SELECT { id }
    } when MySQL {
        FROM Booking WHERE archived == false SELECT { id }
    }?
    return rows.len()
}

Use when for structural differences. When only a scalar SQL spelling differs, use a per-engine @sql.function instead — see Escape hatches.

Row locking

FOR UPDATE and FOR UPDATE SKIP LOCKED mark a read as claiming its rows. Only PostgreSQL, AlloyDB, and MySQL have row-level locking; the SQLite family has none (a write transaction is already exclusive) and DataFusion is analytical, so those engines reject the clause instead of quietly omitting it:

schema seaport

model Job {
    @id
    id: int
    status: string
    priority: int
}

fn claim(): []Job ! QueryError {
    // error[ATOLL3232]: a `FOR UPDATE` locking read is not supported by the Sqlite engine
    return FROM Job
        WHERE status == "pending"
        ORDER BY priority DESC
        LIMIT 10
        FOR UPDATE SKIP LOCKED?
}

SKIP LOCKED is what makes a work queue scale: competing workers step over rows another worker already claimed instead of blocking behind them. Wrap the claim and the follow-up write in a transaction so the lock survives until the row is marked done. A when block lets the same source serve a locking engine in production and a lock-free one in tests:

schema seaport

model Job {
    @id
    id: int
    status: string
    priority: int
}

fn claim(): []Job ! QueryError {
    return when Postgres {
        FROM Job
            WHERE status == "pending"
            ORDER BY priority DESC
            LIMIT 10
            FOR UPDATE SKIP LOCKED
    } when MySQL {
        FROM Job
            WHERE status == "pending"
            ORDER BY priority DESC
            LIMIT 10
            FOR UPDATE SKIP LOCKED
    } else {
        FROM Job
            WHERE status == "pending"
            ORDER BY priority DESC
            LIMIT 10
    }?
}

fn main(): void {
    match claim() {
        Ok(jobs) => println("claimed ${jobs.len()} jobs")
        Err(e) => println("claim failed")
    }
}

FOR UPDATE is a read-only clause; it has no meaning on an UPDATE or DELETE and is rejected there.

Full-text search

A full-text index is declared on the model with USING fts over the columns it covers. The declaration itself compiles anywhere — it is the DDL emission and the search query that are engine-gated:

schema docs

model Article {
    @id
    id: int
    title: string
    body: string
    lang: string

    INDEX article_search (title, body) USING fts
    INDEX (lang)
}

fn main(): void {
    println("article model declared")
}

search(q) in a WHERE, plus score() and highlight(col, open, close) in a projection, query that index. All three calls share a single bound query slot, so the match, the ranking, and the snippet can never drift apart:

schema docs

model Article {
    @id
    id: int
    title: string
    body: string

    INDEX article_search (title, body) USING fts
}

fn find(q: string): []{ id: int, relevance: float, snippet: string } ! QueryError {
    // error[ATOLL3232]: full-text search is not supported by the Sqlite engine
    return FROM Article
        WHERE search(q)
        SELECT { id, relevance: score(), snippet: highlight(body, "<b>", "</b>") }
        ORDER BY relevance DESC?
}

Against PostgreSQL that renders a to_tsvector(...) @@ ... predicate over a GIN expression index with ts_rank and ts_headline; against Turso it renders that engine’s fts_match / fts_score / fts_highlight. Plain SQLite, MySQL, and DataFusion reject it — an index that silently degraded to a b-tree would answer every search wrongly while diffing clean forever.

When a model carries several full-text indexes, name the one to use: search(note_title, q) and score(note_title). Index options travel in a WITH list, as in USING fts WITH (tokenizer = "ngram").

Vector search

A dense embedding column is []f32 annotated with @sql.vector(N); the dimension is part of the model’s type contract, and query vectors must agree with it. An ANN index is declared with USING vector:

schema search

model Doc {
    @id
    id: int
    title: string
    @sql.vector(3)
    embedding: []f32

    INDEX doc_ann (embedding) USING vector
}

fn main(): void {
    println("doc model declared")
}

distance(column, query) projects the metric; ranking by its output alias is what lets the engine plan an index scan:

schema search

model Doc {
    @id
    id: int
    @sql.vector(3)
    embedding: []f32

    INDEX doc_ann (embedding) USING vector
}

fn nearest(q: []f32): []{ id: int, d: float } ! QueryError {
    // error[ATOLL3232]: vector search is not supported by the Sqlite engine
    return FROM Doc
        SELECT { id, d: distance(embedding, q) }
        ORDER BY d
        LIMIT 5?
}

The metric comes from the index — USING vector WITH (metric = "l2") changes the operator the query renders, not just the DDL, so the ranking always matches what the index was built for. Metrics are gated per engine: l1 is pgvector-only, dense jaccard is Turso-only.

Storage and distance are gated hard, because no rewrite produces the right answer without them. A missing ANN index only warns: exact nearest-neighbour ordering is still correct, it just scans more rows.

Escape hatches

A @sql.function declaration is a bodyless fn whose decorator carries the SQL template. One template with no engine is portable; per-engine templates cover dialects that spell the same operation differently. $1, $2, … refer to the call arguments.

schema seaport

model Booking {
    @id
    id: int
    title: string
    created_at: string
}

@sql.function("upper($1)")
fn shout(s: string): string

@sql.function(Postgres, "date_trunc('month', $1)")
@sql.function(MySQL, "DATE_FORMAT($1, '%Y-%m-01')")
@sql.function(SQLite, "date($1, 'start of month')")
fn month_start(t: string): string

fn monthly(): []{ id: int, loud: string, month: string } ! QueryError {
    return FROM Booking
        SELECT { id, loud: shout(title), month: month_start(created_at) }?
}

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

Argument binding, result typing, and the engine gate all survive the escape hatch. A function used against an engine it has no body for is a compile error, not a runtime surprise:

schema seaport

model Booking {
    @id
    id: int
    created_at: string
}

@sql.function(Postgres, "date_trunc('month', $1)")
fn month_start(t: string): string

fn monthly(): int ! QueryError {
    // error[ATOLL3232]: a `@sql.function` used here has no body for the Sqlite engine
    rows := FROM Booking SELECT { id, m: month_start(created_at) }?
    return rows.len()
}

Arbitrary raw SQL, raw query expressions, JSON operators, table-valued functions, and general array operators remain planned. There is deliberately no string-concatenation path from user input to an executable query, and a query that cannot be lowered into an executable statement fails compilation rather than returning an empty placeholder result.