CamusDB Tutorial

CamusDB is an open-source NewSQL distributed database with SQL, indexes, and transactions.

CamusDB is alpha-quality software. Interfaces and storage formats may change between versions, and it should not be used in production.

This tutorial uses camus-cli, the interactive SQL shell. It walks through the basic workflow against a running CamusDB node or cluster: create a database, create a table, insert rows, query data, add an index, and update or delete rows.

For a higher-level overview of why CamusDB is built as a distributed NewSQL database, start with Why CamusDB?.

Start CamusDB

Install the SQL shell:

dotnet tool install --global CamusDB.SqlSh

Start CamusDB in standalone mode for local use, or run a cluster when you want to try distributed storage. Then open the SQL shell:

camus-cli

You should see an interactive prompt:

camus>

Create A Database

Databases must be created explicitly before use. Create a tutorial database, then switch the shell to it:

CREATE DATABASE IF NOT EXISTS tutorial;
use tutorial;

If you already started the shell with camus-cli tutorial, you still need the CREATE DATABASE IF NOT EXISTS tutorial; statement the first time that database name is used.

Create A Table

Create a table for robot records:

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

The table has:

Column	Type	Notes
id	OID	Primary key object id.
name	STRING	Required robot name.
kind	STRING	Required category or model family.
year	INT64	Optional year with a default value.

Inspect The Schema

Show the tables in the current database:

SHOW TABLES;

Show the columns in robots:

SHOW COLUMNS FROM robots;

Other useful inspection commands:

DESCRIBE robots;
SHOW CREATE TABLE robots;
SHOW INDEX FROM robots;

Insert Rows

Insert a single row:

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

Insert more than one row with a single statement:

INSERT INTO robots (id, name, kind, year)
VALUES
  (GEN_ID(), "C-3PO", "protocol", 1977),
  (GEN_ID(), "T-800", "android", 1984);

Use DEFAULT when you want CamusDB to apply the column default:

INSERT INTO robots (id, name, kind, year)
VALUES (GEN_ID(), "K-2SO", "security", DEFAULT);

Query Rows

Select rows from the table:

SELECT id, name, kind, year
FROM robots
ORDER BY year ASC;

Filter results with WHERE:

SELECT name, year
FROM robots
WHERE year >= 1980;

Pattern matching is supported with LIKE and ILIKE:

SELECT id, name
FROM robots
WHERE name ILIKE "r%";

Aggregate rows:

SELECT COUNT(*) FROM robots;
SELECT MIN(year), MAX(year) FROM robots;

Create An Index

Indexes help CamusDB avoid scanning every row for matching data.

CREATE INDEX robots_kind_idx ON robots (kind);

Inspect indexes:

SHOW INDEXES FROM robots;

Rename Schema Objects

Tables and columns can be renamed without rewriting row data:

ALTER TABLE robots RENAME COLUMN kind TO category;
ALTER TABLE robots RENAME TO machines;

Update Rows

SQL updates require a WHERE clause.

UPDATE machines
SET year = 1982
WHERE name = "T-800";

Confirm the change:

SELECT name, year
FROM machines
WHERE name = "T-800";

Delete Rows

SQL deletes also require a WHERE clause.

DELETE FROM machines
WHERE name = "K-2SO";

Column Types

SQL type	Notes
STRING	Text values.
INT64	Signed 64-bit integers.
FLOAT64	Double-precision floating point values.
BOOL	Boolean values.
OID	24-character object id values.

Continue with the SQL overview, Tables And Schema, and Query Features for joins, grouping, subqueries, and derived tables.