XTQL Transactions (Clojure)

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

This document provides examples for EDN transaction operations, to be submitted to xt/execute-tx or xt/submit-tx.

Transaction operations

`put-docs`

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

[:put-docs
 ;; -- required

 ;; options map
 ;; * can just provide `<table>` rather than a map if there are
 ;;   no other options
 {;; -- required

  ;; table to put docs into (keyword)
  :into <table>

  ;; --optional

  ;; valid-from, valid-to can be `java.util.Date`, `java.time.Instant`
  ;; or `java.time.ZonedDateTime`
  :valid-from #inst "..."
  :valid-to #inst "..."
  }

 ;; -- required
 ;; documents to submit (variadic, 0..n)
 ;; * each must contain `:xt/id`
 & <docs>
 ]

Examples

single document
```
[:put-docs :my-table {:xt/id :foo}]
```

with options

[:put-docs {:into :my-table, :valid-from #inst "2024-01-01"}
 {:xt/id :foo, ...}
 {:xt/id :bar, ...}]

dynamically generated

(into [:put-docs {:into :my-table, ...}]
      (->> (range 100)
           (map (fn [n]
                  {:xt/id n, :n-str (str n)}))))

`patch-docs`

Upserts documents into the given table, merging them with any existing documents, optionally during the given valid time period.

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.

[:patch-docs
 ;; -- required

 ;; options map
 ;; * can just provide `<table>` rather than a map if there are
 ;;   no other options
 {;; -- required

  ;; table to patch docs into (keyword)
  :into <table>

  ;; --optional

  ;; valid-from, valid-to can be `java.util.Date`, `java.time.Instant`
  ;; or `java.time.ZonedDateTime`
  :valid-from #inst "..."
  :valid-to #inst "..."
  }

 ;; -- required
 ;; documents to submit (variadic, 0..n)
 ;; * each must contain `:xt/id`
 & <docs>
 ]

Examples

single document

[:put-docs :my-table {:xt/id :foo, :a 1}]
[:patch-docs :my-table {:xt/id :foo, :b 2}]

;; => {:xt/id :foo, :a 1, :b 2}

with options

[:patch-docs {:into :my-table, :valid-from #inst "2024-01-01"}
 {:xt/id :foo, ...}
 {:xt/id :bar, ...}]

dynamically generated

(into [:patch-docs {:into :my-table, ...}]
      (->> (range 100)
           (map (fn [n]
                  {:xt/id n, :n-str (str n)}))))

`delete-docs`

Deletes documents from the given table, optionally during the given valid time period. The default valid time behaviour is the same as put, above.

[:delete-docs
 ;; -- required

 ;; options map
 ;; * can just provide `<table>` rather than a map if there are no other options
 {;; -- required

  ;; table to delete docs from
  :from <table>

  ;; --optional

  ;; valid-from, valid-to can be `java.util.Date`, `java.time.Instant` or `java.time.ZonedDateTime`
  :valid-from #inst "..."
  :valid-to #inst "..."
  }

 ;; -- required
 ;; document ids to delete (variadic, 0..n)
 & <ids>
 ]

Examples:

single document
```
[:delete-docs :my-table :foo]
```

with options

[:delete-docs {:from :my-table, :valid-from #inst "2024-01-01"}
 :foo :bar ...]

dynamically generated

(into [:delete-docs {:from :my-table, ...}]
      (range 100))

`erase-docs`

Irrevocably erases documents from the given table (including through system time), for all valid-time.

[:erase-docs
 ;; -- required

 ;; table to erase documents from
 <table>

 ;; document ids to erase (variadic, 0..n)
 & <ids>
 ]

Examples:

single document
```
[:erase-docs :my-table :foo]
```

dynamically generated

(into [:erase-docs :my-table] (range 100))

`insert-into`

Inserts documents into a table from the given query.

[:insert-into
 ;; -- required
 <table>
 <query>]

Example:

copy from another table

INSERT INTO users SELECT _id, first_name AS given_name, last_name AS family_name FROM old_users

[:insert-into :users (from :old-users [xt/id {:first-name given-name} {:last-name family-name}])]

`update`

Updates documents in a given table based on the provided query.

[:update
 {;; -- required
  ;; table: keyword
  :table <table>

  ;; set-specs: column -> expr map
  :set <set-specs>

  ;; -- optional

  ;; specify the valid-time of the update
  ;; see 'temporal filters'
  :for-valid-time (from <valid-from>)
  :for-valid-time (to <valid-to>)
  :for-valid-time (in <valid-from> <valid-to>)

  ;; select the documents to update using binding constraints.
  ;; * if not provided, will update every document in the table
  ;; * may also bind logic variables for use in `:unify`
  ;; see 'binding specs'
  :bind [& <bindings>]

  ;; extra clauses to unify with (e.g. to join against another table)
  ;; see 'unify'
  :unify [& <unify-clauses>]}

 ;; -- optional, variadic (0..n)
 ;; argument-rows to be used in the `:bind` or `:unify` clauses.
 ;; * if provided, the update operation will be executed for every argument-row.
 ;; * see 'argument specs'
 & <arg-rows>]

Example:

increment a version column

UPDATE documents SET version = version + 1 WHERE _id = ?

[:update '{:table :documents
           :bind [version {:xt/id $doc-id}]
           :set {:version (+ version 1)}}
 {:doc-id "my-doc"}]

`delete`

Deletes documents from the given table, based on the provided query.

[:delete
 {;; -- required
  ;; table: keyword
  :from <table>

  ;; -- optional

  ;; specify the valid-time of the delete
  ;; see 'temporal filters'
  :for-valid-time (from <valid-from>)
  :for-valid-time (to <valid-to>)
  :for-valid-time (in <valid-from> <valid-to>)

  ;; select the documents to delete using binding constraints.
  ;; * if not provided, will delete every document in the table
  ;; * may also bind logic variables for use in `:unify`
  ;; see 'binding specs'
  :bind [& <bindings>]

  ;; extra clauses to unify with (e.g. to join against another table)
  ;; see 'unify'
  :unify [& <unify-clauses>]}

 ;; -- optional, variadic (0..n)
 ;; 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'
 & <arg-rows>]

Example:

delete by email

DELETE FROM users WHERE email = 'james@example.com'

[:delete {:from :users
          :bind [{:email $james-email}]}
 {:james-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.

[:erase
 {;; -- required
  ;; table: keyword
  :from <table>

  ;; -- optional

  ;; select the documents to erase using binding constraints.
  ;; * if not provided, will erase every document in the table
  ;; * may also bind logic variables for use in `:unify`
  ;; see 'binding specs'
  :bind [& <bindings>]

  ;; extra clauses to unify with (e.g. to join against another table)
  ;; see 'unify'
  :unify [& <unify-clauses>]}

 ;; -- optional, variadic (0..n)
 ;; 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'
 & <arg-rows>]

Example:

erase by email

ERASE FROM users WHERE email = 'james@example.com'

[:erase {:table :users
         :bind [{:email $email}]}
 {: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.

[:assert-exists
 ;; -- required
 <query>

 ;; -- optional
 ;; maps
 & <arg-rows>]

[:assert-not-exists
 ;; -- required
 <query>

 ;; -- optional
 ;; maps
 & <arg-rows>]

Example:

check email doesn’t exist

[;; first, we assert the email doesn't already exist
 [:assert-not-exists '(from :users [{:email $email}])
  {:email "james@example.com"}]

 ;; then, if the pre-condition holds, put a document
 [:put-docs :users {:xt/id :james, :email "james@example.com", ...}]]

`call`

Call a transaction function.

[:call
 ;; -- required
 <fn-id>

 ;; -- optional
 ;; variadic: arguments to pass to the function
 & <args>]

Example:

call a function to increment a field:
```
[:call :increment-version :my-eid]
```

Transaction functions are defined using :put-fn:

[:put-fn
 ;; -- required

 <fn-id>
 <fn-body>]

Transaction functions are evaluated with the Small Clojure Interpreter (SCI). They should return a vector of other transaction operations (including invoking other transaction functions). If they return false, or throw an exception, the transaction will be rolled back.

There are a few functions available in scope during the transaction function:

(q <query> <opts>?) runs an XTQL/SQL query
*current-tx*: the current transaction being indexed.

Example:

function to increment a version

[:put-fn :increment-version
 '(fn [eid]
    (let [doc (first (q '(from :my-table [{:xt/id $eid} *])
                        {:args {:eid eid}}))]
      [[:put-docs :my-table
        (-> doc (update :version inc))]]))]

(in practice, use :update for this exact function)

Transaction options

Transaction options are an optional map of the following keys:

{;; -- optional
 :system-time #inst "2024-01-01"
 :default-tz #time/zone "America/Los_Angeles"}

XTQL Transactions (Clojure)

Transaction operations

put-docs

Examples

patch-docs

Examples

delete-docs

erase-docs

insert-into

update

delete

erase

Asserts: assertExists, assertNotExists

call

Transaction options

`put-docs`

`patch-docs`

`delete-docs`

`erase-docs`

`insert-into`

`update`

`delete`

`erase`

Asserts: `assertExists`, `assertNotExists`

`call`