Debugging ClojureQL

Since last week, I embarked on a new endeavor, contributing to ClojureQL. My future projects involving Clojure will need to access some databases, some of which doesn't behave in the same way. Sure, there is already clojure.contrib.sql that give you a wrapper around JDBC, but there are two problems with this approach. First, JDBC is quite good at what it does, yet is not a very sophisticated tool. It gives you access to a portable subset of SQL, that mainly support querying and updating data. This leaves a lot of advanced features out of the deal, these are still available, but in a non-portable way. The other way around, if a database system doesn't support a common feature, JDBC cannot do anything about it, the driver can though. Second, the clojure.contrib sql API has a very procedural feeling to it, it doesn't even let you play with an intermediary form as it executes statement directly. That's enough for basic database interactions, but not for serious database agnostic development.

Lets put off advocacy and talk about something concrete. While contributing to ClojureQL, I realized there was some issues with debugging. It was working well for Postgres (which I had an instance running), I simply had to use the compile-sql method. A problem arise when you're testing changes that affect all backends. In this case, to verify the SQL generated, you need a server for each DBMS you want to test. After looking at the code for some time, I found an easy way of compiling statements without a connection. We can create mock objects using Clojure's proxy macro and use them instead of live connections. For now, it's really simple as no backend implementations are actually using the connector. To put this idea into practice, I wrote a macro to debug multiple databases.

(defmacro debug [db ast]
  (let [connector (.getName (db *connectors*))]
    `(compile-sql ~ast (proxy [~(symbol connector)] []))))

It uses a map containing the interfaces used by all backends with keywords as keys.

(def *connectors* {
  :postgres org.postgresql.PGConnection
  :mysql    com.mysql.jdbc.Connection
  :derby    org.apache.derby.iapi.jdbc.EngineConnection
  :generic  Object})

With this code you can easily debug statements for every databases ClojureQL support. We can add a final touch to be able to see the SQL output for all backends with a single command.

(defmacro debug-and-print-all [ast]
  (let [longest (reduce max (map (comp count str) (keys *connectors*)))
        debug-and-print (fn [db] `(println (format (str "%1$-" ~longest "s : %2$s")
                                           ~(subs (str db) 1)
                                           (debug ~db ~ast))))]
    `(do ~@(map debug-and-print (keys *connectors*)))))

Finally, you can see an example output of the debug-and-show-all macro at the REPL using my ClojureQL clone.

clojureql-test> (debug-and-print-all (create-table test [id int title text date date] :non-nulls * :primary-key id :auto-inc id :unique title))
postgres  : CREATE TABLE test (id SERIAL,title text NOT NULL,date date NOT NULL,PRIMARY KEY ("id"),UNIQUE ("title"))
mysql     : CREATE TABLE test (id int NOT NULL  AUTO_INCREMENT ,title text NOT NULL ,date date NOT NULL ,PRIMARY KEY (`id`),UNIQUE (`title`)) 
derby     : CREATE TABLE test (id int NOT NULL  GENERATED ALWAYS AS IDENTITY ,title text NOT NULL ,date date NOT NULL ,PRIMARY KEY ("id"),UNIQUE ("title"))
generic   : CREATE TABLE test (id int NOT NULL ,title text NOT NULL ,date date NOT NULL ,PRIMARY KEY ("id"),UNIQUE ("title"))

This code is not working properly on the main repository for the moment, as there's some issues with Derby and the generic backend. It's enough for this post, I'll go back hacking my way through ClojureQL code to find other useful tricks and speed up version 1.0 release.