Clojure, YeSQL and PostgreSQL Arrays

For the past few weeks I’ve been experimenting with using the yesql library to interface with PostgreSQL from a Clojure web-app. One of the big upsides of yesql is the ability to write database-specific SQL which leverages the features of the underlying database.

This can potentially lead to a huge pay-off with Postges and its Array, JSON and Hstore column types. However, I did hit one snag with the Array columns.

The Problem

When using YeSQL with the PostgreSQL driver, array columns are returned from the database as objects of the Jdbc4Array class, and are not automatically cast to the appropriate clojure vector type. This fact only became apparent to me while building the JSON api for a data entity which used the Array column type, with the cheshire json parser choking on the Jdbc4Array instance.

Jdbc4Array has a getArray method, which looks promising. However, if you call the getArray method on the object instance it will raise an exception. A bit of googling led to a stackoverflow answer which suggested that the call to getArray needs to be called while the connection is still open, which is no good if you’ve now got the data out of the DB layer and just want to render it into a useful form.

Luckly yesql functions can be called inside a jdbc transaction, which allows us to do some work on the data returned from the query before the transaction, and its connection, are closed:

(defn get-snippet [snippet-id]  (jdbc/with-db-transaction [conn db-spec]    (let [row (first (-get-snippet conn snippet-id))]      (update-in row [:tags] (fn [ts] (vec (.getArray ts))))))) ;; -get-snippet is a yesql query function, ;; ’snippet‘ is a data entity with a :tags array attribute, ;; here we get the array and turn it into a clojure vec ;; before the transaction completes.

This is quite a bit more clumsy than I’d like it to be and could probably be solved by implementing a Collection interface on the Jdbc4Array class. Having said that, I do feel that YeSQL is a great library and a net-win for working with SQL databases from clojure-land.