# Read-only SQL v1

DreamDB's opt-in `dreamdb-sql` crate provides a small read-only SQL surface over
an existing Dataset. It reuses the scalar, vector and BM25 readers rather than
introducing a query server or another storage format.

> **Note:** SQL v1 was merged in [core PR #378](https://github.com/dreamlake-ai/dreamdb-core/pull/378)
> on 2026-09-15. The Rust example below targets `7d7c440`.
> Python, npm (Node/browser) and CLI adapters are implemented in
> [PR #380](https://github.com/dreamlake-ai/dreamdb-core/pull/380), pinned here to
> `db78d0cb71d182fce8e1ced2785058d312f3b79c`. These are **unreleased source APIs**;
> the examples below require that revision, not the previously published PyPI
> or npm package. This documentation update neither merges the core PR nor
> publishes a package. Follow the PR and package release notes for release status.
> As checked on 2026-09-16, PyPI `dreamdb==0.0.13` and npm
> `@dreamlake/dreamdb@0.5.5` do not include these SQL adapters.

## Open a session and prepare a query

Use the crates from the same core source revision. For example, in a native
Rust application's `Cargo.toml`:

```toml
[dependencies]
dreamdb-sql = { git = "https://github.com/dreamlake-ai/dreamdb-core", rev = "7d7c440db3b8c097e995e97003a0c0cd3c837ed5" }
dreamdb-connector = { git = "https://github.com/dreamlake-ai/dreamdb-core", rev = "7d7c440db3b8c097e995e97003a0c0cd3c837ed5" }
```

The following function takes an existing Connector and opens Ref `catalog`.
The Dataset must already have scalar fields `label` and `title`; `label` must
support the existing scalar query path. Run the function inside your application's
async runtime. SQL does not create fields or indexes.

```rust
use std::sync::Arc;
use dreamdb_connector::Connector;
use dreamdb_sql::{SqlSession, QueryOptions, Value};

async fn query(connector: Arc<dyn Connector>) -> dreamdb_sql::Result<()> {
    let session = SqlSession::open(connector, "catalog", QueryOptions::default()).await?;
    let query = session.prepare(
        "SELECT _anchor, title FROM dataset WHERE label = $1 LIMIT 20",
        &[Value::String("cat".into())],
        QueryOptions::default(),
    )?;
    println!("{:#?}", query.explain());
    let result = query.execute().await?;
    println!("{:?}", result.reads);
    Ok(())
}
```

`$1`, `$2`, and so on are one-based typed parameters, not string interpolation.
Bind values with `Value::String`, `Int`, `UInt`, `Float`, `Bool`, `Null`, or
`Vector`. Do not construct SQL by concatenating user input.

`result.columns` describes names, types and nullability in SELECT-list order.
Each `result.batches` entry contains column-major values: `batch.columns[i]`
holds `batch.len` values for output column `i`. Missing scalar values are
`Value::Null`; unreadable or corrupt dependencies return an error, not NULL.

The session pins the Ref on open, including internal reader reopens. Open a new
session to observe newer data. Prepared queries borrow their session, and
executions within one session serialize. SQL cannot select a different Dataset,
URL, credential or file, and has no write path.

## Python, npm and CLI source adapters

All three adapters use the same Rust binder, planner and executor. Each opens
its own pinned session; it does not follow an independently opened Dataset or
Space. The examples assume an existing Ref `catalog` with indexed scalar field
`label`. No fields, indexes or data are created by SQL.

### Build the unreleased revision

Check out the exact adapter revision in a separate source directory:

```bash
git clone https://github.com/dreamlake-ai/dreamdb-core.git dreamdb-sql-source
cd dreamdb-sql-source
git checkout --detach db78d0cb71d182fce8e1ced2785058d312f3b79c
```

Use the repository's Rust toolchain requirements. For Python, create an isolated
virtual environment and build the wheel locally; this is not a PyPI install:

```bash
python3 -m venv .venv
source .venv/bin/activate
python -m pip install maturin
maturin develop --manifest-path dreamdb-dataset-python/Cargo.toml
```

For npm artifacts, install `wasm-pack` 0.15.0 and the WASM target, then run the
package's existing build script (Node.js is also required):

```bash
rustup target add wasm32-unknown-unknown
cargo install wasm-pack --version 0.15.0 --locked
bash dreamdb-dataset-wasm/build.sh
```

The result is `dreamdb-dataset-wasm/dist/`, containing the package entry points
and type declarations. The Node example below imports that local artifact from
the checkout root. Applications can install this local package instead; do not
substitute a registry version that has not released SQL.

### Python

```python
from dreamdb import SqlSession

session = SqlSession.open("catalog", "file:///existing/dataset")
sql = "SELECT _anchor, label FROM dataset WHERE label = $1 LIMIT 20"
result = session.query(sql, ["cat"], {"max_rows": 1000, "timeout_ms": 10000})
print(result["batches"])  # column-major; integer cells are Python int
print(session.explain(sql, ["cat"]))
print(session.manifest)
```

The Python boundary is synchronous and releases the GIL for native I/O.
Parameters accept `None`, booleans, strings, finite numbers, i64/u64 integers,
and numeric arrays for vectors. Results preserve integer precision and return
`None` for NULL. Invalid SQL, options and execution errors raise `ValueError`;
invalid Python argument shapes can raise `TypeError`.

### JavaScript and TypeScript

```js
// Source-build example, run from the core checkout root (not a registry import).
import { SqlSession } from './dreamdb-dataset-wasm/dist/node.mjs'

const session = await SqlSession.open('catalog', 'https://data.example/dataset')
const sql = 'SELECT _anchor, label FROM dataset WHERE label = $1 LIMIT 20'
const result = await session.query(sql, ['cat'], {
  max_rows: 1000,
  timeout_ms: 10000,
})
console.log(result.batches)
console.log(session.explain(sql, ['cat']))
console.log(session.manifest)
```

`open` and `query` return Promises; `explain` is synchronous after opening.
The backend is an existing Backend object or an anonymous HTTP(S) base URL.
Use a Backend object for credentials or local storage, not a `file://` URL.
Exact integer parameters use `bigint`; unsafe integer Numbers are rejected
rather than rounded. Vector parameters accept numeric arrays or `Float32Array`.
Integer result cells, batch lengths, plan size/count options and read counters
are `bigint`; scores are Numbers, the plan format's `version` is a Number,
and SQL NULL is `null`. Standard `JSON.stringify` needs an explicit BigInt
conversion policy—do not convert anchors to Number and lose precision.

The package's browser and `/web` entry points expose the same SQL factory.
They load a separate read-only SQL WASM artifact on first `SqlSession.open`;
ordinary Space/Writer use does not download it. Preserve the package's `sql/`
assets when deploying. Node includes SQL directly. The local build measured
about 3.40 MB raw for the ordinary browser artifact and an additional 5.05 MB
raw for SQL: deferred download, not zero cost or a query-speed claim.

### CLI

Build the CLI from the same checkout:

```bash
cargo build --release -p dreamdb-cli --bin dreamdb
./target/release/dreamdb sql \
  --backend file:///existing/dataset --ref-name catalog \
  --query 'SELECT _anchor, label FROM dataset WHERE label = $1 LIMIT 20' \
  --params '["cat"]' --options '{"max_rows":1000,"timeout_ms":10000}'
```

Add `--explain` to bind and plan without execution. stdout is one JSON result;
errors go to stderr with a nonzero exit status. JSON parameters can include
exact integer tags such as `[{"type":"uint","value":"9007199254740993"}]`.
Integer results are decimal JSON numbers: use a lossless parser, not ordinary
JavaScript `JSON.parse` for large anchors.

### Shared adapter options and results

`query(sql, params, options)` and `explain(sql, params, options)` default to empty
parameters and options. Options use **snake_case**: `allow_scan`,
`max_candidates`, `max_rows`, `batch_size`, `max_requests`, `max_bytes`,
`max_sql_bytes`, `timeout_ms`, `rerank_mode`, `rerank_pool_size`, `query_spec_id`.
Rerank modes are `inherit`, `approximate`, and `exact`. Unknown options and
invalid ranges are errors. Adapter `timeout_ms` replaces a caller-supplied Rust
Instant; there is no adapter AbortSignal/cancel-handle API yet.

Results contain `columns`, column-major `batches`, `explain`, and `reads`.
Marshalling is eager and adds allocations. Batching is not a streaming or
hard-memory-bound guarantee. Query read budgets exclude session-open metadata.
The indexed execution and projection rules below apply equally to the adapters.

## Supported subset

| Area | SQL v1 behavior |
| --- | --- |
| Statement | One `SELECT`, optionally prefixed with `EXPLAIN` |
| Projection | Explicit scalar fields and `_anchor`, `_timeline`; `_score` only for search sources; optional `AS` aliases |
| Source | `dataset`, `vector_search(...)`, or `text_search(...)` |
| Predicate | `=`, `!=`, `<`, `<=`, `>`, `>=`, `AND`, `OR`, `NOT`, parentheses, `IS NULL`, `IS NOT NULL` |
| Parameters | One-based `$N`, strictly typed; timestamp comparisons use integer nanoseconds |
| Result limit | Nonnegative integer `LIMIT`; resource-policy violations fail instead of silently truncating |

Unquoted field names fold to lowercase; double-quoted names match the Schema
exactly. Output names must be unique. SQL's three-valued NULL rules apply:
WHERE keeps only TRUE, and `NOT UNKNOWN` remains UNKNOWN.

Ordinary `dataset` rows are the union of live anchors in scalar fields on the
opened Timeline, independent of projection. They do not include media-only or
embedding-only anchors. Scalar fields align by **exact anchor**, not nearest
neighbor or overlapping time interval. Ordinary results are anchor-ascending.

V1 rejects `SELECT *`, media/vector-body projections, expression projections,
table aliases, joins, subqueries, `WITH`, aggregates, `DISTINCT`, `ORDER BY`,
`OFFSET`, multiple statements and every write statement. This is a defined
subset, not a claim of general SQL or PostgreSQL compatibility.

## Vector and text search

```sql
SELECT _anchor, _score FROM vector_search('embedding', $1, 10, 8)

SELECT _anchor, title, _score FROM text_search('title', $1, 10)
  WHERE _anchor >= 100 LIMIT 5
```

For `vector_search(field, vector, k, nprobe)`, bind `$1` as
`Value::Vector(Vec<f32>)` with the field's declared dimension. `k` and `nprobe`
must be positive. `QueryOptions.vector_options` carries rerank mode/pool controls;
`query_spec_id` carries optional embedding identity. Existing exactness and
identity checks still apply. Graph queries reject the explicit partition-probe
control; SQL does not silently ignore it or fall back to another search mode.

For `text_search(field, query, k)`, bind a string. The field must already have
the product's typed BM25 binding to its source Track. An arbitrary unindexed
string field is not a text-search source.

**WHERE is applied after top-k**, then LIMIT. Filtering may return fewer than
`k` hits and never refills. This is not prefiltered ANN or hybrid score fusion.
Search results are ordered by descending product score, then ascending anchor
within the returned set; this does not change selection at the underlying
top-k cutoff or guarantee ANN recall.

## Plan before reading

`query.explain()` reports the pinned Manifest, source, indexed fields, candidate
operator, projected fields, residual filtering, search controls and limits. It
does not search, scan buckets or hydrate columns; opening the session has its
own metadata reads. Parameter values and search text/vectors are not printed.

```sql
EXPLAIN SELECT _anchor FROM dataset WHERE rank >= $1 LIMIT 20
```

Scalar comparisons can drive candidate anchors through existing indexes. AND
can use an indexed branch; OR needs complete indexed candidates on both sides.
An indexed anchor-only query need not fetch the scalar values, and no query
implicitly hydrates unrequested media. Output-only columns are read after
filtering and LIMIT.

Anchor-window enumeration, an unindexed NULL test, or an otherwise unindexable
ordinary query requires explicit scan permission. EXPLAIN can show that plan
without granting execution permission when the SQL itself starts with `EXPLAIN`.
The adapter `explain(sql, ...)` and CLI `--explain` prepare the supplied SQL;
for an unindexed plain SELECT they still need `allow_scan: true`, or an explicit
`EXPLAIN SELECT ...` statement. Pass options such as these to Rust `prepare`:

```rust
use dreamdb_sql::QueryOptions;

let options = QueryOptions {
    allow_scan: true,
    max_candidates: 50_000,
    ..Default::default()
};
```

For example, `WHERE _anchor >= $1 AND _anchor < $2` alone requires that opt-in.
Prefer an indexed selective predicate when it expresses the query you need.

### Performance boundaries

Scalar indexes map values to anchors. Materializing even a few output rows can
walk a selected field's value index and overlapping buckets. Legacy ordered
comparisons may scan a bitmap index. **LIMIT does not promise O(LIMIT) I/O**,
and batched output does not make eager underlying operators streaming.

The existing local acceptance fixtures compared SQL with equivalent direct
Dataset APIs at identical search controls:

| Query | SQL logical reads / returned bytes | Direct API |
| --- | --- | --- |
| Scalar equality, anchor-only | 5 / 7,057 B | Same |
| Vector, k=5 and nprobe=2 | 5 / 5,284 B | 6 / 5,317 B |
| BM25, k=5 | 1 / 1,521 B | Same |
| B-tree rank < 2 over 4,200 rows | 7 / 327,584 B; 2 of 5 pages | Same |

Results matched. The vector difference is a cached 33-byte Ref. These are small
MemoryConnector fixtures, not production S3 latency, throughput, or general
scalability measurements. Projection adds its own cost. Read statistics exclude
session-open metadata, reset per execution, and retain warm product caches.
They count logical connector operations, not HTTP requests/retries or wire
traffic; multi-range batching is preserved and charged per requested range.

## Limits and failure behavior

| `QueryOptions` field | Default |
| --- | --- |
| `allow_scan` | `false` |
| `max_candidates` | 100,000 |
| `max_rows` | 10,000 |
| `batch_size` | 256 |
| `max_requests` | 10,000 logical reads |
| `max_bytes` | 256 MiB returned bytes |
| `max_sql_bytes` | 64 KiB |
| `deadline` | None; caller may supply an `Instant` |

SQL also has a 1,024-token limit and parser/predicate depth limit of 64.
`Cancellation::cancel()` supplies cooperative cancellation. Open-time options
apply to metadata reads; execution options are supplied separately to `prepare`.

Limits fail with an error, not a successful partial result. Requests are charged
before dispatch and bytes after the response, so a response may overshoot the
byte budget before rejection. Eager index/search intermediates may allocate
before candidate checks: these options are **not a hard memory bound**.
Cancellation/deadlines do not preempt synchronous decoding/scoring or retract
in-flight backend requests.

## Contract and extension points

See the [SQL v1 contract (spec 0028)](https://github.com/dreamlake-ai/dreamdb-core/blob/7d7c440db3b8c097e995e97003a0c0cd3c837ed5/spec/0028-readonly-sql.md),
[design and implementation plan (0015)](https://github.com/dreamlake-ai/dreamdb-core/blob/7d7c440db3b8c097e995e97003a0c0cd3c837ed5/design/0015-readonly-sql.md),
and [crate documentation](https://github.com/dreamlake-ai/dreamdb-core/blob/7d7c440db3b8c097e995e97003a0c0cd3c837ed5/dreamdb-sql/README.md).

The unreleased adapter contract is [spec 0028 §7](https://github.com/dreamlake-ai/dreamdb-core/blob/db78d0cb71d182fce8e1ced2785058d312f3b79c/spec/0028-readonly-sql.md),
with [design 0016](https://github.com/dreamlake-ai/dreamdb-core/blob/db78d0cb71d182fce8e1ced2785058d312f3b79c/design/0016-sql-adapters.md)
and [adapter usage](https://github.com/dreamlake-ai/dreamdb-core/blob/db78d0cb71d182fce8e1ced2785058d312f3b79c/dreamdb-sql/ADAPTERS.md).

Parser, bound query, physical plan and typed output are separate so future
operators can extend the API without changing storage. Joins,
aggregation, sorting, streaming execution and remote SQL remain future work;
their appearance in a design is not an implemented capability.