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

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.

[: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}])]

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 {:table :users
            :bind [{:email $email}]}
   {: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 #xt.time/zone "America/Los_Angeles"}