reference · sql

SQL reference

OriginChain runs SQL against the same instance that holds your vectors, full-text indexes, and graph relationships. This page is a reference for what works today, with code examples for every shape.

All examples below assume you have a client set up. If you haven't yet, see Quickstart.

At a glance.

works today

SELECT — projection, *, DISTINCT
WHERE — = != < <= > >= BETWEEN IN IS NULL LIKE, combined with AND / OR / NOT
ORDER BY (multi-column, ASC/DESC), LIMIT, OFFSET
GROUP BY + COUNT, SUM, AVG, MIN, MAX, HAVING, COUNT(DISTINCT)
JOIN — INNER, LEFT, RIGHT, FULL OUTER (up to 32 tables), and JOIN + GROUP BY
Window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM/AVG OVER (PARTITION BY)
Subqueries — IN / EXISTS / scalar, correlated and uncorrelated
UNION / INTERSECT / EXCEPT, CASE
INSERT, UPDATE (single row by primary key)
Transactions — BEGIN / COMMIT / ROLLBACK, atomic even across nodes — see Transactions
CREATE TABLE, EXPLAIN

not yet

CTEs (WITH ... AS) and WITH RECURSIVE — not reachable via /sql yet
Set-based writes — UPDATE / DELETE match one row by primary key, not a WHERE across many rows
Bare DELETE outside a transaction — wrap deletes in BEGIN … COMMIT (see Writes)
Prepared params ($1, ?) — inline literal values for now
Window frame clauses (ROWS BETWEEN) and subqueries in SELECT / FROM
ALTER / TRUNCATE / DROP via SQL, and multiple ;-separated statements

Try anything in the "not yet" list and you get a 400 with a clear reason. Nothing is silently re-interpreted.

The endpoint.

POST /v1/tenants/:tenant/sql
Authorization: Bearer $OC_TOKEN
Content-Type:  application/json

{ "sql": "SELECT ..." }

The response shape depends on the statement kind. Every response carries a "kind" field so your code can switch on it:

kind	When	What's in the body
"select"	SELECT / EXPLAIN	`rows: [{...}, ...]` - one object per row.
"insert"	INSERT	`schema`, `rows` - the inserted rows. INSERT executes and enforces foreign keys. See Writes below.
"update"	UPDATE	`rows_affected` - UPDATE executes for a single row matched by its primary key.
"delete"	DELETE	`schema`, `pk` - run deletes inside a transaction (`BEGIN … COMMIT`). See Writes.

   
Column types.
 
You declare a column's type when you register a schema (see schemas) or with CREATE TABLE. The engine validates every value against it. These are the types it stores:
     Type  SQL aliases  Holds  
 
  i64 / u64 INT, BIGINT, SMALLINT Signed / unsigned integers.
 f64 FLOAT, REAL, DOUBLE Floating-point numbers.
 decimal DECIMAL(p,s), NUMERIC Exact fixed-point (money).
 str / text TEXT, VARCHAR, CHAR UTF-8 strings.
 bool BOOL, BOOLEAN True / false.
 timestamp / date TIMESTAMP, DATETIME, DATE Instants / calendar dates.
 uuid UUID Format-validated UUIDs.
 enum ENUM(...) One of a fixed set of strings.
 json JSON, JSONB Arbitrary JSON values.
 list ARRAY Ordered lists.
 bytes BLOB, BYTEA, BINARY Raw byte strings.
 inet / interval / point INET, INTERVAL, POINT IP addresses, durations, geo points.
 
 
 
 
   on this page
  SELECT basics
 WHERE filters
 GROUP BY + aggregates
 JOIN
 Writes via SQL
 EXPLAIN
 
 
   
1. SELECT basics.
  what this does
 
Read rows back from a table. Pick which columns you want, filter with WHERE, cap the result count with LIMIT.
 
     POST /v1/tenants/:t/sql   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, name, price_cents FROM shop.products WHERE category = '\''electronics'\'' LIMIT 50"
  }'
result = db.sql("""
    SELECT id, name, price_cents
      FROM shop.products
     WHERE category = 'electronics'
     LIMIT 50
""")

# result is a SqlSelect when the statement was a SELECT.
for row in result.rows:
    print(row["id"], row["name"], row["price_cents"])
const result = await db.sql(`
  SELECT id, name, price_cents
    FROM shop.products
   WHERE category = 'electronics'
   LIMIT 50
`);

if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row.id, row.name, row.price_cents);
  }
}
result, err := db.SQL(ctx, `
    SELECT id, name, price_cents
      FROM shop.products
     WHERE category = 'electronics'
     LIMIT 50
`)
if err != nil { /* handle */ }

if result.Kind == "select" {
    for _, row := range result.Rows {
        fmt.Println(row["id"], row["name"], row["price_cents"])
    }
}
 
    common mistakes
  ORDER BY runs server-side. Sort by one or more columns with ASC/DESC, and page through results with LIMIT + OFFSET. (Window frame clauses like ROWS BETWEEN are the only ordering-related piece not yet supported.)
 Missing LIMIT. A SELECT without LIMIT returns every matching row. For large tables this can be slow. Always include a LIMIT during development.
 Schema vs. table name. Use the full schema.table form (here, shop.products). The bare table name without the schema doesn't resolve.
 
 
 
   
2. WHERE filters.
  what this does
 
Narrow down which rows come back. Combine any number of conditions with AND.
 
     POST /v1/tenants/:t/sql   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, name, price_cents FROM shop.products WHERE category = '\''electronics'\'' AND price_cents > 10000 AND price_cents < 50000"
  }'
# Multiple AND conditions. WHERE supports = != < <= > >=, BETWEEN,
# IN, IS NULL, IS NOT NULL, and LIKE. Combine with AND only.
result = db.sql("""
    SELECT id, name, price_cents
      FROM shop.products
     WHERE category = 'electronics'
       AND price_cents > 10000
       AND price_cents < 50000
""")
// Multiple AND conditions. WHERE supports = != < <= > >=, BETWEEN,
// IN, IS NULL, IS NOT NULL, and LIKE. Combine with AND only.
const result = await db.sql(`
  SELECT id, name, price_cents
    FROM shop.products
   WHERE category = 'electronics'
     AND price_cents > 10000
     AND price_cents < 50000
`);
// Multiple AND conditions. WHERE supports = != < <= > >=, BETWEEN,
// IN, IS NULL, IS NOT NULL, and LIKE. Combine with AND only.
result, _ := db.SQL(ctx, `
    SELECT id, name, price_cents
      FROM shop.products
     WHERE category = 'electronics'
       AND price_cents > 10000
       AND price_cents < 50000
`)
 
    operators you can use
     Operator  Example  
 
   = != < <= > >=  price_cents > 1000  
  BETWEEN  price_cents BETWEEN 1000 AND 20000  
  IN (list)  category IN ('electronics', 'books')  
  IS NULL / IS NOT NULL  description IS NOT NULL  
  LIKE  name LIKE 'Wireless%'  
 
 
 
 
  common mistakes
  AND, OR, and NOT all work. Combine them freely — e.g. WHERE (category = 'books' OR category = 'toys') AND price_cents < 5000. IN (...) is still the tidiest way to match a set of values.
 No bind parameters. Inline literal values - $1 and ? placeholders are not supported yet. If you build SQL from user input, escape strings carefully.
 String quoting in cURL. Single quotes inside a JSON string need to be escaped as '\\''. Easier to use a Python / TS / Go SDK for any non-trivial query.
 
 
 
   
3. GROUP BY + aggregates.
  what this does
 
Roll rows up by one or more columns and compute aggregates (counts, sums, averages, min/max). Useful for any "how many X per Y" question.
 
     POST /v1/tenants/:t/sql   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT category, COUNT(*) AS n, SUM(price_cents) AS total, AVG(price_cents) AS avg_price FROM shop.products GROUP BY category"
  }'
result = db.sql("""
    SELECT category,
           COUNT(*) AS n,
           SUM(price_cents) AS total,
           AVG(price_cents) AS avg_price
      FROM shop.products
     GROUP BY category
""")

for row in result.rows:
    print(row["category"], row["n"], row["total"], row["avg_price"])
const result = await db.sql(`
  SELECT category,
         COUNT(*) AS n,
         SUM(price_cents) AS total,
         AVG(price_cents) AS avg_price
    FROM shop.products
   GROUP BY category
`);

if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row.category, row.n, row.total, row.avg_price);
  }
}
result, _ := db.SQL(ctx, `
    SELECT category,
           COUNT(*) AS n,
           SUM(price_cents) AS total,
           AVG(price_cents) AS avg_price
      FROM shop.products
     GROUP BY category
`)

if result.Kind == "select" {
    for _, row := range result.Rows {
        fmt.Println(row["category"], row["n"], row["total"], row["avg_price"])
    }
}
 
    supported aggregate functions
     Function  What it returns  
 
   COUNT(*)  Number of rows in the group.  
  COUNT(col)  Number of non-null values of col.  
  SUM(col)  Sum of values.  
  AVG(col)  Arithmetic mean of values.  
  MIN(col), MAX(col)  Smallest / largest value.  
 
 
 
 
  common mistakes
  Use HAVING to filter groups. WHERE filters rows before grouping; HAVING filters groups after — e.g. GROUP BY category HAVING COUNT(*) > 3. Both run server-side.
 DISTINCT aggregates work. COUNT(DISTINCT col), SUM(DISTINCT col), and AVG(DISTINCT col) all execute. (COUNT(DISTINCT *) is the one form that isn't allowed.)
 Every selected column needs to be in GROUP BY or an aggregate. Standard SQL rule - if you select a column you didn't group by, you'll see 400.
 
 
 
   
4. JOIN tables.
  what this does
 
Combine rows from two or more tables on a matching column. INNER, LEFT, RIGHT, and FULL OUTER joins are supported. Up to 32 tables in one query.
 
     POST /v1/tenants/:t/sql   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT o.id, o.qty, p.name FROM shop.orders o INNER JOIN shop.products p ON o.product_id = p.id WHERE o.status = '\''paid'\'' LIMIT 100"
  }'
result = db.sql("""
    SELECT o.id, o.qty, p.name
      FROM shop.orders o
     INNER JOIN shop.products p ON o.product_id = p.id
     WHERE o.status = 'paid'
     LIMIT 100
""")

for row in result.rows:
    print(row["o.id"], row["o.qty"], row["p.name"])
const result = await db.sql(`
  SELECT o.id, o.qty, p.name
    FROM shop.orders o
   INNER JOIN shop.products p ON o.product_id = p.id
   WHERE o.status = 'paid'
   LIMIT 100
`);

if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row["o.id"], row["o.qty"], row["p.name"]);
  }
}
result, _ := db.SQL(ctx, `
    SELECT o.id, o.qty, p.name
      FROM shop.orders o
     INNER JOIN shop.products p ON o.product_id = p.id
     WHERE o.status = 'paid'
     LIMIT 100
`)

if result.Kind == "select" {
    for _, row := range result.Rows {
        fmt.Println(row["o.id"], row["o.qty"], row["p.name"])
    }
}
 
    join types
     Type  What you get  
 
   INNER JOIN  Only rows that have a match on both sides.  
  LEFT JOIN  Every row from the left side, plus matches from the right (null if no match).  
  RIGHT JOIN  Mirror of LEFT - every row from the right side, plus matches from the left.  
  FULL OUTER JOIN  Every row from both sides; nulls fill the gaps.  
 
 
 
 
  common mistakes
  No CROSS JOIN. Always use an explicit ON condition. CROSS JOIN returns 400.
 Ambiguous column names. When two tables have the same column name, qualify with the alias (o.id, p.id) - otherwise the parser refuses.
 33+ tables. The cap is 32 tables per query. Larger joins return 400. (You almost never want more than 5 in practice.)
 
 
 
   
5. Writes via SQL (preview).
  INSERT and UPDATE execute against the engine. INSERT writes the rows and enforces foreign keys; UPDATE changes a single row matched by its primary key and returns rows_affected. For high-volume ingest, the dedicated row endpoints are still the fastest path.
     POST /v1/tenants/:t/sql - INSERT   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO shop.products (id, name, category, price_cents) VALUES ('\''p-x-1'\'', '\''New Product'\'', '\''electronics'\'', 5555)"
  }'
 
   response
 {
  "kind":   "insert",
  "schema": "shop.products",
  "rows":   [
    { "id": "p-x-1", "name": "New Product", "category": "electronics", "price_cents": 5555 }
  ]
}
  deletes
 
Run row deletes inside a transaction — BEGIN; DELETE FROM shop.products WHERE id = 'p-x-1'; COMMIT; — which buffers the delete and applies it on commit. A bare DELETE outside a transaction is not a reliable removal. Both UPDATE and DELETE match exactly one row by primary key; they are not set-based. See Transactions for the full lifecycle, error handling, and retry pattern.
 
 
   
6. EXPLAIN.
  what this does
 
Prefix any SELECT with EXPLAIN to see the query plan the engine would run, without executing it. Useful for checking whether your indexes are being used.
 
     POST /v1/tenants/:t/sql   
 curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "EXPLAIN SELECT id, name FROM shop.products WHERE category = '\''electronics'\''"
  }'
result = db.sql("EXPLAIN SELECT id, name FROM shop.products WHERE category = 'electronics'")
print(result.rows[0])    # → the plan tree as JSON
const result = await db.sql(
  `EXPLAIN SELECT id, name FROM shop.products WHERE category = 'electronics'`
);
if (result.kind === "select") console.log(result.rows[0]);
result, _ := db.SQL(ctx,
    "EXPLAIN SELECT id, name FROM shop.products WHERE category = 'electronics'")
if result.Kind == "select" {
    fmt.Println(result.Rows[0])
}
 
   
The plan tree includes operator names like Scan, Filter, IndexScan, HashJoin, Aggregate. If you see Scan where you expected IndexScan, you likely need an [[indexes]] declaration on the schema.
 
  ← Docs home Next: vector search →

Type	SQL aliases	Holds
i64 / u64	INT, BIGINT, SMALLINT	Signed / unsigned integers.
f64	FLOAT, REAL, DOUBLE	Floating-point numbers.
decimal	DECIMAL(p,s), NUMERIC	Exact fixed-point (money).
str / text	TEXT, VARCHAR, CHAR	UTF-8 strings.
bool	BOOL, BOOLEAN	True / false.
timestamp / date	TIMESTAMP, DATETIME, DATE	Instants / calendar dates.
uuid	UUID	Format-validated UUIDs.
enum	ENUM(...)	One of a fixed set of strings.
json	JSON, JSONB	Arbitrary JSON values.
list	ARRAY	Ordered lists.
bytes	BLOB, BYTEA, BINARY	Raw byte strings.
inet / interval / point	INET, INTERVAL, POINT	IP addresses, durations, geo points.

Operator	Example
= != < <= > >=	price_cents > 1000
BETWEEN	price_cents BETWEEN 1000 AND 20000
IN (list)	category IN ('electronics', 'books')
IS NULL / IS NOT NULL	description IS NOT NULL
LIKE	name LIKE 'Wireless%'

Function	What it returns
COUNT(*)	Number of rows in the group.
COUNT(col)	Number of non-null values of `col`.
SUM(col)	Sum of values.
AVG(col)	Arithmetic mean of values.
MIN(col), MAX(col)	Smallest / largest value.

Type	What you get
INNER JOIN	Only rows that have a match on both sides.
LEFT JOIN	Every row from the left side, plus matches from the right (null if no match).
RIGHT JOIN	Mirror of LEFT - every row from the right side, plus matches from the left.
FULL OUTER JOIN	Every row from both sides; nulls fill the gaps.