SQL reference

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, customer, amount_cents FROM shop.orders WHERE status = '\''paid'\'' ORDER BY placed_ms DESC LIMIT 3"
  }'

result = db.sql.query("""
    SELECT id, customer, amount_cents
      FROM shop.orders
     WHERE status = 'paid'
     ORDER BY placed_ms DESC
     LIMIT 3
""")

print(result.columns)          # -> ['id', 'customer', 'amount_cents']
for row in result.rows:
    print(row["id"], row["amount_cents"])

const res = await db.sql(`
  SELECT id, customer, amount_cents
    FROM shop.orders
   WHERE status = 'paid'
   ORDER BY placed_ms DESC
   LIMIT 3
`);

if (res.kind === "select") {
  for (const row of res.rows as Record<string, unknown>[]) {
    console.log(row.id, row.amount_cents);
  }
}

res, err := db.SQL(ctx, `
    SELECT id, customer, amount_cents
      FROM shop.orders
     WHERE status = 'paid'
     ORDER BY placed_ms DESC
     LIMIT 3
`)
if err != nil { /* handle */ }

if res.Kind == "select" {
    for _, row := range res.Rows {
        fmt.Println(row["id"], row["amount_cents"])
    }
}

# Placeholders are PostgreSQL-style and positional: $1, $2, ...
# params[0] fills $1. JDBC-style "?" is refused.
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, amount_cents FROM shop.orders WHERE status = $1 AND amount_cents > $2 LIMIT $3",
    "params": ["paid", 1000, 20]
  }'

import os, requests

BASE = f"https://{os.environ['OC_HOST']}/v1/tenants/{os.environ['OC_TENANT']}"
H    = {"Authorization": f"Bearer {os.environ['OC_TOKEN']}"}

# The Python client's params= argument sends a NAMED mapping, which this
# engine does not accept - it binds positional $1, $2 from a JSON array.
# Until the client is updated, bind through the HTTP API directly:
res = requests.post(f"{BASE}/sql", headers=H, json={
    "sql":    "SELECT id, amount_cents FROM shop.orders "
              "WHERE status = $1 AND amount_cents > $2 LIMIT $3",
    "params": ["paid", 1000, 20],
})
res.raise_for_status()
for row in res.json()["rows"]:
    print(row["id"], row["amount_cents"])

// The TypeScript client takes a positional array and sends it as-is.
const res = await db.sql(
  `SELECT id, amount_cents FROM shop.orders
     WHERE status = $1 AND amount_cents > $2 LIMIT $3`,
  ["paid", 1000, 20],
);

if (res.kind === "select") console.log(res.rows.length);

// The Go client's variadic params are sent as a positional JSON array.
// (Its doc comment predates parameter support in the engine.)
res, err := db.SQL(ctx,
    `SELECT id, amount_cents FROM shop.orders
       WHERE status = $1 AND amount_cents > $2 LIMIT $3`,
    "paid", 1000, 20,
)
if err != nil { /* handle */ }
fmt.Println(len(res.Rows))

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total FROM shop.orders WHERE status = '\''paid'\'' GROUP BY customer HAVING SUM(amount_cents) > 5000 ORDER BY total DESC LIMIT 10"
  }'

result = db.sql.query("""
    SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total
      FROM shop.orders
     WHERE status = 'paid'
     GROUP BY customer
    HAVING SUM(amount_cents) > 5000
     ORDER BY total DESC
     LIMIT 10
""")
for row in result.rows:
    print(row["customer"], row["orders"], row["total"])

const res = await db.sql(`
  SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total
    FROM shop.orders
   WHERE status = 'paid'
   GROUP BY customer
  HAVING SUM(amount_cents) > 5000
   ORDER BY total DESC
   LIMIT 10
`);
if (res.kind === "select") console.table(res.rows);

res, err := db.SQL(ctx, `
    SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total
      FROM shop.orders
     WHERE status = 'paid'
     GROUP BY customer
    HAVING SUM(amount_cents) > 5000
     ORDER BY total DESC
     LIMIT 10
`)
if err != nil { /* handle */ }
for _, row := range res.Rows {
    fmt.Println(row["customer"], row["orders"], row["total"])
}

# One equality per ON clause, both sides qualified: alias.column.
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, c.name, o.amount_cents FROM shop.orders o INNER JOIN shop.customers c ON o.customer = c.id WHERE o.status = '\''paid'\'' LIMIT 10"
  }'

# Projected columns keep their alias prefix: the result columns are
# literally "o.id", "c.name", "o.amount_cents".
#
# JOIN is included from the Thunder configuration up - see section 14.

result = db.sql.query("""
    SELECT o.id, c.name, o.amount_cents
      FROM shop.orders o
     INNER JOIN shop.customers c ON o.customer = c.id
     WHERE o.status = 'paid'
     LIMIT 10
""")
for row in result.rows:
    print(row["o.id"], row["c.name"])   # note the alias-qualified keys

const res = await db.sql(`
  SELECT o.id, c.name, o.amount_cents
    FROM shop.orders o
   INNER JOIN shop.customers c ON o.customer = c.id
   WHERE o.status = 'paid'
   LIMIT 10
`);
if (res.kind === "select") {
  for (const row of res.rows as Record<string, unknown>[]) {
    console.log(row["o.id"], row["c.name"]);  // alias-qualified keys
  }
}

res, err := db.SQL(ctx, `
    SELECT o.id, c.name, o.amount_cents
      FROM shop.orders o
     INNER JOIN shop.customers c ON o.customer = c.id
     WHERE o.status = 'paid'
     LIMIT 10
`)
if err != nil { /* handle */ }
for _, row := range res.Rows {
    fmt.Println(row["o.id"], row["c.name"])   // alias-qualified keys
}

# INSERT executes and writes durably. The column list is MANDATORY.
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.orders (id, customer, amount_cents, status, notes, placed_ms) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, status",
    "params": ["ord-3001", "cus-90", 2500, "pending", "", 1714478400000]
  }'

# Explicit upsert instead:
#   INSERT INTO shop.orders (id, status) VALUES ('ord-3001', 'shipped')
#   ON CONFLICT (id) DO UPDATE SET status = EXCLUDED.status

res = db.sql.execute("""
    INSERT INTO shop.orders (id, customer, amount_cents, status, notes, placed_ms)
    VALUES ('ord-3001', 'cus-90', 2500, 'pending', '', 1714478400000)
""")
print(res.kind)      # -> insert

# Bulk loading? Use the row batch endpoint instead - it is far faster.
db.rows.put_batch("shop.orders", rows)

const res = await db.sql(`
  INSERT INTO shop.orders (id, customer, amount_cents, status, notes, placed_ms)
  VALUES ('ord-3001', 'cus-90', 2500, 'pending', '', 1714478400000)
`);

if (res.kind === "insert") {
  console.log(res.schema, res.rows);
}

res, err := db.SQL(ctx, `
    INSERT INTO shop.orders (id, customer, amount_cents, status, notes, placed_ms)
    VALUES ('ord-3001', 'cus-90', 2500, 'pending', '', 1714478400000)
`)
if err != nil { /* handle */ }
fmt.Println(res.Kind, res.Schema)   // -> insert shop.orders

# Check whether an index is actually being used.
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 FROM shop.orders WHERE status = '\''paid'\''"}'

# EXPLAIN ANALYZE runs the query and adds per-operator timings.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" -H "Content-Type: application/json" \
  -d '{"sql":"EXPLAIN ANALYZE SELECT id FROM shop.orders WHERE status = '\''paid'\''"}'