Clojurians Log v2

how does one use honeysql-postgres's returning in combination with jdbc/execute! ? https://github.com/nilenso/honeysql-postgres

(jdbc/execute! conn (sql/format {:insert-into :my_table :values [{:a 1 :b 2}] :returning [:id :a :b]}) my-query-opts) should return a vector of maps with returned values.

doesn't work for me. what's in my-query-opts?

{:return-keys true}

Stuff like :builder-fn would go in my-query-opts. (https://github.com/seancorfield/next-jdbc/blob/develop/doc/all-the-options.md#generating-rows-and-result-sets)

What error are you getting?

so far I've used only hugsql, I'm trying to convert to honeysql for some of the queries

:return-keys true does something, I'll try to see if I can get this to work with that option

Oops, forgot to throw conn in my above invocation

Have you required the honeysql format namespace?

I've used :returning consistently and never had a problem with it.

I've required these:

[honeysql.core :as hsql]
   [honeysql.helpers :as h]
   [honeysql-postgres.format]
   [honeysql-postgres.helpers :as psqlh]
   

Okay, that looks good to me

The query also looks good btw, but I don't get the keys from returning

@borkdude I think you need to require [honeysql-postgres.format :refer :all]

If that's true, then honeysql-postgres made a design mistake 😉

You don't need to do that

anyway, this is at the end of my query RETURNING collection_uuid uri. Looks good

Oh sorry my bad. You don’t need to 🙂

but as the result with :return-keys true I only get a single map with {:uri ...} while I'm inserting multiple values...

Maybe something is off with your connection config?

the records are inserted fine, it's just this returning stuff that doesn't work. I might return to hugsql because this is too time consuming

This is a working config if it helps: https://github.com/codonnell/mygiftlist-blog/blob/master/src/rocks/mygiftlist/db.clj

I'm using the same connection that works with hugsql (has worked for years) so I don't think it's a config issue in the connection

If :return-keys is provided, db-do-prepared-return-keys will be called
  instead of db-do-prepared, and the result will be a sequence of maps
  containing the generated keys.

(jdbc/execute! (svc/database) q {:return-keys true }) ;;=> {:uri #uuid "1b19090c-b475-11ea-9bb7-001b21bedfe8"}

(the uri is also incorrect, the uri is not a uuid!)

That's really weird. hugsql isn't doing anything particularly magical: https://github.com/layerware/hugsql/blob/master/hugsql-adapter-next-jdbc/src/hugsql/adapter/next_jdbc.clj. (Just using :return-keys, it appears.)

@borkdude Are you using next-jdbc?

no

Yeah, I had the same problem

I fixed it by using next-jdbc

do you think next-jdbc is backwards compatible?

we have a pretty big surface area with previous-jdbc

I honestly don't have enough experience with jdbc to answer that

https://cljdoc.org/d/seancorfield/next.jdbc/1.0.462/doc/migration-from-clojure-java-jdbc

yeah, not ready to go into that rabbit hole right now

hmm, it seems hugsql calls this function directly: https://github.com/layerware/hugsql/blob/18f1fa72883cd64a22dd2252a12bbee331ca0ac5/hugsql-adapter-clojure-java-jdbc/src/hugsql/adapter/clojure_java_jdbc.clj#L11

instead of going through execute

maybe for the same reason

Oh you're using clojure.java.jdbc! Have you tried using jdbc/query instead of jdbc/execute!? IIRC it always returns a sequence of maps, while jdbc/execute! has some magic behavior.

@codonnell I'm inserting values. Should I be using jdbc/query in that case?

I guess up to you. I've never seen a ton of value in differentiating the calls; I'm going to look at the SQL to understand what's happening regardless.

@codonnell the SQL looks good. did you return multiple fields or just one? just one might work, but I'm returning multiple

It's been a little while since I've used clojure.java.jdbc; let me see if I can find an example from an older project. I'm pretty sure I've returned multiple values without issue.

hmm, (jdbc/db-do-prepared-return-keys (svc/database) q) has the same issue: returns a single map with one key

the documentation in https://github.com/nilenso/honeysql-postgres about returning isn't every explicit when it comes to returning multiple columns

If you look at the generated query, it should be clear whether the right SQL (with multiple columns) is generated or not.

aha, it seems it's missing a comma. when I do (psqlh/returning (hsql/raw "collection_uuid, uri")) I get a map with all the fields. Still just one map though, not like 6 that I'm expecting

the comma thing seems like a bug to me in honeysql-postgres

Looks like it should be comma-separated looking at the code: https://github.com/nilenso/honeysql-postgres/blob/master/src/honeysql_postgres/format.cljc#L132

Maybe you're passing it a vector of columns instead of the columns as individual arguments?

I don't usually use the helpers, so not sure exactly how they're supposed to be invoked.

I've tried all kinds of combinations.

Though that wouldn't fix the issue of only getting one return value back

With and without aliases. In a vector, as separate args, etc.

@borkdude returning expects column names as parameters.

(jdbc/db-do-prepared-return-keys transaction (-> (insert-into table)
                                                 (values [record])
                                                 (psqlh/returning :field-a :field-b)
                                                 sql/format))

This mimimal repro works well for me: https://gist.github.com/codonnell/51d87e6a9f85607525142c21e8721e1a

thanks, this now works! now the only remaining problem is that (jdbc/execute! ... {:return-keys true}) only returns one map, while I'm inserting 6 rows

I'll try your repro, codonnell

return-keys expects a collection of record fields you expect to be returned. Also, depending on your jdbc version, db-do-prepared-return-keys (which execute! ends up calling in the presence of return-keys) might be overwriting return-keys with true so it ends up returning a boolean based on the presence of a return value. https://github.com/clojure/java.jdbc/blob/37587dc32a29ea39ace9d944a250445619f528a3/src/main/clojure/clojure/java/jdbc.clj#L942

@codonnell your repro works for me. I'm slightly confused why query is used to insert record, but if it works...

and now also my own query works. thanks all, this was super confusing but now it works

so: (jdbc/query conn q) (note: query, not execute!) with q having (psqlh/returning :u.collection_uuid :u.uri) in it and NOT using {:return-keys true}.

❤️

:man-shrugging: I'm not familiar enough with jdbc to understand without spending a bunch of time reading clojure.java.jdbc source code. I just know query returns results effectively, so I have used it for queries that return results (even an insert with returning clause). FWIW the jdbc.next API is much simpler; there's just jdbc/execute! and jdbc/execute-one!. I like it much more.

yeah, I look forward to using that in a next greenfield project, but I'm not ready to rewrite all our stuff using namespaced keywords etc

You can pass in options so a query returns unnamespaced keywords with jdbc.next

that still requires me to pass in options everywhere 🙂 maybe I can migrate query by query, assuming these libs can be used simultaneously

Can always write little wrapper functions like https://github.com/codonnell/mygiftlist-blog/blob/master/src/rocks/mygiftlist/db.clj#L52-L60. That said, I'm sure there's some corner cases that would need to be dealt with; I haven't upgraded an existing project from clojure.java.jdbc to jdbc.next personally.

Anyways, hope I'm not coming across as pushy. You should do whatever is best for your project. 🙂

not pushy, thanks for the help

Hey #honeysql folks, I am looking for tooling for convenient, interactive development of SQL statements resp. honeysql query definitions. Currently I am using Oracle SQL Developer and manual translation to honeysql. It sucks. Some of the nice features I would like to have: • preview with result paging • auto-completion for table names, column names, ... • separate sessions for multiple connections

@johannes.f.knauf Probably a well supported Clojure editor + REPL comes closest to this

Maybe in combination with REBL for paging

How would I provide the auto-completion on DB-side data in that case?

Well, if you use a keyword more than once your editor will likely autocomplete that, but I understand that's a much more basic feature than you would like. Maybe going the "generate a .sql file from Clojure using honeysql" route and then opening it in a capable SQL editor is better for your use case then?

Hmm, I think there is some middle ground: Grabbing the schema definitions as a one-time script activity and using that for auto-completion -- similar to the good old CTAGS approach.

I would rather like to get rid of the SQL-only editors, which do not allow me to do non-SQL data manipulation, visualisation, ....

Yeah. You can find a project here which generates tags for Clojure vars: https://github.com/sogaiu/alc.index-defs Maybe it's helpful for getting started

I can see myself writing a script with babashka and babashka-sql-pods for doing the same for Postgres. There is some work on an Oracle driver but I haven't heard from that person for a while

Do you have some link to material to get me started? Sounds like a fun evening activity.

Where would I have to integrate the oracle driver?

@johannes.f.knauf To connect to a database from babashka, there is https://github.com/babashka/babashka-sql-pods But currently only Postgres and HSQLDB are supported. I have no Oracle database but I would welcome a PR to add Oracle

I don't know if there are legal issues with distributing a binary that contains an Oracle driver, that would be good to check

Yes, there are. Typically what you do is provide a sample snippet for users how to provide the ojdbc jar file e.g. from a local maven repo.

Would that work? What dependency mechanism does babashka use? (Sorry if that is a stupid question. Never used it so far. Shame on me.)

Yeah, I think people would just have to compile pod-babashka-oracle themselves.

Babashka just uses Clojars/maven to gather dependencies and then creates a binary out of those.

A pod is another standalone binary which can act as a library to babashka.

I see. Sounds feasible.

babashka itself also supports the honeysql library

@borkdude I wasn't awake for your big, long discussion but, yeah, you can absolutely use c.j.j and next.jdbc together -- we do at work as we're writing all new code with next.jdbc and still maintaining code based on c.j.j (we even use both in the same namespace in a few places).

The query vs execute! thing in c.j.j is to do with whether it expects to process a full result set or not -- and several of the functions in c.j.j call different functions in JDBC. That's all solved in next.jdbc which uses .execute() everywhere and will always return a full result set if JDBC makes one available (whereas c.j.j will ignore the result set in several situations).

thanks