SQL Basics

XTDB implements a SQL API that closely follows the ISO standard specifications and draws inspiration from Postgres where needed, however unlike most SQL systems XTDB does not require an explicit schema to be declared upfront.

The only caveat is that each record in XTDB must contain a user-provided xt$id primary key column, but other columns are fully dynamic - meaning each row in XTDB has the flexibility of a document in a document database.

Note: xt$ is a reserved prefix convention for namepacing system-mandated columns and other XTDB-specific database objects.

Insert a row into a new table

To run your first INSERT transaction, simply enter the following into xtsql:

INSERT INTO people (xt$id, name) VALUES (6, 'fred');

Here the people table has not been defined anywhere beforehand - the table is created dynamically during the INSERT statement, along with any supplied columns and inferrable type information.

Query for that same row

Querying this data back again is a simple matter of:

SELECT * FROM people;

Evolve the table

If we now INSERT another row with a slightly different shape, XTDB will gracefully evolve the schema of the people table to reflect the union of all the records it contains:

INSERT INTO people (xt$id, name, likes)
  VALUES (9, 'bob', ['fishing', 3.14, {'dynamic':'data'}]);

Re-running our SELECT * FROM people you should now see:

| xt$id | likes                                   | name    |
|-------|-----------------------------------------|---------|
| 6     | None                                    | 'fred'  |
| 9     | ['fishing', 3.14, {'dynamic': 'data'}]  | 'bob'   |

Handling ‘documents’

XTDB is designed to work with JSON-like nested data as a first-class concept (i.e. highly flexible but not restricted to JSON or JSONB types). This means you can easily store deeply-nested document structures:

UPDATE people
SET info = {'contact': [{'loc': 'home',
                         'tel': '123'},
                        {'loc': 'work',
                         'tel': '456'}]}
  WHERE people.name = 'fred';

And you can then query this nested data intuitively:

SELECT people.info.contact[2].tel FROM people
  WHERE people.name = 'fred';

Pretty neat! But what if we made a mistake somewhere and wanted to undo a change? The thing that makes XTDB most interesting is the approach to immutability and time-travel, read on…​