Fork me on GitHub
#sql
<
2019-07-24
>
manuel09:07:50

@seancorfield just an FYI: latest next.jdbc with built-in support for HikariCP is great. Much simpler configuration of the db "layer" now. Thank you.

kirill.salykin09:07:35

Is there an example of using with hikari? maybe you can share one? Thanks

manuel09:07:37

No problem. 🙂

seancorfield16:07:28

There's an example in the Getting Started documentation too.

seancorfield16:07:34

Creating a connection pool for a single SQL operation is a terrible idea @U4TE22XR8!

seancorfield16:07:17

The expectation is that you create the connection pool when your app starts up and use that (a DataSource) throughout your program.

seancorfield16:07:52

Creating a connection pool is even heavier work than creating a single connection so you don't want to do it every time.

manuel17:07:17

thank you. It was a quick test done this morning in the office, TBH. I'm working right now on it from home. 🙂

seancorfield17:07:29

Yeah, you probably want the pool setup in your -main and then pass the datasource down into your code.

manuel17:07:31

Before that change, I was storing the datasource in an atom,

manuel17:07:59

something like this:

(defn connect!
  [config]
  (let [db-spec (:postgresql config)
        ^HikariDataSource ds (connection/->pool HikariDataSource db-spec)]
    (reset! datasource ds)))

seancorfield18:07:05

I'm strongly against using globals for that sort of thing -- code is much easier to maintain and test when you pass stuff via parameters. But, however you do it, create the pool once at startup and use that datasource everywhere.

manuel18:07:53

Yes, it's working now with the atom, but I agree with you on globals. So I'll refactor my code to pass down the "state". Thanks again for the tip.

manuel18:07:36

although, with the pool created at startup, I can't use with-open now, can I? It'll close the datasource, right?

seancorfield18:07:16

If you create it in -main and call into your code, passing the datasource, yes, you can 🙂

manuel18:07:39

superb, thank you

seancorfield18:07:06

I originally had let in the examples with an explicit .close call at the end of the block -- which is why I changed to with-open

seancorfield18:07:37

You don't really need to close the datasource / pool at the end of your app tho'...

manuel18:07:55

sure, I'm not doing that. I was wondering about with-open.

manuel18:07:13

sorry, but I'm not getting how to pass it down to the rest of the code. This is a web application, with APIs (compojure). The main only starts the HTTP server, which takes care of making the routes available.

seancorfield18:07:57

Your main handler can be a closure over your "system" and can then inject it into every Ring request under a particular key.

seancorfield18:07:17

Or use middleware

manuel18:07:08

I see, thanks. I'll see what I can do.

seancorfield18:07:23

(defn add-ds [handler ds]
  (fn [req]
    (handler (assoc req :system/datasource ds))))

seancorfield18:07:46

Then

(start-server (add-ds my-app datasource))

manuel18:07:12

oh it's that simple. 🙂 Thanks a lot.

seancorfield18:07:36

We have a whole system Component (using Stuart Sierra's library) that we pass into every Ring request.

manuel18:07:14

I'm actually using aero in my application, but that add-ds will come in handy.

manuel06:07:45

@seancorfield I did this:

(defn -main
  [& args]
  (let [port (get-in config [:http :port])
        db-spec (:postgresql config)
        ^HikariDataSource ds (connection/->pool HikariDataSource db-spec)]
    (http/start-server! ds port)))
where start-server does this:
(defn start-server!
  [datasource port]
  (let [app-routes (compojure/routes (make-routes datasource))]
    (reset! server (httpkit/run-server app-routes {:port port}))))
And make-routes adds the datasource correctly to the request. However, I am still getting SQLException HikariDataSource HikariDataSource (HikariPool-1) has been closed. after the first query. I mean, the first one works fine, but every query after that results in that exception.

manuel06:07:26

And I wrapped jdbc/execute! with with-open.

seancorfield06:07:10

I don't see with-open anywhere in that repo?

seancorfield06:07:54

Just found it -- took me a while to trace through all that code

seancorfield06:07:06

That's your bug.

seancorfield06:07:22

You are closing the datasource with that statement.

manuel06:07:36

yep, I thought I could use with-open now

seancorfield06:07:59

If you pass the datasource to execute! it will get a connection and close (return) it.

manuel06:07:39

I see, no need to wrap it with with-open then.

seancorfield06:07:52

Else you could do

(with-open [con (jdbc/get-connection datasource)]
  (jdbc/execute! con q))

seancorfield06:07:16

You only need to use with-open when you are creating something closeable.

manuel06:07:24

Would there be any benefits with this approach?

seancorfield06:07:34

(with-open [ds datasource] ... does not create anything.

manuel06:07:49

(with get-connection I mean)

seancorfield07:07:08

I wouldn't bother explicitly creating the connection, unless you wanted to reuse it in multiple operations -- as the documentation says.

manuel07:07:40

great stuff. Thanks again, you're always helpful Sean.

seancorfield07:07:10

As soon as you remove that with-open it should all work 🙂

manuel07:07:37

yes, testing it right now and it works just fine 👍

seancorfield07:07:10

Cool. Midnight here. I'm off to bed!

manuel07:07:31

good night, then, and thanks again!

shaun-mahood16:07:49

I'm trying to migrate an existing db-spec over to use with jdbc-next, and I'm running into a problem that's either something to do with MS SQL Server or something I'm doing wrong - details will be in the thread.

shaun-mahood16:07:55

I've always used the form

{:classname   "net.sourceforge.jtds.jdbc.Driver"
 :subprotocol "jtds:sqlserver"
 :subname     (str "//" servername "/" dbname)
 :user        username
 :password    password}

shaun-mahood16:07:12

When I try to use

{:classname "net.sourceforge.jtds.jdbc.Driver"
 :dbtype    "mssql"
 :dbname    dbname
 :host      hostname
 :user      username
 :password  password}
I can't get it to work in either clojure.java.jdbc or next.jdbc

shaun-mahood16:07:30

I get an error No suitable driver found for jdbc: in next.jdbc and No suitable driver found for jdbc:

shaun-mahood16:07:06

Any ideas what I'm doing wrong?

seancorfield16:07:18

Use :dbtype "jtds"

seancorfield16:07:24

Omit :classname

shaun-mahood16:07:07

Ahh, thanks - works perfectly now! The link in the getting started page goes to https://cljdoc.org/d/seancorfield/next.jdbc/1.0.3/api/next.jdbc#get-datasource which isn't working (I was planning on letting you know that, too, in case you weren't aware).

shaun-mahood16:07:48

Oh, just saw your message in cljdoc so I guess you saw that too 🙂

seancorfield16:07:02

Yeah, not sure what's going on there...

seancorfield16:07:28

I guess, if in doubt, check the docstrings 🙂

seancorfield16:07:49

There's also next.jdbc.connection/dbtypes as a var you can inspect in the REPL.

shaun-mahood16:07:17

Hopefully I'll remember that next time I have a problem with this - it's probably been a few years since I first got it working in old.jdbc and I've just been copying the thing that worked from project to project 🙂

jonpither17:07:35

Anyone got a mysql or oracle-xe docker setup going to use against next.jdbc? I'd be interested in your journey, after some stop/starts myself.

seancorfield17:07:54

I regularly use Percona 5.7 (MySQL) via Docker with next.jdbc @jonpither -- what do you need to know?

seancorfield17:07:52

I also use Docker to test against PostgreSQL (for clojure.java.jdbc -- I haven't hooked that up for next.jdbc stuff yet).

jonpither18:07:22

Thanks @seancorfield I kept getting a connection error with mysql, so I ended up cheating and installing it directly into my OS 🙂

jonpither18:07:44

I'd be interested if you ever fought to get oracle-xe going?

jonpither18:07:19

would be nice to test what I'm doing against Oracle, but I when I tried the docker route earlier, it was very difficult

seancorfield18:07:11

Here's what I do for PostgreSQL:

docker run -p 5432:5432 --name clojure_test -e POSTGRES_PASSWORD=clojure_test -e POSTGRES_USER=clojure_test -d postgres
And then I can connect to 127.0.0.1:5432

seancorfield18:07:01

I'd expect something very similar to work for MySQL (or any other database).

jonpither18:07:33

yep, will favour that approach in future

seancorfield18:07:03

Here's what's in my docker-compose.yml for Percona:

mysql:
    image: percona:5.7
    environment:
      - MYSQL_ROOT_PASSWORD
    ports:
      - "3306:3306"
    command:
      [--character-set-server=utf8mb4, --collation-server=utf8mb4_unicode_ci]
(with an env var for my root user password for MySQL)

seancorfield18:07:35

Oooh! I didn't know there was an embedded version! That would make my testing much easier!!

jonpither18:07:57

thanks to @dominicm for finding it for us

seancorfield18:07:40

Am I right that it's not on Maven Central?

seancorfield18:07:02

I can't find any artifacts under com.opentable that seem to be PostgreSQL...

jonpither18:07:11

Works for us as a Lein dep com.opentable.components/otj-pg-embedded

seancorfield18:07:09

Ah, I had the wrong group ID. Thanks!

mpenet19:07:56

We use that too. It's quite handy

Scott McCaughie21:07:32

@seancorfield Hey Sean - I'm using Apache Derby with clojure.java.jdbc - I instinctively reached for the latest version 10.15.1.3 and ran into issues. I notice the tests in clojure.java.jdbc and next.jdbc for that matter, all run against the prior version 10.14.2.0 which works fine for me. I'm curious though... are there a bunch of breaking changes in the latest derby release?

seancorfield21:07:35

I think Derby 10.15.* requires a more recent JDK than 8? What was the error -- class version mismatch 53 vs 52?

Scott McCaughie21:07:38

Execution error (ClassNotFoundException) at jdk.internal.loader.BuiltinClassLoader/loadClass (BuiltinClassLoader.java:583).
org.apache.derby.shared.common.security.SystemPermission

seancorfield21:07:16

What JDK are you using?

seancorfield21:07:09

Ah, OK. So you wouldn't get the version mismatch. If 10.14.* works, use that. I haven't tested 10.15.* since I'm based on OpenJDK 8 at the moment

Scott McCaughie21:07:02

Cool 🙂 yeh no issues using 10.14.x - was just curious. Thanks!