Has anyone made use of PGJDBC-NG, https://impossibl.github.io/pgjdbc-ng in Clojure projects? I see that it has much better support for some of Postgres' features, like custom composite types. I've been using Hugsql as a higher level framework, and it supports custom adapters, but a community one hasn't been contributed yet. Maybe someone here has done some lower-level work with it.

clojure.java.jdbc was tested with it against an older version of PostgreSQL. next.jdbc should work with it too, although I only test against the official driver (and use the Embedded PostgreSQL engine for testing).

That said @bdrillard HugSQL has an adapter for next.jdbc so you could use HugSQL with next.jdbc and the Impossibl JDBC driver.

How exactly does :return-keys work in next.jdbc?

does it map to something standard in jdbc or is it database dependent?

(to the degree that "standard" means a given database driver won't do a wacky thing)

Same functionality as in clojure.java.jdbc 🙂

Yes, it maps to standard JDBC functionality... just a sec... let me pull up the source...

If it's a vector of column names, it's passed into .prepareStatement as a String[] to suggest that JDBC return those columns after execution. Else Statement/RETURN_GENERATED_KEYS is passed to .prepareStatement

With the caveat that

(str ":concurrency, :cursors, and :result-type "
                          "may not be specified with :return-keys."))))

The ultimate behavior of :return-keys is, of course, vendor-specific -- not all drivers support an array of columns; not all drivers actually return keys even when you ask for them.

@emccue Does that help?

The other thing that next.jdbc does with :return-keys is that it triggers a call to .getGeneratedKeys on any operation that does not automatically return a ResultSet, i.e., when .execute returns false.

yeah, that makes sense

so some dbs support Statement/RETURN_GENERATED_KEYS and others only do .getGeneratedKeys and others don't support it at all

because sql is sql

Well, .getGeneratedKeys only works if either Statement/RETURN_GENERATED_KEYS or a String[] of columns was accepted in .prepareStatement -- I only mention them separately because you can create a prepared statement independently of executing it and you need to pass :return-keys in both contexts if you do that.

If you rely on next.jdbc (or clojure.java.jdbc) creating the prepared statement for you behind the scenes, it automatically does both things.

For example, in PostgreSQL, insert gives you back a result set with all columns in it (so the .getGeneratedKeys path never gets executed because .execute returns true and we call .getResultSet instead I believe).

(I haven't confirmed that -- it may be that all columns is the default from .getGeneratedKeys but the effect is the same: a result set with all columns in it)

And if you say insert ... returning * in PostgreSQL I think that returns you all the columns as well (but as a result set in the first place rather than via .getGeneratedKeys -- again, I haven't specifically confirmed that, but I suspect you don't even need :return-keys in such a case?)

"because sql is sql"