Skip to content
XTDB

XTQL Transactions

Transactions in XTDB are submitted to the transaction log, to be processed asynchronously. They each consist of an array of operations.

Clojure Kotlin

For details about how transactions are submitted, see your individual client language documentation:

Transaction operations

Clojure Kotlin

putDocs

Upserts documents into the given table, optionally during the given valid time period.

{
  // -- 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"
}

Clojure Kotlin

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"
}

Clojure Kotlin

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",
}

Clojure Kotlin

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" } }
    ]
  }
}

Clojure Kotlin

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:

Clojure Kotlin

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" } ]
    }
  ]
}

Clojure Kotlin

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" } ]
}

Clojure Kotlin

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" } ]
}

Clojure Kotlin

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.

Clojure Kotlin

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,
}