Columns & Rows

Once a table exists, these verbs read and write its data. Every result row comes back as an object, accessed exactly the way any OcaltQL object is — ?results(0)("columnname").

INSERT INTO

Example
INSERT INTO DB "shopdb" TABLE "people" ROW "name" AS "Alice" AND "email" AS "alice@shop.com" AND "age" AS 30 SET ?id
AFTER EMIT ?id

Columns marked AUTOINCREMENT or AUTOTIMESTAMP should not be supplied — they fill themselves in. SET ?var captures the new row's id.

SELECT ROWS

Example
SELECT ROWS FROM DB "shopdb" TABLE "people"
WHERE "age" IS GREATER THAN 18
ORDER BY "age" DESC
LIMIT 10
SET ?results
AFTER EMIT ?results

WHERE, ORDER BY, and LIMIT are all optional. Omitting WHERE returns every row in the table.

SELECT COLUMNS

SELECT COLUMNS returns only the columns named, in the order named, and takes the same WHERE, ORDER BY and LIMIT as SELECT ROWS. A name that matches no column is an error, never a column of empty values.

Choosing columns
SELECT COLUMNS "name" AND "email" FROM DB "shopdb" TABLE "people"
WHERE "age" IS GREATER THAN 18
ORDER BY "name"
SET ?rows
AFTER EMIT ?rows

Columns are matched by name. BY HEADING matches the names given against headings instead, and WITH HEADINGS, on either verb, returns each row keyed by heading rather than by name, which suits a page or a report.

By heading
SELECT COLUMNS "Date of Birth" AND "Email address" BY HEADING
FROM DB "shopdb" TABLE "staff" SET ?rows
AFTER EMIT ?rows
(* each row keyed by name: {"dob": ..., "email": ...} *)
Rows keyed by heading
SELECT ROWS FROM DB "shopdb" TABLE "staff" WITH HEADINGS SET ?rows
AFTER EMIT ?rows
(* {"id": 1, "Date of Birth": ..., "Email address": ..., "notes": ...} *)

UPDATE ROWS

Example
UPDATE ROWS FROM DB "shopdb" TABLE "people"
WHERE "name" IS IDENTICAL TO "Alice"
SET "age" AS 31 AND "active" AS true

Every row matching WHERE is updated. Any column with AUTOUPDATED refreshes automatically as part of this.

DELETE ROW / DELETE ROWS

DELETE ROW — first match only
DELETE ROW FROM DB "shopdb" TABLE "people" WHERE "name" IS IDENTICAL TO "Alice"
DELETE ROWS — every match
DELETE ROWS FROM DB "shopdb" TABLE "people" WHERE "age" IS LESS THAN 18

COUNT ROWS

Example
COUNT ROWS FROM DB "shopdb" TABLE "people" WHERE "active" IS EQUAL TO true SET ?n
AFTER EMIT ?n

Returns just a number, without building full row objects — the cheaper choice when you only need a total.

GROUP BY

Example
GROUP BY "status" FROM DB "shopdb" TABLE "orders" SET ?grouped
AFTER EMIT ?grouped

Returns one object per distinct value in the named column, each with a count field — the building block for reports and breakdowns.

JOIN SELECT

Joining combines rows from two tables based on a relationship between them. The relationship and any extra filtering both live in the same WHERE clause, connected with OF to point at a value on a specific table.

Inner join — only matched rows
JOIN SELECT DB "shopdb" TABLES "orders" AND "users"
WHERE "user_id" OF "orders" IS IDENTICAL TO "id" OF "users"
AND "status" OF "orders" IS IDENTICAL TO "shipped"
SET ?results

Only orders that have a matching user come back. If an order's user no longer exists, that order is dropped from the results entirely.

Left join — keep every row from the first table
JOIN LEFT SELECT DB "shopdb" TABLES "orders" AND "users"
WHERE "user_id" OF "orders" IS IDENTICAL TO "id" OF "users"
SET ?results

Every order from the first table comes back regardless of a match. If a matching user doesn't exist, that side of the result is simply empty instead of the row being dropped — useful for audits where nothing should silently disappear.

Results from either form are nested objects, keyed by table name: ?results(0)("orders")("status") and ?results(0)("users")("email").

Database Syntax →