Skip to content
Contents

SQL

What a live query can say

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 BY columns.
  • No renaming (AS), no DISTINCT, 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 50

The 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_id
type Issue = {
  id: number;
  title: string;
  comments: { id: number; body: string; author: User[] }[];
  assignee: User | null;
};

WITH name OF table ON child_column = parent_column (…):

  • name is the field on each row. OF table defaults to name.
  • ON matches the related table’s column, on the left, to this row’s column, on the right. Join on several columns with AND.
  • The parentheses are optional. Inside them goes the related rows’ WHERE, ORDER BY, LIMIT, and more WITH clauses, nested to any depth.
  • WITH ONE gives a single row, or null, 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 issue

sum 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 JOIN is not supported. But see WITH which is almost always more useful.
  • GROUP BY, HAVING, and top-level aggregates like SELECT count(*) are not supported, but could be added. The query builder API does have these.
  • AUTOINCREMENT is not supported yet. An INSERT names 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:

  • read answers the assembled view — each row with its WITH clauses as nested arrays, the same tree a live query delivers.
  • db.execute answers a rectangle: columns, with their declared types, and rows in column order. A query with a WITH clause 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:

  • INTEGER is the floating-point column here. Only BIGINT and INT8 declare an exact 64-bit integer; every other …INT… spelling is a double, so a ported id INTEGER PRIMARY KEY rounds 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 TABLE
  • ALTER TABLE … ADD COLUMN, ALTER TABLE … DROP COLUMN
  • ALTER 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