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 with column projection, *
WHERE with = != < <= > >= BETWEEN IN IS NULL LIKE (combined with AND)
GROUP BY with COUNT, SUM, AVG, MIN, MAX
JOIN: INNER, LEFT, RIGHT, FULL OUTER (up to 32 tables)
LIMIT
EXPLAIN
Transactions: BEGIN / COMMIT / ROLLBACK

not yet

ORDER BY via SQL (use LIMIT + filter on the client for now)
HAVING on aggregates
OR in WHERE (rewrite as two SELECTs or IN-list)
Window functions (ROW_NUMBER, LAG, etc.)
CTEs (WITH ... AS)
Correlated subqueries (uncorrelated IN (SELECT) works)
Prepared params ($1, ?) - inline values for now

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 engine translates the INSERT into a typed payload but does not execute it. See Writes below.
"delete"	DELETE	`schema`, `pk` - same caveat as INSERT.

   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
  No ORDER BY yet. If you need a sorted top-N, the workaround for now is to fetch a wider LIMIT and sort in your app. ORDER BY at the SQL layer is on the roadmap.
 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
  OR is not supported yet. Rewrite WHERE x = 1 OR x = 2 as WHERE x IN (1, 2). For more complex disjunctions, run two SELECTs and union them in your app.
 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
  HAVING isn't supported yet. To filter groups by their aggregate (e.g., "only categories with more than 3 products"), filter the result in your app code. HAVING is on the roadmap.
 DISTINCT inside an aggregate. COUNT(DISTINCT col) returns a 400. Workaround: do a sub-SELECT (uncorrelated) or aggregate client-side.
 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).
  important
 
Today, INSERT, UPDATE, and DELETE via the SQL endpoint translate the statement into a typed payload - they do not execute the write. To actually save the row, re-issue the translated payload against the /rows/:schema endpoint. For all real write workloads, use the row endpoints directly - see Insert data.
 
 
For completeness, here is what the translation looks like.
     POST /v1/tenants/:t/sql - INSERT translation   
 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 (the translation)
 {
  "kind":   "insert",
  "schema": "shop.products",
  "rows":   [
    { "id": "p-x-1", "name": "New Product", "category": "electronics", "price_cents": 5555 }
  ]
}
 
Inline execution of writes via the SQL endpoint is on the roadmap. For now, point your code at the row endpoints instead.
 
   
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 →

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.