Projections | Atoll Book

SELECT { ... } chooses the result columns and mints a typed row shape for them. The result is an anonymous record type, not a partially filled model.

schema shop

model Booking {
    @id
    id: int
    reference: string
    passengers: int
    price_each: float
}

fn summaries(): []{ id: int, reference: string } ! QueryError {
    return FROM Booking SELECT { id, reference }?
}

fn main(): void {
    match summaries() {
        Ok(rows) => {
            for row in rows { println("${row.id}: ${row.reference}") }
        }
        Err(e) => println("read failed")
    }
}

Fields need commas

A projection body is a record literal. Fields are comma separated; newlines alone are not a separator, and forgetting the commas is the single most common projection mistake:

schema shop

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

fn f(): []{ id: int, reference: string } ! QueryError {
    return FROM Booking SELECT {
        id
        reference
    }?
}

That reports ATOLL1006: Expected '}', followed by a cascade of unresolved-name errors from the rest of the query — the parser stopped after id. With commas — including an optional trailing one — the multi-line form is fine, and it is what you want once computed fields appear.

Bare fields and named fields

A bare field name keeps its own name and type. name: expression renames a column or introduces a computed one.

schema shop

model Booking {
    @id
    id: int
    reference: string
    passengers: int
    price_each: float
}

type Line = { id: int, code: string, total: float }

fn lines(): []Line ! QueryError {
    return FROM Booking
        SELECT {
            id,
            code: reference,
            total: price_each * passengers,
        }?
}

fn main(): void {
    match lines() {
        Ok(rows) => {
            for line in rows { println("${line.code} = ${line.total}") }
        }
        Err(e) => println("read failed")
    }
}

The row type here is spelled once as a type alias and reused as the return type. That is the cleanest way to give a projection a name — the compiler-minted row is structural, so any alias with matching field names and types unifies with it.

Rows are ordinary values once decoded:

schema shop

model Booking {
    @id
    id: int
    passengers: int
    price_each: float
}

fn revenue(): float ! QueryError {
    lines := FROM Booking SELECT { id, total: price_each * passengers }?
    mut sum := 0.0
    for line in lines { sum = sum + line.total }
    return sum
}

fn main(): void {
    match revenue() {
        Ok(total) => println("revenue ${total}")
        Err(e) => println("read failed")
    }
}

Projections keep the verb’s shape

Adding SELECT changes the row type, never the container:

schema shop

model Booking {
    @id
    id: int
    reference: string
    passengers: int
}

type Row = { id: int, reference: string }

fn many(): []Row ! QueryError {
    return FROM Booking SELECT { id, reference }?
}

fn one(minimum: int): Row? ! QueryError {
    return ONE FROM Booking WHERE passengers >= minimum SELECT { id, reference }?
}

fn keyed(booking: int): Row? ! QueryError {
    return GET Booking(booking) SELECT { id, reference }?
}

fn batched(ids: []int): Map[int, Row] ! QueryError {
    return GET Booking(ids) SELECT { id, reference }?
}

fn main(): void {
    match many() { Ok(rows) => println("${rows.len()}") Err(e) => println("failed") }
    match one(2) { Ok(row) => println("${row?.reference ?? "-"}") Err(e) => println("failed") }
    match keyed(1) { Ok(row) => println("${row?.reference ?? "-"}") Err(e) => println("failed") }
    match batched([1, 2]) { Ok(m) => println("${m.len()}") Err(e) => println("failed") }
}

ONE FROM is the one verb that requires a projection: without one it has no row encoding and does not lower. A GET over a model with a nullable column is in the same position, and GET ... SELECT is the fix. See Reads.

A projection is not a model

A projection row and a declared struct are different types even when their fields line up. The row is the anonymous record { id: int, reference: string } and it will not coerce to a nominal type:

schema shop

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

struct Summary {
    id: int
    reference: string
}

fn f(): []Summary ! QueryError {
    return FROM Booking SELECT { id, reference }?
}

Use a type alias for the record shape instead of a struct, and build a struct explicitly if a nominal type is genuinely required at an API boundary:

schema shop

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

type Row = { id: int, reference: string }

struct Summary {
    id: int
    reference: string
}

fn rows(): []Row ! QueryError {
    return FROM Booking SELECT { id, reference }?
}

fn summaries(): []Summary ! QueryError {
    mut out: []Summary = []
    for r in rows()? {
        out.add(Summary { id: r.id, reference: r.reference })
    }
    return out
}

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

An explicit type at an exported boundary is worth the extra step: it fixes the field spelling, optionality, and numeric types a caller sees, and lets the internal query grow predicates, joins, or unreturned columns without changing the public signature.

Column names are also checked against the model, so a typo is caught here rather than by the database:

schema shop

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

fn f(): []{ id: int } ! QueryError {
    return FROM Booking SELECT { id, bogus }?
}

Computed values

Projection expressions may combine columns, literals, arithmetic, scalar functions, and aggregates. The compiler types each field from SQL expression rules — division may widen, and nullability propagates through optional operands.

schema shop

model Booking {
    @id
    id: int
    passengers: int
    price_each: float
}

type Priced = { id: int, kind: string, total: float }

fn priced(): []Priced ! QueryError {
    return FROM Booking
        SELECT {
            id,
            kind: "booking",
            total: price_each * passengers,
        }?
}

fn main(): void {
    match priced() {
        Ok(rows) => {
            for row in rows { println("${row.kind} ${row.id}: ${row.total}") }
        }
        Err(e) => println("read failed")
    }
}

Aggregates project like any other expression. A whole-table aggregate comes back as a one-row list, so read element zero:

schema shop

model Booking {
    @id
    id: int
    passengers: int
    price_each: float
}

fn stats() {
    return FROM Booking SELECT {
        n: count(),
        heads: sum(passengers),
        mean: avg(price_each),
        cheapest: min(price_each),
        dearest: max(price_each),
    }
}

fn live_count(): int ! QueryError {
    rows := FROM Booking SELECT { n: count() }?
    return rows.get(0)?.n ?? 0
}

fn main(): void {
    match stats() {
        Ok(rows) => println("${rows.len()} stat row(s)")
        Err(e) => println("failed")
    }
    match live_count() {
        Ok(n) => println("${n} bookings")
        Err(e) => println("failed")
    }
}

stats has no declared return type and no ?, so it returns the whole Result and its row type is inferred from the projection.

Add GROUP BY and the projection becomes one row per group — see Aggregates:

schema shop

model Booking {
    @id
    id: int
    status: string
    passengers: int
}

type Bucket = { status: string, heads: int }

fn by_status(): []Bucket ! QueryError {
    return FROM Booking
        GROUP BY status
        SELECT { status, heads: sum(passengers) }?
}

fn main(): void {
    match by_status() {
        Ok(buckets) => {
            for b in buckets { println("${b.status}: ${b.heads}") }
        }
        Err(e) => println("failed")
    }
}

Distinct rows

SELECT DISTINCT { ... } de-duplicates on the projected shape, not on the source model:

schema shop

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

fn statuses(): []{ status: string } ! QueryError {
    return FROM Booking SELECT DISTINCT { status } ORDER BY status?
}

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

Ordering is independent of distinctness, so state it explicitly when callers need a deterministic sequence.

Joins and name collisions

Two joined models can offer the same column name. Qualify with the model name and give each projected field a distinct name of its own:

schema shop

model Schedule {
    @id
    id: int
    vessel: string
}

model Booking {
    @id
    id: int
    schedule_id: int
    reference: string

    BELONGS TO schedule: Schedule VIA schedule_id
}

type ManifestRow = { booking_id: int, reference: string, vessel: string }

fn manifest(): []ManifestRow ! QueryError {
    return FROM Booking
        JOIN Schedule
        SELECT {
            booking_id: Booking.id,
            reference,
            vessel: Schedule.vessel,
        }?
}

fn main(): void {
    match manifest() {
        Ok(rows) => {
            for row in rows { println("${row.reference} on ${row.vessel}") }
        }
        Err(e) => println("failed")
    }
}

Both models have an id; the unqualified reference is unambiguous, so it stays bare. Some joined and advanced query shapes cannot materialize a whole-model carrier at all — if lowering reports that a row shape is unsupported, adding a SELECT with the exact columns the caller needs is usually the fix.

Cost

A projection narrows the row the guest decodes; it does not make the read lazy. A projected FROM still materializes its whole result list, computed expressions run in the engine, and conversion into Atoll fields happens during row decoding.

Select only what the caller consumes — especially for large text or binary columns — but never drop a sort key that callers need for paging or merging. Frames use the projection row as their compile-time schema, and a later collect() decodes host-resident columns into the same named fields.

A composed example

schema shop

model Schedule {
    @id
    id: int
    vessel: string
    departs_at: DateTime
}

model Booking {
    @id
    id: int
    schedule_id: int
    reference: string
    passengers: int
    price_each: float
    cancelled: bool

    BELONGS TO schedule: Schedule VIA schedule_id

    INDEX (schedule_id, reference)
}

type ManifestRow = {
    reference: string,
    vessel: string,
    passengers: int,
    revenue: float,
}

fn manifest(schedule: int, size: int): []ManifestRow ! QueryError {
    return FROM Booking
        JOIN Schedule
        WHERE Booking.schedule_id == schedule
        WHERE cancelled == false
        ORDER BY reference ASC
        LIMIT size
        SELECT {
            reference,
            vessel: Schedule.vessel,
            passengers,
            revenue: price_each * passengers,
        }?
}

fn total_revenue(schedule: int): float ! QueryError {
    mut sum := 0.0
    for row in manifest(schedule, 500)? {
        sum = sum + row.revenue
    }
    return sum
}

fn main(): void {
    match total_revenue(11) {
        Ok(total) => println("sailing revenue ${total}")
        Err(e) => println("manifest unavailable")
    }
}

Four columns cross the ABI per row instead of the full booking and schedule, and ManifestRow is the type every caller sees.