Upsert semantics via INSERT ... ON DUPLICATE KEY UPDATE Syntax

[rboyd]

Handling upsert logic in the application can introduce tricky race conditions. MySQL offers upsert semantics as detailed in INSERT ... ON DUPLICATE KEY UPDATE Syntax

What will korma's philosophy be on how to handle dbms-specific implementations? Is there room for introducing per-dbms aux libs?

I attempted to construct an upsert query using existing korma insert/update/raw fns, but was unable to come up with anything workable.

[jmglov]

I did this from outside Korma, then Robert Hooked it in:

(ns foo
  (:require [korma.sql.engine :as korma-engine]
            [korma.core :refer :all]
            [robert.hooke :refer [add-hook]]))

(defn upsert
  [f & [q :as args]]
  (let [result (apply f args)]
    (if-let [[k v] (:upsert q)]
      (assoc result :sql-str (format "%s ON DUPLICATE KEY UPDATE %s = %s"
                                     (:sql-str result) (name k) v))
      result)))

(add-hook #'korma-engine/sql-insert #'upsert)

(defentity bar)

(defn upsert-bar! [id timestamp]
  (-> (insert* bar)
      (values [{:id id, :timestamp timestamp}])
      (assoc :upsert [:timestamp timestamp])
      (insert)))

Perhaps something like this could be folded into Korma with an upsert macro so you could say:

(upsert bar [:timestamp timestamp]
 (values [{:id id, :timestamp timestamp}]))

[afandian]

Bump. It would be great if this was supported properly, with the correct handling of types (e.g. date).

[immoh]

As far as I know, this is supported by MySQL only. Currently there are now plans to start supporting features specific to single RDBMS.

[ku1ik]

Not: PostgreSQL supports UPSERT since 9.5.

[immoh]

@sickill: But with different syntax compared to MySQL.