{
// -- required
// array of documents to put, each of which must contain `xt$id`.
"putDocs": [ { "xt$id" : 1, "foo" : "bar" }, ...],
// table to insert into
"into": "docs",
// -- optional
// Defaults to the current time of the transaction if not provided.
// "validFrom": "2023-01-01T00:00:00Z",
// Defaults to the end-of-time if not provided.
// "validTo": "2024-01-01T00:00:00Z"
}
XTQL Transactions
Transactions in XTDB are submitted to the transaction log, to be processed asynchronously. They each consist of an array of operations.
For details about how transactions are submitted, see your individual client language documentation:
Transaction operations
putDocs
Upserts documents into the given table, optionally during the given valid time period.
deleteDocs
Deletes documents from the given table, optionally during the given valid time period. The default valid time behaviour is the same as put, above.
{
// -- required
// array of ids to delete.
"deleteDocs": [ 1, "alan" ],
// table to delete from
"from": "docs",
// -- optional
// Defaults to the current time of the transaction if not provided.
// "validFrom": "2023-01-01T00:00:00Z",
// Defaults to the end-of-time if not provided.
// "validTo": "2024-01-01T00:00:00Z"
}
eraseDocs
Irrevocably erases documents from the given table (including through system time), for all valid-time.
{
// -- required
// array of ids to erase.
"eraseDocs": [ 1, "alan" ]
// table to erase from
"from": "docs",
}
insertInto
Inserts documents into a table from the given query.
{
// -- required
// table to insert into.
"insertInto": "users",
// XTQL query
// * must yield an `xt$id` column
// * may yield `xt$validFrom`/`xt$validTo` to set validity period
// otherwise, defaults as per 'put'
"query": [ ... ]
}
Example:
-
copy from another table:
INSERT INTO users SELECT xt$id, first_name AS given_name, last_name AS family_name FROM old_users
{ "insertInto": "users", "query": { "from": "oldUsers", "bind": [ "xt$id" { "firstName": { "xt:lvar": "givenName" } }, { "lastName": { "xt:lvar": "familyName" } } ] } }
update
Updates documents in a given table based on the provided query.
{
// -- required
// table to update
"update": "users",
// -- optional
// the valid time period to update the rows for
// * see 'temporal filters'
// "forValidTime": { "from" : "2024-01-01T00:00:00Z" },
// binding specs - selects the documents to update
// * if not provided, will update every document in the table
// * see 'binding specs'
// * may also bind extra logic variables for use in the `set` or `unify` clauses.
"bind": [ { "email": { "xt:param": "$email" } }, "version" ],
// -- required
// columns to update (array of maps, string -> expression)
// * see 'expressions'
// * altering any `xt$...` column in set will result in an error.
"set": [
{ "version": { "xt:call": "+", "args": [ { "xt:lvar": "version" }, 1 ] } }
],
// -- optional
// extra clauses to unify with (e.g. to join against another table)
// * see 'unify' for more details.
// "unify": [ ... ],
// argument-rows to be used in the `bind`, `set` or `unify` clauses.
// * if provided, the update operation will be executed for every argument-row.
// * see 'argument specs'
"argRows": [ { "email": "james@example.com" } ],
}
Example:
-
Increment a version for a user account, by email:
UPDATE users SET version = version + 1 WHERE email = ? -- with argument 'james@example.com'
{ "update": "users", "bind": [ { "email": { "xt:param": "$email" } }, "version" ], "set": [ { "version": { "xt:call": "+", "args": [ { "xt:lvar": "version" }, 1 ] } } ], "argRows": [ { "email": "james@example.com" } ], }
See also:
delete
Deletes documents from the given table, based on the provided query.
{
// -- required
// table to delete from
"deleteFrom": "users",
// -- optional
// the valid time period to delete the rows for
// * see 'temporal filters'
// "forValidTime": { "from" : "2024-01-01T00:00:00Z" },
// select the documents to delete using binding constraints
// * if not provided, will delete every document in the table
// * see 'binding specs'
// * may also bind extra logic variables for use in the `unify` clauses.
"bind": [ { "email": "james@example.com" } ],
// extra clauses to unify with (e.g. to join against another table)
// * see 'unify' for more details.
// "unify": [ ... ],
// argument-rows to be used in the `bind` or `unify` clauses.
// * if provided, the delete operation will be executed for every argument-row.
// * see 'argument specs'
// "argRows": [ { "email": "james@example.com" } ],
}
See also:
Examples:
-
DELETE FROM users WHERE email = 'james@example.com'
{ "deleteFrom": "users", "bind": [ { "email": "james@example.com" } ] }
-
Deleting using a sub-query
DELETE FROM users WHERE author_id IN (SELECT author_id FROM authors WHERE email = 'james@example.com'
{ "deleteFrom": "posts", "bind": [ "authorId" ], "unify": [ { "from": "authors", "bind": [ { "xt$id": { "xt:lvar": "authorId" } }, { "email": "james@example.com" } ] } ] }
erase
Irrevocably erase the document from the given table (for all valid-time, for all system-time), based on the provided query.
{
// -- required
// table to erase from
"eraseFrom": "users",
// -- optional
// select the documents to erase using binding constraints
// * if not provided, will erase every document in the table
// * see 'binding specs'
// * may also bind extra logic variables for use in the `unify` clauses.
"bind": [ { "email": "james@example.com" } ],
// extra clauses to unify with (e.g. to join against another table)
// * see 'unify' for more details.
// "unify": [ ... ],
// argument-rows to be used in the `bind` or `unify` clauses.
// * if provided, the erase operation will be executed for every argument-row.
// * see 'argument specs'
// "argRows": [ { "email": "james@example.com" } ],
}
See also:
Examples:
-
erase by email:
ERASE FROM users WHERE email = ? -- 'james@example.com' supplied separately as an argument
{ "eraseFrom": "users", "bind": [ { "email": { "xt:param": "$email" } } ], "argRows": [ { "email": "james@example.com" } ] }
-
erasing using a sub-query:
ERASE FROM users WHERE author_id IN (SELECT author_id FROM authors WHERE email = ?) -- 'james@example.com' supplied separately as an argument
{ "eraseFrom": "posts", "bind": [ "authorId" ], "unify": [ { "from": "authors", "bind": [ { "xt$id": { "xt:lvar": "authorId" } }, { "email": { "xt:param": "$email" } } ] } ], "argRows": [ { "email": "james@example.com" } ] }
Asserts: assertExists
, assertNotExists
Within a transaction, assertExists
/assertNotExists
operations assert that the given query returns at least one row/no rows respectively - if not, the transaction will roll back.
{
// -- required: one of `assertExists` or `assertNotExists`
// XTQL query
// for `assertExists` the transaction will roll back if the query returns zero rows
"assertExists": [ ... ],
// XTQL query
// for `assertNotExists` the transaction will roll back if the query returns any rows
"assertNotExists": [ ... ],
// -- optional
// argument-rows to be used in the query.
// * if provided, the assert operation will be executed for every argument-row.
// * see 'argument specs'
"argRows": [ { "email": "james@example.com" } ]
}
call
Call a transaction function.
{
// -- required
// function to call
fnId: "fn-id",
// -- optional
// list of arguments to the function
// args: [ ... ]
}
Transaction functions can currently only be defined in Clojure.
Transaction options
Transaction options are an optional map of the following keys:
{
// -- optional
// overrides system-time for the transaction
// * mustn't be earlier than any previous system time
"systemTime": "2024-01-01T00:00:00Z",
// overrides the default time zone for operations in the transaction.
// default: "UTC"
"defaultTz": "America/Los_Angeles",
// whether to default to "all valid-time" if not explicitly specified in the query
// default: false
"defaultAllValidTime": false,
}