Clojure with SQL databases

There are times when a specific piece of technology captures my attention in such a way that I feel the need to study more, and do things with it. Since I began to study Clojure, and after two jobs working with it, I’m still in love with it – I think it matches what I expect in a language most of the time, and also matches my repl-driven-development workflow.

So why I’m not considering it for new projects? Even personal ones?

When I need to prototype something really fast, I end up going for Ruby, with Sinatra (sorry rails, but you’re a terrible choice for me). I saw that in my last project, I worked with Sequel, postgresql, and Sinatra. For tests, I used VCR and recorded the external api calls that I had to make.

Well, I could probably prototype a simple VCR that would record a specific function call in Clojure. Sinatra, I could use Compojure. As for Sequel… Or ActiveRecord, for that matter…

There are a few libraries that can be used to communicate with SQL: Korma, that doesn’t support select ... for update and have some implicit connection problems and other issues, HoneySQL that interprets Clojure maps and converts then to SQL, and lots of other libraries that simply ask you to write your query and then, somehow, call then for you. I didn’t care too much for the later, after all, if I want to write queries I prefer to not depend on a library that does it to me – I know that there are people that don’t like to have SQL in the middle of the code, but I don’t see any problem with it.

What I do want is to compose queries in a simple way – not by mashing strings. I have lots of situations where I need to decide if I want to filter by one, two or more fields, and sometimes these fields will require a join. It’s really tedious to do all those string concatenations by hand, and also it really prone to error. Consider the following code:

;; Assume there's a "params" variable in scope that can contain
;; possible filters: :name will filter by names, :age by age,
;; and :account will have to join another table and filter by account
(require '[clojure.java.jdbc :as jdbc]
         '[clojure.string :as str])

(let [[filters joins]
      (cond-> [[] []]
              (:name params) 
              (update 0 conj ["\"people\".\"name\" LIKE ?" (:name params)])

              (:age params) 
              (update 0 conj ["\"people\".\"age\" = ?" (:age params)])

              (:account params) 
              (-> (update 0 conj ["\"accounts\".\"account\" = ?" (:account params)])
                  (update 1 conj "INNER JOIN \"accounts\" 
                                  ON \"accounts\".\"user_id\" = 
                                  \"people\".\"id\"")))

      query
      (cond-> "SELECT \"people\".* FROM \"people\""
              (not-empty joins) (str " " (str/join " " joins))
              (not-empty filters) (str " WHERE " (str/join " AND " (map first filters))))]
  (jdbc/query db (cons query (map second filters))))))

And compare it to the following version, using HoneySQL:

(require '[clojure.java.jdbc :as jdbc]
         '[honeysql.core :as honey])

(let [honey 
      (cond-> {:from [:people] :select [:people.*]}

              (:name params)
              (update :where conj [:like :people.name (:name params)])

              (:age params)
              (update :where conj [:= :people.age (:age params)])

              (:account params) 
              (-> (update :where conj 
                          [:= :accounts.account (:account params)])
                  (update :join 
                          #(concat % [:accounts 
                                      [:= :accounts.user_id :people.id]]))))
      query-map (update honey :where #(cons :and %))]

  (jdbc/query db (honey/format query-map :quoting :ansi)))))

It’s clearer that honey is way simpler. But even with Honey approach, you need to do all these joins by hand. I would like to use the ActiveRecord approach, where you define how your domain is persisted in the database and then it produces queries that would join associated models.

Considering that most Clojure structures are just maps anyway, and that HoneySQL also uses maps, the easiest way would be to model a map that would be used to ease the creation of honey’s map. Then, came the idea of Bee Record.

Under the hood, the project is kinda simple: model generates a template so that we can already send this template to honey and generate a valid query (so, if you map users model, for instance, you can already call (sql/query users db) and things will just work). This template will be passed to some helper methods that will normalize field names and also produce joins and take care of preload (but you don’t need to use Bee’s query function – you can feed it directly to HoneySQL and generate a valid query, except for cases when multiple queries are emitted, like preloads).

But there are still lots of edge cases that I still need to think about – for instance, a map is a valid filter for where in Bee, but it is also a valid query information on HoneySQL. It’s not clear for the code yet when to use which one, and it’s probable that we’ll never know (and need to fallback to honey. Not a big deal, really). But this means that I’m now able to re-write the above code with the following: first, we map the models:

(require '[clojure.java.jdbc :as jdbc]
         '[bee-record.sql :as sql])

(def accounts 
  (sql/model {:table :accounts
              :pk :id
              :fields [:id :account]}))

(def people
  (sql/model {:table :people :fields [:id :name :age]
              :pk :id
              :associations {:accounts {:model accounts
                                        :on {:id :user-id}}}}))

Now, every query that you need to do using these models becomes easier:

(cond-> people

        (:name params)
        (sql/restrict [:like :name (:name params)])

        (:age params)
        (sql/restrict {:age (:age params)})

        (:account params)
        (-> (sql/restrict {:accounts/account (:account params)})
            (sql/join :inner :accounts))

        :then
        (sql/query db))))

For now, I’m publishing the first snapshot versions. It’s not yet complete and there’s next to no documentation, but I hope it’ll be able to help push Clojure forward. And, as always, the code is on Github, so contributions are more than welcome!