This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-01-28
Channels
- # asami (5)
- # babashka (44)
- # beginners (22)
- # biff (7)
- # clerk (86)
- # clj-kondo (5)
- # clojure (33)
- # clojure-europe (8)
- # clr (6)
- # community-development (2)
- # fulcro (20)
- # graalvm (5)
- # graphql (1)
- # hugsql (3)
- # integrant (5)
- # java (11)
- # joyride (2)
- # leiningen (4)
- # malli (12)
- # nbb (15)
- # off-topic (28)
- # pathom (23)
- # reitit (8)
- # releases (1)
- # sci (6)
- # shadow-cljs (39)
- # tools-deps (15)
- # tree-sitter (1)
I was interested in making a generic upsert function in hugsql. It felt a little gross, but I wound up with this:
-- :name upserting! :insert :returning-execute
-- :doc Upserts multiple records, handling updates on conflict
-- :require [clojure.string :as str]
insert into :identifier:table (:identifier*:cols) values :tuple*:vals
/*~
(let [update-cols (:update-cols params)
multi-update? (> (count update-cols) 1)
format-excluded (fn [cols] (->> cols
(map (fn [s] (str "excluded." s)))
(str/join ", ")))
on-conflict " on conflict (:identifier*:conflict-cols) do "]
(case (:action params)
:nothing (str on-conflict "nothing")
:update (str on-conflict
"update set "
(if multi-update?
(str "(:identifier*:update-cols)" " = " "(" (format-excluded update-cols) ")")
(str ":identifier*:update-cols" " = " (format-excluded update-cols))))
""))
~*/
If anyone is searching in the future: ON CONFLICT DO UPDATE SET
Usage looks like this:
(upserting! @db {:table "bogus"
:cols ["id" "foo" "bar"]
:vals [[1 2 99]
[2 3 42]
[4 4 1000]]
:conflict-cols ["id"]
:update-cols ["foo" "bar"]
:action :update})
where :action
can be :nothing
or :update
.
If anyone has any suggestions on cleaning this up, I'd be interested to hear them.Also, is there a way I'm missing that will convert keys to strings so for instance, :table is :bogus
in the calling fn?
Another question I had while tinkering: What if I want to tell a user that they haven't supplied the required parameters? For instance in the above, :action is required. I tried asserting above the insert
piece, but it gives me an execution error and says SQL is empty
. I suppose that's better than nothing, but it would be nice if I could communicate a better error message.