SQL

CamusDB uses a compact SQL dialect for database lifecycle, schema changes, writes, reads, indexes, and transactions.

SQL keywords are case-insensitive.

Identifiers

CamusDB stores database, table, column, and index names in the exact case used when they are created. Later references match those names case-insensitively.

CREATE TABLE Robots (
  Id OID PRIMARY KEY NOT NULL,
  RobotName STRING NOT NULL
);

INSERT INTO robots (id, robotname) VALUES (GEN_ID(), "R2-D2");
SELECT ROBOTNAME FROM ROBOTS;

The table still displays as Robots, and the columns still display as Id and RobotName, but robots, ROBOTS, and Robots all refer to the same table. Names are also unique case-insensitively, so Robots and robots cannot exist as two different tables in the same database.

Use backticks when an identifier would otherwise conflict with a reserved SQL keyword, type name, or function name.

CREATE TABLE `order` (
  `select` STRING NOT NULL,
  `from` STRING
);

SELECT `select`, `from`
FROM `order`;

For example, CASE and END are reserved keywords. Use backticks for a column named end, such as SELECT `end` FROM events.

Backticks escape identifiers only. String literals use single quotes or double quotes:

SELECT "literal text", 'literal text';

Statement Reference

Area	Page
Database lifecycle	Databases
Recover dropped databases and tables	Recover Dropped Objects
Database branching	Database Branching
Tables, columns, and schema changes	Tables And Schema
Database, table, column, and index comments	Schema Comments
Check and not-null constraints	Check Constraints
Column types and literal formats	Data Types
Indexes, covering indexes, and index DDL	Indexes
Inserts, updates, and deletes	Writing Data
SELECT, filters, grouping, and ordering	Querying Data
Historical SELECT snapshots	Time-Travel Reads
FROM-less SELECT	FROM-less SELECT
Query result caching	Query Result Cache
Planner statistics and automatic analyze	Automatic Analyze
Transactions	SQL Transactions
Authentication and grants	Authentication And Authorization
SHOW, DESCRIBE, and EXPLAIN	Schema Inspection
Node-local engine metrics	Engine Stats
Parameter placeholders	SQL Parameters
Prepared statement handles	Prepared Statements
SQL comments	SQL Comments

Common Workflow

CREATE DATABASE IF NOT EXISTS app;

CREATE TABLE robots (
  id OID PRIMARY KEY NOT NULL,
  name STRING NOT NULL,
  year INT64 DEFAULT (2024)
);

CREATE INDEX robots_year_idx ON robots (year DESC);

INSERT INTO robots (id, name, year)
VALUES (GEN_ID(), "R2-D2", 1977);

SELECT id, name, year
FROM robots
WHERE year >= 1970
ORDER BY year DESC;

Query Features

For joins, subqueries, derived tables, grouped aggregate behavior, table hints, and planner notes, see Query Features.

For utility SELECT statements without a table source, see FROM-less SELECT.

For historical read-only snapshots, see Time-Travel Reads.

For plan selection and plan inspection, see Query Planning and Explaining Queries And Commands. For opt-in caching of repeated single-table reads, see Query Result Cache.