Databases & Tables

A table defines the shape of your data — its columns, their types, and the rules that protect them. Every table lives inside exactly one database.

Creating a database

Before a table can exist, it needs a database to live in. Creating one is a single statement.

Example

NEW DB "shopdb"

Creating a table

Example

NEW TABLE "people" IN DB "shopdb" COLUMNS
ADD "id" TYPE NUMBER AUTOINCREMENT PRIMARY
AND ADD "name" LENGTH 100 TYPE STRING REQUIRED
AND ADD "email" LENGTH 255 TYPE STRING UNIQUE REQUIRED
AND ADD "age" TYPE NUMBER DEFAULT 0

Column types

TYPE	Description
`STRING`	Text. Pair with `LENGTH` to cap how many characters are stored.
`NUMBER`	Any numeric value, integer or decimal.
`BOOL`	`true` or `false`.
`DATE`	A timestamp, stored and compared numerically but displayed as a readable date — same as values produced by NEW DATE.

Column modifiers

Modifier	Effect
`LENGTH n`	Maximum length for a `STRING` column.
`AUTOINCREMENT`	Value is generated automatically on insert — never supply it yourself.
`PRIMARY`	Marks this column as the row's primary identifier. Indexed for fast lookups.
`UNIQUE`	Rejects any insert or update that would duplicate an existing value in this column. Indexed for fast lookups.
`REQUIRED`	Rejects an insert if this column is missing and has no default.
`DEFAULT value`	Fills the column automatically when not supplied.
`AUTOTIMESTAMP`	A `DATE` column stamped once, automatically, at the moment a row is inserted.
`AUTOUPDATED`	A `DATE` column re-stamped automatically every time the row is changed by `UPDATE`.

Indexes

Any column marked PRIMARY or UNIQUE gets a real index behind the scenes. Filtering on that column in a WHERE clause is fast regardless of how many rows the table holds — it never needs to scan every row to find a match.

Automatic timestamps

Example

NEW TABLE "posts" IN DB "shopdb" COLUMNS
ADD "id" TYPE NUMBER AUTOINCREMENT PRIMARY
AND ADD "title" TYPE STRING REQUIRED
AND ADD "created_at" TYPE DATE AUTOTIMESTAMP
AND ADD "updated_at" TYPE DATE AUTOUPDATED

created_at is set once and never changes again. updated_at refreshes automatically every time the row is modified by UPDATE — a true "last modified" timestamp with no manual work required.

Inspecting the schema

Three verbs read back what already exists. Nothing is created or changed — they report the shape of the namespace as it stands, so a script can work against a schema it did not write.

Every database in the namespace

LIST DATABASES SET ?dbs
AFTER EMIT ?dbs
(* An array of database names, alphabetically *)

Every table in one database

LIST TABLES FROM DB "shopdb" SET ?tables
AFTER EMIT ?tables
(* Output: orders, people, users *)

IN DB is accepted wherever FROM DB is, so LIST TABLES IN DB "shopdb" reads the same.

A table's columns

DESCRIBE TABLE "people" FROM DB "shopdb" SET ?columns
AFTER EMIT ?columns

DESCRIBE TABLE returns an array of objects, one per column, in the order the table was defined. Every column modifier is reported, whether or not it was used:

Key	Value
`name`	Column name
`type`	`string`, `number`, `bool`, or `date`
`length`	The `LENGTH` cap, or null
`primary` `unique` `required`	Booleans, one per modifier
`autoincrement` `autotimestamp` `autoupdated`	Booleans, one per modifier
`default`	The `DEFAULT` value, or null

Walking a schema

DESCRIBE TABLE "people" FROM DB "shopdb" SET ?columns
AFTER COUNT ?columns SET ?n
AFTER EMIT "columns: " & ?n
AFTER EMIT " first: " & ?columns(0)("name") & " (" & ?columns(0)("type") & ")"
(* Output: columns: 5 first: id (number) *)

A database that does not exist is a catchable E2001; a table that does not exist is E3002. Both carry ?err("message"), ?err("code"), and ?err("verb") — see Error Codes.

Clearing and removing tables

FLUSH — empty a table, keep its schema

FLUSH TABLE "people" FROM DB "shopdb"

DROP — remove the table entirely

DROP TABLE "people" FROM DB "shopdb"

What's next

Continue to Columns & Rows to insert, query, update, and join data.

← Video & Audio Processing Columns & Rows →