[RELEASE] v2.0.0-beta8

[jarohen]

Here's beta8 🚀

We've got some XTQL changes to tell you about, some improvements to the type-handling in the XT JDBC driver, and some breaking changes.

Let's get started!

XTQL-in-SQL

Previously, SQL and XTQL have lived in two separate worlds in XTDB. With this release, XTQL is brought back onto the main stage: XTQL queries are now a first-class part of our SQL syntax, and can be sent through the JDBC driver!

Here we're taking advantage of SQL's relational algebra roots - the ability to compose larger queries out of smaller sub-queries. Each relational algebra operator (e.g. select, project, join, aggregate) has a common structure - relation(s) in, relation out - and it's this homogeneity (or, to give it the algebraic term, 'closure' under its operations) that means we can simply add XTQL as another source operator.

It's included as a string within SQL - most commonly, using dollar-delimited strings:

-- as a top-level query - like SQL's `VALUES`
XTQL $$
  (-> (from :my-table [...])
      ...)
$$

-- as part of a larger SQL query - again, like SQL's `VALUES`
FROM (XTQL $$
  (-> (from :my-table [...])
      ...)
$$) my_table
  JOIN ...
WHERE ...
ORDER BY ...

This means that you can use XTQL in your favourite SQL BI tooling - like Metabase:

We'd love to hear how you make use of this new XTQL reach!

To integrate XTQL into SQL queries, we've had to make one non-trivial breaking change to XTQL syntax: given SQL's parameters are positional rather than named, we've brought XTQL in line too. In your query, you now use fn to name the parameters, as follows:

(xt/q node ['(fn [uid]
               (-> (from :users [{:xt/id uid} given-name family-name])
                   ...))
            "jms"])
                  
;; or, using Clojure's `#{}` reader macro, in simple cases:
(xt/q node ['#(-> (from :users [{:xt/id %} given-name family-name])
                   ...)
            "jms"])

XTDB JDBC driver

Previously, the XTDB JDBC driver was simply a very thin wrapper around the Postgres JDBC driver - an indulgence so that you could specify jdbc:xtdb://... as your connection string rather than jdbc:postgresql://....

Now, it actually earns its keep!

Through the changes in beta8, our JDBC driver now understands the wide range of types available in XTDB:

• post Java 8 java.time types, bringing JDBC into the 21st century - including timestamps that preserve timezone information.
• XTDB's first-class collections - maps, vectors, sets.
• XTDB's extension types: keywords, URIs.

If you're using our JDBC driver - either directly or through libraries like Clojure's next.jdbc, you can now submit these directly as query arguments, and you should also transparently see these types in your result set.

Finally, if you're using an in-memory node, this now implements javax.sql.DataSource, so you can pass this directly to libraries like next.jdbc or HikariCP.

(require '[next.jdbc :as jdbc]
         '[xtdb.node :as xtn])
         
(with-open [node (xtn/start-node {})]
  (jdbc/execute! node ["INSERT INTO users RECORDS ?"
                       {:xt/id "jms", :given-name "James", :family-name "Henderson"}])

  (jdbc/execute! node ["SELECT * FROM users WHERE _id = ?" "jms"]))

This also involved a couple of breaking changes:

• Obviously the change itself is breaking - if you're calling .getObject on the returned result-set, you'll get a richer type than PgObject or java.sql.Timestamp.
• If you were using our next.jdbc helper functions, you'll likely no longer need to - and indeed, most of them have been removed (particularly ->pg-obj).
• Our JDBC driver is now part of the xtdb-api artifact - you'll need to remove xtdb-jdbc from your dependency manager.

Operational improvements

In beta8, we've also been working on operational improvements, largely around backup/restore and disaster recovery - these are now documented in the operations manual.

One to draw to your attention, though, is the new 'log epoch' safety net: if your transaction log is corrupted/lost/misplaced, XTDB will now smoke-test the log topic on startup, and refuse to start if there are messages missing.

To resolve this, you now have the option to keep whatever data has been persisted in XTDB's object store, but start with a new, empty topic. Let the XTDB cluster know what's happened by incrementing the log epoch, and XTDB will continue from the most recently persisted block.

SQL changes

There are a few quality-of-life SQL changes to tell you about:

• (breaking) FROM my_table FOR VALID_TIME FROM NULL TO <to>, FROM my_table FOR VALID_TIME TO <to> (i.e. FROM <from> elided), and similarly for UPDATE|DELETE <table> FOR PORTION OF VALID_TIME FROM ... TO ... will now return/update data from the start of time, rather than from now.

  If the whole clause is elided (e.g. FROM my_table, UPDATE|DELETE <table> SET ...), the default remains for VT 'now -> ∞', as before.

• FOR VALID_TIME AS OF|FROM now support parameters and/or full expressions - e.g.:

  • FROM my_table FOR VALID_TIME AS OF ?
  • FROM my_table FOR VALID_TIME FROM (NOW - INTERVAL 'P30D') TO NOW

• In BEGIN, you can set various options within the scope of the transaction:

  BEGIN READ WRITE WITH (system_time = ?, timezone = 'Europe/Berlin');
  ...
  COMMIT;
  
  BEGIN READ ONLY WITH (snapshot_time = ?, clock_time = '2025-05-01T00:00:00Z');
  ...
  COMMIT;

  These, too, support parameters and/or full expressions.

• We've added URI functions in the standard library - no need to regex your way out of this one any more!

Elsewhere

So much for beta8 being a small release!

As always, for a full list of closed cards, see the milestone.

Cheers!

James & the XT team

---

This discussion was created from the release v2.0.0-beta8: Here's beta8 🚀.