Schema for SQL.

schema · sql

SQL queries read and write rows the schema TOML declares. Four fields are required to make a table SQL-queryable at all; six more are optional but each turns on specific query shapes or guards writes.

Engine surface: POST /v1/tenants/:t/sql with { "sql": "..." } body. Translator: sqlparser (GenericDialect) → oc_query::Plan tree → executor → JSON rows.

Required schema fields.

Without these, this query surface doesn't function at all.

field	effect
namespace	Logical namespace. Combined with table to form the schema id used in FROM clauses (e.g. shop.orders).
table	Table identifier.
primary_key	Array of column names. WHERE pk_col = literal becomes O(1) hash lookup — sub-millisecond point read.
[[columns]]	Typed column declarations. Required for typed projections + write-time validation.

Optional fields — what each one unlocks.

Add only the fields whose effect you need. Each one buys a specific capability — speed up a predicate, guard a write, or unlock a new query shape.

field	type	default	effect
[[columns]] required	bool	false	When true, INSERT / UPDATE returns 400 if the column is missing. Use for PK + business-required fields.
[[indexes]] name + columns	string + [string]	—	Equality WHERE on the listed columns becomes sub-linear hash lookup. Composite indexes match left-prefix only.
[[extractions]] name + path + ty	object	—	Walk a dotted JSON path inside a nested row at put time and materialise a typed derived column. SELECT it like any other column.
[[foreign_keys]] from_col → target_schema.target_col	object	—	Validate INSERT / UPDATE of from_col against the target's PK. Refuses orphan refs with 409 fk_violation.
[[check_constraints]] name + expr	SQL-subset expr	—	Boolean predicate evaluated on each row at write. Returns 409 check_violation on fail.
version	u32	1	Monotonic counter for online migrations. Re-registering with a higher version triggers backfill + atomic cutover.

What works on any schema (no knob needed).

SELECT with column projection or *
WHERE with = != < <= > >= BETWEEN IN IS NULL IS NOT NULL LIKE - combined with AND
Aggregates: COUNT(*), COUNT(col), SUM, AVG, MIN, MAX
GROUP BY single or multiple columns
INNER, LEFT, RIGHT, and FULL OUTER JOIN (up to 32 tables)
LIMIT
INSERT VALUES, UPDATE, DELETE (note: writes via /sql return a typed translation - use /rows endpoints for real production writes)
EXPLAIN - returns the Plan tree as JSON
BEGIN / COMMIT / ROLLBACK transactions

Not in v0 — refused shapes.

The engine returns a typed 400 with a hint instead of running these. Knowing them up front avoids a debugging round-trip.

shape	why
ORDER BY	Plan::Sort exists but isn't wired through the SQL translator. Fetch a wider LIMIT and sort client-side for now.
HAVING on aggregates	Filter the result client-side. On the roadmap.
OR in WHERE	Rewrite as IN (...) for value alternatives, or run two SELECTs and union client-side.
DISTINCT inside aggregates (COUNT DISTINCT)	Aggregate client-side or use a sub-SELECT (uncorrelated).
Window functions (ROW_NUMBER, LAG, etc.)	Not yet supported. Compute ranks client-side.
WITH ... AS (CTEs)	Inline the CTE body.
Correlated subqueries / EXISTS	Uncorrelated IN (SELECT ...) works; correlated forms don't. Flatten to JOINs.
UNION / INTERSECT / EXCEPT	Issue separate SELECTs and union client-side.
Bind params ($1, ?)	Inline literals only - prepared statements are on the roadmap.
INSERT ... ON CONFLICT	Row writes are overwrite-by-default. Use ?expect=insert on the row endpoint to fail on duplicates.
DDL (ALTER TABLE / DROP TABLE)	Use POST /v1/schemas with a TOML manifest. Migrations follow the online-migration contract.
CROSS JOIN	Always use an explicit ON condition with INNER / LEFT / RIGHT / FULL OUTER JOIN.

Abbreviation legend.

token	meaning
PK	Primary key — column(s) listed in primary_key = [...]
FK	Foreign key — referential integrity declared in [[foreign_keys]]
REQ	Required schema field — without it the query type doesn't work
UNLOCK	Field enables new query shapes
speed	Field speeds existing queries (sub-linear vs full scan)
guard	Field is enforced only at write time
v0 / v1 / v3	Engine API generations — v0 today, v1 next release, v3 roadmap

Worked example.

Schema TOML — copy + register via POST /v1/tenants/:t/schemas with Content-Type: text/plain.

namespace   = "shop"
table       = "products"
primary_key = ["id"]

[[columns]]
name = "id"          
ty = "str"  
required = true
[[columns]]
name = "name"        
ty = "str"  
required = true
[[columns]]
name = "category"    
ty = "str"
[[columns]]
name = "price_cents" 
ty = "i64"
[[columns]]
name = "description" 
ty = "str"

# Speed up WHERE category = '…'
[[indexes]]
name    = "by_category"
columns = ["category"]

# Refuse non-positive prices at write time
[[check_constraints]]
name = "price_positive"
expression = "price_cents > 0"

Queries it enables.

# Point read (primary-key fast path)
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"SELECT * FROM shop.products WHERE id = '\''p001'\''"}'

# Index-fast equality
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"SELECT id, name FROM shop.products WHERE category = '\''electronics'\'' LIMIT 50"}'

# GROUP BY + aggregate
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"SELECT category, COUNT(*) AS n, AVG(price_cents) AS avg_price FROM shop.products GROUP BY category"}'

# INNER JOIN
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"SELECT o.id, p.name FROM shop.orders o INNER JOIN shop.products p ON o.product_id = p.id LIMIT 5"}'

# Multi-condition WHERE (AND)
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"SELECT id FROM shop.products WHERE category = '\''electronics'\'' AND price_cents > 10000 LIMIT 100"}'

# EXPLAIN - check whether indexes are being used
curl -X POST $BASE/v1/tenants/$T/sql -H "Authorization: Bearer $BEARER" \
  -d '{"sql":"EXPLAIN SELECT id FROM shop.products WHERE category = '\''electronics'\''"}'