SQL

POST /v1/namespaces/{name}/sql with {"sql": "SELECT ..."} runs one query over the namespace's documents (read scope; the MCP tool is sql_query). It reads the same copy of the data that search uses: each document is a row, its id and every top-level meta key are columns, and the text index works inside WHERE. SQL is in beta: the dialect will grow, and what's listed here works today.

For how to shape data for SQL and worked examples, see Analytics with SQL.

curl -s $B/namespaces/support/sql -H "Authorization: Bearer $KEY" \
  -d '{"sql": "SELECT status, COUNT(*) AS n FROM docs WHERE contains(text, '\''refund'\'') GROUP BY status ORDER BY n DESC"}'

from skimmerdb import SkimmerDB

ns = SkimmerDB().namespace("support")
r = ns.sql("SELECT status, COUNT(*) AS n FROM docs WHERE contains(text, 'refund') GROUP BY status ORDER BY n DESC")
print(r.columns, r.rows)

import { SkimmerDB } from "skimmerdb";

const ns = new SkimmerDB().namespace("support");
const r = await ns.sql("SELECT status, COUNT(*) AS n FROM docs WHERE contains(text, 'refund') GROUP BY status ORDER BY n DESC");
console.log(r.columns, r.rows);

#Statement shape

One SELECT a request:

SELECT expr [AS name], ...  |  *
FROM docs
[WHERE predicate]
[GROUP BY expr | alias | position, ...]
[HAVING predicate]
[ORDER BY expr | alias | position [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[LIMIT n [OFFSET m]]

• FROM docs is the namespace's documents, and only them. Any other table name, a join, a subquery or a UNION is an error.
• Aliases with or without AS. An unnamed expression's column is named c<n>.
• GROUP BY and ORDER BY take a column, an alias, an expression or a 1-based position in the select list.
• ORDER BY defaults to ascending and to NULLS LAST in both directions.
• LIMIT and OFFSET take whole numbers.
• SELECT * returns id and every column.
• Identifiers may be double-quoted ("user plan"), and keywords aren't case-sensitive. Strings are single-quoted, with '' for a quote inside one.

SHOW COLUMNS (or DESCRIBE) lists the namespace's columns: name, type, and how many segments hold each one.

#Columns and types

Each document is a row. The columns are:

text itself isn't a column: reach it with contains and grep.

Types come from the values written:

• There's no schema to declare. The namespace's schema is the union of what its documents hold: a new key becomes a column with the next write, and documents without it read it as NULL.
• A key whose values change type is read at the widest type seen: int < float < text. If priority is 3 in one document and "high" in another, it's text, and priority > 2 becomes an error. See Data model for keeping types consistent.
• The 256 most frequent keys of each part of the namespace are columns. Keys named id (the document id wins) and keys starting with # aren't.
• TRUE and FALSE in SQL are the integers 1 and 0.

#NULL

A missing key and a JSON null are both SQL NULL, distinct from 0 and ''.

• Comparisons with NULL are unknown, and WHERE and HAVING keep only rows where the predicate is true. Use IS NULL and IS NOT NULL to test for it.
• COUNT(*) counts rows; COUNT(x) and the other aggregates skip NULL. Over no rows (or only NULLs), COUNT(x) is 0 and SUM, AVG, MIN and MAX are NULL.
• NULL grouping keys form one group.
• A CASE with no matching branch and no ELSE is NULL.
• Imported CSV treats empty fields as missing. NDJSON and Parquet keep NULL apart from empty strings.

#Operators

• Like with like. Text compares with text and numbers with numbers; mixing them is an error, never an empty answer. Quote numbers that are stored as text: code = '42'.
• Dates and times compare with strings that read as one: placed_at >= '2026-10-01'.
• LIKE: % matches any sequence, _ exactly one character (newline included). Without ESCAPE, a backslash is an ordinary character; with ESCAPE '!', 'a!_b' matches the literal a_b.

#Functions

#Aggregates

SUM(DISTINCT x) and AVG(DISTINCT x) work too. An aggregate can't hold another.

SELECT tool, COUNT(*) AS calls, COUNT(DISTINCT agent) AS agents, AVG(latency_ms) AS avg_ms, MAX(latency_ms) AS max_ms
FROM docs GROUP BY tool ORDER BY calls DESC

#Scalar functions

-- an hourly series over integer-second timestamps
SELECT DATE_TRUNC('hour', ts) AS hour, COUNT(*) AS calls,
       SUM(CASE WHEN ok = 0 THEN 1 ELSE 0 END) AS errors
FROM docs WHERE ts >= 1760572800 GROUP BY hour ORDER BY hour

-- a URL's host, and a default for a missing key
SELECT REGEXP_REPLACE(url, '^https?://([^/]+).*$', '\1') AS host, COALESCE(plan, 'free') AS plan, COUNT(*)
FROM docs GROUP BY host, plan

#Text predicates

Two functions reach a document's text from WHERE, through the same index search uses:

They combine with anything else in WHERE (AND, OR, NOT), up to 8 a query. Blocks of documents with no match are skipped before any column is read.

SELECT agent, COUNT(*) AS n FROM docs
WHERE grep(text, 'E5[0-9]{2}') AND ts >= 1760572800
GROUP BY agent ORDER BY n DESC

#The answer

{"namespace": "support", "columns": ["status", "n"], "types": ["text", "int"], "rows": [["open", 2], ["closed", 1]],
 "chunks": 2, "scanned": 2, "partial": false, "truncated": false, "took_ms": 0.9,
 "temperature": "warm", "store_gets": 0, "store_bytes": 0, "cpu_ms": 0.4, "timed_out": false}

Replaced and deleted documents never count.

#Limits

• Memory. A query's groups, distinct values, rows in flight and reads from object storage are capped by a share of the server's memory. Past a cap the query stops with a 422 (or a 413 for reads, refused before anything is read). It never takes the server down.
• Metering. A SQL query is one query, metered and rate-limited like a search. See Limits and plans.

#Errors

#Not supported yet