SQL queries and writes
NonoDB supports a practical SQL subset over one collection:
- read-only
SELECT; - one-collection
INSERT; - one-collection
UPDATE; - one-collection
DELETE.
SQL is a translation layer on top of the normal storage paths, not a second
storage engine. SELECT uses the same filters, indexes, TTL handling, LMDB
snapshots, aggregation limits, and authorization as the JSON API. INSERT and
UPDATE run through the normal single writer and one LMDB write transaction, so
a statement is atomic and never bypasses indexes, TTL, history, or vault checks.
Use SQL for reporting jobs, BI tools, ad-hoc operators, and simple maintenance.
Use the JSON API for find_and_modify, upserts, pipeline updates, joins, and
features such as full-text, fuzzy, geo, and vector search.
SELECT
Section titled “SELECT”{ "op": "sql", "query": "SELECT name, age FROM users WHERE age >= $1 ORDER BY age DESC LIMIT 10", "params": [18]}Response:
{ "ok": true, "result": { "results": [ {"name": "Ada", "age": 36}, {"name": "Grace", "age": 31} ] }}params is optional and defaults to an empty array. Positional placeholders can
be ? or $1-style; $1 is the first parameter. Do not mix the two styles in
one statement.
INSERT
Section titled “INSERT”{ "op": "sql", "query": "INSERT INTO users (name, age, team) VALUES ('Ada', $1, 'core'), ('Grace', 31, 'core')", "params": [36]}Response:
{ "ok": true, "result": { "results": [ { "inserted": 2, "inserted_ids": ["...", "..."] } ] }}INSERT requires a column list. Columns may be dotted paths such as
profile.city; missing intermediate objects are created. Values may be string,
number, boolean, NULL, or positional parameters. A multi-row INSERT is
atomic: if any row fails, the whole statement is rolled back.
_id may be supplied explicitly with a string value. If omitted, NonoDB
generates the normal UUID _id.
UPDATE
Section titled “UPDATE”{ "op": "sql", "query": "UPDATE users SET age = $1, active = TRUE WHERE team = $2", "params": [37, "core"]}Response:
{ "ok": true, "result": { "results": [ { "matched": 3, "modified": 3 } ] }}UPDATE supports:
- dotted assignment paths, e.g.
SET profile.level = 9; - literal values, positional parameters, and document expressions such as
SET age = age + 1orSET name = LOWER(name); WHEREusing the same conditions asSELECT;- a table alias, e.g.
UPDATE users AS u SET u.age = $1 WHERE u.team = $2.
UPDATE affects every matching document. Expressions are evaluated per
document; statements that do not reference document fields use the normal
single-statement update path. There is no LIMIT on UPDATE, no RETURNING,
and no upsert. Use the JSON API for those cases.
All assignments are applied in one writer transaction and $set semantics are
used. matched is the number of matching documents; modified is the number
whose stored value changed.
DELETE
Section titled “DELETE”{ "op": "sql", "query": "DELETE FROM users WHERE age < $1", "params": [18]}Response:
{ "ok": true, "result": { "results": [ { "deleted": 3 } ] }}DELETE supports the same WHERE conditions and optional table aliases as
SELECT/UPDATE. It deletes every matching document in one writer transaction.
There is no LIMIT, RETURNING, or truncate shortcut.
Supported SELECT syntax
Section titled “Supported SELECT syntax”SELECT [DISTINCT] select_listFROM collection [AS alias][WHERE condition][GROUP BY field [, field ...]][HAVING condition][ORDER BY field [ASC | DESC] [, ...]][LIMIT count [OFFSET offset]][OFFSET offset]The select_list is either * or a comma-separated list of:
- a field path such as
name,address.city, oru.name; - an aggregate function:
COUNT(*),COUNT(field),SUM(field),AVG(field),MIN(field),MAX(field); - a scalar expression (see below);
- an optional alias:
name AS person,COUNT(*) AS n,age + 1 AS next_age.
The FROM table is a collection name. A table alias is optional and is
stripped from qualified field names (u.name becomes name).
Conditions
Section titled “Conditions”age = 18age != 18age <> 18age < 18age <= 18age > 18age >= 18age BETWEEN 18 AND 30age NOT BETWEEN 18 AND 30city IN ('London', 'New York')city NOT IN ('London')deleted_at IS NULLdeleted_at IS NOT NULLname LIKE 'A%'name NOT LIKE '%test%'Conditions combine with AND, OR, NOT, and parentheses. Precedence is
NOT, then AND, then OR.
LIKE is case-sensitive. % matches any sequence and _ matches one
character; the pattern is anchored to the whole value. Other regex
metacharacters are escaped, so LIKE 'a.b' matches the literal dot. Use the
JSON API’s $regex for advanced regular expressions.
Scalar expressions
Section titled “Scalar expressions”Expressions can be used in SELECT and inside CASE:
age + 1score * 2price / 100name || '!'CASE WHEN age >= 65 THEN 'senior' WHEN age >= 18 THEN 'adult' ELSE 'minor' ENDSupported operators:
+,-,*,/;- string concatenation with
||; - comparisons
=,!=,<>,<,<=,>,>=; - boolean
AND,OR,NOT; IN,NOT IN,BETWEEN,NOT BETWEEN,IS NULL,IS NOT NULLin conditional expressions.
Supported functions:
| Function | Notes |
|---|---|
LOWER(s), UPPER(s) |
Unicode case conversion |
LENGTH(s) |
Character count; arrays return their length |
SUBSTR(s, start, length) / SUBSTRING |
Zero-based character offsets |
TRIM(s), LTRIM(s), RTRIM(s) |
Whitespace trimming |
COALESCE(a, b, ...) |
First non-null value |
NULLIF(a, b) |
NULL when a = b, otherwise a |
ABS(n), CEIL(n) / CEILING(n), FLOOR(n), ROUND(n [, digits]) |
Numeric helpers |
Expressions without an alias use generated output names (expr1, expr2, …).
Use AS for stable column names.
Grouping and aggregates
Section titled “Grouping and aggregates”SELECT team, COUNT(*) AS n, SUM(score) AS totalFROM usersWHERE active = TRUEGROUP BY teamHAVING SUM(score) > 100ORDER BY total DESCLIMIT 20Rules:
- An aggregate function without
GROUP BYreturns one row. - Every non-aggregate selected field must appear in
GROUP BY. SELECT *cannot be combined withGROUP BYor aggregate functions.ORDER BYin a grouped query must reference a selected output column. AGROUP BYfield counts only when it is selected (possibly under an alias).- Aggregate output columns use the alias when provided. Otherwise generated
names are
count,sum_<field>,avg_<field>,min_<field>, andmax_<field>; duplicates get a numeric suffix. HAVINGcan reference selected columns, selected aggregate aliases, or aggregate calls. An unselected aggregate used inHAVINGis computed as a hidden column and removed before returning results.HAVINGcomparisons must compare a column/aggregate with a literal or parameter. Comparing two aggregates directly is not supported yet.
DISTINCT
Section titled “DISTINCT”SELECT DISTINCT team FROM users ORDER BY team;SELECT DISTINCT LOWER(city) AS city FROM users WHERE city IS NOT NULL;SELECT DISTINCT team, COUNT(*) AS n FROM users GROUP BY team HAVING COUNT(*) > 1;DISTINCT is applied after WHERE, grouping, and HAVING, and before
ORDER BY, OFFSET, and LIMIT. It can be combined with GROUP BY and
HAVING.
SELECT DISTINCT * is allowed but is effectively a no-op because SELECT *
always includes the unique _id.
COUNT(DISTINCT field) is supported and ignores missing/NULL values.
SUM(DISTINCT ...), AVG(DISTINCT ...), MIN(DISTINCT ...), and
MAX(DISTINCT ...) are not supported yet.
Projection semantics
Section titled “Projection semantics”SELECT *returns full documents, including_id.- An explicit select list returns only the selected fields.
_idis excluded unless it is selected explicitly.- Nested fields are selected by their dotted path. Output keys use the selected
name or alias; for example,
SELECT address.city AS cityreturns{"city": "..."}. - A field reference that does not exist is omitted from the output object, matching JSON document projection behavior.
EXPLAIN
Section titled “EXPLAIN”Use explain_sql to inspect the selected plan without executing the query:
{ "op": "explain_sql", "query": "SELECT name FROM users WHERE age >= $1 LIMIT 10", "params": [18]}Simple field-only selects delegate to the normal find planner and return the
same access_path, index, bounds, candidates, and actual counts as
explain. Grouped, distinct, and computed queries return a SQL pipeline plan:
{ "collection": "users", "access_path": "aggregation_group", "distinct": false, "grouped": true, "has_having": true, "has_filter": false, "pipeline": [ {"$group": {"_id": "$team", "n": {"$count": {}}}}, {"$project": {"_id": 0, "team": "$_id", "n": "$n"}}, {"$match": {"n": {"$gt": 1}}} ]}explain_sql accepts only SELECT.
Execution and limits
Section titled “Execution and limits”- Simple field-only
SELECTstatements usefinddirectly. They are not subject toNONODB_AGGREGATE_MAX_DOCSand benefit from normal early exit. DISTINCT, grouping, computed columns, andHAVINGuse the aggregation engine. TheNONODB_AGGREGATE_MAX_DOCSlimit applies to intermediate aggregation documents; increase it if a large report needs more rows.- A
WHEREclause becomes a leading aggregation$match, so secondary indexes,_idranges, and index-backed sorting can be used by the normal planner. INSERT,UPDATE, andDELETEare executed by the daemon’s single writer. Each SQL write statement is isolated in its own transaction; a failed multi-rowINSERT,UPDATE, orDELETErolls back entirely.- SQL writes accept only standard collections, not vault collections.
- SQL writes are not allowed inside a read
batch; use a normalsqlrequest. CREATE,DROP, transactions, joins, subqueries,RETURNING,INSERT ... SELECT, andUPDATE ... FROMare not supported.- One statement reads or writes one collection.
- Scalar expressions in a grouped
SELECTare not supported yet. Use a selectedGROUP BYfield or an aggregate alias. - Ordering a grouped query by a
GROUP BYfield that is not selected is not supported yet. - A missing collection returns an empty
resultsarray forSELECT, consistent withaggregate. SELECTandexplain_sqlare available in readbatchoperations and are cacheable in clients that enable the read cache. Write statements are never cached.- SQL parse and planning failures return
invalid_sql(HTTP 400).
Authorization
Section titled “Authorization”The collection name is parsed from the statement before execution.
SELECTandexplain_sqlrequireread:<collection>.INSERT,UPDATE, andDELETErequirewrite:<collection>.
A matching wildcard scope also works. Parse errors return invalid_sql with
HTTP 400.
Client examples
Section titled “Client examples”Python:
rows = db.sql( "SELECT DISTINCT team FROM users WHERE age >= $1 ORDER BY team", [18],)plan = db.explain_sql( "SELECT team, COUNT(*) AS n FROM users GROUP BY team HAVING COUNT(*) > 1")inserted = db.sql( "INSERT INTO users (name, age) VALUES ($1, $2)", ["Ada", 36],)updated = db.sql( "UPDATE users SET age = $1 WHERE name = $2", [37, "Ada"],)deleted = db.sql("DELETE FROM users WHERE age < $1", [18])Rust:
let rows = db.sql( "SELECT team, COUNT(*) AS n FROM users GROUP BY team HAVING COUNT(*) > $1", &[json!(1)],)?;let plan = db.explain_sql("SELECT * FROM users WHERE age >= $1", &[json!(18)])?;let inserted = db.sql( "INSERT INTO users (name, age) VALUES ($1, $2)", &[json!("Ada"), json!(36)],)?;Node.js:
const rows = await db.sql( "SELECT DISTINCT team FROM users GROUP BY team HAVING COUNT(*) > 1",);const plan = await db.explainSql('SELECT * FROM users WHERE age >= $1', [18]);const inserted = await db.sql( 'INSERT INTO users (name, age) VALUES ($1, $2)', ['Ada', 36],);Go:
rows, err := db.SQL(ctx, "SELECT team, COUNT(*) AS n FROM users GROUP BY team HAVING COUNT(*) > $1", 1,)plan, err := db.ExplainSQL(ctx, "SELECT * FROM users WHERE age >= $1", 18)inserted, err := db.SQL(ctx, "INSERT INTO users (name, age) VALUES ($1, $2)", "Ada", 36,)Raw HTTP
Section titled “Raw HTTP”curl -s --unix-socket /run/nonodb.sock \ -H 'content-type: application/json' \ -X POST http://localhost/v1/exec \ -d '{"op":"sql","query":"UPDATE users SET age = ? WHERE name = ?","params":[37,"Ada"]}'nonodb-cli sql "SELECT team, COUNT(*) AS n FROM users GROUP BY team"nonodb-cli sql "SELECT name FROM users WHERE age >= $1" --param 18nonodb-cli sql --file report.sqlnonodb-cli shellSee Command-line interface for all options.
Metrics and slow SQL
Section titled “Metrics and slow SQL”GET /metrics includes:
sql_select_totalsql_insert_totalsql_update_totalsql_delete_totalsql_errors_totalsql_duration_ns_total
Set NONODB_SQL_SLOW_MS to a positive number to log statements slower than that
threshold. The log includes the statement category, collection, duration, and
success flag; it does not include parameters or document bodies.
Error codes
Section titled “Error codes”SQL parse and planning failures return invalid_sql (HTTP 400), for example:
{ "ok": false, "error": { "code": "invalid_sql", "message": "invalid SQL: expected keyword `SET`" }}Missing parameters, unsupported clauses, malformed values, invalid
GROUP BY/HAVING/ORDER BY references, failed write statements, and write
attempts inside a read batch all use this code.