Clojurians Log v2

so, started using next.jdbc today (thanks @seancorfield!) and I think it feels a bit awkward wrt the api ergonomics at times and left me wondering if I'm not doing something wrong (which I'd assume it's the case). 100% of my cases I have to use the opts to set the builder-fn (unfortunately I have to drop the kw namespace so I can spec the returns properly) and it starts getting in my way since it's the last parameter and the connectable is the first parameter so I can't use thread macros as I usually do. Am I getting something wrong? Like, is there a way of setting the default options or something like that?

@caio Specs work with qualified keywords. That's part of why next.jdbc auto-qualifies column names with their table by default.

yeah, I know they do, but it's trickier for my case unfortunately. basically it's a (temporary) monolith connecting to different databases that may have tables with the same name across them

And I know I'd be able to further specify the keyword returned by prepending something like the db name with the qualify-fn , but it'd be the same issue ( opts always being necessary)

If I have a namespace that I'm migrating to next.jdbc and need non-default behavior, I put

(def ^:private jdbc-options {:builder-fn rs/as-unqualified-maps ,,,})

Or you could always create a wrapper namespace for next.jdbc that exposes the same primary API but adds options into all calls.

Since next.jdbc works with native JDBC (Java) objects, there's nothing to hang options on.

(def ^:private default-jdbc-opts {:builder-fn jdbc.result-set/as-unqualified-lower-maps}) same 🙂 yeah, I was thinking about going through the wrapper route, just wanted to make sure I wasn't missing anything

I have been looking at some sort of "middleware" or annotated "connectable" that can have default options but it's a much harder problem than it looks 🙂

(because if you have an annotated DataSource you have to ensure that all paths that produce Connection still actually produce a java.sql.Connection object and yet can still carry the options around -- JDBC doesn't make this very easy 😐 )

As for the threading macros, I'm not quite sure what you're asking there... The API of next.jdbc is very similar to clojure.java.jdbc which also has the db-spec as the first argument and the options as the last argument.

oh yeah, that annotated connectable would be pretty cool and does sound hard. re thread macros: yeah, that's true, I guess I just didn't have to use options as the last argument that much and got used to just (->> at the end of any transform for passing it to clojure.java.jdbc API

Gotcha...

You can see my middleware experiment here https://github.com/seancorfield/next-jdbc/blob/master/test/next/jdbc/middleware.clj with tests/usages examples here https://github.com/seancorfield/next-jdbc/blob/master/test/next/jdbc/middleware_test.clj -- but it doesn't compose the way I want (which is why it's all in test and undocumented 🙂 )

I may simplify it to just deal with options and drop the "interceptor" stuff... but that does have its uses...

Are there any examples of using next.jdbc when doing a join and then mapping the results?

Mapping the results? Would you get something different than a seq of maps?

Mini rant: JDBC is clunky and full of annoyances. I’m amazed at the rough edges of dealing with the Postgres JDBC driver, and the amount of extra work needed to cast various parameters to the correct type.

OTOH HugSQL is brilliant. Goes most of the way to solve the sql composability problem.

Yes, you get back a map, but if youre using a join, then you would get back duplicate data, so say given this:

[{:address/id 1
  :address/name "foo"
  :address/surname "bar"}
 {:address/id 2
  :address/name "baz"
  :address/surname "wibble"}
 {:person/name "Cuthbert"
  :person/surname "McDibble"}
 {:person/name "Cuthbert"
  :person/surname "McDibble"}]

which is a inner join between a person and an address (with a person having multiple addresses), I was wondering if there is an example of how to map that so that I can pull out the person, then all the addresses.

If you’re using Postgres you might use one of the jsonb agg functions.

This isn't json

What's edn that I've just put down as the result of invoking next.jdbc + honeysql 🙂

No no I mean, the jsonb agg functions happen on the sql side

But it's not json

I'm not storing json, it's just normal relations with scalars

https://medium.com/@geekuillaume/sql-tip-jsonb-agg-in-postgresql-for-simple-one-to-many-joins-bde8caa30c46

You get a jsonb column based on the scalars you have. The original data is not jsonb.

If that doesn’t work (you might need data that isn’t json encodable) you probably need to fall back to https://clojuredocs.org/clojure.core/group-by

In SQL Server, you can just do this:

select *,
    (select * 
     from posts p 
     where p.user_id = a.i for json path) as posts
 from users a
 for json path

I’m on a mobile so can’t try any code right now...

Checked myself, and apparently you can do this in PG:

select row_to_json(t)
from (
  select text, pronunciation,
    (
      select array_to_json(array_agg(row_to_json(d)))
      from (
        select part_of_speech, body
        from definitions
        where word_id=words.id
        order by position asc
      ) d
    ) as definitions
  from words
  where text = 'autumn'
) t

ooh, just trying out the jsonb_agg - seems to do what I want!

Never knew!

I pored over the postgresql documentation over the last few weeks, there’s ton of hidden gems like that

If only we could have EDN 😄

🙂

thanks!

@seancorfield I didn't have enough time to go through your experiments, but if you want to solve it just for opts, why not introducing some dynamic var like *default-opts* and an accompanying macro (with-default-opts {}) that'd just bind that var. this way all the internal next.jdbc functions that use options would just need to first (merge *default-opts* user-provided-opts)

I can try writing a PR for that if it makes sense

@caio Dynamic vars are evil, and besides, that would introduce a runtime overhead that everyone would pay even if they never used default options.

@dharrigan I'm a bit puzzled about your join above -- that result doesn't look like a join to me since your two pairs of rows have disjoint columns.

true dat 😕

However default options end up being handled, the overhead they incur should be completely opt-in.

just wondering if there's a good sql shell built on top of clojure and jdbc ...

it sure does sound like a feasible implementation for a tool

@seancorfield I'll come back to your question in a bit. Do you know what the correct way to do group (by) with honeysql, with multiple expressions? If I try (group [:a :b]) it renders as group-by a b but if I try (group [[:a] [:b]]) I get a spec error.

(there's no example in the docs that has a group with more than one expression)

@dharrigan I'm not sure how it works with the helper functions, but if you are building your SQL map directly then {:group-by [:a :b]} should work

Have you tried (group :a :b)? It looks like it should work (though I haven't tried it with a real query)

that does work!

user=> (-> (select :a) (from :table) (group :a :b) (h/format))
["SELECT a FROM table GROUP BY a, b"]

I can't believe I didn't try that one! 🙂

I guess I got blinded by all the vectors 🙂

Updated the readme to show more than one column in group https://github.com/jkk/honeysql/blob/master/README.md#big-complicated-example

w00t

now, that's what I call service!