SQL Transactions

For examples on how to submit SQL transactions in each client library, see the individual driver documentation.

Transaction operations

INSERT

Inserts documents into a table.

• Documents must contain an _id column.

• By default, the document will be inserted for valid-time between now and end-of-time. This can be overridden by including _valid_from and/or _valid_to columns in the document.

• If the document already exists, 'insert' behaves like an upsert - it will overwrite the existing document for the valid-time range specified (or now → end-of-time if not provided).

UPDATE

Updates documents in a table, optionally for a period of valid-time.

• If the valid-time range is not provided, the effective valid-time range of the update will be from now to the end of time. (SQL:2011 specifies that updates without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)

• The _id column cannot be updated - instead, users should delete this document and re-insert a new one.

• The valid-time columns cannot be updated outside of the for-valid-time clause (i.e. not in the SET clause).

PATCH

Patches documents already in a table with the given document - updating those that exist, inserting any that don’t (an 'upsert') - optionally for a period of valid-time.

• If the valid-time range is not provided, the effective valid-time range of the update will be from now to the end of time.

• The _id column cannot be patched - instead, users should delete this document and re-insert a new one.

• The valid-time columns cannot be updated outside of the for-valid-time clause (i.e. not in the records themselves).

• Documents are currently merged at the granularity of individual keys - e.g. if a key is present in the patch document, it will override the same key in the database document; if a key is absent or null, the key from the document already in the database will be preserved.

DELETE

Deletes documents from a table, optionally for a period of valid-time.

• If the valid-time clause is not provided, the effective valid-time range of the delete will be from now to the end of time. (SQL:2011 specifies that deletes without this clause should be effective for all valid time; the now→end-of-time default is an XTDB deviation.)

ERASE

Irrevocably erases documents from a table, for all valid-time, for all system-time.

While XTDB is immutable, in some cases it is legally necessary to irretrievably delete data (e.g. for a GDPR request). This operation removes data such that even queries as of a previous system-time no longer return the erased data.

ASSERT

Rolls back the transaction if the provided predicate is false.

This is used to enforce constraints on the data in a concurrent environment, such as ensuring that a document with a given ID does not already exist.

• We check to see whether the email address already exists in the database - if not, we can insert the new user.

  ASSERT NOT EXISTS (SELECT 1 FROM users WHERE email = 'james@example.com')
  
  INSERT INTO users (_id, name, email) VALUES ('james', 'James', 'james@example.com')

• Check the xt.txs table for the transaction result to see if the assertion failed.

  SELECT * FROM xt.txs;

   _id  | committed |            error              |          system_time
  ------+-----------+-------------------------------+-------------------------------
      0 | t         | null                          | "2024-06-25T16:45:16.492255Z"
      1 | t         | null                          | "2024-06-25T16:45:26.985539Z"
      2 | f         | ... "Precondition failed" ... | "2024-06-25T16:45:32.577224Z"
  (3 rows)