the collection query language

lmq

Read stored data from a script. Safe mode, read-only by construction, and it needs the host's db bridge — which means POST /api/lmx/run or Studio's LMX Playground, not the CLI.

`lmq` — the collection query language

Read stored data from a script. Safe mode, read-only by construction, and it needs the host's db bridge — which means POST /api/lmx/run or Studio's LMX Playground, not the CLI.

FROM orders
  WHERE status IN ("paid", "shipped") AND createdAt > days_ago(30)
  SELECT id, total, buyer.city
  ORDER BY total DESC
  LIMIT 20

Why it isn't SQL

Three reasons, and none of them is taste.

Both storage backends are collection-oriented. LibsqlAdapter and JsonFileAdapter behind the

dynamic connector both expose read(collection, query). A dialect written for one would not run on the other.

Every gate in the tree is keyed by collection. createDynamicCrudRouter takes

protectedCollections, adminOnlyCollections and staffOnlyCollections. lmq reuses exactly those lists. A SQL surface would be gated by table, and the two would drift.

There is no raw-SQL path against leumas.db today. Adding one for a scripting language would

have made the first one a side door around all of the above.

So lmq reads the same rows /db reads, through the same adapter, under the same rules.

Statements

A script is a sequence of statements. A statement starts at a line beginning with FROM or COLLECTIONS and runs until the next one, so clauses may be indented on their own lines or written inline — both parse identically. # starts a comment, except inside a string.

Each statement prints its result. The script's return value is the last statement's rows, the way every query console behaves.

`COLLECTIONS`

COLLECTIONS

Lists what you may query. Denied collections are not listed — a script cannot even learn they exist.

`FROM …`

Clause	Form	Notes
`FROM`	`FROM <collection>`	starts the statement
`WHERE`	expression	see below
`SELECT`	`SELECT a, b, nested.c`	omitted → the whole row
`ORDER BY`	`ORDER BY <path> [ASC\	DESC]`	`ASC` is the default
`LIMIT` / `OFFSET`	integers	`LIMIT` is clamped to 1000
`COUNT`	`COUNT` or `COUNT BY <path>`
`SUM` `AVG` `MIN` `MAX`	`<agg> <path> [BY <path>]`	non-numeric values are skipped

A statement is either a SELECT or an aggregate, never both.

`WHERE`

FROM orders
  WHERE (status = "paid" OR status = "shipped")
    AND total >= 100
    AND NOT plan = "free"
    AND buyer.city LIKE "lis%"
    AND email EXISTS

AND binds tighter than OR. NOT prefixes, parentheses group.

Operators

Operator	Meaning
`=` `!=` `<>` `>` `>=` `<` `<=`	comparison
`IN (a, b)` / `NOT IN (a, b)`	membership
`LIKE "a%b"`	case-insensitive match; `%` is the only wildcard, everything else is literal
`MATCHES "^re"`	a real regular expression
`<path> EXISTS`	the field is present (postfix)

Values and functions

Strings are quoted. Numbers, true, false and null are bare. Anything else bare is an error — status = paid will not silently become the string "paid", it will tell you to quote it.

Function	Returns
`now()`	epoch ms, at run time
`days_ago(n)` `hours_ago(n)` `minutes_ago(n)`	epoch ms, n units ago
`lower(x)` `upper(x)`	case-folded

Field paths

Dotted: buyer.city, meta.source.campaign. Dynamic-collection rows hydrate their JSON data column into the row, so nested access is just property access. A missing field never matches a comparison and is not an error — a schema-less collection legitimately has rows that lack a key.

Two things that would otherwise bite

Timestamps are epoch milliseconds

The dynamic connector writes createdAt/updatedAt as Date.now() — numbers, not ISO strings. days_ago(30) returns epoch ms to match. A hand-written document field is usually an ISO string, so comparison bridges the mixed case and compares them as time; without that, "2024-01-01" against 1700000000000 would order by ASCII and quietly give the wrong answer.

Pushdown is an optimisation, never a filter

lmq hands the storage adapter the = and IN terms from the top-level AND spine so it can fetch fewer rows. Everything else — >, LIKE, MATCHES, anything under an OR, anything with a dotted path or a function call — is evaluated in the interpreter. The full expression is re-evaluated either way, so pushdown can only ever cost rows, never invent them.

What bounds a query

Gate	Effect
Deny list	The union of `/db`'s protected, admin-only, staff-only and platform collections, plus `provider_keys`. Refused by `FROM`, hidden from `COLLECTIONS`.
Per-row `canRead`	`isAdmin \	\	canRead(row, uid) `— the identical filter at` connector/router.js:234`. You see your rows and public rows.
Row cap	5000 readable rows per statement. Over it, the statement fails: a silently short answer is worse than none, because nothing about it says it was cut.
`LIMIT` clamp	1…1000, mirroring `clampLimit` in `@leumas/connectors`. A larger `LIMIT` is clamped with a printed note, not an error.

What it deliberately cannot do

Write anything. The bridge object has no create, update or delete method. This is not a

check that could be bypassed; there is nothing to call.

Join. One collection per statement. Run two statements.
Raw SQL. See above.
Run from the CLI. No bridge there, so it refuses with a message naming /api/lmx/run rather

than returning an empty result that reads like "nothing matched".

Running it

curl -s -X POST localhost:3000/api/lmx/run \
  -H 'content-type: application/json' \
  -d '{"mode":"lmq","source":"COLLECTIONS"}'

Bundled examples: shared/engines/lmx/src/scripts/demo.lmq, shared/engines/lmx/src/scripts/report-users.lmq. Tests: shared/engines/lmx/src/test/lmq.test.js. Guard: pnpm smoke:lmx.

lmq

lmq — the collection query language