SQL Overview

NodeDB uses SQL as its primary query language. Whether you connect via ndb, psql, or HTTP — the same SQL works everywhere.

Query Execution

ndb CLI (NDB protocol)   ──┐
psql    (pgwire)         ──┼──► SQL Parser ──► EngineRules ──► PhysicalPlan ──► Data Plane
HTTP    (REST/JSON)      ──┘

Three doors, one room. Same parser, same optimizer, same execution engine.

Standard SQL Support

NodeDB supports standard SQL with high PostgreSQL compatibility:

• SELECT with WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
• JOINs (INNER, LEFT, RIGHT, CROSS, SEMI, ANTI)
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER)
• CTEs (WITH, WITH RECURSIVE)
• Subqueries and derived tables
• Set operations (UNION, INTERSECT, EXCEPT)
• Transactions with savepoints (BEGIN, COMMIT, ROLLBACK, SAVEPOINT)
• Prepared statements

Engine-Specific Extensions

Beyond standard SQL, NodeDB extends the language for each engine:

• Vector — vector_distance() with <-> / <=> / <#> operators, ANN-aware ORDER BY ... LIMIT k
• Graph — GRAPH TRAVERSE, GRAPH ALGO, MATCH patterns
• Full-Text — text_match(), bm25_score(), search_score()
• Spatial — ST_DWithin(), ST_Contains(), ST_Distance(), etc.
• Timeseries — time_bucket(), ts_rate(), continuous aggregates
• KV — KV_INCR(), KV_CAS(), sorted indexes, rate gates
• CRDT — crdt_state(), crdt_apply()

PostgreSQL Compatibility

These standard Postgres surface elements work natively over pgwire — no rewriting, no extension.

Identifiers vs. Literals

"quoted" is a case-preserved identifier; 'quoted' is a string literal. SELECT "userId" projects the column named userId exactly; SELECT 'userId' returns the constant string.

Pattern Matching

SELECT * FROM users WHERE name LIKE 'Ali%';            -- case-sensitive
SELECT * FROM users WHERE email ILIKE '%@EXAMPLE.COM'; -- case-insensitive

JSON Operators

doc -> 'addr'              -- field as JSON
doc ->> 'name'             -- field as text
doc #> '{addr,city}'       -- nested path as JSON
doc #>> '{addr,city}'      -- nested path as text
doc @> '{"role":"admin"}'  -- left contains right
doc <@ '{"role":"admin"}'  -- left contained in right
doc ? 'email'              -- top-level key exists
doc ?| ARRAY['phone','email']  -- any key exists
doc ?& ARRAY['name','email']   -- all keys exist

See JSON Operators for full reference and examples.

Full-Text Search (Postgres syntax)

SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'transformer & attention');

NodeDB also exposes text_match(), bm25_score(), and search_score() for finer control — see Full-Text Search.

Vector Distance Operators

SELECT id FROM articles ORDER BY embedding <=> $query LIMIT 10;

Isolation Level

Snapshot Isolation (SI). Reads see a consistent snapshot from BEGIN time. Write conflicts detected at COMMIT.