Advanced

The integrated query AST covers advanced SQL without falling back to raw strings. Every form remains checked against its datasource engine.

Status

Subqueries

Predicates can use scalar, IN, and EXISTS subqueries. An IN subquery must project exactly one column:

rows := FROM Booking
    WHERE customer_id in (
        FROM Customer
        WHERE active
        SELECT { id }
    )?

Correlated references and derived-table joins are supported where the renderer can preserve scope and result shape.

A scalar subquery must produce one column and is used where a scalar expression is expected. EXISTS cares only whether a row is present. An IN subquery compares against each value of its single projected column. Aliases establish the scope used to disambiguate fields in correlated or joined queries.

Sets

Compatible reads can be combined with UNION, UNION ALL, INTERSECT, and EXCEPT. Branch projection arity and column types must agree. Ordering and paging apply at the compound-query level under engine rules.

UNION removes duplicates; UNION ALL preserves them. INTERSECT keeps rows present in both branches, and EXCEPT keeps rows present only in the left branch. A set expression still has one checked result-row type.

CTEs

WITH binds a read for the following main read; WITH RECURSIVE supports recursive shapes on PostgreSQL and MySQL. Turso currently rejects recursive CTEs. CTE bodies are checked with the same clause and engine gates as a top level query.

A CTE name is visible to the main query and later bindings in the same WITH list. Recursive CTEs require an anchor and recursive branch with compatible columns. They are not a runtime collection or a way to issue several statements.

Windows

Window functions support OVER, partition keys, ordering, and ROWS or RANGE frames:

ranked := FROM Booking
    SELECT {
        id
        rank: row_number() OVER (
            PARTITION BY customer_id
            ORDER BY total DESC
        )
    }?

Function and frame support varies by dialect.

The frame type determines which rows precede, follow, or belong to the current row. ROWS counts physical rows; RANGE groups peers by the ordering value. The compiler validates the function, partition/order expressions, and engine feature matrix before rendering.

Conditional queries

When engines require different whole-query shapes, select a branch at compile time:

rows := when Postgres {
    FROM Booking
        WHERE archived == false
        FOR UPDATE
} when MySQL {
    FROM Booking
        WHERE archived == false
        FOR UPDATE
} else {
    FROM Booking
        WHERE archived == false
}?

The queried model’s datasource engine chooses the first matching branch. Other branches are parsed but not type-checked. else is optional, but compilation fails if no branch matches and no fallback exists. Use this form for structural differences; use engine-specific @sql.function bodies when only a scalar SQL spelling differs.

Search

Full-text search and indexes are implemented for PostgreSQL and Turso through portable compiler-known functions and USING fts model indexes. Because index support is essential to that contract, unsupported engines reject it.

Dense vector columns use @sql.vector(N) on []f32. Distance metrics and exact nearest-neighbor queries are supported on PostgreSQL/AlloyDB and Turso; ANN index methods differ, and unsupported storage engines reject vector queries. Index acceleration may warn rather than fail when an exact scan is still correct.

Vector dimensions are part of the model type contract. Query vectors and stored vectors must have compatible dimensions, and distance metric/index combinations are checked per engine. Exact nearest-neighbor ordering can remain correct without a supported ANN index, although it may scan more data.

Locking

FOR UPDATE and SKIP LOCKED support worker and transactional locking patterns on PostgreSQL/MySQL:

job := ONE FROM Job
    WHERE status == pending
    ORDER BY priority DESC
    FOR UPDATE SKIP LOCKED?

Turso and DataFusion reject the clause because they cannot provide equivalent row-lock semantics; the compiler does not silently omit it.

Locking belongs inside a transaction when the row must remain protected through a subsequent read or write. SKIP LOCKED lets competing workers ignore rows already claimed instead of waiting.

Escape hatches

Arbitrary raw SQL, raw query expressions, JSON operators, table-valued functions, and general array operators remain planned. If a query cannot be lowered into an executable statement, compilation fails rather than returning an empty placeholder result.

Prefer a checked @sql.function declaration for a missing scalar or aggregate function. It preserves argument binding, result typing, and engine gates. There is deliberately no general string-concatenation path from user input to an executable query.