Clojurians Log v2

Has anyone used honeysql for multiple queries? I'm quite performance sensitive in my inserts, but I've got to issue multiple and I'm not interested in the return values at all. I haven't tried it yet, but I was thinking that sending all 4 inserts in a single execution would reduce round-trip times (ideally pg would parallelize for me, but I dunno how that would work).

Interestingly, this almost works, but lacks a semi-colon:

(sql/format
    [{:insert-into :foo :values [{:a 1}]} {:insert-into :bar :values [{:b 1}]}])

What about using a CTE?

I don't know what that abbreviation is short for I'm afraid!

Common Table Expression https://www.geeksforgeeks.org/cte-in-sql/

Feel free to swing by the #honeysql channel and discussion your needs. I'm taking on board a lot of input right now for HoneySQL 2.0.

Hello. I posted the following message on Reddit and @seancorfield suggested that I seek help here I connected to a database I have using https://cljdoc.org/d/seancorfield/next.jdbc/1.1.582. From the awesome docs for that library, I copied and pasted the code for working with jsonb/json datatypes in Postgres. The code can be found at this link: https://cljdoc.org/d/seancorfield/next.jdbc/1.1.582/doc/getting-started/tips-tricks#working-with-json-and-jsonb This works well when the jsonb column has data or is postgres null. However, I get `Execution error (NullPointerException)` when the data is 'null' in the jsonb column (note 'null' rather than missing in the postgres db) . How can I modify the above code to deal with 'null' strings? I have figured that the exception occurs in the code shown below: (I have added the try catch by myself to catch the exception). Essentially, it appears that (with-meta (<-json value) {:pgtype type}) is unable to deal with when a postgres jsonb column if it contains a json null or string.

(defn <-pgobject
  "Transform PGobject containing `json` or `jsonb` value to Clojure
  data."
  [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (try ;; I added the try block to deal with strings and null value for now.
        (with-meta (<-json value) {:pgtype type})
        (catch Exception e (str "caught exception: " (.getMessage e))))
      value)))

Which is line 44 in your code @curiouslearn?

I would assume it's (<-json value) so it sounds like the <-json function that someone contributed to the docs doesn't allow for value to be nil?

And, indeed, it's just calling jsonista's function on it so, yeah, that will not accept nil

So you'd want this instead:

(defn <-pgobject
  "Transform PGobject containing `json` or `jsonb` value to Clojure
  data."
  [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (when value
        (with-meta (<-json value) {:pgtype type}))
      value)))

I suspect that whoever contributed that part of the docs makes sure they don't put JSON null in their DB, they put SQL null instead... Let me see who that was so I can ask them here...

That was @valtteri who is not around at the moment I suspect but can no doubt comment on this when they are...

I can update the example in the docs to handle JSON null but I'd rather wait for their input first...

Yeah, in fact, looking at the ->pgobject code, that would never get invoked for nil being stored in the DB so the expectation is that you would never get a JSON null in the column: you'd either get a SQL null (which the code handles, as you observed) or you'd get a (non-null) JSON expression.

And BTW @curiouslearn "Welcome to Slack!" Glad you figured out the invite/sign-up process!

Thank you @seancorfield. Appreciate your help with the issue and the link for getting on slack. As you said, I hope @valtteri responds. Since Postgres jsonb column allows for JSON null and for JSON strings I suppose it would make sense to allow for those possibilities. What do you think? I am super new to clojure, and don't know Java, so I don't know where to start to make that change. Just to clarify: with JSON string, I get a different exception (not the NULLPointerException). I don't remember exactly, but it is an exception because of JSON string.

Much depends on where your JSON input data is coming from. The docs that Valtteri provided assume you have Clojure data and you control the process of inserting it into the database. If that's the case, you'd never convert nil to JSON null, you'd simply insert nil -- which would produce a column with a SQL NULL value, and that will read back correctly using the example code because it would produce nil and not try to decode a PGobject and therefore not try to convert it from JSON (because it wouldn't be JSON).

If the database is being populated by someone else, and they're just blindly storing JSON values (or even blindly converting incoming nil/`null` values to JSON null), then yes you need the when clause I added to the example code so that you get back nil instead of blowing up trying to convert null from JSON.

If in doubt, add that when value guard 🙂

Sorry @dominicm, should have written it out! Now that I'm at a laptop again, using a common table expression your code could look something like:

(sql/format
  {:with [:foo {:insert-into :foo :values [{:a 1}]}
          :bar {:insert-into :bar :values [{:b 1}]}]
   :select [1]})