# 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. 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 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. 1. **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. 2. **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. 3. **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` ```lmq COLLECTIONS ``` Lists what you may query. Denied collections are not listed — a script cannot even learn they exist. ### `FROM … ` | Clause | Form | Notes | |---|---|---| | `FROM` | `FROM ` | starts the statement | | `WHERE` | expression | see below | | `SELECT` | `SELECT a, b, nested.c` | omitted → the whole row | | `ORDER BY` | `ORDER BY [ASC\|DESC]` | `ASC` is the default | | `LIMIT` / `OFFSET` | integers | `LIMIT` is clamped to **1000** | | `COUNT` | `COUNT` or `COUNT BY ` | | | `SUM` `AVG` `MIN` `MAX` | ` [BY ]` | non-numeric values are skipped | A statement is either a `SELECT` **or** an aggregate, never both. --- ## `WHERE` ```lmq 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 | | ` 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 ```bash 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`. --- Source: .claude/skills/leumas-lmx/reference/lmq.md Canonical: https://docs.leumas.tech/p/skills/leumas-lmx/lmq