How to cause JDBC results to return kebab-cased rather than snake_cased?

[Webdev_Tory]

HoneySQL takes care of translating anything I write to the DB with :kebab-case to the matching sql snake_case form. In some applications I have a translation occurring in my read functions that converts the result keys into kebab-case, but sometimes this misses (e.g. when I’m not “reading” but getting the postgres result of a create action). What’s the simplest way of ensuring I’ve got kebab-case results in everything I see coming from the DB? Is it meticulously adding the conversion fn to each accessor, or is there some higher-level way?

[seancorfield]

That will depend on which JDBC wrapper you are using: both clojure.java.jdbc and next.jdbc support ways to automatically turn SQL entities in result sets into Clojure keywords. HoneySQL only generates the SQL – it does not execute it – so it doesn’t even see the result sets coming back.

In clojure.java.jdbc, you can specify the :identifiers option and provide a function that operates on the string that is the SQL entity name. The default value for this is clojure.string/lower-case.

In next.jdbc, there are a range of :builder-fn options in the next.jdbc.result-set namespace, including as-modified-maps and as-unqualified-modified-maps which accept :qualifier-fn and :label-fn options to operate on the qualifier (table name) and column label respectively.

There’s a great library called camel-snake-kebab which handles all sorts of translations related to this. That means you don’t need to write your own string manipulation.

[Webdev_Tory]

I can verify that camel-snake-kebab is terrifically useful. And somehow I knew one answer would probably be next.jdbc As I haven’t scoped out serious the change to our application stack would be with next instead of normal jdbc, though, I’m glad to hear that jdbc has a solution, too.

[seancorfield]

Which JDBC library are you using?

[Webdev_Tory]

clojure.java.jdbc. It’s the one I learned from Luminus originally, and I’ve never changed.

[seancorfield]

Happy to help if you do decide to switch. This covers most of the basic differences: https://cljdoc.org/d/seancorfield/next.jdbc/1.0.462/doc/migration-from-clojure-java-jdbc

But clojure.java.jdbc is rock solid. We’ve used it in production for nine years and I’ve been keeping it maintained all that time. I didn’t want to make breaking changes to it, at this point, so next.jdbc is the “1.0” that I never quite got to with c.j.j.

[Webdev_Tory]

I’m having a bad time figuring out where that :identifiers option is supposed to be specified. Right now I setup a pool like so:

(defstate ^:dynamic *db*
  :start {:datasource
          (let [db (-> env :humforms :db)]
            (hik/make-datasource db))}
  :stop (hik/close-datasource (:datasource *db*)))

I thought I would add :identifiers to my db map there (which is the one that includes user credentials, pool information, etc) but I get

Execution error at com.zaxxer.hikari.util.PropertyElf/setProperty (PropertyElf.java:135).
Property identifiers does not exist on target class org.postgresql.ds.PGSimpleDataSource

[seancorfield]

Ugh! Mount

What are you passing into the clojure.java.jdbc calls? *db*? If so, you should be able to add default settings into *db* at startup. Not db which is coming from your environment – which is the map passed to hik/make-datasource.

(defstate ^:dynamic *db*
  :start {:identifiers csk/->kebab-case ; or whatever
          :datasource
          (let [db (-> env :humforms :db)]
            (hik/make-datasource db))}
  :stop (hik/close-datasource (:datasource *db*)))

You could also add :entities csk/->snake_case to default the behavior for c.j.j.'s insert/update etc.

[seancorfield]

Just wanted to revisit this and mention that as of 1.1.569, next.jdbc provides out-of-the-box support for camel-snake-kebab (if you have it on your classpath): https://github.com/seancorfield/next-jdbc/releases/tag/v1.1.569

That same release provides built-in support for Stuart Sierra’s Component library in the form of next.jdbc.connection/component which provides a Component wrapped around the ->pool function for building connection pooled datasources with HikariCP and c3p0. I guess Mount users could leverage ->pool here.

In the latest version (1.1.582), the ability to construct a JDBC URL has been directly exposed in next.jdbc.connection/jdbc-url so that you can construct a connection string easily that can then be passed to ->pool or component as a :jdbcUrl attribute. This allows for a cleaner separation of “db spec” attributes and connection pool parameters.

In addition, 1.1.569 (mentioned above) introduced next.jdbc/with-options which allows you to wrap a java.sql.DataSource with options, such as :builder-fn, :table-fn, :column-fn, so you can globally control case conversions (with some caveats – read the docs please!).