Clojurians Log v2

Yep, working nicely

Thanks for testing it!

Np, thank you for your work!

The documentation explains why that has to be the case: with-transaction (and a few other functions in next.jdbc) produce raw Java objects for performance reasons.

tx* is a java.sql.Connection. with-options wraps it in a record, along with the options, and which implements Executable so that execute-one! knows how to unwrap the Connection and options again for use.

In my case, tx* is a data-source with-options already. It'd be nice that with-transaction allows for inheriting those options.

I could also do my own with-transaction wrapper.

No, tx* is a Connection -- that's what with-transaction does. tx is a DefaultOptions record (wrapper). Your rdb/data-source is also a DefaultOptions record, based on what you said.

with-connection follows a similar pattern, yielding a raw Connection. Since this seems to crop up fairly regularly (at least, since I grudgingly added with-options 🙂 ), I might add with-transaction+options and with-connection+options that would re-wrap the Connection in DefaultOptions -- I can't change the existing functions/macros because code in the wild likely depends on the raw Connection type for interop-related calls.

It could be possible to accept more options in with-transaction?

It currently accepts transaction options

Anyway, just a possibility. Thanks for your help @seancorfield!

I want with-transaction to "look like" (and lint like) with-open -- having a third argument would look weird. I also don't like functions that "return" different types based on arity.

with-transaction already accepts 3 items in the vector

True, and I regret that 🙂

But yeah, another macro would be good as well

Which is why I don't want to make it worse. I'm creating an issue to add +options versions of with-transaction and with-connection.

That'd be great, thank you!

Btw, how could you pass transaction parameters without adding a 3 item?

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

with-transaction could perhaps have been made to look more like with-open by binding a second symbol with the tx-opts:

(jdbc/with-transaction [tx db-spec tx-opts {:isolation :serializable}]
  ;; can use both tx and tx-opts here
  ;; with-transaction treats second optional binding as way to get the 3rd arg for the transact function
  ...)

I didn't think of that at the time 🙂

(jdbc/with-transaction ^:{:isolation :serializable}[tx ds]
  ...)

> I didn't think of that at the time Wouldn't it be feasible still? It'd be possible to detect 4 args.

Yeah, but I'd still have to support the 3-arg version so it's hardly worth it now 😞 As for metadata, since it's a macro, that wouldn't work for folks who wanted to pass tx-opts at runtime rather than just static...

(API design is hard 🙂 )

> but I'd still have to support the 3-arg version so it's hardly worth it now There could be a clear separation in macro code. No effect on runtime performance.

In this case, I'm not worried about performance. Just that I'd have to continue to support the 3-arg version "forever" so adding a 4-arg version doesn't gain anything. The 3-arg version already requires custom linting (and there's a clj-kondo hook for it already).

I see, there is more to it than just the macro

@titogarcia I just pushed a 1.3.999-SNAPSHOT version that includes with-transaction+options for you to try out (or you can use a git dep to test it). There are no doc updates yet, beyond the docstring changes I've made.

This may help:

(defn json-agg-subquery
  "Use in a SELECT clause to obtain a given subquery as a JSON array."
  ; Wraps in a subquery for preserving SELECT aliases as JSON keys.
  ; See 
  [alias subquery]
  [{:select [[[:jsonb_agg :subquery/*] alias]]
    :from [[subquery :subquery]]}])

Then decode JSON for every resulting row:

(defn decode-json [^PGobject pgobj]
  (when pgobj
    (-> pgobj .getValue json/read-value)))

Well this seems awfully relevant to this discussion 😃 https://www.tamizhvendan.in/blog/announcing-honeyeql-1.0/

Interesting, need to check it out (although I'm not looking forward to 3rd migration of the DB layer in my project 😢 ), I wonder how it avoids the N+1 problem

😅

I’m very curious about this too. I tend to get very hesitant about these magic layers, but I’m a bit optimistic here for some reason 😃

Say I have two tables.

TABLE 1 (product):
product.id
product.title
product.description

TABLE 2 (photo)
photo.id
photo.product_id
photo.filename
photo.creation_date

{:product/id     ""
 :product/title  ""
 :product/photos [{:photo/id            ""
                   :photo/filename      ""
                   :photo/creation_date #inst""}]}

SELECT product.*, JSON_AGG(TO_JSONB(photo.*)) AS "product/photos"
FROM product
INNER JOIN photo ON photo.product_id = product.id;

I haven't found a workable solution because JSON_... doesn't know anything about dates/timestamps/etc. So all my query functions have a standard post-processing to convert fields in JSONB fields to proper types

😢

Why not do a query whose result has a row per photo, and then postprocess into your desired structure? group-py gets you most of the way there. There’s been some discussion about a standard helper for this sort of thing, here’s the relevant issue: https://github.com/seancorfield/next-jdbc/issues/201

It’s a fair question. Cardinality explosion sucks, I suppose. Wonder if this is really a moot point due to efficient data representation. Otherwise it’s possible. I’d prefer to do the aggregation at a lower level — depending on keyword ns as data feels icky.

The cardinality of your data doesn’t change, just that the columns of the “one” side of the one-to-many are repeated a bunch. The data still scales predominantly with the “many” side. The keyword namespaces are part of the contract provided by next.jdbc, I think it’s perfectly reasonable to depend on them. Ultimately the problem is that SQL is row-oriented, even higher-level ORMs in other languages are still doing this aggregation via post-processing (or at worst, N+1 queries 😬)

The repeated rows are the cardinality explosion to which I refer — N “one” * M “many”. Dunno how much of a problem this is in practice. While true, it still feels icky. I’m not sure keywords were intended to be a data format. {:table "" :column-name ""} would be much nicer. More 😢

I don't use namespaced keywords either :-) eventually everything ends up being JSON or Avro so that was already a pain to deal with

Haha yeah. I’ve even fully embraced _ delimited keys since - is a pita in nearly every other lang.

to clarify: I don't use namespaced kws in SQL/JDBC stuff

Un-namespacing the keywords after the transformation is very easy to do, and if you don’t want namespaced query results by default I think you can even set that on a query-by-query basis

💯 I have a top-level ns that wraps JDBC & HoneySQL and exposes fns like :

(defn execute! [conn query]
  (jdbc/execute! conn query
                 jdbc/unqualified-snake-kebab-opts))

I don't think you can achieve this purely in SQL with aggregate result. You will have to do some sort of post processing. You can do it via Malli, if you use that. There's mt/json-transformer