Writing safely

Every write in TessariQL says how far it reaches — by naming the one record it changes, or by stating a condition and a bound. No write reaches an unstated number of records, and that is the first safety property on this page rather than a missing feature.

DEFINE TABLE users (name string, age int, city string);
DEFINE TABLE r (at int);
CREATE users:1 = { name: 'ada', age: 17 };
CREATE users:2 = { name: 'bob', age: 30 };

r is declared here because the section on bounded deletes further down writes to it. Everything on this page runs in the order it is written, against the store those four statements leave behind.

Creating twice is refused

CREATE users:1 = { name: 'dup' };

Refused — RecordExists.

error: record 1 already exists — say `UPDATE` to replace it

CREATE means this is new. When you do not know whether it is, say so:

UPSERT users:2 = { name: 'ada2' };   -- there: replaced
UPSERT users:9 = { name: 'new' };    -- not there: created

Replace and merge are different words

This is the distinction most worth having in your fingers, because both spellings succeed and only one of them is usually what you meant.

UPDATE users:1 SET age = 18 RETURN AFTER;

{ age: 18, name: 'ada' }

UPDATE users:1 MERGE { city: 'Paris' } RETURN AFTER;

{ age: 18, city: 'Paris', name: 'ada' }

UPSERT users:1 = { name: 'only' } RETURN AFTER;

{ name: 'only' }

The age is gone. = { … } is the whole record, so anything the object does not mention is not kept — which is right for CREATE and is a data-loss bug in a save handler that reposts a form with three of the record's five fields.

Reach for MERGE when the object you hold is a patch, and = { … } only when it is the record. SET changes named fields and leaves the rest alone, like MERGE, and reads better when the new value is computed rather than supplied.

RETURN BEFORE and RETURN AFTER

A write answers with ok unless you ask it for something:

UPDATE users:1 SET age = 18 RETURN BEFORE;

{ name: 'only' }

That is the record the previous section left behind — no age, because the UPSERT above replaced it away. Which is the point of asking for BEFORE: it answers with what was actually there, not with what you assumed was there.

BEFORE is the record as it stood; AFTER is what it became. BEFORE is the one people forget exists, and it is the cheapest audit trail there is — the previous value, returned by the write itself, in the same transaction, with no second read that could see something else.

DELETE takes it too, which is the only chance you get:

DELETE users:9 RETURN BEFORE;

{ name: 'new' }

A write says how far it reaches

A write either names one record, or states a condition and a bound. It is never allowed to leave the reach unsaid.

DELETE FROM r;

Refused — UnexpectedToken.

error: expected `WHERE` and what to remove, found `;`

DELETE FROM r WHERE at < 5;

Also UnexpectedToken.

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

Both halves have to be there:

DELETE FROM r WHERE at < 5 LIMIT 1;     -- at most one
DELETE FROM r WHERE at < 5 LIMIT ALL;   -- however many match

LIMIT ALL is not ceremony. A DELETE whose WHERE matched more records than the author believed — or whose WHERE was lost in an edit — is one of the two or three ways working databases are most often damaged, and the damage is finished before anything reports it. Making the unbounded case a phrase you have to type means it cannot happen by omission: the dangerous statement and the safe one no longer differ by a clause that is missing, they differ by a clause that is present and says ALL.

An UPDATE names its record:

UPDATE users SET age = 0;

Refused — UnexpectedToken.

error: expected `:` and the record's identity, found `SET`

To change many, read them and write each. That is more typing, and it is meant to be — the read is a step where you can look at what you are about to change before you change it.

THROW abandons the whole transaction

BEGIN;
  UPDATE users:1 SET age = 99;
  THROW 'no';
COMMIT;

Refused — Thrown.

error: no

And afterwards:

SELECT * FROM users:1;

1: { age: 18, name: 'only' }
(1 record(s), via record)

The update is not there. THROW raises the message you give it, and nothing inside the transaction survives — so a check written in the middle of a multi-statement write can refuse the whole thing without having to undo the part that already ran.

Use it for the condition the schema cannot state: a balance that must not go negative, a status the record is not allowed to move to from where it is, a count that has to match something read a moment earlier. See Transactions for what BEGIN and COMMIT guarantee around it.