Clojurians Log v2

hmm , i was reading over next-jdbc doc and somehow am failing to see a way how to construct queries with named placeholders

in essence to write SELECT * FROM cities WHERE name = :town_name: and providing an extra param { :town_name "chicago" } .... is such functionality not present at all or am i just bad at reading the docs .... there is the ? placeholder support, but that gets horrid to use when you reach tables with 10 or 12 columns

@kulminaator I have a macro for that

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

(defn messages-since
  "returns a max `n` messages since the given instant."
  [{:keys [db]} {:keys [since n group-id]
                 :or {since (Instant/ofEpochMilli 0)
                      n Integer/MAX_VALUE}}]
  (jdbc/execute!
    db
    (db/expand-named-parameters-compile-time
      "SELECT * FROM \"group\"
       WHERE message.created_at > :since
       ORDER BY message.created_at ASC
       JOIN message ON message.to_group_id = :group-id
       LIMIT :n"
      {:since since
       :n n
       :group-id group-id})))

this is from a side project so uhh, you can have it MIT license or whatever

so for your example it would be

(jdbc/execute! 
  db
  (db/expand-named-parameters-compile-time 
    "SELECT * FROM cities WHERE name = :town_name"
    {:town_name "chicago"}))

For comparison:

;; sql is honey.sql; select, from, where are from honey.sql.helpers:
(jdbc/execute! db (-> (select :*) (from :cities) (where [:= :name :?town_name]) (sql/format {:town_name "Chicago"}))

i dont mix sql with any language, not even clojure 🙂

but ok, having a macro or a wrapping function around it indeed is not difficult to write

We have a lot of very complex, conditionally composed queries in our reporting app -- a colleague gave a Clojure/West talk about it -- so HoneySQL is perfect for that 🙂 but I know some people lean more to things like HugSQL where you can keep your SQL and code completely separate (and which, I believe, supports named parameters).

i often need to grab queries out from the programming langauge and launch at the database, if i have to start reverse engineering my own code at that point or add full logging to capture the actual statements and then run the captures .. i will get impatient quickly 🙂 (usually the need for this arises when the servers are hogged or almost down due to some anomaly, no time to play around)

so whatever i use, has to stay native sql in writeup. placeholders are ok. mix of 2 languages is not my cup of tea.

@kulminaator A final comment on HoneySQL v2 then: in response to specific requests -- for exactly that scenario -- there's an option for sql/format that renders the SQL statement with all the parameters inlined into the string so that you can just copy/paste it into your favorite database tool.