Clojurians Log v2
xtdb
I was trying to setup hikari cp with XTDB beta9 and next.jdbc. When I use next.jdbc without hikari, everything works fine. When I use connection pooling, I can only select. If I try to insert it says "no hstore extension installed". Seems like it has to do with the way I setup the datasource.
(def datasource-options {:auto-commit true
:read-only false
:connection-timeout 30000
:validation-timeout 5000
:idle-timeout 600000
:max-lifetime 1800000
:minimum-idle 10
:maximum-pool-size 20
:pool-name "db-pool"
:adapter "postgresql"
:database-name "xtdb"
:register-mbeans false})
(def datasource
(delay (make-datasource datasource-options)))
(defn do-query [query]
(let [my-datasource @datasource]
(with-open [connection (jdbc/get-connection my-datasource)]
(jdbc/execute! connection query))))
(do-query ["INSERT INTO users RECORDS ?" {:xt/id "test", :first-name "test"}])
that sounds like you're using the Postgres driver rather than the XT one - Postgres doesn't natively understand how to submit maps like that
Ah, that makes sense. Would it just be better to set that in :jdbc-url or will hikari be able to find the xtdb driver?
Have you tried creating the connection pool with next.jdbc instead of using hikari-cp? https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.1002/api/next.jdbc.connection#-%3Epool You probably only need to specify {:dbtype "xtdb" :dbname "xtdb"} although I haven't explored that much yet.
I didn't know it could make a pool! cool.
I did get it working by using :jdbc-url "jdbc: in the datasource for hikari
I don't consider hikari-cp "supported" by next.jdbc -- since next.jdbc knows how to create connection pools for both HikariCP and c3p0 natively.
I see, so just make a pool through next.jdbc. I'm having difficulty getting the right settings for that. When using hikari directly, the jdbc-url was all I needed. But when making a datasource with next.jdbc, it doesn't recognize xtdb.
(with-open [^HikariDataSource ds (connection/->pool HikariDataSource
{:jdbc-url "jdbc:"
:dbtype "xtdb"
:maximumPoolSize 20
:minimumIdle 10})]
;; this code initializes the pool and performs a validation check:
(.close (jdbc/get-connection ds))) Don't provide :jdbc-url. Just :dbtype "xtdb" :dbname "xtdb" like I showed above.
And HikariCP recommends you do not override the pool sizes etc.
That's what I was doing originally, but I had Unknown dbtype: xtdb, and :classname not provided.
I upgraded next.jdbc from 1.3.955 to 1.3.1002 and it worked.
Oh, gosh, I never thought about you being on an old version sorry. Yes, XTDB support is relatively new in next.jdbc.
https://github.com/seancorfield/next-jdbc/releases/tag/v1.3.967 added XTDB support in December 2024.
That was my fault. It worked when I gave a jdbc-url directly to jdbc/get-connection so I assumed the connection pool would work as well! I don't understand drivers very well so there must've been some way the old version was picking up the xtdb driver?
The XTDB2 https://docs.xtdb.com/drivers/clojure.html#_jdbc list 1.3.955 and it worked fine, because I wasn't making a pool. Just passing a string directly to get-connection
Right but 1.3.955 still only supported it via :dbtype "postgres". :dbtype "xtdb" came a few months later. And it didn't matter until recently, as XTDB's driver diverged from pure PG I think.
I highly recommend https://github.com/liquidz/antq BTW 🙂
Thanks!
Still slightly confused. The version did fix it, but just trying to understand why. When I was using the old next.jdbc, this worked
(with-open [connection (jdbc/get-connection "jdbc:")]
(jdbc/execute! connection ["INSERT INTO users RECORDS ?" {:xt/id "joe", :first-name "Joe"}])) No hstore extension installed.
(def ^HikariDataSource datasource (connection/->pool
HikariDataSource
{:dbtype "postgres"
:dbname "xtdb"
:minimumIdle 10}))
(with-open [connection (jdbc/get-connection datasource)]
(jdbc/execute! connection ["INSERT INTO users RECORDS ?" {:xt/id "joe", :first-name "Joe"}]))You have :dbtype "postgres" in that second version -- but xtdb in the JDBC URL version. Is that intentional?
I thought you were saying next.jdbc, previously would read the "xtdb" string and substitute the postgres driver. If you were saying it wasn't supported at all, I'm surprised an xtdb string worked on the older version. If next.jdbc didn't support the xtdb driver, why does that jdbc url work? Why not for the pooling option?
If the JDBC driver registers its type and class correctly, the driver manager class can deduce how to load that driver based on the jdbc:<dbtype>:... prefix in the URL. However, next.jdbc itself didn't know about xtdb as a :dbtype until 1.3.967 (Dec 2, 2024) so if you asked it to build a JDBC URL, instead of just giving it a pre-built one, it wouldn't know what to do with xtdb.
:jdbcUrl is passed directly to JDBC to figure out, but :dbtype / :dbname etc is passed through next.jdbc first -- you could pass :dbtype "xtdb" :classname "xtdb.jdbc.Driver" to older versions and that would work too, but :classname would be required if next.jdbc didn't know about that :dbtype.
Got it. Thank you for explaining!