Postgres Upsert in Clojure using clojure.java.jdbc and honeysql.

upsert.clj

(ns postgres.upsert
  (:require [honeysql.core :as sql]
            [clojure.java.jdbc :as jdbc]
            [clojure.string :as str]
            [clojure.set :as set]))
         
(defn- keyword->colname [kwd]
  (-> kwd
      name
      (str/replace #"-" "_")))

(defn- colname->keyword [colname]
  (-> colname
      (str/replace #"_" "-")
      keyword))

(defn- map-values
  "Apply a function on all values of a map and return the corresponding map (all
   keys untouched)"
  [f m]
  (when m
    (persistent! (reduce-kv (fn [out-m k v]
                              (assoc! out-m k (f v)))
                            (transient (empty m))
                            m))))

(defn upsert!
  ([db table uniq-cols row-maps]
   (upsert! db true table uniq-cols row-maps))
  ([db transaction? table uniq-cols row-maps]
   {:pre [(not-empty uniq-cols)]}
   (if (empty? row-maps)
     []
     (let [cols          (keys (first row-maps))
           non-uniq-cols (set/difference (set cols) (set uniq-cols))
           on-conflict   (str "ON CONFLICT (" (str/join ", " (map keyword->colname uniq-cols)) ")")
           qfied         (fn [table col] (->> col name (str (name table) ".") keyword))

           [on-conflict-update] (sql/format
                                  {:set   (->> non-uniq-cols
                                               (mapcat #(vector % (qfied :excluded %)))
                                               (apply hash-map))
                                   :where (list* :and
                                                 (map #(vector := (qfied table %) (qfied :excluded %))
                                                      uniq-cols))})]
       (doall (map (fn [row-map]
                     (let [value (->> row-map seq (sort-by #(->> % first (.indexOf cols))) (map second))
                           [query & params] (sql/format {:insert-into table
                                                         :columns     cols
                                                         :values      [value]})

                           query (if (empty? non-uniq-cols)
                                   (format "%s %s DO NOTHING" query on-conflict)
                                   (format "%s %s DO UPDATE %s" query on-conflict on-conflict-update))]
                       (if-let [res (jdbc/db-do-prepared-return-keys db transaction? query params)]
                         (map-keys #(-> % name colname->keyword) res)
                         (let [sel-query (->> {:select [:*] :from [table]
                                               :where  (list* :and
                                                         (map #(vector := (qfied table %) (get row-map %))
                                                              uniq-cols))}
                                              (sql/format))]
                            (jdbc/query db sel-query :identifiers colname->keyword)))))
                   row-maps))))))

Where is upsert defined? I was really excited I could drop this into where my insert-multi was and have it Just-Work tm.

@gleenn It looks like a typo. It seems like that arity is only there in order to default transaction? to true. If you correct the typo by adding the exclamation point, it looks like it will 'just work' for you.