# SQL

> The SQL dialect SkimmerDB runs over a namespace's documents: statements, types, NULLs, operators, functions, text predicates, answers and limits.

`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](/guides/analytics).

```sh title="curl"
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"}'
```

```python title="Python"
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)
```

```ts title="TypeScript"
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:

```sql
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:

| Column | What |
|---|---|
| `id` | The document id (`text`) |
| every top-level `meta` key | Typed from its values |

`text` itself isn't a column: reach it with [`contains` and `grep`](#text-predicates).

Types come from the values written:

| Values | Type |
|---|---|
| Integers, booleans (`true` is `1`, `false` is `0`) | `int` |
| Other numbers | `float` |
| Strings, nested objects and arrays (as their compact JSON) | `text` |
| Imported CSV `YYYY-MM-DD` | `date` |
| Imported CSV `YYYY-MM-DD HH:MM:SS` | `time` |
| Only nulls so far | `null`, until a value gives it a type |

- **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](/guides/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 `NULL`s), `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

| Operator | Meaning |
|---|---|
| `=`, `<>`, `!=`, `<`, `<=`, `>`, `>=` | Comparison |
| `x IN (a, b, ...)`, `x NOT IN (...)` | One of a list of literals |
| `x LIKE 'pattern' [ESCAPE 'c']`, `x NOT LIKE ...` | Pattern match on text |
| `x IS NULL`, `x IS NOT NULL` | Missing or null |
| `AND`, `OR`, `NOT`, `( )` | Logic, with SQL's three-valued results |
| `a + b`, `a - b` | Addition and subtraction of numbers |

- **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

| Function | Returns |
|---|---|
| `COUNT(*)` | Rows |
| `COUNT(x)` | Rows where `x` isn't `NULL` |
| `COUNT(DISTINCT x)` | Distinct non-null values of `x` |
| `SUM(x)` | Sum of a number column (`int` for `int`, else `float`) |
| `AVG(x)` | Average, as `float` |
| `MIN(x)`, `MAX(x)` | Smallest and largest value, of `x`'s type (numbers, text, dates) |

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

```sql
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

| Function | Returns |
|---|---|
| `LENGTH(x)` (also `CHAR_LENGTH`, `STRLEN`) | The length of a text value in **bytes** (UTF-8), not characters |
| `DATE_TRUNC('minute' \| 'hour' \| 'day', x)` | `x` rounded down, as a `time`. `x` is a `date` or `time` column, or an integer of seconds since 1970 |
| `EXTRACT(MINUTE FROM x)` | The minute (0-59) of a `time` or integer-seconds value |
| `REGEXP_REPLACE(x, 'pattern', 'replacement')` | `x` with every match of an RE2 pattern replaced |
| `CASE WHEN p THEN a [WHEN ...] [ELSE b] END` | The first branch whose predicate is true |
| `COALESCE(a, b, ...)` | The first argument that isn't `NULL` (all of the same kind) |

```sql
-- 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
```

> **Note** `DATE_TRUNC` refuses text. Store times as integer seconds (`"ts": 1760572800`) in `meta`, or import a CSV column of `YYYY-MM-DD HH:MM:SS`, which becomes a `time` column.

## Text predicates

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

| Predicate | Matches |
|---|---|
| `contains(text, 'words')` | Documents whose text contains the string: literal, ignoring case (as search's `q`) |
| `grep(text, 'regex')` | Documents whose text matches an RE2 pattern: case-sensitive unless it starts with `(?i)` (as search with `"regex": true`) |

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.

```sql
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

```json
{"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}
```

| Field | What |
|---|---|
| `columns`, `types` | The result's column names and types (`int`, `float`, `text`, `date`, `time`) |
| `rows` | The rows, numbers as JSON numbers and SQL `NULL` as `null` |
| `chunks`, `scanned` | Column blocks in the namespace, and how many the query read |
| `truncated` | More than 10,000 rows: these are the first 10,000 |
| `partial` | Kept for compatibility: a query that can't read all its data fails instead |
| `took_ms`, `temperature`, `store_gets`, `store_bytes`, `cpu_ms` | What the query cost, as every query reports it |

Replaced and deleted documents never count.

## Limits

| | Free | Pro |
|---|---|---|
| Time limit a query | 10 s | 30 s |
| SQL queries running at once, per tenant (per server) | 2 | 8 |
| Rows in an answer | 10,000 | 10,000 |
| Text predicates a query | 8 | 8 |

- **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](/reference/limits).

## Errors

| Status | When |
|---|---|
| 422 `invalid` | The SQL is wrong: the message says why (`no column x`, `expected FROM`, a text compared with a number, ...) |
| 422 `query_timeout` | Past the plan's time limit |
| 413 `too_large` | The query would read more than its share of memory |
| 429 `rate_limited` | The tenant has its plan's SQL queries running already: retry shortly |
| 503 `unavailable` | The query couldn't read its data: retry |

## Not supported yet

| Missing | Instead |
|---|---|
| Joins, subqueries, `UNION`, CTEs | Query each namespace and combine in your code |
| Window functions | Compute running values in your code |
| `*`, `/`, `%` | Count the parts in one query and divide in your code |
| `BETWEEN`, `NULLIF` | `x >= a AND x <= b`; `CASE WHEN` |
| `SELECT DISTINCT` | `GROUP BY` the columns |
| `STDDEV`, `MEDIAN` and other statistical aggregates | Read the values and compute them in your code |
| Character-length `LENGTH` | `LENGTH` counts bytes |
| Date units other than minute, hour and day | Bucket integer seconds in your code |