SQL API Reference
SochDB ships a SQL engine alongside its native key-value, vector, and graph capabilities. This page is a reference for the statements, grammar, data types, and functions that the engine actually implements in v2.0.3, plus an explicit list of what is not supported yet.
Overview
The SQL engine supports a SQL-92-flavored dialect with SochDB-specific extensions for vectors and embeddings. At a high level it covers:
- DDL:
CREATE TABLE,DROP TABLE,CREATE INDEX,DROP INDEX,ALTER TABLE(ADD/DROP COLUMN) - DML:
SELECT,INSERT(withON CONFLICTvariants),UPDATE,DELETE - Transactions:
BEGIN,COMMIT,ROLLBACK - Query features:
WHERE,GROUP BY,HAVING,ORDER BY,LIMIT,OFFSET, joins (INNER/LEFT/RIGHT/FULL/CROSS) - Aggregates:
COUNT,SUM,AVG,MIN,MAX(plusMEDIANandSTDDEVon one execution path — see below) - Diagnostics:
EXPLAIN - Vector extensions:
VECTOR_SEARCH(...), plusVECTOR(dims)andEMBEDDING(dims)column types
SochDB has more than one SQL code path. The production path is the storage-backed dispatcher (the "bridge") layered over a Volcano-style operator executor; that combination gives the fullest coverage (joins, aggregates, DDL/DML, CREATE INDEX, ALTER TABLE). There is also a small in-memory SqlExecutor used as a reference/standalone engine — it deliberately handles only a subset (single-table FROM, no CREATE INDEX). Where the two diverge, this page documents the production behavior and calls out the difference.
Architecture
SQL text -> Lexer -> Parser -> AST -> Planner -> Volcano operators -> Storage
The planner builds a pipeline of operators (SeqScan, Filter, HashAggregate, Project, Sort, Limit, the join nodes, Explain) that each pull rows one at a time. The engine lives in sochdb-query/src/sql/ (lexer, parser, AST, bridge) and sochdb-query/src/executor/ (the operators and planner).
Quick Start
- Python
- Rust
- TypeScript / Node.js
from sochdb import Database
db = Database.open("./mydb")
# Create table
db.execute("""
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
age INTEGER
)
""")
# Insert data
db.execute("INSERT INTO users (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 30)")
# Query data
result = db.execute("SELECT * FROM users WHERE age > 25")
for row in result.rows:
print(f"{row['name']}: {row['email']}")
# Update / delete
db.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")
db.execute("DELETE FROM users WHERE age < 18")
db.close()
The Database class here is from the pure-Python SDK (sochdb 0.5.9).
use sochdb_query::sql::{SqlExecutor, ExecutionResult};
let mut executor = SqlExecutor::new();
executor.execute(
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL, stock INTEGER DEFAULT 0)",
)?;
executor.execute("INSERT INTO products (id, name, price, stock) VALUES (1, 'Widget', 29.99, 100)")?;
let result = executor.execute("SELECT name, price FROM products WHERE price < 50.0")?;
if let ExecutionResult::Rows { columns, rows } = result {
for row in rows {
println!("{:?}", row);
}
}
# Ok::<(), Box<dyn std::error::Error>>(())
The standalone SqlExecutor is in-memory and single-table. For storage-backed SQL with joins and indexes, drive SQL through the sochdb crate's database handle (the bridge path).
import { Database } from '@sochdb/sochdb';
const db = await Database.open('./mydb');
await db.execute(`
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
total REAL,
status TEXT
)
`);
await db.execute("INSERT INTO orders (id, customer_id, total, status) VALUES (1, 101, 299.99, 'pending')");
const result = await db.execute("SELECT * FROM orders WHERE status = 'pending'");
result.rows.forEach((row) => {
console.log(`Order ${row.id}: $${row.total}`);
});
await db.close();
The Go SDK (github.com/sochdb/sochdb-go) is a key-value / context / memory client and does not expose a SQL execute method. It is remote-first by default; the embedded engine (embedded.Open) is behind the sochdb_embedded build tag and is also key-value only. Use the Python, Node.js, or Rust APIs for SQL.
Data Types
Column types are declared in CREATE TABLE. The parser recognizes the following type keywords (case-insensitive). Synonyms map to the same internal type.
| Declared keyword(s) | Internal type | Notes |
|---|---|---|
TINYINT | TinyInt | 8-bit integer |
SMALLINT | SmallInt | 16-bit integer |
INT, INTEGER | Int | signed integer |
BIGINT | BigInt | 64-bit integer |
FLOAT, REAL | Float | floating point |
DOUBLE | Double | double precision |
DECIMAL(p, s) | Decimal | optional precision/scale |
CHAR(n) | Char | fixed-length string (length optional) |
VARCHAR(n) | Varchar | variable-length string (length optional) |
TEXT | Text | UTF-8 string |
BINARY(n), VARBINARY(n), BLOB | Binary / Varbinary / Blob | binary data |
DATE, TIME, TIMESTAMP, DATETIME, INTERVAL | date/time types | |
BOOLEAN, BOOL | Boolean | true / false |
JSON, JSONB | Json / Jsonb | JSON document |
VECTOR(dims) | Vector | SochDB extension; defaults to 128 dims if omitted |
EMBEDDING(dims) | Embedding | SochDB extension; defaults to 1536 dims if omitted |
Any unrecognized identifier is preserved as a Custom type rather than rejected.
CAST(expr AS type) parses and evaluates, but the executor currently passes the inner value through without real type coercion. Do not rely on CAST to convert or validate a value.
Vector / embedding columns
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
title TEXT,
content TEXT,
embedding EMBEDDING(1536) -- e.g. OpenAI text-embedding-3-small
);
CREATE TABLE faces (
id INTEGER PRIMARY KEY,
descriptor VECTOR(128) -- 128-dim face descriptor
);
DDL (Data Definition Language)
CREATE TABLE
CREATE TABLE [IF NOT EXISTS] table_name (
column_name datatype [constraints],
...
)
Example:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
department TEXT,
salary REAL,
hired_date TEXT,
is_active BOOLEAN DEFAULT TRUE
)
Column constraints:
PRIMARY KEYNOT NULLUNIQUEDEFAULT value
DROP TABLE
DROP TABLE [IF EXISTS] table_name
CREATE INDEX
CREATE INDEX [IF NOT EXISTS] index_name ON table_name (column1, column2, ...)
CREATE INDEX idx_email ON employees (email);
CREATE INDEX idx_dept_salary ON employees (department, salary);
CREATE INDEX / DROP INDEX are handled by the storage-backed bridge path. The standalone in-memory SqlExecutor does not implement them.
DROP INDEX
DROP INDEX [IF EXISTS] index_name
ALTER TABLE
ALTER TABLE is partially supported: only ADD COLUMN and DROP COLUMN are implemented.
ALTER TABLE employees ADD COLUMN manager_id INTEGER;
ALTER TABLE employees DROP COLUMN hired_date;
DML (Data Manipulation Language)
SELECT
SELECT [DISTINCT] select_list
FROM table_or_join
[WHERE condition]
[GROUP BY expr, ...]
[HAVING condition]
[ORDER BY expr [ASC | DESC] [NULLS FIRST | NULLS LAST], ...]
[LIMIT count]
[OFFSET skip]
The planner applies clauses in this order: FROM -> WHERE -> GROUP BY/aggregates -> HAVING -> SELECT projection -> ORDER BY -> LIMIT/OFFSET.
-- All columns
SELECT * FROM users;
-- Specific columns
SELECT name, email FROM users;
-- WHERE
SELECT * FROM users WHERE age >= 18 AND status = 'active';
-- ORDER BY (NULLS ordering defaults to NULLS LAST for ASC)
SELECT name, salary FROM employees ORDER BY salary DESC;
-- LIMIT / OFFSET (must be integer literals)
SELECT * FROM products ORDER BY price ASC LIMIT 10;
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
DISTINCT is parsed but not executedThe grammar accepts SELECT DISTINCT, but the planner does not yet have a de-duplication operator, so DISTINCT currently has no effect on results. Do not rely on it.
LIMIT and OFFSET must be integer literals. Expressions or bind parameters in those positions are rejected by the planner.
WHERE operators and expressions
The expression evaluator supports:
| Category | Operators / forms |
|---|---|
| Comparison | =, != / <>, <, <=, >, >= |
| Logical | AND, OR, NOT (short-circuit) |
| Arithmetic | +, -, *, / (division by zero errors), % (modulo) |
| String | || (concatenation), LIKE / NOT LIKE |
| Bitwise | &, |, ^, <<, >>, ~ |
| Null | IS NULL, IS NOT NULL |
| Range | BETWEEN ... AND ..., NOT BETWEEN |
| Set | IN (...), NOT IN (...) |
| Conditional | CASE WHEN ... THEN ... ELSE ... END |
| Functions | scalar functions such as COALESCE, NULLIF |
NULL comparisons follow SQL three-valued logic: comparing NULL to anything yields NULL (treated as not-true), not an error.
SELECT * FROM users WHERE age >= 18 AND status = 'active';
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
SELECT * FROM accounts WHERE balance IS NOT NULL;
SELECT id, COALESCE(nickname, name) AS display FROM users;
LIKE / pattern matching
LIKE uses a single canonical matcher with well-defined, predictable semantics:
- Case sensitive (SQL-92; no collation folding).
%matches zero or more characters._matches exactly one character.- All other characters are matched literally, including regex metacharacters like
.,*,(,[,+. There is no regex engine behindLIKE, so'file.txt' LIKE 'file.txt'matches but'fileXtxt'does not (the.is literal).
SELECT * FROM users WHERE name LIKE 'A%'; -- starts with 'A'
SELECT * FROM users WHERE email LIKE '%.com'; -- ends with '.com'
SELECT * FROM users WHERE email LIKE '%admin%';-- contains 'admin'
SELECT * FROM files WHERE path LIKE 'doc_'; -- 'doc' + exactly one char
INSERT
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)[, (...), ...]
[ON CONFLICT ...]
-- Single row
INSERT INTO users (id, name, email, age)
VALUES (1, 'Alice', 'alice@example.com', 30);
-- Multiple rows
INSERT INTO users (id, name, email, age) VALUES
(2, 'Bob', 'bob@example.com', 25),
(3, 'Charlie', 'charlie@example.com', 35);
-- Omitted columns take their DEFAULT
INSERT INTO products (id, name) VALUES (1, 'Widget');
Conflict handling. Several upsert spellings are accepted and normalized internally:
- Fully supported:
ON CONFLICT DO NOTHING,INSERT IGNORE,INSERT OR IGNORE. - Partially supported:
ON CONFLICT DO UPDATE,INSERT OR REPLACE,ON DUPLICATE KEY UPDATE.
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT DO NOTHING;
UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
UPDATE users SET age = 31 WHERE id = 1;
UPDATE employees
SET salary = 65000, department = 'Engineering'
WHERE id = 123;
-- Expression in SET
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- No WHERE updates every row -- use with care
UPDATE users SET status = 'active';
DELETE
DELETE FROM table_name
[WHERE condition]
DELETE FROM users WHERE id = 1;
DELETE FROM sessions WHERE expires_at < '2024-01-01';
-- No WHERE deletes every row -- use with care
DELETE FROM temp_data;
Aggregates, GROUP BY and HAVING
Aggregates are evaluated by a hash-aggregate operator. GROUP BY and HAVING are both supported; HAVING is a filter applied to the aggregated rows.
-- Global aggregate (no GROUP BY)
SELECT COUNT(*) AS total FROM users;
SELECT AVG(salary) AS avg_salary, MAX(salary) AS top FROM employees;
-- Grouped
SELECT department, COUNT(*) AS count
FROM employees
GROUP BY department;
-- HAVING filters groups
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING avg_sal > 50000;
-- COUNT DISTINCT
SELECT COUNT(DISTINCT department) AS departments FROM employees;
Supported aggregate functions
| Function | Available on operator path | Available on sql/aggregate path | Notes |
|---|---|---|---|
COUNT(*) / COUNT(col) | yes | yes | COUNT(*) counts all rows including NULLs |
COUNT(DISTINCT col) | yes | yes | |
SUM | yes | yes | |
AVG (alias MEAN) | yes | yes | |
MIN | yes | yes | |
MAX | yes | yes | |
MEDIAN | no | yes | |
STDDEV (aliases STDDEV_SAMP, STDEV, SD) | no | yes | sample standard deviation (n-1), Welford online variance |
NULL inputs are skipped by all aggregates except COUNT(*), per the SQL standard.
MEDIAN and STDDEV are implemented only in the dedicated sql/aggregate engine, not in the Volcano hash-aggregate operator. Whether they work in a given query depends on which path executes it. COUNT/SUM/AVG/MIN/MAX are available on both paths and are the safe choice for portable queries.
Joins
The executor implements hash joins (equi-joins), nested-loop joins (theta and cross), and merge joins. Join types INNER, LEFT, RIGHT, FULL, and CROSS are supported, with outer joins emitting NULLs for non-matching rows.
-- INNER JOIN
SELECT users.name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;
-- LEFT JOIN
SELECT users.name, COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id;
-- USING
SELECT * FROM orders JOIN customers USING (customer_id);
-- Multiple joins
SELECT u.name, p.title, c.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN comments c ON p.id = c.post_id;
How the planner picks a join operator:
ON a = b(equi-join) -> hash joinONwith a non-equality predicate (theta join) -> nested-loop joinUSING (col)-> hash join oncol = col- Multiple tables in
FROMwith no join -> implicitCROSS JOINvia nested-loop
NATURAL JOIN is parsed but not implemented as a real natural join — it currently degrades to a CROSS JOIN. Spell out the join columns with ON or USING instead.
Transactions
Execute multiple statements atomically with BEGIN / COMMIT / ROLLBACK.
- Python
- TypeScript / Node.js
txn = db.begin_transaction()
try:
# SQL run via the transaction handle participates in its isolation/atomicity
txn.execute("INSERT INTO accounts (id, balance) VALUES (1, 1000)")
txn.execute("INSERT INTO accounts (id, balance) VALUES (2, 500)")
txn.commit() # returns the HLC commit timestamp
except Exception:
txn.abort()
raise
# Or use the context-manager form, which commits on success and aborts on error:
with db.transaction() as txn:
txn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
txn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
// The public transaction API is withTransaction: it commits on success
// and aborts automatically if the callback throws.
await db.withTransaction(async (txn) => {
await txn.put('orders/1', JSON.stringify({ total: 100 }));
await txn.put('inventory/widget', '49');
});
In the Node.js SDK the Transaction object exposes key-value operations
(get/put/delete/scan); SQL statements are issued through db.execute(...).
Transaction.commit() returns Promise<void>, and the rollback method is abort().
SAVEPOINT and RELEASE are not implemented; only the top-level BEGIN/COMMIT/ROLLBACK cycle is available.
EXPLAIN
EXPLAIN returns the query plan as text rows under a single QUERY PLAN column, showing the operator tree (scan, filter for WHERE, hash-aggregate, filter for HAVING, projection, sort, limit, and join nodes).
EXPLAIN SELECT department, AVG(salary)
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY department
LIMIT 10;
EXPLAIN is produced by the Volcano executor. The storage-backed bridge does not implement EXPLAIN directly — route explain queries through the operator/planner path.
Vector search in SQL
SochDB SQL exposes nearest-neighbor search through the VECTOR_SEARCH function:
VECTOR_SEARCH(column, query_vector, k, metric)
column— theVECTOR/EMBEDDINGcolumn to search.query_vector— the query, e.g. a bind parameter ($1) or a vector literal.k— an integer literal number of neighbors.metric— optional; one ofCOSINE,EUCLIDEAN,DOT_PRODUCT. Defaults toCOSINEif omitted.
-- k-NN over an embedding column, threshold on the similarity score
SELECT id, title
FROM documents
WHERE VECTOR_SEARCH(embedding, $1, 10, COSINE) > 0.8;
-- Default metric (COSINE)
SELECT id FROM documents
WHERE VECTOR_SEARCH(embedding, $1, 5) > 0.7;
Vector literals can be written with the ::VECTOR suffix, e.g. [1.0, 2.0, 3.0]::VECTOR.
VECTOR_SEARCH is recognized by the parser and planned as a vector-index operation. It is not evaluated as a plain row-by-row scalar expression — the scalar expression evaluator rejects a VectorSearch node. In practice use it in the position the optimizer can route to the vector index (typically the WHERE predicate, as shown above); arbitrary use inside other scalar expressions is not supported.
SIMILAR TO is SochQL, not SQLThere is no SIMILAR TO / SIMILAR_TO keyword in the SQL grammar. SIMILAR TO exists only as a SochQL comparison operator. In SQL, use VECTOR_SEARCH(...).
Parsing SQL directly (Rust)
For tooling, you can parse SQL into an AST without executing it.
Parser::parse returns Result<Statement, Vec<ParseError>> (it collects every parse error), so handle the error vector explicitly rather than using ?.
use sochdb_query::sql::{Parser, Statement};
let stmt = Parser::parse("SELECT * FROM users WHERE age > 25")
.map_err(|errs| format!("parse failed: {errs:?}"))?;
if let Statement::Select(select) = stmt {
println!("columns: {:?}", select.columns);
println!("from: {:?}", select.from);
}
// Multiple statements
let stmts = Parser::parse_statements("INSERT INTO t VALUES (1); INSERT INTO t VALUES (2);")
.map_err(|errs| format!("parse failed: {errs:?}"))?;
for stmt in stmts {
// process each
}
# Ok::<(), Box<dyn std::error::Error>>(())
Not supported (yet)
The following are commonly expected but are not implemented (or only partially) in v2.0.3. Avoid them in portable queries:
| Feature | Status |
|---|---|
SELECT DISTINCT | parsed, but no effect (no de-duplication operator) |
Window functions (OVER (...)) | not supported |
Common table expressions (WITH) | not supported |
Subqueries in WHERE / SELECT | not supported (planned) |
Subquery in FROM | partial |
UNION | partial; INTERSECT / EXCEPT planned |
| Stored procedures | not supported |
| Table-valued functions | not supported |
NATURAL JOIN | falls back to CROSS JOIN |
Real CAST coercion | pass-through only |
SAVEPOINT / RELEASE | not supported |
MEDIAN / STDDEV on the operator path | only on the sql/aggregate path |
Graph traversal operators (->, <-, <->) | not supported in SQL scalar evaluation |
Correlated and uncorrelated subqueries in WHERE/SELECT are not yet executed. Rewrite them as joins or as separate queries. The earlier documentation showed subquery and CTE examples — those reflected a planned grammar, not the current engine.
Error handling
- Python
- Rust
- TypeScript / Node.js
try:
result = db.execute("SELECT * FROM nonexistent")
except Exception as e:
print(f"SQL error: {e}")
match executor.execute("SELECT * FROM users") {
Ok(result) => { /* process result */ }
Err(e) => eprintln!("query failed: {e}"),
}
try {
const result = await db.execute("SELECT * FROM users");
} catch (error) {
console.error('query error:', error);
}
SQL vs Key-Value API
SochDB supports both paradigms over the same storage.
| Feature | SQL API | Key-Value API |
|---|---|---|
| Schema | Required (CREATE TABLE) | Schema-free |
| Queries | Rich (WHERE, joins, aggregates) | Prefix scans, range queries |
| Use case | Structured data, analytics | Hierarchical keys, JSON docs |
| Performance | Optimized for complex queries | Ultra-fast point lookups |
Use SQL when you have structured relational data, complex multi-condition queries, or reporting needs. Use key-value when you have hierarchical/path-based data, high-throughput simple operations, or flexible-schema documents.