SQL Reference

TensorDB provides a full SQL query surface over its append-only ledger storage. SQL is the primary interface for creating tables, inserting data, and querying.

Quick Demo

TensorDB SQL

SELECT name, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 5;

Results 5 rows

name	email	created_at
Alice Chen	alice@example.com	2025-03-15 09:22:01
Bob Smith	bob@example.com	2025-03-14 14:05:33
Carol Wu	carol@example.com	2025-03-14 08:47:19
Dave Park	dave@example.com	2025-03-13 22:11:45
Eve Jones	eve@example.com	2025-03-12 16:33:08

INSERT INTO transactions (id, account, amount, currency)
VALUES ('txn_001', 'acc_alice', 250.00, 'USD');

-- Every write becomes an immutable fact with:
--   commit_ts  = auto-assigned system timestamp
--   valid_from = now (or specified)
--   valid_to   = infinity

Results 1 row

result
1 row inserted (commit_ts: 1042)

-- Read the database as it was at commit 500
SELECT account, balance
FROM accounts
AS OF 500
WHERE account = 'acc_alice';

Results 1 row

account	balance
acc_alice	1,500.00

-- What was the policy rate valid on 2024-06-15?
SELECT policy_id, rate, valid_from, valid_to
FROM insurance_policies
VALID AT 1718409600
WHERE policy_id = 'POL-2024-001';

Results 1 row

policy_id	rate	valid_from	valid_to
POL-2024-001	4.25	1704067200	1735689600

SELECT u.name, COUNT(o.id) as order_count,
       SUM(o.total) as total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name
HAVING total_spent > 100
ORDER BY total_spent DESC;

Results 3 rows

name	order_count	total_spent
Alice Chen	12	2,450.00
Bob Smith	8	1,823.50
Carol Wu	5	967.25

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 'u_alice'
  AND status = 'shipped';

Results 7 rows

plan
Filter { predicate: status = 'shipped' }
└─ PointLookup { key: 'u_alice', cost: 1.00 }

execution_time_us: 42
rows_returned: 3
bloom_filter_hits: 1
cache_hits: 2

SQL Capabilities

Category	Features
DDL	CREATE TABLE, DROP TABLE, ALTER TABLE (ADD/DROP/RENAME COLUMN)
DML	INSERT, UPDATE (creates new version), DELETE (soft delete)
Queries	SELECT, WHERE, ORDER BY, LIMIT/OFFSET, GROUP BY, HAVING
Joins	INNER JOIN, LEFT JOIN, CROSS JOIN
Subqueries	Scalar subqueries, IN subqueries, EXISTS
CTEs	WITH … AS (common table expressions)
Aggregates	COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT
Window Functions	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER
Temporal	AS OF (time travel), VALID AT (bitemporal)
Explain	EXPLAIN, EXPLAIN ANALYZE
Prepared	Parameterized queries with $1, $2, …
Diagnostics	SHOW STATS, SHOW SLOW QUERIES, SHOW ACTIVE QUERIES, SHOW STORAGE, SHOW COMPACTION STATUS
Functions	60+ string, numeric, datetime, and utility functions

Sections

DDL — CREATE, DROP, ALTER TABLE
DML — INSERT, UPDATE, DELETE
Queries — SELECT, JOINs, CTEs, subqueries
Temporal Queries — AS OF, VALID AT
Functions — All built-in functions
EXPLAIN — Query plan analysis
Prepared Statements — Parameterized queries