next.jdbc query debugging with Portal

intro.md

Intro

Portal has a ton of useful formatters and because it leverages Hiccup, it's really easy to build on top of it. I wrote a tiny toolkit for debugging:

• queries generated by HoneySQL
• dealing with invalid query exceptions thrown by Postgres

For small queries it's easy to figure out where the error is, but with multiple CTEs, conditionally building parts of the query, things get messy.

Helper code

(ns db
  (:require
   [clojure.string :as str]
   [honey.sql :as sql]
   honey.sql.pg-ops
   [next.jdbc :as jdbc])
  (:import
   (com.github.vertical_blank.sqlformatter SqlFormatter) ;; https://github.com/vertical-blank/sql-formatter
   (org.postgresql.util
    PSQLException)))

(defn debug-query->portal
  [sql-vec]
  (tap> (with-meta
          [:div
           [:h1 "Query"]
           [:portal.viewer/code (SqlFormatter/format ^String (-> sql-vec first))]
           (when-let [params (->> sql-vec
                                  rest
                                  seq
                                  (map-indexed (fn [i p] {:i i :p p})))]
             [:div
              [:h2 "Params"]
              [:ul
               (for [{:keys [i p]} params]
                 [:li [:strong (str "$" i)] " " p])]])]
          {:portal.viewer/default :portal.viewer/hiccup}))
  sql-vec)

(defn- pg-exception->portal [e sql-vec]
  (let [msg (.getMessage e)
        position? (->> msg
                       (str/split-lines)
                       (filter #(re-find #"Position:" %))
                       (first))
        position (when position?
                   (-> position?
                       (str/split #": ")
                       (last)
                       parse-long))]
    (tap>
     (with-meta [:div
                 [:h1 "Query exception"]
                 [:div
                  [:h2 "Error"]
                  msg]
                 [:h3 position]
                 (when position
                   [:div
                    [:code
                     (subs (first sql-vec) 0 (dec position))
                     [:strong {:style {:color "red"}} "HERE ->"]
                     [:code
                      (subs (first sql-vec) (dec (dec position)))]]])]
       {:portal.viewer/default :portal.viewer/hiccup}))))

(defn execute!-with-result-debug [conn query]
  (try
    (jdbc/execute! conn query
                   jdbc/unqualified-snake-kebab-opts)
    (catch Exception e
      (pg-exception->portal e query)
      (throw e))))

Usage

(->> {:select [[:json_agg :payload] :id :created-at :failed-at]
      :from [:background_jobs]
      :where [:= :id 1]}
     (honey.sql/format)
     (db/debug-query->portal)
     (db/execute!-with-result-debug pg-conn))

How does it look like?