Tables

Creating a table

A table is a named set of typed columns inside a namespace. Column types include integers (int1 through int16, uint1 through uint16), floats (float4, float8), bool, strings (utf8, with text as an alias), blob, and temporal types (date, time, datetime, duration). See Data Types for the full list. Run the snippets on this page in order:

create namespace dm_tbl;
create table dm_tbl::products {
  id: int4,
  name: utf8,
  price: float8,
  discontinued: bool
};
insert dm_tbl::products [
  { id: 1, name: "Widget", price: 9.99, discontinued: false },
  { id: 2, name: "Gadget", price: 24.5, discontinued: false }
];
from dm_tbl::products

Reads return the newest rows first by default; add sort for an explicit order.

Optional columns

Columns are non-nullable unless declared Option(type). An optional column that was never set holds none - ReifyDB's explicit absent value, which queries can test for and which renders distinctly in results. See Working with none.

create table dm_tbl::customers {
  id: int4,
  name: utf8,
  referral: Option(utf8)
};
insert dm_tbl::customers [
  { id: 1, name: "Ada", referral: "friend" },
  { id: 2, name: "Grace" }
];
from dm_tbl::customers

Auto-increment columns

An integer column declared with { auto_increment } is assigned the next value of a per-column sequence on insert, so writers never need to coordinate IDs. The sequence itself can be inspected and repositioned - see Sequences.

create table dm_tbl::tickets {
  id: int8 with { auto_increment },
  title: utf8
};
insert dm_tbl::tickets [{ title: "First" }, { title: "Second" }];
from dm_tbl::tickets

Primary keys

A primary key is declared as a separate statement after the table exists, and can span multiple columns: create primary key on ns::table { col1, col2 }.

create table dm_tbl::accounts { id: int4, owner: utf8 };
create primary key on dm_tbl::accounts { id }

Updating and deleting

update sets the listed fields and leaves every other column untouched; delete removes whatever matches the filter. Both take a filter to select rows, and both accept returning to hand back the affected rows in the same statement - useful when the write itself computes something you need, like a generated ID or the post-update value:

update dm_tbl::products { price: 19.99 }
filter { name == "Gadget" }
returning { id, name, price }

Without returning, mutations report what happened as a count:

update dm_tbl::products { discontinued: true } filter { id == 1 };
delete dm_tbl::products filter { discontinued == true }

System columns

Every stored row carries engine-maintained columns prefixed with #. They are not returned by default; project them explicitly when you need them. #rownum is the row's stable number within its table:

from dm_tbl::customers map { row: #rownum, name }

Evolving the schema

alter table adds and drops columns in place. Add new columns as Option(type) when existing rows have no value for them:

alter table dm_tbl::customers add column email: Option(utf8)

When a table is not the right shape

• --Bounded recent history with automatic eviction: use a ring buffer
• --Time-ordered measurements and audit records: use a series
• --Repeated low-cardinality strings: intern them with a dictionary
• --State computed from other state: never write it by hand - derive it with a view