Introducing XTQL

XTDB is queryable using two query languages: SQL and XTQL.

XTQL is our new, data-oriented, composable query language:

• It is inspired by the strong theoretical bases of both Datalog and relational algebra. These two combine to create a joyful, productive, interactive development experience, with the ability to build queries iteratively, testing and debugging smaller parts in isolation.
• It is designed to be highly amenable to dynamic query generation - we believe that our industry has spent more than enough time trying to generate SQL strings (not to mention the concomitant security vulnerabilities).

Querying XTQL

XTQL can either be queried within an SQL query, or via the Clojure API.

To query XTQL within a SQL query, you can either execute it:

• as a top-level query (like SQL’s VALUES): XTQL $$ <query> $$:

  XTQL $$
    (-> (from :users [first-name last-name])
        ...)
  $$

• or within a wider SQL query:

  SELECT ...
  FROM (XTQL $$
         (-> (from :users [first-name last-name])
             ...)
        $$) u
  ORDER BY u.last_name DESC, u.first_name DESC
  LIMIT 10

‘Operators’ and ‘relations’

XTQL is built up of small, composable ‘operators’, which combine together using ‘pipelines’ into larger queries.

• ‘Source’ operators (e.g. ‘read from a table’) each yield a ‘relation’ - an unordered bag of rows ¹.
• ‘Tail’ operators (e.g. ‘filter a relation’, ‘calculate extra fields’) transform a relation into another relation.

From these simple operators, we can build arbitrarily complex queries.

Our first operator is from:

from

The from operator allows us to read from an XTDB table. In this first example, we’re reading the first-name and last-name fields from the users table - i.e. SELECT first_name, last_name FROM users:

(from :users [first-name last-name])

It’s in the from operator that we specify the temporal filter for the table. By default, this shows the table at the current time, but it can be overridden:

• to view the table at another point in time

• to view the changes to the table within a given range

• to view the entire history of the table

  (from :users {:bind [first-name last-name]
  
                ;; at another point in time
                :for-valid-time (at #inst "2023-01-01")
  
                ;; within a given range
                :for-valid-time (in #inst "2023-01-01", #inst "2024-01-01")
                :for-valid-time (from #inst "2023-01-01")
                :for-valid-time (to #inst "2024-01-01")
  
                ;; for all time
                :for-valid-time :all-time
  
                ;; and all of the above :for-system-time too.
                })

In the from operator, we can also rename columns, and filter rows based on field values:

• We rename a column using a binding map:

  (from :users [{:xt/id user-id} first-name last-name])

  SELECT _id AS user_id, first_name, last_name FROM users

• We can look up a single user-id by specifying a literal in the binding map:

  (from :users [{:xt/id "ivan"} first-name last-name])

  SELECT first_name, last_name
  FROM users
  WHERE _id = 'ivan'

Another source operator is rel, which allows you to specify an inline relation.

You can check out the source operators reference for more details.

Pipelines

We can then transform the rows in a table using tail operators, which we pass in an operator ‘pipeline’. Pipelines consist of a single source operator, and then arbitrarily many tail operators.

Here, we demonstrate SELECT first_name, last_name FROM users ORDER BY last_name, first_name LIMIT 10, introducing the ‘order by’ and ‘limit’ operators:

In Clojure, we use -> to denote a pipeline - in a similar vein to the threading macro in Clojure ‘core’ ², we take one source operator and then pass it through a series of transformations.

(-> (from :users [first-name last-name])
    (order-by last-name first-name)
    (limit 10))

By building queries using pipelines, we are now free to build these up incrementally, trivially re-use parts of pipelines in different queries, or temporarily disable some operators to test parts of the pipeline in isolation.

Other tail operators include where (to filter rows), return (to specify the columns to output), with (to add additional columns based on the existing ones), and aggregate (grouping rows - counts, sums, etc). For a full list, see the tail operators reference.

Multiple tables - introducing unify

Joining multiple tables in XTQL is achieved using Datalog-based ‘unification’.

We introduce the unify source operator, which takes an unordered bag of input relations and joins them together using ‘unification constraints’ (similar to join conditions).

Each input relation (e.g. from) defines a set of ‘logic variables’ in its bindings. If a logic variable appears more than once within a single unify clause, the results are constrained such that the logic variable has the same value everywhere it’s used. This has the effect of imposing ‘join conditions’ over the inputs.

• In this case, we re-use the user-id logic variable to indicate that the :xt/id from the :users table should be matched with the :author-id of the :articles table.

  (unify (from :users [{:xt/id user-id} first-name last-name])
         (from :articles [{:author-id user-id} title content]))

  SELECT u._id AS user_id, u.first_name, u.last_name,
         a.title, a.content
  FROM users u
    JOIN articles a ON u._id = a.author_id

• For non-equality cases, we can use a where clause (where we have a full SQL-inspired expression standard library at our disposal)

  ;; 'find me all the users who are the same age'
  (unify (from :users [{:xt/id uid1} age])
         (from :users [{:xt/id uid2} age])
         (where (<> uid1 uid2)))

  SELECT u1._id AS uid1, u2._id AS uid2, u1.age
  FROM users u1
    JOIN users u2 ON (u1.age = u2.age)
  WHERE u1._id <> u2._id

• We can specify that a certain match is optional using left-join:

  (-> (unify (from :customers [{:xt/id cid}])
             (left-join (from :orders [{:xt/id oid, :customer-id cid} currency order-value])
                        [cid currency order-value]))
      (limit 100))

  SELECT c._id AS cid, o.currency, o.order_value
  FROM customers c
    LEFT JOIN orders o ON (c._id = o.customer_id)
  LIMIT 100

  Here, we’re asking to additionally return customers who haven’t yet any orders (for which the order-table columns will be absent in the results).

• Or, we can specify that we only want to return customers who don’t have any orders, using not exists?:

  (-> (unify (from :customers [{:xt/id cid}])
             (where (not (exists? (fn [cid]
                                    (from :orders [{:customer-id cid}]))))))
      (limit 100))

  SELECT _id AS cid
  FROM customers c
  WHERE _id NOT IN (SELECT orders.customer_id FROM orders)
  LIMIT 100

The unify operator accepts ‘unify clauses’ - e.g. from, where, with, join, left-join - a full list of which can be found in the unify clause reference guide.

Projections

• We can create new columns from old ones using with:

  (-> (from :users [first-name last-name])
      (with {:full-name (concat first-name " " last-name)}))

  SELECT first_name, last_name, (first_name || ' ' || last_name) AS full_name
  FROM users AS u

  We can also use with within unify - this creates new logic variables which we can then unify in the same way.

• Where with adds to the available columns, return only yields the specified columns to the next operation:

  (-> (unify (from :users [{:xt/id user-id} first-name last-name])
             (from :articles [{:author-id user-id} title content]))
      (return {:full-name (concat first-name " " last-name)} title content))

  SELECT (u.first_name || ' ' || u.last_name) AS full_name, a.title, a.content
  FROM users AS u
    JOIN articles a ON u._id = a.author_id

• Where we don’t need any additional projections, we can use without:

  (-> (unify (from :users [{:xt/id user-id} first-name last-name])
             (from :articles [{:author-id user-id} title content]))
      (without :user-id))

  SELECT u.first_name, u.last_name, a.title, a.content
  FROM users AS u
    JOIN articles a ON u._id = a.author_id

Aggregations

To count/sum/average values, we use aggregate:

(-> (unify (from :customers [{:xt/id cid}])
           (left-join (from :orders [{:xt/id oid :customer-id cid} currency order-value])
                      [oid cid currency order-value]))
    (aggregate cid currency
               {:order-count (count oid)
                :total-value (sum order-value)})
    (with {:total-value (coalesce total-value 0)})
    (order-by {:val total-value :dir :desc})
    (limit 100))

SELECT c._id AS cid, o.currency, COUNT(o._id) AS order_count, COALESCE(SUM(o.order_value), 0) AS total_value
FROM customers c
  LEFT JOIN orders o ON (c._id = o.customer_id)
GROUP BY c._id, o.currency
ORDER BY total_value DESC
LIMIT 100

‘Pull’

When we’ve found the documents we’re interested in, it’s common to then want a tree of related information. For example, if a user is reading an article, we might also want to show them details about the author as well as any comments.

(Users of existing EDN Datalog databases may already be familiar with ‘pull’ - in XTQL, because subqueries are a first-class concept, we rely extensively on these to express a more powerful/composable behaviour.)

(-> (from :articles [{:xt/id article-id} title content author-id])

    (with {:author (pull (fn [author-id]
                           (from :authors [{:xt/id author-id} first-name last-name])))

           :comments (pull* (fn [article-id]
                              (-> (from :comments [{:article-id article-id} created-at comment])
                                  (order-by {:val created-at :dir :desc})
                                  (limit 10))))}))

;; => [{:title "...", :content "...",
;;      :author {:first-name "...", :last-name "..."}
;;      :comments [{:comment "...", :name "..."}, ...]}]

-- using XTDB's 'NEST_ONE'/'NEST_MANY'

FROM articles AS a
SELECT _id AS article_id, title, content, author_id,
       NEST_ONE(FROM authors WHERE _id = a.author_id
                SELECT first_name, last_name)
         AS author,
       NEST_MANY(FROM comments WHERE article_id = a._id
                 SELECT created_at, comment
                 ORDER BY created_at DESC
                 LIMIT 10)
         AS comments

In this example, we use pull to pull back a single map - we know that there’s only one author per article (in our system). When it’s a one-to-many relationship, we use pull* - this returns any matches in a vector.

Also note that, because we have the full power of subqueries, we can express requirements like ‘only get me the most recent 10 comments’ using ordinary query operations, without any support within pull itself.

Bitemporality

It wouldn’t be XTDB without bitemporality, of course - indeed, some may be wondering how I’ve gotten this far without mentioning it!

(I’ll assume you’re roughly familiar with bitemporality for this section. If not, forgive me - we’ll follow this up with more XTDB 2.x bitemporality content soon!)

• In XTDB 1.x, queries had to be ‘point-in-time’ - you had to pick a single valid/transaction time for the whole query.

  In XTQL, while there are sensible defaults set for the whole query, you can override this on a per-from basis by wrapping the table name in a vector and providing temporal parameters:

  (from :users {:for-valid-time (at #inst "2020-01-01")
                :bind [first-name last-name]})

  (from :users {:for-valid-time :all-time
                :bind [first-name last-name]})

  SELECT first_name, last_name FROM users FOR VALID_TIME AS OF DATE '2020-01-01'

  SELECT first_name, last_name FROM users FOR ALL VALID_TIME

  • You can also specify (from <time>), (to <time>) or (in <from-time> <to-time>), to give fine-grained, in-query control over the history returned for the given rows.
  • System time (formerly ‘transaction time’, renamed for consistency with SQL:2011) is filtered in the same map with :for-system-time.

• This means that you can (for example) query the same table at two points-in-time in the same query - ‘who worked here in both 2018 and 2023’:

  (unify (from :users {:for-valid-time (at #inst "2018")
                       :bind [{:xt/id user-id}]})
  
         (from :users {:for-valid-time (at #inst "2023")
                       :bind [{:xt/id user-id}]}))

For more information

Congratulations - this is the majority of the theory behind XTQL! You now understand the fundamentals behind how to construct XTQL queries from its simple building blocks - from here, it’s much more about incrementally learning what each individual operator does, and how to work with it via edn.

You can:

• check out the reference guides for XTQL queries and transactions.

We’re very much in listening mode right now - as a keen early adopter, we’d love to hear your first impressions, thoughts and opinions on where we’re headed with XTQL. Please do get in touch via the usual channels!

Footnotes

1. rows …​ which themselves are otherwise known as ‘maps’, ‘structs’, ‘records’ or ‘dictionaries’ depending on your persuasion 😄 ↩

2. although XTQL’s -> isn’t technically macro-expanded - it’s just data. ↩