When I wrote boodle, I didn’t think the database layer thoroughly. The model was simple enough to figure out: one namespace for each table. However, I stuck the queries at the top of these namespaces, using plain strings to compose them.
A working solution, yes, but far from being optimal. Let’s be honest: it’s just plain ugly and prone to error. White spaces, characters to escapes. Not a mess, but neither a smooth ride.
At 7bridges I recently had the chance to play with honeysql. At first I wrongly mistook it for syntactic sugar. A DSL on top of SQL? The horrors of ORM sprang to mind in a rush of anxiety, but I set aside my fears and gave it a chance anyway.
It took me ten minutes to fall in love with
honeysql. And turning to
for a proper refactoring was the following thought.
A quick example of how prettier queries are now:
(defn select-aims-with-transactions  (-> (hh/select :a.id [:a.name :aim] :a.target :t.amount) (hh/from [:transactions :t]) (hh/right-join [:aims :a] [:= :a.id :t.id_aim]) (hh/where [:= :a.achieved false]) hc/build db/query))
No strings, no white spaces, no escaping characters and quoting values. Heaven.
honeysql is also extensible, which makes it simple to add support for
(:require [honeysql.format :as fmt]) ;; … other code … (defmethod fmt/fn-handler "ilike" [_ col qstr] (str (fmt/to-sql col) " ilike " (fmt/to-sql qstr)))
During the refactoring process, I noticed something else that needed a better solution: dates.
I was relying on PostgreSQL
TO_DATE to format my dates in the queries.
honeysql is database-agnostic, so it pushed me to look for a better option.
(:require [java-time :as jt]) ;; … other code … (extend-protocol jdbc/IResultSetReadColumn Date (result-set-read-column [v _ _] (-> v jt/local-date ud/format-date)) Timestamp (result-set-read-column [v _ _] (-> v jt/local-date ud/format-date))) (extend-protocol jdbc/ISQLValue java.time.LocalDateTime (sql-value [v] (jt/sql-timestamp v)) java.time.LocalDate (sql-value [v] (jt/sql-timestamp v))) ;; … other code …
format-date is just a utility function:
(:require [java-time :as jt]) ;; … other code … (defn format-date "Return `date` in dd/MM/yyyy format." [date] (jt/format "dd/MM/yyyy" date))
If you have any experience with Java, you know dealing with dates and times
had not been a piece of cake before Java 8. I briefly mentioned
reporting on my experience with Java
Now that I am working with Clojure, dates and times strike back. And thanks to Vadim Platonov, I can safely use Java 8 Date-Time API with clojure.java-time.