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))
                                (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?

Picture by AAB_BAA

3 responses to “Automatically converting case between SQL and Clojure”

  1. Jonathon McKitrick Avatar
    Jonathon McKitrick

    This is exactly the problem I was about to start working on. Does this cover table names as well as column names?

    1. J. Pablo Fernández Avatar

      It doesn’t cover table names but with HugSQL, table names do not leak into symbols in the Clojure side, so, it’s not necessary. Are you finding it’s otherwise?

      1. Jonathon McKitrick Avatar
        Jonathon McKitrick

        I’ve been using Yesql, but I’m considering migrating to HugSQL if it’s not too much work.

Leave a Reply

You may also like:

If you want to work with me or hire me? Contact me

You can follow me or connect with me:

Or get new content delivered directly to your inbox.

Join 5,043 other subscribers

I wrote a book:

Stack of copies of How to Hire and Manage Remote Teams

How to Hire and Manage Remote Teams, where I distill all the techniques I've been using to build and manage distributed teams for the past 10 years.

I write about:

announcement blogging book book review book reviews books building Sano Business C# Clojure ClojureScript Common Lisp database Debian Esperanto Git ham radio history idea Java Kubuntu Lisp management Non-Fiction OpenID programming Python Radio Society of Great Britain Rails rant re-frame release Ruby Ruby on Rails Sano science science fiction security self-help Star Trek technology Ubuntu web Windows WordPress

I've been writing for a while:


%d bloggers like this: