H2 database upsert implementation

h2-honeysql-upsert.clj

(ns com.myproject.model
  (:require [honey.sql :as sql]
            [clojure.string :as s]))

;; honeysql clause extension

;; Updates existing rows, and insert rows that don't exist. 
;; If no key column is specified, the primary key columns are used to find the row. 
;; If more than one row per new row is affected, an exception is thrown.

(sql/register-clause! :vals
                      (fn [c [pk xs]]
                        (let [cols-1 (keys (first xs))
                              cols-n (into #{} (mapcat keys) xs)
                              cols (if (= (set cols-1) cols-n) cols-1 cols-n)
                              [sqls params]
                              (reduce (fn [[sql params] [sqls' params']]
                                        [(conj sql (str "(" (s/join ", " sqls') ")"))
                                         (if params' (into params params') params')])
                                      [[] []]
                                      (map (fn [m]
                                             (sql/format-expr-list
                                               (map #(get m %) cols)))
                                           xs))]
                          (into [(str "("
                                      (s/join ", "
                                              (map #(sql/format-entity % {:drop-ns true}) cols))
                                      ") "
                                      (when pk (str (sql/sql-kw :key) "("
                                                    (if (sequential? pk)
                                                      (s/join "," (map #(sql/format-entity % {:drop-ns true}) pk))
                                                      (sql/format-entity pk {:drop-ns true}))
                                                    ") "))
                                      (sql/sql-kw :values)
                                      " "
                                      (s/join ", " sqls))]
                                params)))
                      nil)

(sql/register-clause! :merge-into
                      (fn [clause x]
                        (let [[sql & params]
                              (if (ident? x)
                                (sql/format-expr x)
                                (sql/format-dsl x))]
                          (into [(str (sql/sql-kw clause) " " sql)] params)))
                      :vals)                                ; <!> MERGE INTO ... ... KEY(_pk) VALUES ...


;; impl example

(defn- upsert [data table & [pk]]
  (-> {:merge-into table
       :vals       [pk data]}
      sql/format))

;; usage examples

(upsert [{:aa 11 :bb 22}] :table)
;; ["MERGE INTO table (aa, bb) VALUES (?, ?)" 11 22]
(upsert [{:aa 11 :bb 22}] :table :aa)
;; ["MERGE INTO table (aa, bb) KEY(aa) VALUES (?, ?)" 11 22]
(upsert [{:aa 11 :bb 22}] :table [:p :s])
;; ["MERGE INTO table (aa, bb) KEY(p,s) VALUES (?, ?)" 11 22]