Transactions cookbook

This document provides examples for EDN transaction operations, to be submitted to xtdb.api/submit-tx.

Transaction operations

`put-docs`

Main article: put-docs

[: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`

Main article: delete-docs

[: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`

Main article: erase-docs

[: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`

Main article: insert-into

[:insert-into
 ;; -- required
 <table>
 <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

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

`update`

Main article: update

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

  ;; -- 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 `:set` or `:unify`
  ;; see 'binding specs'
  :bind [& <bindings>]

  ;; -- required
  ;; Map<Keyword, Expr>
  ;; see 'expressions'
  :set { ... }

  ;; -- optional
  ;; 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`, `:set` or `:unify` clauses.
 ;; * if provided, the update operation will be executed for every argument-row.
 ;; * see 'argument specs'
 & <arg-rows>]

Example:

increment a 'version' field:

UPDATE users SET version = version + 1 WHERE email = 'james@example.com'

[:update {:table :users,
          :bind [{:email $email}, version],
          :set {:version (+ version 1)}}
 {:email "james@example.com"}]

`delete`

Main article: delete

[: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`

Main article: erase

[: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

Main article: asserts

[: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`

Main article: call

[: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

Main article: transaction options

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