Handling nil parameters with Clojure / Hugsql

Question

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

When I write the Hugsql query specification for “update”, I don’t know what values ​​will be present on the card that I am transmitting. Therefore, if I write a request:

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

but pass the user card

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

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

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

I looked at the source of Hugsql - it calls a function (validate-parameters) that throws an exception that I cannot see. I am sure that I am missing something obvious: this does not seem like an unusual requirement, and I do not want to write a separate SQL query for each possible combination of additional columns.

Is there a way to deal with missing parameters that I am missing? Am I abusing a database by having extra columns?

Answer 1

You can use HugSQL Clojure Expressions to conditionally enable the desired SQL based on the parameters provided at run time. 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. id=id is a little dumb here to deal with commas. In this example, you can do something more robust and generalized from 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 also recommend that you look and find out what is available in the jdbc core library. The default HugSQL adapter is clojure.java.jdbc, and its function is update! has similar features.