Faster PostgreSQL upsert in HoneySQL and funcool/clojure.jdbc

helpers.clj

(ns app.jdbc.helpers
  (:require [jdbc.core :as jdbc]
            [jdbc.proto :as jdbc-proto]
            [honeysql.core :as sql]

    ; Load extensions
            [honeysql-postgres.format]))

(defn -upsert-sql
  [table keys pk]
  (first (sql/format {:insert-into    table
                      :columns        keys
                      :values         [(repeat (count keys) (sql/param nil))]
                      :on-conflict    [pk]
                      :do-update-set! (into {}
                                            (for [k keys
                                                  :when (not= k pk)]
                                              [k (sql/qualify "EXCLUDED" k)]))})))

(defn -upsert-stmt
  [conn table keys pk]
  (jdbc/prepared-statement conn (-upsert-sql table keys pk)))

(defn upsert-q
  "Returns prepared statement."
  [conn table pk data]
  (let [stmt (-upsert-stmt conn table (keys data) pk)]
    (->> (vals data)
         (map-indexed #(jdbc-proto/set-stmt-parameter! %2 conn stmt (inc %1)))
         (dorun))
    stmt))