Fork me on GitHub
#sql
<
2021-06-03
>
richiardiandrea17:06:32

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?

seancorfield18:06:04

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

richiardiandrea20:06:59

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

seancorfield20:06:53

Show code please.

richiardiandrea20:06:49

(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

seancorfield20:06:11

How are you using that nested hash map?

richiardiandrea20:06:18

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

seancorfield20:06:23

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 🙂

seancorfield20:06:42

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

richiardiandrea20:06:47

yeah I am trying to understand what the wrong is

seancorfield20:06:00

OK, and spec in that call is … what?

seancorfield20:06:17

(:spec env-db) specifically you mean?

richiardiandrea20:06:06

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

richiardiandrea20:06:05

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

richiardiandrea20:06:42

checking now the library source

seancorfield20:06:00

Can you show me the connection string that works?

seancorfield20:06:38

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

richiardiandrea20:06:16

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

seancorfield20:06:13

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

richiardiandrea21:06:41

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

seancorfield21:06:43

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

👍 3
jasonrussell19:06:12

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.

jasonrussell19:06:32

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

seancorfield19:06:24

You don’t need the Hikari Clojure lib.

seancorfield19:06:52

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

seancorfield19:06:10

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.

jasonrussell19:06:19

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.

jasonrussell19:06:12

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?

seancorfield19:06:47

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

jasonrussell19:06:16

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

seancorfield19:06:19

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.

jasonrussell19:06:11

Will do so, thanks for your help 🙂