2025-01-09 honeysql | Clojure Slack Archive

honeysql

2025-01-09T19:24:05.104519Z

Hey team, is there a way I can include a sql comment with honeysql? Context: I generate a pretty involved list of CTEs, from simpler input arguments. Here's a simplified example:

(defn sql-pretty [sql-str]
  (-> (SqlFormatter/of "PostgreSql")
      (.format sql-str)))

(defn hsql-pretty [x]
  (-> x
      hsql/format
      first
      sql-pretty
      println))

(defn make-query [inputs]
    {:with (map
            (fn [input] [input {:select :* :from :input}])
            inputs)
     :select :* :from (last inputs)})

(hsql-pretty
   (make-query [:foo :bar]))

; (out) WITH foo AS (
; (out)   SELECT
; (out)     *
; (out)   FROM
; (out)     input
; (out) ),
; (out) bar AS (
; (out)   SELECT
; (out)     *
; (out)   FROM
; (out)     input
; (out) )
; (out) SELECT
; (out)   *
; (out) FROM
; (out)   bar

For each WITH clause, I would love to include a pr-str of the input argument which caused it. For example:

WITH /* :foo */ foo AS ( 

) ...

Is there some way I can insert the comment /* :foo */, when writing the query? I naively tried something like this:

(comment
  (defn make-query [inputs]
    {:with (map
            (fn [input] [[:raw (str "/* " (pr-str input) " */ " (name input))]
                         {:select :* :from :input}])
            inputs)
     :select :* :from (last inputs)})
  (hsql-pretty
   (make-query [:foo :bar])))

But got:

#error {
 :cause "class java.lang.Character cannot be cast to class java.util.Map$Entry (java.lang.Character and java.util.Map$Entry are in module java.base of loader 'bootstrap')"
 :via
 [{:type java.lang.ClassCastException
   :message "class java.lang.Character cannot be cast to class java.util.Map$Entry (java.lang.Character and java.util.Map$Entry are in module java.base of loader 'bootstrap')"
   :at [clojure.lang.APersistentMap$KeySeq first "APersistentMap.java" 171]}]

p-himik 2025-01-09T20:06:47.570229Z

For my own information - what's the purpose of those comments?

2025-01-09T20:20:36.909469Z

I sometimes need to debug slow queries, which can get really large (~500 LOC). The actual inputs are much smaller, so as I go through the CTEs, I would love to see the inputs that generated them

p-himik 2025-01-09T20:22:18.252899Z

But /* :foo */ doesn't let you see the inputs. The full HoneySQL map does. So you'd have to manually map /* :foo */ in SQL to :foo in Clojure. And at that point, why is it better than mapping foo to :foo?

2025-01-09T20:25:09.091499Z

The example is a bit contrived, but in reality looks something like this: input: [?user :groups "group-id"] CTE:

WITH match_0_1 AS ( 
  SELECT
    entity_id AS match_0_0_entity_id,
    attr_id AS match_0_0_attr_id,
    value AS match_0_0_value_blob,
    CASE
      WHEN eav THEN CAST(value ->> 0 AS UUID)
      ELSE null
    END AS match_0_0_value_uuid,
    created_at AS match_0_0_created_at
  FROM
    triples
  WHERE
    (
      app_id = 'a749930e-6737-4dcf-b039-60c7f5e4e2e6' :: uuid
    )
    AND (vae = true)
    AND (
      attr_id = 'a15d4213-a976-493a-b88b-b88bd6f2f324' :: uuid
    )
    AND (
      (
        value = CAST('"15d0dbcf-e80d-4706-a179-5649035380b4"' AS JSONB)
      )
    )
)

I would love it to look like:

-- [?user :groups "group-id"]
WITH match_0_1 AS ( 
  SELECT
    entity_id AS match_0_0_entity_id,
    attr_id AS match_0_0_attr_id,
    value AS match_0_0_value_blob,
    CASE
      WHEN eav THEN CAST(value ->> 0 AS UUID)
      ELSE null
    END AS match_0_0_value_uuid,
    created_at AS match_0_0_created_at
  FROM
    triples
  WHERE
    (
      app_id = 'a7491230e-6727-4dcf-b039-60c7f5e4e2e6' :: uuid
    )
    AND (vae = true)
    AND (
      attr_id = 'a15234213-a176-493a-b88b-b88bd6f2f324' :: uuid
    )
    AND (
      (
        value = CAST('"15d0dbcf-e80d-6706-a179-5649035380b4"' AS JSONB)
      )
    )
)

p-himik 2025-01-09T20:26:25.048399Z

Ah, I see.

p-himik 2025-01-09T20:31:01.268139Z

Not sure if it's something that could be described as "officially supported", but it somewhat works:

(let [inputs [:foo :bar]]
  (sql/format
    {:with   (map
               (fn [input] [[input {:raw (str "/* " input " */")}]
                            {:select :* :from :input}])
               inputs)
     :select :* :from (last inputs)}))

2025-01-09T21:50:27.443129Z

Oh, this is great! Thanks @p-himik!

👍 1

igrishaev 2025-01-10T07:09:06.144279Z

tl;dr: use :raw expression with earmuffs, that's the simplest approach. You can extend honeysql with a :comment clause that produces /*...*/ when rendering.

igrishaev 2025-01-10T07:10:31.944269Z

@p-himik in rare cases, comments might help. various ORM systems put additional data into comments for investigation, for example the name of controller, request-id, host-id, etc.

p-himik 2025-01-10T07:15:16.816429Z

I log SQL queries separately from the RDBMS, so I put those there. But I get that the comments could be useful. It's just logging :foo when foo is already there that confused me.

Clojurians Log v2

honeysql