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`.
`HEADING "text"`	A human label for the column, such as `Date of Birth`. Optional; without it the heading is the column's name.
`SOURCE TYPE "type"`	The type the column had where it came from, such as `DECIMAL(10,2)`. Set by imports so an export reproduces the original schema; rarely written by hand.

Headings

Every column has a name, which scripts use, and a heading, which people read. They are the same unless a heading is given. A heading is what a spreadsheet shows at the top of a column or what an SQL dump records as a column comment, so keeping it separate from the name lets a table carry a label such as Date of Birth while scripts refer to dob. Tables made without headings behave exactly as before.

Columns with headings

NEW TABLE "staff" IN DB "shopdb" COLUMNS
ADD "id" TYPE NUMBER AUTOINCREMENT PRIMARY
AND ADD "dob" TYPE DATE HEADING "Date of Birth"
AND ADD "email" LENGTH 255 TYPE STRING UNIQUE HEADING "Email address"
AND ADD "notes" TYPE STRING

A table can gain columns after it is made. EXTEND TABLE takes the same ADD list as NEW TABLE, headings included, and returns how many columns the table now has. Existing rows take each new column's DEFAULT, or no value. A name already in the table is refused rather than redefined.

Extending a table

EXTEND TABLE "staff" IN DB "shopdb" COLUMNS
ADD "phone" TYPE STRING HEADING "Phone number"
AND ADD "active" TYPE BOOL DEFAULT true SET ?count
AFTER EMIT ?count

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.

DESCRIBE TABLE reports each column's heading, and a source_type for columns that came from an import. When only the names or only the headings are wanted, in the order the table defines them:

Column names and headings

LIST COLUMNS FROM DB "shopdb" TABLE "staff" SET ?names
AFTER LIST HEADINGS FROM DB "shopdb" TABLE "staff" SET ?labels
AFTER EMIT ?names & " | " & ?labels
(* id, dob, email, notes | id, Date of Birth, Email address, notes *)

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 Import & Export Database →