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.

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:

[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.

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:

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:

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):

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

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

// 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:

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

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

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.

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:

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:

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

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), design and implementation plan (0015), and crate documentation.

The unreleased adapter contract is spec 0028 §7, with design 0016 and adapter usage.

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.