The SQL dialect

Larva accepts real SQL strings. The dialect is a deliberately small, fully documented subset — small enough that an agent’s system prompt can enumerate it, and small enough that the parser produces precise, correcting error messages instead of generic syntax errors. It covers roughly 90% of the queries small applications actually write.

Supported

SELECT

SELECT DISTINCT customers.name, orders.total * 1.1 AS withTax
FROM orders
INNER JOIN customers ON orders.customerId = customers.id
WHERE orders.status = 'paid' AND orders.total BETWEEN 50 AND 500
ORDER BY withTax DESC
LIMIT 10 OFFSET 20

• Full scalar expressions in the select list: arithmetic, || concatenation, CASE WHEN (searched and simple), CAST(x AS text/integer/real/boolean).
• WHERE with =, !=, <, >, <=, >=, AND, OR, NOT, IN, BETWEEN, LIKE, IS NULL.
• ORDER BY source columns or select-item aliases; LIMIT / OFFSET.
• INNER JOIN and LEFT JOIN on equality predicates — any number of tables, including self-joins (give each occurrence its own alias: FROM staff e JOIN staff m ON e.managerId = m.id). Each ON compares a column of the joined table with a column of any table already in scope.

Uncorrelated subqueries

SELECT name FROM customers
WHERE id IN (SELECT customerId FROM orders WHERE total > 500)
 
SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders)

The inner query runs first against the same snapshot; its result feeds the outer plan as plain values (so an IN (SELECT …) list even participates in zone-map pruning). Scalar subqueries must yield one row and one column. NULLs in the subquery result are ignored — so NOT IN (SELECT …) behaves the way you intend even when the inner column has NULLs, instead of inheriting SQL’s NULL-poisoning trap. Correlated subqueries — ones that reference the outer query’s tables — are not supported and never will be; the error says so and points at JOIN.

Scalar functions

UPPER LOWER LENGTH TRIM ROUND ABS COALESCE NULLIF IFNULL REPLACE CEIL FLOOR MOD SUBSTR

Dates — cheap by construction

Timestamps are ISO 8601 text, so date functions are string operations and range filters compare lexicographically:

SELECT DATE(createdAt) AS day, SUM(total) AS revenue
FROM orders GROUP BY day ORDER BY day

NOW() / CURRENT_TIMESTAMP, DATE(x), STRFTIME('%Y-%m', x) (%Y %m %d %H %M %S).

Grouping

GROUP BY over full expressions or select aliases, with COUNT / SUM / AVG / MIN / MAX / GROUP_CONCAT(x, sep) — over arbitrary expressions, including COUNT(DISTINCT col) — and HAVING:

SELECT customerId, SUM(total) AS revenue,
  CASE WHEN SUM(total) > 500 THEN 'vip' ELSE 'standard' END AS tier
FROM orders GROUP BY customerId HAVING revenue > 100

JSON — over text columns

Store JSON with t.text() + JSON.stringify, then query inside it (SQLite json1 semantics):

SELECT JSON_EXTRACT(payload, '$.user.name') AS who FROM events
SELECT payload ->> 'status' AS status FROM events

Writes

INSERT INTO customers (name, email) VALUES ('Ada', 'ada@example.com') RETURNING *

• Multi-row INSERT, RETURNING on every write statement.
• Upsert: ON CONFLICT (col) DO NOTHING / DO UPDATE SET col = excluded.col. The conflict target must be the primary key, a UNIQUE column, or the exact columns of a composite unique — ON CONFLICT (userId, feature) DO UPDATE ….
• UPDATE … WHERE, DELETE … WHERE — without a WHERE clause they are rejected unless you pass { allowFullTable: true } (the most common catastrophic agent mistake becomes a specific error instead).
• CREATE TABLE / DROP TABLE (agents doing interactive setup use this; a code-first schema stays authoritative).
• ALTER TABLE t ADD COLUMN name type — additive only: a plain nullable column, no rewrite of existing data. Rows written before the ALTER read the new column as NULL; backfill with UPDATE if needed. (With a code-first schema you rarely write this by hand: adding a plain column to defineSchema auto-migrates at connect.) DROP COLUMN and RENAME are rejected — they need a migration story that respects time travel.

Secondary indexes

CREATE INDEX ON orders (customerId)
DROP INDEX ON orders (customerId)

An index makes =, IN, and range filters on a non-key column prune storage reads instead of scanning the table. One index per column, addressed by column (an index name parses and is ignored); declare it in code with t.text().index() — flags sync automatically at connect, and creating one backfills from existing data in one commit. Indexes are performance-only: CREATE UNIQUE INDEX is rejected (uniqueness belongs to the table), and a stale or missing index can never change results — only how much gets read. The primary key and the .partitionBy() column never need one.

Not supported — on purpose

Correlated subqueries, derived tables (subqueries in FROM), window functions, UNION, RIGHT/FULL/CROSS joins, DROP COLUMN/RENAME, views, triggers, nested aggregates. Each exclusion is either rarely emitted by agents writing conservative SQL, or expressible in application code at Larva’s scale.

Every rejection names the feature and says what to do instead, and near-miss spellings are redirected (CONCAT → ||, SUBSTRING → SUBSTR, DATE_TRUNC → DATE/STRFTIME):

UNSUPPORTED_FEATURE: window functions are not supported in Larva v1;
compute windows in application code — tables at this scale fit in memory

This is a design feature, not a limitation dressed up — agents self-correct well when errors are specific.

The bar for growing the dialect

Recorded in design decisions: a construct joins the dialect when agents writing conservative SQL emit it routinely, and it executes within the existing engine shape — in-memory relational algebra after pruning, one atomic commit per write. HAVING, DISTINCT, scalar functions, CASE, and upsert cleared that bar first; uncorrelated subqueries, 3+ table joins, self-joins, and additive ALTER TABLE were re-judged against it in July 2026 and shipped in 2.5. Correlated subqueries and window functions change the engine shape, so they stay out regardless of demand; the answer to needing them is larva export.

Injection safety

The db.sql tagged template parameterizes every ${...} interpolation — values are never string-concatenated into SQL. The parser also rejects multiple statements per string, closing the classic injection-stacking vector.