thanks for a response, will look. of course would be nice to copy opts from the datasource into transaction, but let me read first to understand the reasoning

with-transaction produces a native Connection object, as does get-connection, and user code expects to be able use that as a native Java object directly -- so auto-wrapping would break existing code.

(and in fact get-connection is specifically declared to return a native Connection object)

cc @U1KE7MFDY

thanks for explanation, Sean

@U04V70XH6 If DefaultOptions implemented java.sql.Connection existing code won't break right?

That isn't practical really (I explored that kind of approach originally).

Mind explaining why? Is it because one has to manually enumerate the methods and keep it in sync with JDBC updates?

I mean JDBC adding more methods?

Don't know much of java, but I guess it could be done programmatically, the enumeration of methods?

Would it work to set the dynamic var that tx can read?

No. No dynamic vars.

I'm on a call. This is just not happening. I looked at this problem before.

Clear, and sad a little Would make lufe a bit easier

For the well-documented edge case around with-transaction, adding the overhead of a fully-implemented, fully-delegated Connection proxy is not worth it.

What I could perhaps do is provide an alternative to with-transaction that did the wrapping -- with the understanding that what it binds isn't a Connection object but a "connectable". That makes it opt in, so folks who care about performance don't get hurt by the behavior.

Thats interesting, thanks!

How big performance penalty may be?

https://github.com/seancorfield/next-jdbc/issues/172

Only when you're free Sean. I very much want to deal with java.sql.Connection everywhere and not a wrapped thing. Everything is very much easier that way. But that makes with-options not so useful. If I had to use with-options I would use it just once when I initiated the db-pool. But since it doesn't implement java.sql.Connection, I can't imagine a use case for it. In fact this is what I did first when I migrated to next.jdbc and found dealing with default-options object cumbersome and reverted the change and added the options manually in all functions that wrapped the jdbc execute family of functions (query, insert, et al).

I guess exposing the raw java.sql.Connection was one of the design motives of next.jdbc

Yes, trafficking in native JDBC objects is an important aspect of next.jdbc's design and part of why it's so much faster than clojure.java.jdbc which passes around a hash map everywhere, which means you need to unwrap that every time you need to do something native with JDBC.

It's why next.jdbc only "implements" DataSource: it's a small API and you general either get a connection once and reuse it multiple times, or you use a connection-pooled datasource (which is its own JDBC object, effectively).

Wrapping a datasource with options isn't much of an overhead and doesn't affect much code that would interact with JDBC. Wrapping a connection would be a major deal -- both to implement and then to maintain (since different JDBC drivers add their own stuff), in addition to any other overhead.

It's why next.jdbc didn't even have any sort of "default options" story for a long time (and why I generally don't encourage its use).

(`with-options` came in 1.0.475, a year ago)

What I'll do with #172 above is probably add with-transaction-options which will conditionally rewrap the connection -- but I'll need to clearly document that you will need to unwrap it yourself if you want to do any Connection method calls on it (you'll be able to pass it to next.jdbc functions directly, but not do save points or anything like that).

Yeah I was not missing much by not using using with-options. Had to the jdbc options in 7 places instead of one if I had to use with-options . I can very much live without with-options (pun intended I guess).

One possibility would be to have the usage be:

(jdbc/with-transaction-options [[con con-opts] connectable]
  ..)

Right, at work we use with-options only in one section of the code and in a handful of tests (where we specifically need to interact with older code that still uses clojure.java.jdbc and builds result sets differently).

A lot of us might not really require with-transaction-options, at least I don't given the object doesn't implement java.sql.Connection and javax.sql.Datasource . And I guess you're already cautious that the API surface area of the library remains small.

@kirill.salykin would like that API though 🙂

indeed

🙂

Just bear in mind you'll have the same rewrapping issue with with-logging as well and I don't plan to add a combinatorial explosion of these things 🙂

so, how do you guys deal with it?

pass over options everywhere?

if i want my query/insert/update to understand kebab case (unqualified) - what shall i do? copy paste options in every tx?

@UMPJRJU9E, you mention a lot of you might not reallt require this - how would you provide opts inside a tx? copy it over and over?

Per the docs, you can manually rewrap the connection inside the tx call.

(the docs literally have an example of what code to use)

thanks, i’ll have a look

Or you can just declare an options map somewhere and just add it to every next.jdbc call -- which is mostly what we do on a per-namespace basis (since we're dealing with a mix of new code trafficking in qualified hash maps and old code trafficking in unqualified lowercase hash maps and some legacy code trafficking in unqualified mixed case hash maps 👀 ) -- but the majority of our new next.jdbc code uses the defaults.

And I strongly encourage people to use the defaults and not try to convert to unqualified keys.

Consequently, we almost never actually pass any options -- except in legacy code/code that interacts with legacy code.

well, defaults doesnt work for everybody 🙂 copying it to every ns seems redundant thanks, I’ll look into the rewrapping

(and part of why I initially resisted adding with-options and why I didn't want to "fix" the unwrapping inherent in calling get-connection or with-transaction was because I want not using the defaults to be painful, to try to discourage people from not using the default behavior!)

by rewrapping you meant this:

(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

@kirill.salykin I have wrappers for jdbc.sql functions that pass in the options, 7-8, of them. And these are what are used throughout the code base instead of calling the next.jdbc functions directly.

Aye, wrapping the (small) API is another possibility that I've seen quite a few folks do. Another good reason for a small API.

(worth noting that we use very, very few transactions in our code -- because they are rarely needed in practice -- but I see lots of people slavishly wrapping every "group" of calls in with-transaction still)

@UMPJRJU9E would it be possible for you to share the code)

> because they are rarely needed in practice i would not agree here a lot

but that other topic

thanks for your input, time to go to bed for me

have a great day/night!

In our 113K line code base we have just 19 with-transaction calls in 12 files.

Wow! Interesting. I've seen Java folks inspired by the onion architecture open transactions and pass that around to all the subsequent functions.

I mean when they start doing functional clojure

Reminded me of RH mentioning in the HOPL talk that there is only one usage of atom in all of the Datomic codebase

Connections are auto-commit by default, so -- outside explicit transactions -- everything is like its own single-operation TX. Then as soon as you intersperse any sort of external interactions around SQL operations, a SQL TX alone won't help you -- it won't roll back any of the external stuff -- and a lot of real-world stuff falls into that category in my experience. So you are much better off in general recognizing that (quote) "your coffee shop does not use two-phase commit" and planning accordingly...

https://ieeexplore.ieee.org/document/1407829 is the paper I'm referring to