Handling nil parameters with Clojure/Hugsql

Question

I'm using Hugsql with Clojure to access a Postgresql db. Several of my database tables have optional columns - for a simple example consider a "users" table with various address columns - address1, address2, city, etc.

When I write the Hugsql query specification for an "update" I don't know which values will be present in the map I pass in. So if I write a query:

-- :name update-user! :! :n
UPDATE users set firstname = :firstname, address1 = :address1 where id = :id

but pass in a user map

(update-user! {:id "testuser" :firstname "Bartholamew"})

then an exception is thrown. I'd expect it to create something like

UPDATE users SET firstname='Bartholamew', address1=NULL where id='testuser'

I've looked at the Hugsql source - it calls a (validate-parameters) function that throws the exception that I can't see a way around. I'm sure I'm missing something obvious: this doesn't seem like an unusual requirement, and I sure don't want to write a distinct SQL query spec for every possible combination of optional columns.

Is there a way to handle missing parameters that I'm missing? Am I abusing the database by having optional columns?

If all things fail you could supply defaults yourself like `(update-user! (merge user-defaults {:id "testuser" :firstname "Bartholamew"}))`. — Erik Dannenberg, Nov 04 '16 at 12:06
I'm doing what Erik suggested, but it still feels clumsy. It'd be nice to be able to just specify some parameters as optional and have them map to NULL if missing (or even an optional default value). — Christopher Davies, Nov 03 '17 at 21:21

score 12 · Accepted Answer · answered Nov 04 '16 at 18:20

You can use HugSQL's Clojure Expressions to support conditionally including your desired SQL based on the parameters provided at runtime. So, you can write something like this:

-- :name update-user! :! :n
UPDATE users SET
id = id
--~ (when (contains? params :firstname) ",firstname = :firstname")
--~ (when (contains? params :address1) ",address1 = :address1")
WHERE id = :id

Note: The id=id is a bit silly here to deal with the commas. You can certainly do something more robust and generic with this example from the HugSQL docs:

SQL:

-- :name clj-expr-generic-update :! :n
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
update :i:table set
/*~
(string/join ","
  (for [[field _] (:updates params)]
    (str (identifier-param-quote (name field) options)
      " = :v:updates." (name field))))
~*/
where id = :id

Clojure:

(clj-expr-generic-update db {:table "test"
                             :updates {:name "X"}
                             :id 3})

I'd also encourage you to look at and know what's available in the underlying jdbc library. HugSQL's default adapter is clojure.java.jdbc, and its update! function has similar functionality.

Handling nil parameters with Clojure/Hugsql

1 Answers1