Clojurians Log v2

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

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

ok thank you!

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

(honeysql is great tho)

;; ------------------------------------------------------------------------
(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))

here thee go

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

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

and give some better errors on bad input

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

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

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

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