Skip to content

Instantly share code, notes, and snippets.

@Ramblurr
Last active August 24, 2020 12:10
Show Gist options
  • Save Ramblurr/d131024b3142270aa579a0af2674024b to your computer and use it in GitHub Desktop.
Save Ramblurr/d131024b3142270aa579a0af2674024b to your computer and use it in GitHub Desktop.
(:requrie [next.jdbc.result-set :as rs]
[next.jdbc.prepare :as prepare]
[gungnir.model :as model]
[jsonista.core :as json]
[honeysql.format :as hf])
;; given a table
;; CREATE TABLE example ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, json_value JSONB );
;; and the corresponding model
(model/register!
{:example
[:map
[:example/id {:primary-key true :auto true} int?]
[:example/json-value {:before-save [:raw-value]} map?]]})
;; then
;; elsewhere, where you define your gungnir datasource
;; we wrap the value with honeysql.format/value which tells honeysql
;; NOT to intepret the map as a nested subqery
(defmethod model/before-save :raw-value [_k v]
(hf/value v))
;; the rest id from next.jdbc docs
;; https://cljdoc.org/d/seancorfield/next.jdbc/1.1.582/doc/getting-started/tips-tricks#working-with-json-and-jsonb
;;
;; :decode-key-fn here specifies that JSON-keys will become keywords:
(def mapper (json/object-mapper {:decode-key-fn keyword}))
(def ->json json/write-value-as-string)
(def <-json #(json/read-value % mapper))
(defn ->pgobject
"Transforms Clojure data to a PGobject that contains the data as
JSON. PGObject type defaults to `jsonb` but can be changed via
metadata key `:pgtype`"
[x]
(let [pgtype (or (:pgtype (meta x)) "jsonb")]
(doto (PGobject.)
(.setType pgtype)
(.setValue (->json x)))))
(defn <-pgobject
"Transform PGobject containing `json` or `jsonb` value to Clojure
data."
[^org.postgresql.util.PGobject v]
(let [type (.getType v)
value (.getValue v)]
(if (#{"jsonb" "json"} type)
(with-meta (<-json value) {:pgtype type})
value)))
;; if a SQL parameter is a Clojure hash map or vector, it'll be transformed
;; to a PGobject for JSON/JSONB:
(extend-protocol prepare/SettableParameter
clojure.lang.IPersistentMap
(set-parameter [m ^PreparedStatement s i]
(.setObject s i (->pgobject m)))
clojure.lang.IPersistentVector
(set-parameter [v ^PreparedStatement s i]
(.setObject s i (->pgobject v))))
;; if a row contains a PGobject then we'll convert them to Clojure data
;; while reading (if column is either "json" or "jsonb" type):
(extend-protocol rs/ReadableColumn
Array
(read-column-by-label [^Array v _] (vec (.getArray v)))
(read-column-by-index [^Array v _ _] (vec (.getArray v)))
org.postgresql.util.PGobject
(read-column-by-label [^org.postgresql.util.PGobject v _]
(<-pgobject v))
(read-column-by-index [^org.postgresql.util.PGobject v _2 _3]
(<-pgobject v)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment