examples · sql · 1 / 13

1. SELECT with projection

what this does

Read specific columns from one table. We ask for just id and email from the customers table - the engine reads only those columns from the row payload, not the whole row.

when to use it

You only need a couple of fields per row, not the whole record.
You're driving a UI list view (id + display field) and the rest of the row would just be bytes on the wire.
You want to keep payloads small to keep response latency predictable.

Use SELECT * when you genuinely need every column - explicit projection is just an optimization, not a correctness requirement.

the schema

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

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "email"
ty   = "str"

[[columns]]
name = "region"
ty   = "str"

[[columns]]
name = "tier"
ty   = "str"

[[indexes]]
name    = "by_region"
columns = ["region"]

seed data

Load three customers via the bulk-insert endpoint so the response below has something to return.

# One-time seed of three customers - skip if you've already loaded data.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/rows/shop.customers/_batch" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '[
    { "id": "c_1", "email": "alice@example.com", "region": "IN", "tier": "gold" },
    { "id": "c_2", "email": "bob@example.com",   "region": "US", "tier": "silver" },
    { "id": "c_3", "email": "carol@example.com", "region": "DE", "tier": "gold" }
  ]'

the request

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, email FROM shop.customers LIMIT 5"
  }'

result = db.sql("SELECT id, email FROM shop.customers LIMIT 5")
for row in result.rows:
    print(row["id"], row["email"])

const result = await db.sql("SELECT id, email FROM shop.customers LIMIT 5");
if (result.kind === "select") {
  for (const row of result.rows) {
    console.log(row.id, row.email);
  }
}

result, err := db.SQL(ctx, "SELECT id, email FROM shop.customers LIMIT 5")
if err != nil { /* handle */ }
for _, row := range result.Rows {
    fmt.Println(row["id"], row["email"])
}

what you get back

{
  "kind": "select",
  "rows": [
    { "id": "c_1", "email": "alice@example.com" },
    { "id": "c_2", "email": "bob@example.com" },
    { "id": "c_3", "email": "carol@example.com" }
  ]
}

Every /sql response carries a kind field. "select" means the engine ran the query and the rows are in rows; each row is a JSON object keyed by column name.

how it works

The SQL parser turns the statement into an AST.
The translator folds the AST into a Plan tree - here, Limit(5) → Project(id, email) → Scan(shop.customers).
The executor runs the plan bottom-up. Scan iterates the row store; Project drops every field except the ones you asked for; Limit stops at 5 rows.
The trimmed rows come back as JSON.

common mistakes

Missing LIMIT. Without a LIMIT, the query returns every row. On large tables that's slow and expensive. Add LIMIT early, drop it only when you mean it.
Forgot to qualify the table. Tables are addressed as namespace.table - FROM shop.customers, not FROM customers.
Asking for an undeclared column. Selecting a column that doesn't exist in the schema returns 400 unknown_column.