Re-implementing boodle database layer

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 Honey SQL. 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 boodle 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 operators.

(: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 LocalDate when reporting on my experience with Java 8.²

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.