SQL, at least PostgreSQL, likes using snake case for table names, such as user_name, while in Clojure, kebab case is preferred, such as user-name. When you use the library Yesql you are likely to end up with keywords in snake case unless you do some conversion. In our toy project, Ninja Tools, I wanted to perform these conversions automatically.
To achieve this automatic conversion I wanted to wrap every single function generated by Yesql and do the conversion both ways. This sounded familiar. Dmitri Sotnikov and I came up with a neat trick to do that in Conman, a connection manager for Yesql, that wraps all the Yesql functions binding them to a connection.
This code wraps around the result for Conman but if you just need to do something similar with plain Yesql I’d recommend looking at Conman’s code. Normally, this is how you would use Conman:
(ns ninjatools.db.core ;... ) (defonce ^:dynamic conn (atom nil)) (conman/bind-connection ninjatools.db.core/conn "sql/queries.sql")]
and this is the code to do the automatic wrapping to convert case style:
(ns ninjatools.db.core
;...
)
(defonce ^:dynamic conn (atom nil))
(ns ninjatools.db.core.queries
(:require [conman.core :as conman]
[camel-snake-kebab.core :as csk]
[camel-snake-kebab.extras :as csk-extras]))
(doall (for [yesql-query (conman/bind-connection ninjatools.db.core/conn "sql/queries.sql")]
(intern 'ninjatools.db.core
(with-meta (:name (meta yesql-query)) (meta yesql-query))
(fn [& args]
(let [args (if (< 1 (count args))
args
(cons (csk-extras/transform-keys csk/->snake_case (first args)) (rest args)))]
(csk-extras/transform-keys csk/->kebab-case (apply yesql-query args)))))))
(in-ns 'ninjatools.db.core)
Let me explain what’s going on here. The namespace of the file is ninjatools.db.core. In this namespace we define an atom, conn, to store the connection and then the madness begins.
Line 7 defines another namespace, one that is used to store the original functions created by Conman and which we are not likely to ever access directly. On line 11 we do exactly that, we invoke Conman, and thus Yesql, so the file with the queries is read and turn into a bunch of functions in the ninjatools.db.core.queries namespace. This functions are also returned as a sequence that we are going to iterate over.
In line 12 we call intern to essentially define a function in a different namespace, in this case, the one that matches this file. The name of this new function will be the same as the one defined by Yesql thanks to Clojure’s ability to inspect the meta-data of a function, as we can see in line 13. While we are at it, let’s also make the meta-data be same, just in case.
Since we don’t know how many arguments the function will take, we accept any amount and if there’s more than one, in line 17 we convert the first one from Clojure’s kebab-case to PostgreSQL’s snake_case. The result goest through the reverse process in line 18.
Very important for the sake of the rest of the file, line 19 takes us back to the natural namespace for this file. Neat trick, isn’t it? Obviously it would be better if this wasn’t required a lot, which is the goal of issue 108, “Callback when defining queries”.
Any questions?
