Fork me on GitHub
#honeysql
<
2021-10-03
>
alpox15:10:14

Hi everyone! Short question: Is it possible to make insert-into set DEFAULT instead of NULL on missing keys? Usecase: I want to use the on-conflict and do-update-set of postgres with uuid primary keys. If I now had a list of mixed fields where some already have an id and some not, honeysql inserts a NULL for the primary key where its missing and will therefore not be able to insert the entry due to a missing primary key.

alpox16:10:53

I believe I can get around it by assigning uuids generated by java.util.UUID/randomUUID for entries that don't hold an id already but I'd think it would be cleaner to let the database handle that

seancorfield17:10:36

@alpox not sure I understand your question : honeysql just generates sql, it doesn't interact with the database at all - it doesn't actually insert anything.

seancorfield17:10:27

Honeysql knows nothing about databases, nothing about columns or defaults.

alpox17:10:00

@seancorfield Yes, my issue was with the sql generated by honeysql. If I take the data:

(def fields [{:id #uuid "e352239e-5ccc-4de4-8fcb-d9745a4c7739"
                :name "foo"
                :position 1
                :type "foobar"
                :table-field-name "foo"
                :entity-id (:entity/id new-entity)}

               {:name "bar"
                :position 2
                :type "bar"
                :table-field-name "bar"
                :entity-id (:entity/id new-entity)}])
Where the first field has an id (already exists in the database) and the second one has no id (yet to be created) I give those fields to honeysql through:
(-> (hh/insert-into :field)
                     (hh/values fields) ; fields here
                     (hh/on-conflict :id)
                     (hh/do-update-set
                      :position
                      :type
                      :name
                      :table_field_name)
                     (hh/returning :id)
                     (hsql/format))
The generated insert statement creates INSERT INTO field (id, name, position, type, table_field_name, entity_id) VALUES (?, ?, ?, ?, ?, ?), (NULL, ?, ?, ?, ?, ?) Where the NULL leads to an error due to a missing value for the primary key. If it was instead DEFAULT the id would instead be created by the database. This does not happen if I have only new fields (fields without ids) because the id field is omitted and the default will be used

dharrigan17:10:18

A very simple, quick way of moving on from this, is simply to do a string replacement (as what comes out of (hsql/format) is a string

dharrigan17:10:33

(def sql "INSERT INTO field (id, name, position, type, table_field_name, entity_id) VALUES (?, ?, ?, ?, ?, ?), (NULL, ?, ?, ?, ?, ?)")

dharrigan17:10:46

(clojure.string/replace sql #"\(NULL" "(DEFAULT")

dharrigan17:10:51

"INSERT INTO field (id, name, position, type, table_field_name, entity_id) VALUES (?, ?, ?, ?, ?, ?), (DEFAULT, ?, ?, ?, ?, ?)"

alpox17:10:19

Hmm yes thats a possible approach. But im not sure if this could not lead to followup errors in some cases where NULL is wanted still (e.g. explicitely specified nil)

alpox17:10:42

I guess I have to apply it with caution

dharrigan17:10:44

one thing at a time 🙂 Tackle that bridge if you come to it.

👍 1
alpox17:10:11

Thanks for the feedback :) ill go down that road for now as I dont think Ill run into trouble with this

seancorfield20:10:48

In a lot of situations, you really do want NULL inserted in a column so the choice to use DEFAULT instead is extremely context-sensitive. I'm not sure what HoneySQL could actually do here to help.

seancorfield20:10:22

You might even have a situation where you want NULL inserted for some nil columns (or missing columns), and DEFAULT for other columns in the same INSERT statement so how would HoneySQL know? There would have to be some way for you to tell it which columns should map to DEFAULT is nil/missing. That might be the best approach, on a per-call (to format) basis, as some sort of option with a set of column names?

alpox20:10:19

> That might be the best approach, on a per-call (to `format`) basis, as some sort of option with a set of column names? Maybe something like:

(hsql/format sql {:insert-key-fallback {:id [:raw "DEFAULT"]}})
then? I would have liked to put it together with :values but there is no space for options at that point (which is probably for the best).

seancorfield22:10:30

I'll have to give it sort thought, in terms of naming, and in terms of whether it should consider missing columns differently than nil values (in general HoneySQL doesn't -- and idiomatically Clojure generally doesn't). Not sure whether it needs to be a hash map from column to expression or just a set of columns that should turn into [:default] -- you don't need :raw here.

seancorfield22:10:57

@alpox ☝️:skin-tone-2: (you can't swap the value in your map for :id, just pointing out the syntax)