Fork me on GitHub
#sql
<
2021-07-04
>
Nazral15:07:52

Hi, is there a way to parametrize a request with keywords rather than ? ? Because right now I am building a rather complicated request, and it would be much simpler if I could do WHERE foo = :foo + {:foo "bar"} as parameter

curtis.summers18:07:33

HugSQL provides this functionality: https://hugsql.org

seancorfield15:07:58

Use HoneySQL to build the query. It supports named parameters.

👍 3
Nazral15:07:06

ok thank you!

emccue15:07:55

@archibald.pontier_clo I have a helper fn for that if you want it

emccue15:07:09

(honeysql is great tho)

emccue15:07:51

;; ------------------------------------------------------------------------
(defn expand-named-parameters
  "Given a sql query like \"SELECT name FROM TABLE WHERE id = :id\"
  and a map of the form {:id ..., :other ...}, this will return
  a sqlvec representation of that query with the named parameters in
  the correct position"
  [sql-str params]
  (let [^Pattern replace-pattern  #":([a-zA-Z]|\-|[0-9])+"
        params-in-order (map first (re-seq replace-pattern sql-str))
        with-placeholders (string/replace sql-str replace-pattern "?")
        extract-param-value (fn [param]
                              (->> (keyword (.substring param 1 (count param)))
                                   (get params)))]
    (vec (cons with-placeholders
               (map extract-param-value params-in-order)))))

;; ------------------------------------------------------------------------
(defmacro expand-named-parameters-compile-time
  "Does the same work as [[expand-named-parameters]], but requires a
  string literal and a map literal in exchange for doing the parsing
  work at compile time."
  [sql-str params]
  (let [param-syms (map (fn [[name value]]
                          {:name name
                           :value value
                           :symbol (gensym)})
                        (seq params))
        param-map (into {} (map (juxt :name :symbol) param-syms))
        let-clause `(let ~(vec (mapcat (juxt :symbol :value) param-syms))
                      ~(expand-named-parameters sql-str param-map))]
    let-clause))

emccue15:07:47

here thee go

emccue15:07:33

If i were to package that into a library I would probably make the macro version be the less verbose one

emccue15:07:52

and do some assertions to make sure you aren't mixing named and unnamed params

emccue15:07:15

and give some better errors on bad input

Nazral16:07:44

@emccue thank you, I'll check that out after I fix the massive security issue in my sql requests 😅

emccue16:07:47

@archibald.pontier_clo lmk if you would want it as a library - I could stand to learn how to do that finally

Nazral16:07:23

You should do it regardless then! But I like the function, I think it could be useful yes