Slick Way to Add Array and JSON Support to Postgres Clojure JDBC
The default JDBC behavior in clojure.java.jdbc is not to handle the additional Postgres data types like Array and JSON. But you do have the protocols that you can use to override the behavior of how to store and read things - and this article looks to set this up for both these data types.
The idea is to look at the data type on both the write and read, and do the JSON mapping and we're done. The code is very simple:
(ns our-app.jdbc.json "Inspired by http://www.niwi.be/2014/04/13/ postgresql-json-field-with-clojure-and-jdbc/" (:require [clojure.java.jdbc :as jdbc] [cheshire.core :as json]) (:import org.postgresql.util.PGobject)) (defn value-to-json-pgobject [value] (doto (PGobject.) (.setType "json") (.setValue (json/generate-string value)))) (extend-protocol jdbc/ISQLValue clojure.lang.IPersistentMap (sql-value [value] (value-to-json-pgobject value)) clojure.lang.IPersistentVector (sql-value [value] (value-to-json-pgobject value))) clojure.lang.IPersistentList (sql-value [value] (value-to-json-pgobject value))) clojure.lang.LazySeq (sql-value [value] (value-to-json-pgobject value))) (extend-protocol jdbc/IResultSetReadColumn PGobject (result-set-read-column [pgobj metadata idx] (let [type (.getType pgobj) value (.getValue pgobj)] (case type "json" (json/parse-string value true) :else value))))
Where I have chosen to use Cheshire, as opposed to clojure.data.json. It's a matter of taste, I'll agree, but we're using Cheshire all the time anyway, so the cost is nothing, and it's consistently parsed in the same way.
Very cool.