Skip to content

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.

{
"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.

{
"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.

{
"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 + 1 or SET name = LOWER(name);
  • WHERE using the same conditions as SELECT;
  • 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.

{
"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.

SELECT [DISTINCT] select_list
FROM 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, or u.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).

age = 18
age != 18
age <> 18
age < 18
age <= 18
age > 18
age >= 18
age BETWEEN 18 AND 30
age NOT BETWEEN 18 AND 30
city IN ('London', 'New York')
city NOT IN ('London')
deleted_at IS NULL
deleted_at IS NOT NULL
name 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.

Expressions can be used in SELECT and inside CASE:

age + 1
score * 2
price / 100
name || '!'
CASE WHEN age >= 65 THEN 'senior'
WHEN age >= 18 THEN 'adult'
ELSE 'minor' END

Supported operators:

  • +, -, *, /;
  • string concatenation with ||;
  • comparisons =, !=, <>, <, <=, >, >=;
  • boolean AND, OR, NOT;
  • IN, NOT IN, BETWEEN, NOT BETWEEN, IS NULL, IS NOT NULL in 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.

SELECT team, COUNT(*) AS n, SUM(score) AS total
FROM users
WHERE active = TRUE
GROUP BY team
HAVING SUM(score) > 100
ORDER BY total DESC
LIMIT 20

Rules:

  • An aggregate function without GROUP BY returns one row.
  • Every non-aggregate selected field must appear in GROUP BY.
  • SELECT * cannot be combined with GROUP BY or aggregate functions.
  • ORDER BY in a grouped query must reference a selected output column. A GROUP BY field 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>, and max_<field>; duplicates get a numeric suffix.
  • HAVING can reference selected columns, selected aggregate aliases, or aggregate calls. An unselected aggregate used in HAVING is computed as a hidden column and removed before returning results.
  • HAVING comparisons must compare a column/aggregate with a literal or parameter. Comparing two aggregates directly is not supported yet.
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.

  • SELECT * returns full documents, including _id.
  • An explicit select list returns only the selected fields.
  • _id is 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 city returns {"city": "..."}.
  • A field reference that does not exist is omitted from the output object, matching JSON document projection behavior.

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.

  • Simple field-only SELECT statements use find directly. They are not subject to NONODB_AGGREGATE_MAX_DOCS and benefit from normal early exit.
  • DISTINCT, grouping, computed columns, and HAVING use the aggregation engine. The NONODB_AGGREGATE_MAX_DOCS limit applies to intermediate aggregation documents; increase it if a large report needs more rows.
  • A WHERE clause becomes a leading aggregation $match, so secondary indexes, _id ranges, and index-backed sorting can be used by the normal planner.
  • INSERT, UPDATE, and DELETE are executed by the daemon’s single writer. Each SQL write statement is isolated in its own transaction; a failed multi-row INSERT, UPDATE, or DELETE rolls back entirely.
  • SQL writes accept only standard collections, not vault collections.
  • SQL writes are not allowed inside a read batch; use a normal sql request.
  • CREATE, DROP, transactions, joins, subqueries, RETURNING, INSERT ... SELECT, and UPDATE ... FROM are not supported.
  • One statement reads or writes one collection.
  • Scalar expressions in a grouped SELECT are not supported yet. Use a selected GROUP BY field or an aggregate alias.
  • Ordering a grouped query by a GROUP BY field that is not selected is not supported yet.
  • A missing collection returns an empty results array for SELECT, consistent with aggregate.
  • SELECT and explain_sql are available in read batch operations 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).

The collection name is parsed from the statement before execution.

  • SELECT and explain_sql require read:<collection>.
  • INSERT, UPDATE, and DELETE require write:<collection>.

A matching wildcard scope also works. Parse errors return invalid_sql with HTTP 400.

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,
)
Terminal window
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"]}'
Terminal window
nonodb-cli sql "SELECT team, COUNT(*) AS n FROM users GROUP BY team"
nonodb-cli sql "SELECT name FROM users WHERE age >= $1" --param 18
nonodb-cli sql --file report.sql
nonodb-cli shell

See Command-line interface for all options.

GET /metrics includes:

  • sql_select_total
  • sql_insert_total
  • sql_update_total
  • sql_delete_total
  • sql_errors_total
  • sql_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.

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.