What is XTQL?

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

XTQL is our new, data-oriented, composable query language, 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).
It has JSON and EDN flavours, with client libraries to come in Java/Kotlin, JavaScript, and many more.
It can be used for both queries and transactions.

Let’s start with XTQL queries:

'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^[1].
'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:

JSON

{
  "from": "users",
  "bind": [ "firstName", "lastName" ]
}

Clojure

(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

JSON

{
  "from": "users",
  "bind": [ "firstName", "lastName" ],

  // at another point in time
  "forValidTime": { "at": { "@type": "xt:date", "@value": "2023-01-01" } },

  // within a given range
  "forValidTime": { "in": [ { "@type": "xt:date", "@value": "2023-01-01" },
                            { "@type": "xt:date", "@value": "2024-01-01" } ] },
  "forValidTime": { "from": { "@type": "xt:date", "@value": "2023-01-01" } },
  "forValidTime": { "to": { "@type": "xt:date", "@value": "2024-01-01" } },

  // for all time
  "forValidTime": "allTime",

  // and all of the above "forSystemTime" too.
}

Clojure

(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. 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:

JSON

In JSON, we wrap the operators in an array to denote a pipeline:

[
  { "from": "users", "bind": [ "firstName", "lastName" ] },
  { "orderBy" : [ "lastName", "firstName" ]},
  { "limit": 10 },
]

Clojure

In Clojure, we use -> to denote a pipeline - in a similar vein to the threading macro in Clojure 'core' ^[2], 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.

For example, imagine 'for each order, get me the customer name, order-id and order value'

SELECT c.customer_name, o.xt$id AS order_id, o.order_value
FROM customers c
  JOIN orders o ON (o.customer_id = c.xt$id)

In XTQL, we specify the join condition by re-using a logic variable (customer_id), constraining the two input tables to have the same value for o.customer_id and c.xt$id (customer table primary key):

JSON

[
  {
    "unify": [
      // bind `customer_id` to the `xt$id` of the `customers` table
      { "from": "customers", "bind": [ { "xt$id": "customerId" }, "customerName" ] },

      // also bind `customer_id` to the `customer_id` of the `orders` table
      { "from": "orders", "bind": [ { "xt$id": "orderId" }, "customerId", "orderValue" ] }
    ]
  },

  { "return": [ "customerName", "orderId", "orderValue" ]}
]

Clojure

(-> (unify (from :customers [{:xt/id customer-id} customer-name])
           (from :orders [{:xt/id order-id} customer-id order-value]))
    (return customer-name order-id order-value))

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.

XTQL transactions - DML

XTQL can also be used in XTDB transactions to insert, update, delete and erase documents based on an XTQL query. It uses the same query language as above, with a small wrapper for each of the operations.

These queries are evaluated on XTDB’s single writer thread, so are guaranteed the strongest level of consistency.

`insert`

We can submit 'insert' operations to XTDB - these evaluate a query, and insert every result into the given table.

e.g. INSERT INTO users2 SELECT xt$id, first_name AS given_name, last_name AS family_name FROM users:

JSON

{
  "insert": "users2",
  "query": {
    "from": "users",
    "bind": [ "xt$id", {"firstName": "givenName"}, {"lastName": "familyName"}]
  }
}

Clojure

[:insert-into :users2
 '(from :users [xt/id {:first-name given-name, :last-name family-name}
                xt/valid-from xt/valid-to])]

`update`

Update operations find rows, and specify which fields to update.

Here, we’re incrementing a 'version' attribute - UPDATE docs SET version = version + 1 WHERE xt$id = ?

JSON

{
  "update": "documents",
  "bind": [ { "xt$id": "$docId", "version": "v" }],
  "set": { "version": { "@call": "+", "@args": [ "v", 1 ] } }
}

// separately, we pass the following as the arguments to the query:
{ "docId": "myDocId" }

Clojure

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

 ;; specifying a value for the parameter with args
 {:doc-id "doc-id"}]

`delete`

Delete operations work like 'update' operations, but without the set clause.

Here, we delete all the comments for a given post-id - DELETE FROM comments WHERE post_id = ?

JSON

{
  "delete": "comments",
  "bind": [ { "postId": "$postId" }]
}

// separately, we pass the following as the arguments to the query:
{ "postId": "myPostId" }

Clojure

[:delete {:from :posts, :bind [{:post-id $post-id}]}

 ;; specifying a value for the parameter with args
 {:post-id "post-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 what it looks like in your client language.

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!

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.

What is XTQL?

'Operators' and 'relations'

from

Pipelines

Multiple tables - introducing unify

XTQL transactions - DML

insert

update

delete

For more information

`from`

Multiple tables - introducing `unify`

`insert`

`update`

`delete`