April 06, 2019

Sweet Honeysql and Postgres

Table of Contents

I thought I’d share a snippet of what I love about my HoneySQL + Postgres setup (this is plain Honey without the Postgres extras, which never seem to cover my needs). I don’t take any pride in the actual database model, which is in-use and was not well designed. Nonetheless, the two things that worked out nicely here are:

table applications has a one-to-many relationship with validations, so PG’s “distinct on” was the perfect tool for just matching the most recent of an application’s validations. Honey wasn’t hard to add this PG-specific syntax
I need to match my ip.id to a value in a nested json field of another table, so I needed to navigate the nesting using PG’s -> and -» syntax and then cast the final result into an int for comparison with ip.id
The json value might not actually exist for some of the submissions I need to return, so I need a left-join
cond-> has changed my life with generating SQL queries, allowing me to leverage the db’s optimization by crafting queries that tailor the produced SQL to the args I passed in (you can see it with my :where clause and also at the end when I return only a single result (not a collection of one) if only one is requested)

(defn pg-get-in-json
  "Select to a nested value from a json structure.

  `json-entry` is the postgres jsonb field,
  `internal-path` are the intermediary steps,
  `terminal-node` is the node containing the desired value"

  [json-entry internal-path terminal-node]
  (let [squot #(format "'%s'" %)
        pf (comp squot name)
        beginning-part [(name json-entry)]
        part-> (str/join "->"
                         (->> internal-path (map pf) (into beginning-part)))
        part-terminal (str "->>" (pf terminal-node))]
    (sql/raw (str part-> part-terminal))))

(defn application-list
  "Generate the internships application-list for the front-end display, including the matching validation"
  [& [{:keys [start-date end-date application-id mode yt]
       :or {mode "submission"}}]]
  (cond-> {:select [(sql/raw "DISTINCT ON (applications.id) applications.id")
                    :applications.*
                    :validations.qualifies
                    [:validations.id :validation-id]
                    [:validations.created :validation-date]
                    [:validations.info :validation-details]
                    [:ip.yeart :program-yeart]]
           :from [:applications]
           :join [:validations [:= :validations.application :applications.id]]
           :left-join [[:internship_programs :ip] [:= (keyword "ip.id::text") (pg-get-in-json :applications.application [:form] :internship_program)]]
           :order-by [[:applications.id :desc]
                      [:validations.created :desc]]
           :where (cond->
                      [:and
                       [:= :application_type "internship"]]
                    (and (= mode "submission") start-date) (conj [:>= :submission_date start-date])
                    (and (= mode "submission") end-date) (conj [:<= :submission_date end-date])
                    (= mode "program") (conj [:= :ip.yeart (Integer. yt)])
                    application-id (conj [:= :applications.id application-id]))}
    :always sql/format
    :always dbr
    application-id first))

Sweet Honeysql and Postgres

Related