SQL is an input language here, not an execution plane: every statement is compiled — a
SELECT into a maintained query, a write into a row operation, a schema change into a
migration step — and anything the compiler cannot carry is refused by name, with the rewrite in
the message. Nothing is ever accepted partially, because a silently dropped clause is a
statement that does something other than what its author read.
Live queries are a subset of SQLite SELECT, plus a WITH clause for nested results. Writes
are a subset of SQLite DML. Schema changes are SQLite DDL.
Live Queries
SELECT * FROM issue
WHERE closed = 0 AND title LIKE ?
ORDER BY id DESC
LIMIT 50
WITH comments OF comment ON issue_id = id (ORDER BY id DESC LIMIT 10)One SELECT from one table. Optionally ORDER BY, LIMIT, and any number of WITH clauses.
SELECT
*,table.*, or a list of columns.- A column list must include the primary key and the
ORDER BYcolumns. - No renaming (
AS), noDISTINCT, no expressions, except aggregates.
FROM
One table, optionally with an alias. No JOIN: use WITH for related rows and
EXISTS to filter by them.
WHERE
| Comparisons | = != <> < <= > >=, between a column and a value |
| Null | IS NULL, IS NOT NULL |
| Patterns | LIKE, NOT LIKE, ILIKE, NOT ILIKE |
| Lists | IN (…), NOT IN (…) |
| Ranges | BETWEEN a AND b |
| Booleans | IS TRUE, IS FALSE |
| Logic | AND, OR, to any depth |
| Subqueries | EXISTS (…), NOT EXISTS (…) |
A comparison is always a column and a value. a = b between two columns, arithmetic and function
calls are refused. There is no bare NOT; use !=, NOT LIKE, NOT IN, IS NOT or
NOT EXISTS.
EXISTS
Filters by related rows:
SELECT * FROM note
WHERE owner = ?
OR EXISTS (SELECT 1 FROM share
WHERE share.note_id = note.id AND share.user_id = ?)The subquery selects 1 or * from one table, and its WHERE ties it to the outer row with
=. Qualify the outer column (note.id); an unqualified name means the inner table.
To require a number of related rows, add GROUP BY and HAVING count(*):
SELECT * FROM issue
WHERE EXISTS (SELECT 1 FROM comment WHERE comment.issue_id = issue.id
GROUP BY comment.issue_id HAVING count(*) >= 3)ORDER BY and LIMIT
ORDER BY any columns, ASC or DESC. Rindle adds the primary key to the end so the order is
always total. NULL sorts lowest.
LIMIT n keeps the first n rows, and stays at n as rows come and go.
There is no OFFSET. Page from the last row you have instead:
SELECT * FROM issue
WHERE (created, id) > (?, ?)
ORDER BY created, id
LIMIT 50The columns in the tuple must be the start of an ascending ORDER BY. >= includes the row.
WITH
Adds related rows to each result row, as a nested array:
SELECT * FROM issue
WITH comments OF comment ON issue_id = id (
WHERE deleted = 0
ORDER BY id DESC
LIMIT 10
WITH author OF user ON id = author_id
)
WITH ONE assignee OF user ON id = assignee_idtype Issue = {
id: number;
title: string;
comments: { id: number; body: string; author: User[] }[];
assignee: User | null;
};WITH name OF table ON child_column = parent_column (…):
nameis the field on each row.OF tabledefaults toname.ONmatches the related table’s column, on the left, to this row’s column, on the right. Join on several columns withAND.- The parentheses are optional. Inside them goes the related rows’
WHERE,ORDER BY,LIMIT, and moreWITHclauses, nested to any depth. WITH ONEgives a single row, ornull, instead of an array.
WITH clauses come last. Each LIMIT applies per parent row: above, each issue gets its own 10
newest comments.
Inside the parentheses, the related table’s columns are in scope, not the parent’s.
Aggregates
count(*), sum(column) and avg(column) over related rows go in the SELECT list, as a
subquery named with AS. They stay live like everything else:
SELECT *,
(SELECT count(*) FROM comment WHERE comment.issue_id = issue.id)
AS comment_count,
(SELECT sum(points) FROM comment WHERE comment.issue_id = issue.id)
AS total_points,
(SELECT avg(points) FROM comment WHERE comment.issue_id = issue.id)
AS avg_points
FROM issuesum and avg take one column of the related table. count takes only *.
Parameters
?, ?1, or $1, numbered from 1. Values are strings, numbers, booleans and null. Write a
list as one placeholder per item: IN (?, ?, ?). Named parameters are not supported in live
queries.
Not Supported
- SQL-style
JOINis not supported. But seeWITHwhich is almost always more useful. GROUP BY,HAVING, and top-level aggregates likeSELECT count(*)are not supported, but could be added. The query builder API does have these.AUTOINCREMENTis not supported yet. AnINSERTnames its own key; mint ids in your app (crypto.randomUUID(), a ulid, or a counter row).- Everything else currently unsupported.
This subset is the whole SELECT surface, not just the live one. A one-shot read
(read, db.execute) is compiled and run by
the same engine, so it refuses the same shapes with the same message — there is no second,
larger SQL plane to fall back to. Compute what the subset cannot in your own code, over rows it
can return.
Reads
The subset above answers two shapes, and you pick by which call you make:
readanswers the assembled view — each row with itsWITHclauses as nested arrays, the same tree a live query delivers.db.executeanswers a rectangle: columns, with their declared types, and rows in column order. A query with aWITHclause is refused on this route by name, rather than flattened into a cross-product.
Two things a read inherits from the engine’s value model, both worth knowing before you port a schema:
INTEGERis the floating-point column here. OnlyBIGINTandINT8declare an exact 64-bit integer; every other…INT…spelling is a double, so a portedid INTEGER PRIMARY KEYrounds past 2^53. SQLite is the other way round.- An exact-integer column cannot be
SELECTed yet. The wire carries 64-bit integers exactly; it is the query that refuses them, because exact integers are not yet in a maintained query’s footprint. Writes to such a column are fine.
A one-shot read is answered from the store’s latest committed state: it blocks no write, it
does not queue behind an open transaction, and it does not see one’s uncommitted writes. Reading
outside a transaction you are holding is therefore a choice about what you want to see rather than
something to avoid — through the transaction handle you see its own writes, outside it you see the
state before it. The exception is a batch that mixes reads and writes: that is one transaction,
so its reads see its writes and they do hold the writer while they run.
Hot application reads still belong on the live query plane, which keeps its answer up to date instead of recomputing it.
Writes
db.execute, batch and transaction take INSERT, UPDATE and
DELETE, with a WHERE that may be a predicate over the table’s own columns.
| Statement | |
|---|---|
INSERT INTO t (cols) VALUES (…) |
one row, or several tuples in one statement |
… ON CONFLICT DO NOTHING, INSERT OR IGNORE |
insert if absent |
… ON CONFLICT DO UPDATE SET c = excluded.c, …, INSERT OR REPLACE |
upsert |
UPDATE t SET … WHERE <predicate> |
update every matching row |
DELETE FROM t WHERE <predicate> |
delete every matching row |
DELETE FROM t |
delete every row |
A WHERE is built from a column compared with a literal or a parameter, and the comparisons
compose to any depth:
= != <> < <= > >= |
compare a column with a value |
IS NULL, IS NOT NULL |
the only comparisons that match a NULL |
IN (…), NOT IN (…) |
a list of values |
BETWEEN x AND y, NOT BETWEEN |
an inclusive range |
AND, OR, NOT, ( … ) |
combine them |
rowsAffected is the number of rows the WHERE matched.
await db.execute({ sql: "DELETE FROM viewer WHERE seen < ?", params: [cutoff] });
await db.execute({
sql: "UPDATE issue SET status = ? WHERE project = ? AND status IN (?, ?)",
params: ["triaged", project, "new", "reopened"],
});A WHERE that names the whole primary key by equality costs a key lookup, whatever else it
says. So the guard below is a single-row write, not a scan:
// One lookup. The `status` term is checked against the row the key found.
await db.execute({
sql: "UPDATE job SET status = 'working' WHERE id = ? AND status = 'pending'",
params: [id],
});Anything else reads every row of the table. No secondary index is used yet, so
DELETE FROM event WHERE at < ? scans event even with an index on at. That is fine for a
sweep and wrong for a hot path — put a key in the WHERE where you can, and treat an
unkeyed predicate as a full pass over the table.
NULL follows SQL
A comparison with NULL is unknown, and a WHERE keeps only the rows it is true for. So
WHERE tag != 'a' does not match a row whose tag is NULL, WHERE tag = NULL matches
nothing, and IS NULL is the only way to reach it — exactly as in SQLite. The write plane is
graded against SQLite executing the same statement over the same rows, and the whole table is
compared afterwards.
What is refused
SET takes literal values and parameters, not expressions over columns: SET stock = stock - 1
is refused, and so is CASE WHEN. Read the row, compute, and write the value back — in a
transaction when it matters that nothing changed in between. A
read inside a transaction sees that transaction’s own writes.
A predicate compares a column with a value and nothing else. These are refused by name:
| Refused | Instead |
|---|---|
SET c = <expression> |
read the row, compute, write the value |
LIKE, GLOB, REGEXP, MATCH in a WHERE |
select the rows it matches, then write them by key |
A subquery anywhere, including IN (SELECT …) |
the same — reads are not restricted |
INSERT … SELECT |
read the source rows, then insert the values |
a = b between two columns |
read and compare in your own code |
WHERE id as a truthiness test |
write the comparison out: WHERE id != 0 |
Write ON CONFLICT with no target: a keyed op conflicts on the primary key and nothing else,
so ON CONFLICT (id) DO NOTHING is refused and ON CONFLICT DO NOTHING is the spelling. A
DO UPDATE must give every non-key column a value, because an upsert replaces the whole row.
RETURNING is refused — a write answers with its watermark, not with rows. Read the row back.
An INSERT must name the key column and give it a value: an insert is addressed by its key,
so a row with no key has nowhere to go. AUTOINCREMENT is not supported yet, and there is no
rowid — mint ids in your app (a uuid, a ulid, a counter row you read and write by key).
Every table needs a primary key; a write to a table without one is refused, because the store has no such table to write to.
RETURNING, a common-table-expression WITH, BEGIN/COMMIT (use
transaction), PRAGMA, ATTACH and VACUUM are not accepted.
Guards
UPDATE note SET … WHERE id = ? AND owner = ?, checking rowsAffected for a 403, works: the
predicate is evaluated by the writer against the rows the statement commits against, so the
guard cannot be raced by a concurrent change of the owner.
Parameters are ? or :name. Integers are sent exactly, so one past 2^53 survives the wire.
Schema Changes
Only through migrate:
CREATE TABLE,DROP TABLEALTER TABLE … ADD COLUMN,ALTER TABLE … DROP COLUMNALTER TABLE … RENAME TO,ALTER TABLE … RENAME COLUMN … TO …CREATE INDEX,DROP INDEX
New secondary indexes in a local store contain keys alone by default. A read through one retrieves the full row from the primary index. This saves space and avoids secondary writes when an edit changes no indexed column.
For a read-heavy query, a covering index can avoid those primary lookups:
CREATE INDEX note_owner ON note (owner) WITH (covering = true);A covering index stores another copy of the full row and updates that copy on non-key edits.
A rename is carried as a rename, all the way to every follower — which is why it is written rather than diffed: a diffed rename is indistinguishable from a drop plus an add, and replaying it would throw the column’s data away.
Every table needs a primary key. Refused, each by name and with the reason in the message:
| Refused | Because |
|---|---|
AUTOINCREMENT |
Not supported yet. The store assigns no keys; an INSERT names its own |
UNIQUE, CHECK, FOREIGN KEY, REFERENCES |
Nothing downstream enforces a constraint, so accepting the word would promise a check no store makes |
| An in-place retype | There is none, by design: add a column, rewrite in a data migration, drop the old one. SQLite has no ALTER COLUMN either |
DEFAULT in CREATE TABLE |
A new table has no rows for it to fill. An INSERT that leaves a column out stores NULL, so write the value. ALTER TABLE … ADD COLUMN … DEFAULT takes one, to fill the existing rows |
Generated columns, expression DEFAULTs, expression indexes |
Each needs the store to evaluate an expression; it evaluates none, and a follower that could not reproduce one would diverge |
COLLATE |
Ordering and equality are the engine’s own, and are deliberately two functions that disagree on null |
blob columns |
The value model has no blob |
IF NOT EXISTS / IF EXISTS |
A migration’s (id, checksum) pair is the stronger mechanism: re-sending the same id is already a no-op |
STRICT |
It names type enforcement, and this tree’s type discipline is real but silent, so the keyword would be a promise |
Views, triggers, virtual and temporary tables, CREATE TABLE … AS SELECT |
There is no step for them to become. A maintained query is registered as a query, not stored |