Example how to handle postgresql arrays in result set (next.jdbc)

core.clj

(ns app.database.core
  (:require
    [app.database.sql-array :as sql-array]))

(set! *warn-on-reflection* true)


; SQL Helper functions

(defn text-array
  "Wrap clojure collection as TEXT[] field before writing it in database."
  [coll]
  (when coll
    (sql-array/->SqlArrayParam "TEXT" coll)))

(defn bigint-array
  "Wrap clojure collection as BIGINT[] field before writing it in database."
  [coll]
  (when coll
    (sql-array/->SqlArrayParam "BIGINT" coll)))

sql_array.clj

(ns app.database.sql-array
  (:require
    [next.jdbc.prepare :as p]
    [next.jdbc.result-set :as rs])
  (:import
    (clojure.lang
      Associative Counted ILookup Indexed
      IPersistentCollection IPersistentStack IPersistentVector
      Reversible Seqable)
    (java.sql
      Array PreparedStatement)))

(set! *warn-on-reflection* true)


; Settable parameter

(deftype ^{:doc "Wrapper around clojure collection to use as SQL query parameter."}
  SqlArrayParam [type coll]
  Object
  (toString [_] (str "<" type " " coll ">")))

(extend-protocol p/SettableParameter
  SqlArrayParam
  (set-parameter [^SqlArrayParam v, ^PreparedStatement ps, ^long i]
    (.setObject ps i (.createArrayOf (.getConnection ps) (.-type v) (to-array (.-coll v))))))


; Readable column

(defn as-deferred-vector
  "Wrap SQL array with vector-like interface where array materializes on demand."
  [^Array a]
  (let [v (delay (vec (.getArray a)))]
    (reify
      Object
      (toString [_] (str a))
      IPersistentVector
      (length [_] (.length ^IPersistentVector @v))
      (assocN [_ i val] (.assocN ^IPersistentVector @v i val))
      (cons [_ o] (.cons ^IPersistentVector @v o))
      Indexed
      (nth [_ i] (.nth ^Indexed @v i))
      (nth [_ i o] (.nth ^Indexed @v i o))
      Counted
      (count [_] (.count ^Counted @v))
      Associative
      (containsKey [_ key] (.containsKey ^Associative @v key))
      (entryAt [_ key] (.entryAt ^Associative @v key))
      (assoc [_ key val] (.assoc ^Associative @v key val))
      IPersistentCollection
      (empty [_] (.empty ^IPersistentCollection @v))
      (equiv [_ o] (.equiv ^IPersistentCollection @v o))
      IPersistentStack
      (peek [_] (.peek ^IPersistentStack @v))
      (pop [_] (.pop ^IPersistentStack @v))
      ILookup
      (valAt [_ key] (.valAt ^ILookup @v key))
      (valAt [_ key notFound] (.valAt ^ILookup @v key notFound))
      Seqable
      (seq [_] (.seq ^Seqable @v))
      Reversible
      (rseq [_] (.rseq ^Reversible @v))
      Iterable
      (iterator [_] (.iterator ^Iterable @v))
      (forEach [_ action] (.forEach ^Iterable @v action))
      (spliterator [_] (.spliterator ^Iterable @v)))))

(comment
  (apply sorted-set
         (mapv str (supers (class (vec (into-array [1])))))))

(extend-protocol rs/ReadableColumn
  Array
  (read-column-by-label [^Array v _] (as-deferred-vector v))
  (read-column-by-index [^Array v _ _] (as-deferred-vector v)))