Fork me on GitHub
#sql
<
2020-10-28
>
Darin Douglass01:10:50

thanks for the thorough response to my end-of-the-day ramblings. re performance: it makes sense there's overhead to making every row a clojure object. i guess i've always thought the overhead was generally negligible. i'd be interested in seeing any benchmarks if you had any lying around. re closeable: my flow/thought process was this: 1. created the connection pool 2. started writing several functions that call insert-one!, plan, etc; each passing jdbc/snake-kebab-opts 3. half-remembered talk in this channel about with-options so i figured i'd try to put jdbc/snake-kebab-opts into the most general location i could: where i created my pool 4. doing this stopped get-connection from working as well as stopping the pool from shutting down (since it doesn't "close") 5. so i tried the next step of wrapping the get-connection calls (not yet realizing it wasn't closeable), which obviously died 6. went to the docs and saw this note/snippet https://cljdoc.org/d/seancorfield/next.jdbc/1.1.610/doc/getting-started#datasources-connections--transactions:

Note: Because get-datasource and get-connection return plain JDBC objects (javax.sql.DataSource and java.sql.Connection respectively), next.jdbc/with-options cannot flow options across those calls, so if you are explicitly managing connections or transactions as above, you would need to have local bindings for the wrapped versions:

(with-open [con (jdbc/get-connection ds)]
  (let [con-opts (jdbc/with-options con some-options)]
    (jdbc/execute! con-opts ...) ; committed
    (jdbc/with-transaction [tx con-opts] ; will commit or rollback this group:
      (let [tx-opts (jdbc/with-options tx (:options con-opts)]
        (jdbc/execute! tx-opts ...)
        (jdbc/execute! tx-opts ...)
        (into [] (map :column) (jdbc/plan tx-opts ...))))
    (jdbc/execute! con-opts ...))) ; committed
^ this is the snippet/paradigm i was talking about when i mentioned usability vs POJOs. this snippet feels really clunky to me and (likely naively) my first thought is "can `with-options" be closeable"?

seancorfield01:10:30

@ddouglass This confuses me "doing this stopped get-connection from working as well as stopping the pool from shutting down (since it doesn't "close")" -- the model I work with is wrapping the DataSource with-options, and you can absolutely call get-connection on that (all the internal code does that) but I almost never bother explicitly calling get-connection: I just pass the wrapped ds into each function and let it call get-connection itself as needed (and those are properly closed, i.e., returned to the pool).

seancorfield01:10:11

So the only place where I need to re-wrap a connection with options is the very few places where I use with-transaction (but those are extremely rare in our code at work).

Darin Douglass01:10:45

ok, let me look at the code and likely rethink my mental model of what needs to be where

seancorfield01:10:44

I pretty much never explicitly use with-open / get-connection -- that's kind of what connection pools are for, after all: let all the library functions use the pool.

seancorfield01:10:49

I guess it's important to understand that a wrapped "connectable" (in the general sense) is sourceable, connectable, executable, preparable, and transactable -- it is something that would get a datasource or connection from, or something you would plan or execute on, or get a prepared statement from, or build a transaction on. None of those <thing>able types is closeable -- but something you get from them might be.

Darin Douglass01:10:56

ok yeah after some rejiggering things make much more sense: • wrap the pool during creation, but .close the :connectable • provide the pool directly to all jdbc calls, i.e. don't grab connections manually • re-wrap the single transaction in the opts from the pool i think my brain saw with-open/get-connection shortly after reading the pooling section of the docs and thought i had to use them and completely forgot the examples above used the pooled datasource directly

Darin Douglass01:10:04

thanks for the clarification @seancorfield

seancorfield01:10:55

Re: bullet 1, depending on how you're creating the pool, you could have the pool on hand directly for the .close operation but, yeah, if you wrap at pool creation, closing the connectable from the wrapper is the correct approach.

seancorfield02:10:59

(for different connection pools, it might be .shutdown, or the pool type might not actually be Closeable -- see the discussion comparing c3p0 and HikariCP for example)

Darin Douglass02:10:27

re your re:

(defstate pool
  :start (-> config
             (:db)
             (as-> $ (connection/->pool HikariDataSource $))
             (migrate!)
             (jdbc/with-options jdbc/snake-kebab-opts))
  :stop (.close ^HikariDataSource (:connectable pool)))

Darin Douglass02:10:31

seems to work a-ok

seancorfield02:10:58

If you're using Component, there's code in next.jdbc.connection for managing the raw (pooled) DataSource so you'd still need to wrap that -- I'd literally wrap it with another component.

seancorfield02:10:10

Ah, you use Mount... heh... sorry 🙂

Darin Douglass02:10:07

i've read about your, uhh, dislike of mount. at work we've got mount/component/homegrown-thing. so far i've disliked mount the least of them all, though admittedly mount has more annoying pitfalls

seancorfield02:10:55

We started with global state a decade ago. Hardly a day goes past that I don't regret that.

Darin Douglass02:10:00

if only it wasn't so darn convenient 😉

seancorfield02:10:57

Yup. We went with convenient (easy) at first. We've wished almost every day for years that we'd gone with simple instead 🙂 We have 105k lines of code now, after a decade, so we have a lot of "legacy" Clojure to wrestle with.

❤️ 3
Darin Douglass02:10:27

heh wrestling is an apt term: our main service looks to be 11yrs young, was at one time a monolith (and still kinda is), and is using an in-repo deprecated version of clojure.java.jdbc :)

seancorfield02:10:58

We still have clojure.java.jdbc in the mix as well as next.jdbc, and a couple of different wrappers around the former. It's worse on the date/time side of the house, where we have some raw Java pre-8 stuff, some date-clj, some clj-time, some raw Java Time stuff (8+) and clojure.java-time to wrap that / clean up some interop 🙂

Zor14:10:50

Hello! Has anyone got an elegant way to instrument errors coming from next-jdbc (in my case, a `org.postgresql.util.PSQLException`) to get the query text alongside the error. I can work around this in development by checking the postgres server logs manually. My intuition was to add some kind of interceptor/middleware somewhere, but I couldn't find how or where. I'm connecting to postgres with conman, a library that handles a (next-jdbc/hikari-cp/hugsql) stack.