sql

All things SQL and JDBC...
richiardiandrea 2021-06-03T17:57:32.068500Z

Hi there, we are trying sslkey, sslcert and sslrootcert as variables in the spec but if I set them and try to connect it still complains that it needs the cert. However if I set the connection-uri with those values set it connects. Is there a known problem with passing those keys via db map?

seancorfield 2021-06-03T18:04:04.069100Z

What do you mean by :spec? There’s no such key in clojure.java.jdbc or next.jdbc.

2021-06-03T19:40:12.072Z

Hey all 👋 Quick question from a clojure newbie. I’m trying to create a postgres connection pool with next.jdbc and HikariCP by following this example from the next.jdbc docs:

(def db-spec {:dbtype .. :dbname .. :user .. :password ..
                :serverTimezone "UTC"})
  (def ds (next.jdbc.connection/->pool
           HikariCP {:jdbcUrl (next.jdbc.connection/jdbc-url db-spec)
                     :maximumPoolSize 15}))
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.659/api/next.jdbc.connection#jdbc-url However, I don’t know where/how to require HikariCP as referenced in the above snippet. If anyone could please point me in the right direction it would be much appreciated. Thanks in advanced.

2021-06-03T19:44:32.073600Z

I’ve managed to create a pool using hikari like so:

seancorfield 2021-06-03T19:44:52.074200Z

HikariCP is a class, so you would :import it, not :require it. You’ll need the dependency in your deps.edn file (or project.clj file if you’re still using Leiningen): com.zaxxer/HikariCP, version "3.4.2". And in your namespace you’ll need this: https://github.com/seancorfield/next-jdbc/blob/develop/test/next/jdbc_test.clj#L18

seancorfield 2021-06-03T19:45:24.074400Z

You don’t need the Hikari Clojure lib.

seancorfield 2021-06-03T19:46:10.075Z

This is all explained in the next.jdbc Getting Started documentation: https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.659/doc/getting-started#connection-pooling — let me know if any of that isn’t clear.

2021-06-03T19:51:19.076200Z

Great, its working, thanks @seancorfield 🙂 The only thing I didn’t understand was that [com.zaxxer/HikariCP "3.4.2"] and [hikari-cp "2.13.0"] are different deps.

2021-06-03T19:53:12.077500Z

So if I understand this correctly: I’m adding the java Hikari lib to my deps with [com.zaxxer/HikariCP "3.4.2"], and referencing the underlying Java class through (:import (com.zaxxer.hikari HikariDataSource)) and passing that to next.jdbc.connection/->pool. Is my understanding correct here?

seancorfield 2021-06-03T19:53:47.077800Z

Yup, exactly right. Per the next.jdbc docs 🙂

2021-06-03T19:54:16.078600Z

Great, the docs make complete sense in hindsight. Thanks!

seancorfield 2021-06-03T19:54:19.078700Z

If you have suggestions on how to make any of that clearer, especially for new folks, let me know. Always happy to update the docs.

2021-06-03T19:55:11.079Z

Will do so, thanks for your help 🙂

richiardiandrea 2021-06-03T20:02:59.079100Z

Yeah well right I meant what also jasonrussel down there has - the map that includes all the options

seancorfield 2021-06-03T20:31:53.079500Z

Show code please.

richiardiandrea 2021-06-03T20:46:49.079800Z

(defn env-db
  "Compute the db containing the spec from the environment."
  []
  {:spec {:dbtype "postgresql"
          :host "localhost"
          :dbname "server"
          :user  "user"
          :password                      "INeedToBeMoreSecureThanThis!"
          :port "5432"
          :ssl true
          :sslfactory "org.postgresql.ssl.NonValidatingFactory"
          :sslcert "/path/to/cert"
          :sslkey "/path/to/key"
          :sslrootcert "/path/to/root-cert"}})
And that does not connect whereas the same params passed in a connection string seem to be passed down to the driver and it connects properly

seancorfield 2021-06-03T20:51:11.080Z

How are you using that nested hash map?

richiardiandrea 2021-06-03T20:52:18.080200Z

I am using it in something like this

(jdbc/with-db-transaction [tx spec]
    (expire-existing-configuration! tx procedure-type)
    (let [sequence-id-by-unique-id-lookup (save-sequence-groups tx procedure-type sequence-groups)]
      (save-protocol-groups tx procedure-type sequence-id-by-unique-id-lookup protocol-groups)))
but we also have Hugsql for some other query

seancorfield 2021-06-03T20:52:23.080400Z

We use a db-spec hash map with extra keys to control SSL stuff at work and it works exactly as expected so “you’re doing it wrong” is my first reaction to this 🙂

seancorfield 2021-06-03T20:52:42.080700Z

That’s not next.jdbc, that’s clojure.java.jdbc.

richiardiandrea 2021-06-03T20:52:47.080900Z

yeah I am trying to understand what the wrong is

richiardiandrea 2021-06-03T20:52:49.081100Z

yep

seancorfield 2021-06-03T20:53:00.081300Z

OK, and spec in that call is … what?

richiardiandrea 2021-06-03T20:53:09.081500Z

the nested map

seancorfield 2021-06-03T20:53:17.081700Z

(:spec env-db) specifically you mean?

richiardiandrea 2021-06-03T20:54:06.081900Z

uhm...no I {:keys [spec]} in that function arguments

richiardiandrea 2021-06-03T20:55:05.082100Z

(it connects fine without SSL so I am fairly sure that I got that part right :D)

richiardiandrea 2021-06-03T20:55:42.082300Z

checking now the library source

seancorfield 2021-06-03T20:57:00.082500Z

Can you show me the connection string that works?

richiardiandrea 2021-06-03T20:57:36.082700Z

sure one sec

seancorfield 2021-06-03T20:57:38.082900Z

Basically those extra fields are just passed to as-properties which is then, in turn, passed directly into the JDBC driver itself.

richiardiandrea 2021-06-03T20:58:16.083100Z

I see - yeah I think that's something else there - just a sec I am basically helping a colleague on this

seancorfield 2021-06-03T20:59:13.083300Z

(my suspicion is you don’t have the correct case on the keys in the map compared to the JDBC query parameters)

richiardiandrea 2021-06-03T21:02:41.083500Z

I think you are on the money there - I am debugging that with my colleague - thanks for the hint!

seancorfield 2021-06-03T21:04:43.083700Z

Get your colleague on Slack! The more, the merrier! 🙂

👍 1