Handling nil parameters with Clojure/Hugsql

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?

Solution

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.