CamusDB tutorial

CamusDB is an open-source NewSQL distributed database. It runs on one node, or on a cluster of several nodes. It gives an application a full relational engine, and not a thin layer of SQL over a key/value store.

CamusDB includes these capabilities:

Transactional SQL, for a schema, a write, a read, an index, and an aggregation. It also supports a join, a subquery, a derived table, a view, and a materialized view.
Serializable transactions by default, with the detection of a conflict. CamusDB uses two-phase commit for a write across two partitions.
Distributed storage on the Raft consensus, with a leader for each partition, and with a replicated schema.
A cost-based query planner, with EXPLAIN, statistics, an automatic analyze, and a cache of the results.
A branch of a database, with a copy at the first write.
A time travel read, which reads the data at a point in the past.
Vector search over an embedding, with a distance function that the CPU accelerates.
A recoverable drop of a database and of a table.
Several interfaces: camus-cli, a web console, an HTTP API, a gRPC API, a TypeScript connector, a driver for .NET, a provider for EF Core, and a server for the Model Context Protocol.

This tutorial uses camus-cli, which is the interactive shell of SQL. It walks through the basic workflow, against a CamusDB node or a cluster that runs. The workflow has six steps:

Create a database.
Create a table.
Insert some rows.
Query the data.
Add an index.
Update a row, and delete one.

Start with Why CamusDB? for an overview at a higher level. That page explains the design of CamusDB as a distributed database of SQL.

Try it in your browser

You can run the tutorial commands here before installing anything. The embedded playground starts with a pre-created playground database, but you can also create your own database and switch with the database selector. Click the examples in order, or edit the SQL and experiment.

WebAssembly

Run the tutorial here

This is the real CamusDB engine compiled to WebAssembly. It runs as a single in-memory node inside this browser tab.

Loading the engine...

Database

SQL

CREATE DATABASE IF NOT EXISTS factory;

The engine is loading. The first load downloads the WebAssembly runtime.

Single-node, in-memory engine. Data stays in this tab and resets on reload.

Video walkthrough

Start CamusDB

CamusDB ships as a global tool of .NET. Install the .NET runtime first. Then install the server, and start a standalone node:

$ dotnet tool install --global CamusDB.Server
$ camusdb

The camusdb command starts with its built-in defaults when no file of a configuration exists. It stores the data under the data directory of the user. It listens on the HTTP port 5095, and on the gRPC port 5096:

   ____                          ____  ____
  / ___|__ _ _ __ ___  _   _ ___|  _ \| __ )
 | |   / _` | '_ ` _ \| | | / __| | | |  _ \
 | |__| (_| | | | | | | |_| \__ \ |_| | |_) |
  \____\__,_|_| |_| |_|\__,_|___/____/|____/

Configuration: built-in defaults (no configuration file found)
Data directory: /Users/runner/.local/share/camusdb

Update the installed server:

$ dotnet tool update --global CamusDB.Server

Run this command to create a configuration of a start, under your own ownership:

$ camusdb init

That command writes ~/.camusdb/config.yml. It also creates the default data directory. Edit the file. Then run camusdb again.

You can also start CamusDB with Docker:

$ docker run --rm \
        -p 5095:5095 \
        -p 5096:5096 \
        -v camus-data:/data \
        --name camusdb camusdb/camusdb:latest

Install the shell of SQL:

$ dotnet tool install --global CamusDB.SqlSh

Then open the shell of SQL, in another terminal:

$ camus-cli

An interactive prompt appears:

CamusDB SQL Shell 0.11.0

Connected to http://localhost:5096 over gRPC, database: (none)

camus>

Create a database

You must create a database explicitly before you use it. Create a database. Then move the shell to it:

camus> CREATE DATABASE factory;
Query OK, 0 rows affected (00:00:00.0711685)

camus> use factory;
Database changed to factory

You can start the shell with camus-cli factory. You nevertheless still need the statement CREATE DATABASE factory;, at the first use of that name of a database.

Create a table

Create a table for the records of a robot:

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

The table holds four columns:

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.

For an identifier of a UUID, use the native type UUID. Do not use a STRING. A UUID column stores a compact value of 128 bits. It also uses a smaller key of an index than the text of a UUID.

Inspect the schema

Show the tables of the current database:

camus> show tables
┌────────┐
│ tables │
├────────┤
│ robots │
└────────┘
1 rows in set (00:00:00.0526560)

Show the columns of robots:

camus> show columns from robots
┌───────┬───────────┬──────┬─────┬─────────┬───────┐
│ Field │ Type      │ Null │ Key │ Default │ Extra │
├───────┼───────────┼──────┼─────┼─────────┼───────┤
│ id    │ Id        │ NO   │ PRI │ NULL    │       │
│ name  │ String    │ NO   │     │ NULL    │       │
│ kind  │ String    │ NO   │     │ NULL    │       │
│ year  │ Integer64 │ YES  │     │ 2024    │       │
└───────┴───────────┴──────┴─────┴─────────┴───────┘
4 rows in set (00:00:00.0189059)

Three other commands of an inspection are useful:

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

Show the definition in SQL of the table:

camus> show create table robots;
┌────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Table  │ Create Table                                                                                                                       │
├────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ robots │ CREATE TABLE `robots` ( `id` OID NOT NULL, `name` STRING NOT NULL, `kind` STRING NOT NULL, `year` INT64 NULL, PRIMARY KEY (`id`)); │
└────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
1 rows in set (00:00:00.0037082)

Insert rows

Insert one row:

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

Insert more than one row, with one statement:

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

Use DEFAULT when CamusDB must apply the default of the column:

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

Query rows

Select the rows of the table:

camus> SELECT id, name, kind, year FROM robots ORDER BY year ASC;
┌──────────────────────────┬───────┬──────────┬──────┐
│ id                       │ name  │ kind     │ year │
├──────────────────────────┼───────┼──────────┼──────┤
│ 6a3dd713d615ae230488d7f2 │ R2-D2 │ utility  │ 1977 │
│ 6a3dd71bd615ae230488d7f4 │ C-3PO │ protocol │ 1977 │
│ 6a3dd71bd615ae230488d7f5 │ T-800 │ android  │ 1984 │
│ 6a3dd726d615ae230488d7f8 │ K-2SO │ security │ 2024 │
└──────────────────────────┴───────┴──────────┴──────┘
4 rows in set (00:00:00.0232238)

Filter the result with a WHERE clause:

camus> SELECT name, year FROM robots WHERE year >= 1980;
┌───────┬──────┐
│ name  │ year │
├───────┼──────┤
│ T-800 │ 1984 │
│ K-2SO │ 2024 │
└───────┴──────┘
2 rows in set (00:00:00.0298712)

LIKE and ILIKE match a pattern:

camus> SELECT id, name FROM robots WHERE name ILIKE "r%";
┌──────────────────────────┬───────┐
│ id                       │ name  │
├──────────────────────────┼───────┤
│ 6a3dd713d615ae230488d7f2 │ R2-D2 │
└──────────────────────────┴───────┘
1 rows in set (00:00:00.0254039)

Aggregate the rows:

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

Create an index

An index helps CamusDB. It then does not scan every row to find the matching data.

CREATE INDEX robots_kind_idx ON robots (kind);

Inspect the indexes:

SHOW INDEXES FROM robots;

Rename schema objects

You can rename a table, and a column. CamusDB rewrites no data of a row:

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

Update rows

An UPDATE in SQL needs 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

A DELETE in SQL also needs a WHERE clause.

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

Column types

SQL type	Notes
`OID`	Native object id values.
`UUID`	Native 128-bit UUID values. Prefer this over `STRING` for UUID identifiers.
`INT64`	Signed 64-bit integers.
`FLOAT64`	Double-precision floating point values.
`FLOAT32`	Single-precision floating point values.
`BOOL`	Boolean values.
`STRING`, `STRING(N)`	Text values, optionally with a maximum length.
`DATE`, `DATETIME`	Calendar dates and UTC instants.
`BYTES`	Opaque byte strings.
`ARRAY(T)`	Ordered lists of scalar values.

Continue with three pages: the SQL overview, Data Types, and Tables And Columns.

Then read SELECT for a filter, a group, and an order. Read Joins And Subqueries for a join, a subquery, and a derived table.

Try it in your browser​

Run the tutorial here

Video walkthrough​

Start CamusDB​

Create a database​

Create a table​

Inspect the schema​

Insert rows​

Query rows​

Create an index​

Rename schema objects​

Update rows​

Delete rows​

Column types​