Working with SQL in SochDB

Skill Level: Intermediate Time Required: 25 minutes Requirements: SochDB Core engine 2.0.3, or a SochDB SDK

Learn how to use SQL in SochDB for relational data operations.

---

Introduction

SochDB ships a SQL engine on top of its high-performance storage layer, plus SochDB-specific extensions for vector search. It supports a practical subset of SQL (not the entire SQL-92 standard). This guide documents what is actually implemented in the core engine (v2.0.3) and is explicit about what is not.

Two different SQL engines

There are two distinct things called "SQL" in the SochDB world, and they have different feature sets:

1. The core engine SQL (Rust sochdb crate / server / MCP). This is the full engine documented here: JOINs, GROUP BY/HAVING, aggregates, EXPLAIN, VECTOR_SEARCH, CREATE INDEX, etc.
2. The pure-Python SDK fallback (sochdb Python SDK v0.5.9, reached through Database.execute(...)). This is a small built-in engine over the key-value store: it supports CREATE/DROP TABLE, INSERT, single-table SELECT (with WHERE / ORDER BY / LIMIT and COUNT(*)), UPDATE, and DELETE only. It has no JOINs, no GROUP BY, no CREATE INDEX, no EXPLAIN, and no VECTOR_SEARCH.

To use the full feature set from Python, run against the SochDB server (over HTTP/MCP) rather than the embedded pure-Python execute() path. Examples in this guide that show advanced features assume the core engine.

Setting Up

Installation

Python

pip install sochdb

Node.js

npm install @sochdb/sochdb

Go

go get github.com/sochdb/sochdb-go

Rust

cargo add sochdb

The sochdb crate is currently version 2.0.3.

Component	Version
Core engine (Rust sochdb crate / server / MCP)	2.0.3
Python SDK	0.5.9
Node.js SDK	0.5.3
Go SDK	0.4.5

License

The core engine (Rust workspace, the sochdb crate, server, MCP) is AGPL-3.0-or-later, with commercial licensing available. The language SDKs (Python, Node.js, Go) are Apache-2.0.

Opening a Database

Python

from sochdb import Database
db = Database.open("./mydb")

Node.js

import { Database } from '@sochdb/sochdb';
// The exported `Database` is the embedded FFI class. `open()` is synchronous
// (it returns a Database, not a Promise).
const db = Database.open('./mydb');

In the Node SDK, the exported Database symbol is the embedded FFI class (EmbeddedDatabase). It provides key-value, transaction, namespace/collection, and HNSW operations, but it has no execute() method — there is no SQL query path on the public Node Database. To run SQL, use the core engine over the server (HTTP/MCP) or one of the other SDKs.

Rust

use sochdb::Database;
let db = Database::open("./mydb")?;

Creating Tables

Define a schema with CREATE TABLE. IF NOT EXISTS is supported:

CREATE TABLE IF NOT EXISTS users (
    id        BIGINT,
    username  TEXT,
    email     TEXT,
    created_at TIMESTAMP,
    is_active  BOOLEAN
);

Drop a table with DROP TABLE (and optionally IF EXISTS):

DROP TABLE IF EXISTS users;

Constraints

CREATE TABLE parses column definitions and types. ALTER TABLE is partial (only ADD COLUMN / DROP COLUMN). Rich inline constraints such as CHECK (...) are not enforced by the engine; prefer validating in your application layer.

Supported Data Types

The core engine's DataType set:

Category	Types
Integer	TINYINT, SMALLINT, INT, BIGINT
Floating point	FLOAT, DOUBLE, DECIMAL(precision, scale)
String	CHAR(n), VARCHAR(n), TEXT
Binary	BINARY(n), VARBINARY(n), BLOB
Date / time	DATE, TIME, TIMESTAMP, DATETIME, INTERVAL
Boolean	BOOLEAN
JSON	JSON, JSONB
Vector (SochDB extension)	VECTOR(dims), EMBEDDING(dims)

VECTOR(dims) and EMBEDDING(dims) declare fixed-dimension vector columns, for example VECTOR(768). The SQL grammar also reserves a VECTOR_SEARCH(...) function over these columns, though it is not yet executable in the SQL engine (see Vector Search in SQL).

CREATE TABLE documents (
    id      BIGINT,
    title   TEXT,
    body    TEXT,
    embedding VECTOR(768)
);

Inserting Data

Single Row

INSERT INTO users (id, username, email, created_at)
VALUES (1, 'alice', 'alice@example.com', '2026-01-01');

Multiple Rows

INSERT INTO posts (id, user_id, title, content, likes) VALUES
    (1, 1, 'First Post', 'Hello World!', 10),
    (2, 1, 'Second Post', 'SQL is great', 25),
    (3, 1, 'Third Post', 'More content', 15);

Upsert / Conflict Handling

The engine normalizes several upsert dialects to a single internal form. ON CONFLICT DO NOTHING, INSERT IGNORE, and INSERT OR IGNORE are fully supported; ON CONFLICT DO UPDATE, INSERT OR REPLACE, and ON DUPLICATE KEY UPDATE are partially supported.

INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON CONFLICT DO NOTHING;

Querying Data

Run SQL through the embedded engine in your language of choice. The result exposes the returned rows (and rows_affected for DML).

Python

result = db.execute("SELECT id, username, email FROM users")
for row in result.rows:
    print(f"User: {row['username']} ({row['email']})")

Node.js

// The public Node `Database` (embedded FFI) has no SQL `execute()`. Run SQL
// against the core engine over the server, or use the gRPC client for vector /
// collection queries. The embedded class exposes key-value, namespace, and
// HNSW APIs instead. See the Node.js SDK guide for those.

Basic SELECT

-- All columns
SELECT * FROM users;

-- Specific columns
SELECT id, username, email FROM users;

WHERE Clause

The engine evaluates a rich set of WHERE operators:

• Comparison: =, != / <>, <, <=, >, >=
• Logical: AND, OR, NOT (short-circuit)
• Arithmetic: +, -, *, /, % (division by zero errors)
• String concatenation: ||
• Bitwise: &, |, ^, <<, >>
• BETWEEN / NOT BETWEEN
• IN (...) / NOT IN (...)
• IS NULL / IS NOT NULL
• LIKE / NOT LIKE
• CASE WHEN ... THEN ... ELSE ... END

-- Boolean / equality
SELECT * FROM users WHERE is_active = TRUE;

-- Multiple conditions
SELECT * FROM posts WHERE likes > 20 AND user_id = 1;

-- Range
SELECT * FROM products WHERE price BETWEEN 10.0 AND 50.0;

-- Membership
SELECT * FROM users WHERE id IN (1, 2, 3, 5, 8);

NULL semantics

Comparisons follow SQL three-valued logic: comparing NULL to anything yields NULL (treated as not-true in filters).

LIKE Pattern Matching

LIKE uses a single, unified matcher across the engine:

• It is 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 ., (, *, [, and +. There is no regex engine and no escaping pitfalls. For example, 'file.txt' LIKE 'file.txt' matches, but the string fileXtxt does not match the pattern file.txt because the . is literal.

SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM files WHERE name LIKE 'report_2026.%';

SIMILAR_TO is SochQL, not SQL

SIMILAR_TO (vector similarity, column SIMILAR TO 'query text') is a SochQL operator, not a SQL one. There is no SIMILAR TO keyword in the SQL lexer. For vector search inside SQL, use VECTOR_SEARCH(...) (see below).

Sorting Results

SELECT username, email FROM users ORDER BY username ASC;

SELECT title, likes FROM posts ORDER BY likes DESC;

-- Multiple keys
SELECT * FROM products ORDER BY category ASC, price DESC;

NULLS FIRST / NULLS LAST ordering is honored; the default for a column follows its sort direction.

LIMIT and OFFSET

-- Top 10
SELECT * FROM posts ORDER BY likes DESC LIMIT 10;

-- Pagination: skip 20, take 10
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;

LIMIT and OFFSET must be integer literals (expressions error out). OFFSET is applied before LIMIT.

Aggregate Functions

Core aggregates available in the main query path: COUNT, SUM, AVG, MIN, MAX (and COUNT(DISTINCT col)).

SELECT COUNT(*) AS total FROM users;

SELECT AVG(likes) AS avg_likes FROM posts;

SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;

SELECT SUM(quantity) AS total_stock FROM inventory;

MEDIAN / STDDEV

MEDIAN and STDDEV (sample standard deviation, n-1) are implemented only in the dedicated sql/aggregate path, not in the main (volcano) operator engine. AVG and MEAN are aliases; STDDEV, STDDEV_SAMP, STDEV, and SD all map to sample standard deviation. Depending on how you reach the engine, MEDIAN / STDDEV may not be available. COUNT/SUM/AVG/MIN/MAX are available on all paths. All aggregates except COUNT(*) skip NULL inputs.

GROUP BY and HAVING

-- Posts per user
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id;

-- Filter groups with HAVING
SELECT user_id, AVG(likes) AS avg_likes
FROM posts
GROUP BY user_id
HAVING AVG(likes) > 15;

HAVING is applied after grouping/aggregation. Group output preserves insertion order, and a global (no-GROUP BY) aggregate over an empty table is handled.

Updating Data

-- Single field
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Multiple fields
UPDATE products SET price = 29.99, stock = 100 WHERE sku = 'WIDGET-001';

-- Expression
UPDATE posts SET likes = likes + 1 WHERE id = 5;
UPDATE products SET price = price * 0.9 WHERE category = 'clearance';

Deleting Data

DELETE FROM users WHERE id = 5;

DELETE FROM posts WHERE created_at < '2026-01-01';

-- Multiple conditions
DELETE FROM products WHERE stock = 0 AND discontinued = TRUE;

To clear a table, delete all rows or drop and recreate it:

DELETE FROM temp_table;
-- or
DROP TABLE temp_table;

Joins

The core engine implements INNER, LEFT, RIGHT, FULL, and CROSS joins. Equi-joins (ON a = b or USING (col)) are executed as hash joins; non-equi (theta) conditions and CROSS JOIN use nested-loop joins. LEFT/RIGHT/FULL outer joins emit NULLs for unmatched rows.

Inner Join

SELECT users.username, posts.title, posts.likes
FROM users
INNER JOIN posts ON users.id = posts.user_id
WHERE posts.likes > 10;

Left Join

SELECT users.username, COUNT(posts.id) AS post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.username;

Cross Join

SELECT sizes.label, colors.name
FROM sizes
CROSS JOIN colors;

NATURAL JOIN is not a real natural join

NATURAL JOIN is parsed but falls back to a CROSS JOIN — it does not auto-match same-named columns. Write the join condition explicitly with ON or USING instead.

Indexes

CREATE INDEX and DROP INDEX (with IF [NOT] EXISTS) are supported by the engine's bridge path:

CREATE INDEX idx_users_email ON users (email);

CREATE INDEX idx_posts_user_date ON posts (user_id, created_at);

CREATE UNIQUE INDEX idx_products_sku ON products (sku);

DROP INDEX idx_users_email;

EXPLAIN

EXPLAIN produces a textual query plan (rows under a QUERY PLAN column), showing the operator tree: scans, WHERE filters, aggregation, HAVING, projection, sort, limit, and join nodes (INNER/LEFT/RIGHT/FULL/CROSS).

EXPLAIN
SELECT user_id, COUNT(*) AS post_count
FROM posts
WHERE likes > 5
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10;

note

EXPLAIN runs through the main (volcano) execution path, not the storage bridge.

Vector Search in SQL

SochDB's SQL grammar reserves a VECTOR_SEARCH function for nearest-neighbor queries over VECTOR(dims) / EMBEDDING(dims) columns:

VECTOR_SEARCH(column, query_vector, k, metric)

• column — a vector column.
• query_vector — the query embedding, supplied as a bind parameter (for example $1).
• k — number of neighbors to return (an integer literal).
• metric — optional; one of COSINE, EUCLIDEAN, or DOT_PRODUCT (defaults to COSINE when omitted).

SELECT * FROM documents
WHERE VECTOR_SEARCH(embedding, $1, 10, COSINE) > 0.8;

VECTOR_SEARCH is parsed but not yet executed in SQL

The SQL lexer, AST, and parser recognize VECTOR_SEARCH(...), but the query executor does not yet evaluate it — it returns an "expression type not yet supported in executor" error. (The engine's internal compatibility matrix lists it as supported; that matrix is stale relative to the executor.) Note also that the SQL parser has no bare array-literal syntax like [0.1, 0.2, ...]; pass the query vector as a bind parameter instead.

For real vector workloads today, use the dedicated vector APIs rather than SQL VECTOR_SEARCH. See the Vector Search guide and the HNSW Vector Search guide.

Transactions

Wrap multiple operations for atomicity.

Python

txn = db.begin_transaction()
try:
    txn.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    txn.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    txn.commit()
    print("Transfer completed")
except Exception as e:
    txn.abort()
    print(f"Transfer failed: {e}")

Node.js

// The Node transaction object exposes key-value operations.
// commit() returns Promise<void>; use abort() to roll back.
await db.withTransaction(async (txn) => {
  await txn.put("counter", Buffer.from("1"));
  // ...more operations...
});
// On success the transaction is committed automatically; on a thrown
// error it is aborted.

In SQL terms, BEGIN, COMMIT, and ROLLBACK statements are supported by the core engine; SAVEPOINT / RELEASE are not. In the Node SDK, the transaction object exposes key-value operations plus commit() (returns Promise<void>) and abort(), not a per-transaction SQL method.

What Is NOT Supported

The engine implements a practical subset. The following are not available (do not rely on them):

• DISTINCT in SELECT (no distinct operator).
• Window functions (OVER (...)).
• CTEs / WITH clauses.
• Subqueries in WHERE or SELECT (and correlated subqueries).
• Real CAST coercion — CAST(...) passes the inner value through without type conversion.
• Real NATURAL JOIN — falls back to CROSS JOIN.
• MEDIAN / STDDEV on every path — only on the dedicated aggregate path (see caution above).
• VECTOR_SEARCH(...) execution — parsed but not yet evaluated by the SQL executor (see the caution in Vector Search in SQL).
• Bare array / vector literals in SQL (for example [0.1, 0.2]) — pass vectors as bind parameters.
• SAVEPOINT / RELEASE, stored procedures, INTERSECT / EXCEPT, graph traversal operators (->, <-, <->), and table-valued functions.
• Graph traversal in SQL scalar expressions errors out (graph operators are a SochQL concept, not SQL).

Compatibility matrix vs. reality

The engine's internal compatibility matrix is in places more conservative than the shipped code (for example it marks some outer joins as "planned" even though the executor implements them). When the matrix and the executor disagree, trust the behavior documented here, which reflects the executor/bridge code.

Best Practices

1. Avoid SQL String Injection

Build queries from trusted values. Do not interpolate raw user input into SQL strings; sanitize and validate inputs in your application before composing a statement.

# Risky: interpolating untrusted input
user_input = "admin' OR '1'='1"
db.execute(f"SELECT * FROM users WHERE username = '{user_input}'")  # avoid

2. Batch Operations in a Transaction

txn = db.begin_transaction()
for item in items:
    txn.execute(
        f"INSERT INTO items (id, name) VALUES ({item.id}, '{item.name}')"
    )
txn.commit()

3. Create Indexes for Frequent Queries

CREATE INDEX idx_email ON users (email);
CREATE INDEX idx_created_at ON posts (created_at);

4. Select Only Required Columns

-- Prefer
SELECT id, username FROM users;
-- over
SELECT * FROM users;

5. Use LIMIT for Large Results

SELECT * FROM logs ORDER BY timestamp DESC LIMIT 100;

Real-World Example: E-commerce Orders

CREATE TABLE IF NOT EXISTS orders (
    id          BIGINT,
    customer_id BIGINT,
    total       DOUBLE,
    status      TEXT,
    created_at  TIMESTAMP
);

CREATE TABLE IF NOT EXISTS order_items (
    id         BIGINT,
    order_id   BIGINT,
    product_id BIGINT,
    quantity   INT,
    price      DOUBLE
);

Place an order inside a transaction:

txn = db.begin_transaction()
try:
    txn.execute("""
        INSERT INTO orders (id, customer_id, total, created_at)
        VALUES (101, 1, 299.98, '2026-01-15')
    """)
    txn.execute("""
        INSERT INTO order_items (id, order_id, product_id, quantity, price)
        VALUES (1, 101, 5, 2, 149.99)
    """)
    txn.execute("UPDATE products SET stock = stock - 2 WHERE id = 5")
    txn.commit()
    print("Order placed successfully")
except Exception as e:
    txn.abort()
    print(f"Order failed: {e}")

Read a customer's orders with a join and aggregation:

SELECT o.id, o.total, o.status, o.created_at, COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.customer_id = 1
GROUP BY o.id, o.total, o.status, o.created_at
ORDER BY o.created_at DESC;

Troubleshooting

Query Too Slow?

1. Add indexes on filtered/joined columns.
2. Use LIMIT to reduce result size.
3. Select specific columns instead of SELECT *.
4. Inspect the plan with EXPLAIN.

A Feature Errors Out?

1. Confirm you are on the core engine, not the limited pure-Python execute() path (see the Two SQL engines note).
2. Check the What Is NOT Supported list.

Data Not Appearing?

1. Confirm the transaction committed.
2. Verify WHERE conditions (remember LIKE is case-sensitive).
3. Check for silent failures in DML results.

Next Steps

• Vector Search for semantic queries.
• HNSW Vector Search for indexing and tuning.

See Also

• SQL API Reference
• Python SDK Guide