Records

A record lives in a table and has an id. The two are written together, and that pair is the record's address.

You do not have to invent the id. Write to the table, and the store names the record and answers with the name it chose:

DEFINE TABLE users (name string, email string);
CREATE users = { name: 'ada', email: 'ada@example.com' };
CREATE users = { name: 'grace', email: 'grace@example.com' };

1
2

The table keeps a counter, so those two records are users:1 and users:2, and that pair is the address each one is read back by. An id you invent yourself is a uniqueness problem you have just taken on, and the usual answers to it — a counter kept somewhere else, a timestamp, a random number long enough to feel safe — are all worse than the one the table is already keeping.

Naming the record yourself is the option rather than the default. Write the address in place of the table when the id already means something outside the store — an order number, an account id, a slug — and CREATE uses it: CREATE users:'ada' = { … }. A text id is written in quotes, the same as any other text; an id is an integer, text, a uuid or bytes, and a bare word is none of those. Everything below that addresses a record reads the same either way.

How a read reaches a record

Every read reports the access path it took, and the difference is what the read costs. Three of them cover everything on this page.

By identity, reported as via record — the address is known, so nothing is searched:

SELECT * FROM users:1;

Through an index, reported as via index — a condition over an indexed field:

DEFINE INDEX by_email ON users FIELDS email UNIQUE;
SELECT * FROM users WHERE email = 'grace@example.com';

By scan, reported as via scan — everything else, including a whole table:

SELECT * FROM users;

The node reports which one it used, so this is a property you can observe rather than one you have to reason about.

Those three are not the whole vocabulary. The store names eight access paths — record, index, ordered, scan, approximate, graph, join and materialised — and the other five belong to reads this page has not reached yet. What a read reports is where they are listed and where USING asserts one.

A read that matches nothing is not an error

SELECT * FROM users WHERE email = 'nobody@example.com';

No rows. Asking about something that is not there is a legitimate question with an empty answer.

Create never replaces, update never invents

These are two statements rather than one UPSERT because they mean different things, and collapsing them hides the case you did not expect.

CREATE users:1 = { name: 'someone else' };

Refused — RecordExists. The record is already there and CREATE will not write over it.

UPDATE users:99 = { name: 'nobody' };

Refused — NoSuchRecord. There is nothing at that address to update.

An update with = replaces the record whole:

UPDATE users:1 = { name: 'ada lovelace', email: 'ada@example.com' };
SELECT * FROM users WHERE email = 'ada@example.com';

Writing several records at once

CREATE writes one record. INSERT writes several, and the store names them the same way:

INSERT INTO users (name, email) VALUES
    ('katherine', 'katherine@example.com'),
    ('dorothy', 'dorothy@example.com');

The answer carries the produced identities, in the order the rows were written — always, with no RETURNING to remember. There is nothing else it could usefully say, and a caller who has just handed the store the naming of a record needs the names back or the write is unreachable. A client reads them off the answer, and the command line prints them one to a line.

An identity is produced once, by the node the statement reached, and travels in the log. A replica applying that log holds the same identity rather than deriving its own — two replicas disagreeing about a record's name would be worse than making the caller type it.

The whole statement is one transaction. A row that fails in the middle leaves none of the batch behind:

INSERT INTO users (name, email) VALUES ('ada', 'a@example.com'), ('bad', 42);

Refused, and the row before it is not there either — the table holds the two records it held before the statement ran. That refusal happens while the rows are being written, because 42 is a number where the table declares a string.

A row that holds the wrong number of values fails earlier still: the field list and each row must agree, and a mismatch is refused at parse — InsertRowArity — so a bad row writes nothing before it.

Delete takes the indexes with it

DELETE users:2;
SELECT * FROM users WHERE email = 'grace@example.com';

No rows. The index forgot the record along with the record — an index entry outliving its record is the failure this makes impossible rather than unlikely.

A delete with no condition is refused

DELETE FROM users; does not empty the table. The language reads a delete with no condition as naming one record by identity and refuses it, deliberately, so that a mistyped statement cannot empty a table by accident.

A condition alone is not enough either — a conditional delete must also say how far it reaches:

DELETE FROM users WHERE true;

Refused — UnexpectedToken.

error: expected `LIMIT n` or `LIMIT ALL` — a delete over a set states how much it
may remove at 28..29, found `;`

So emptying a table is asked for out loud, in both halves:

DELETE FROM users WHERE true LIMIT ALL;

See Writing safely for the rest of the write side — replace against merge, RETURN BEFORE, and THROW.