@deleted-user Can you share your code that reproduces this?

You may just need to wrap the value with [:lift ..] to tell HoneySQL it is data, not part of the DSL...

See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.0.0-rc5/doc/getting-started/sql-special-syntax-#lift

No, the elements of HoneySQL's DSL are not namespaced.

If you are passing a structured value, you just wrap it in [:lift ..] -- vector or hash map

[:lift ..] is HoneySQL syntax. It tells HoneySQL not to interpret the value, but treat it as opaque data, i.e., lift it out as a parameter.

If you have any values that could be hash maps or vectors, you must wrap them in [:lift ..].

That's the whole point of that syntax: otherwise HoneySQL will treat a vector as a function call and a hash map as a statement.

I'm not sure what you're asking.

Then wrap it in [:lift ..]

Vectors are function calls in HoneySQL.

That's why you must wrap them in [:lift ..]

(let [v [:lift :some :data]]
  (sql/format {:select :foo :from :bar :where [:= :col [:lift v]]}))

If you're working with JSON values in SQL, you need to flag them as lifted values to prevent them being interpreted as function calls or DSL statements.

Yes, if you don't wrap vectors and hash maps, HoneySQL will interpret them as function calls and DSL statements.

If your vector is [:foo :bar] that is a function call: foo(bar).

You have to wrap structured values always to avoid problems.

You can make lots of mistakes with HoneySQL -- it can't detect most of them.

HoneySQL does not (can not) guarantee valid SQL.

I understand what you're saying. I'm just saying that if you work with structured values, you need to be extra careful.

HoneySQL can't tell the difference between a function call you've built programmatically and any other vector of data.

Same goes for hash maps (DSL statements).

That wouldn't solve the problem.

Post an issue on HoneySQL with examples you think could be detected / dealt with and I'll be happy to post an explanation of why namespacing things won't solve it 🙂

In an arbitrary SQL dialect [:foo 42] could be a valid SQL function call.

The absence of :lift can't be detected (because a function call could be valid in that position).

You're concerned about the broader case of any of HoneySQL's special syntax from masking an actual function call in the SQL code, right?

Unfortunately, with SQL being such a broad and diverse language and so many local variants (in terms of what users have defined), it's almost impossible for HoneySQL to prevent footguns.

If you follow along in the #honeysql channel, you'll see all sorts of stuff that people have shot themselves with.

If you don't use JSON with SQL, you're much less likely to shoot yourself in the foot with HoneySQL.

But mixing JSON and SQL leads to some pretty gnarly expressions that even in plain SQL can be hard to detangle. Add in a DSL based on JSON-like data structures (HoneySQL) and you really are walking a tightrope.

That's an inherent conflict between Clojure data structures being used for two different meanings in the same expression.

If you use JSON with string keys, you're safe:

dev=> (sql/format {:select [[["lift" 42]]]})
["SELECT (?, ?)" "lift" 42]

But if you use keywords or symbols, you're in HoneySQL's DSL territory:

dev=> (sql/format {:select [[[:lift 42]]]})
["SELECT ?" 42]

JSON should be strings though -- keywords and symbols do not exist in JSON, only in Clojure.

(and HoneySQL's DSL has been this way for many, many years at this point)

What machinery is in place to actually turn {:foo 42} into JSON? The database driver isn't doing that.

I'm happy to add more caveats/warnings to the HoneySQL docs about working with JSON requiring [:lift ..] if your JSON actually has keywords in it, rather than string keys.

We're talking about HoneySQL-using code that needs [:lift v] instead of plain v in it.

If you didn't have the "magic" to turn Clojure data structures into JSON, then those (JSON) data structures wouldn't get confused with Clojure data structures that HoneySQL uses/expects 🙂 It's an inherent problem with mixing the two -- you can't avoid conflicts completely.

And HoneySQL 2.x's DSL is almost identical to HoneySQL 1.x's DSL.

That's why HoneySQL 2.x provides an "escape hatch" (`:lift`) so that users can consistently indicate to HoneySQL where the boundaries are between the Clojure data it should process and the Clojure data it shouldn't.

You could also use named parameters and avoid this problem.

I can't change it. There's a lot of HoneySQL code out there.

A key goal of v2, yes.

https://github.com/seancorfield/honeysql/blob/develop/doc/differences-from-1-x.md

(I am fairly sure you'd run into similar problems trying to mix JSON-as-Clojure-data with the 1.x HoneySQL DSL but there were places where 1.x wasn't very consistent)

My point is that this isn't a new problem with v2: if you have a big blog of Clojure data and you expect some parts of it to be handled with different semantics to other parts of it, you-the-user need to add some sort of annotations into that data to delineate those semantics.

If you use named parameters, the problem goes away completely I believe.

[:param :foo] in the DSL and pass {:params {:foo your-json-value}} in the sql/format call.

dev=> (sql/format {:select [[[:param :foo]]]} {:params {:foo [:lift 42]}})
["SELECT ?" [:lift 42]]

That completely separates the two "types" of Clojure data (DSL and JSON) so there can be no confusion.

(anyways, must dash -- my partner and I are off to the garden center)

I'd probably write that upsert-multi! with:

(-> (sql/insert-into table)
    (assoc :on-conflict on-conflict)
    (assoc :do-update-set do-update-set)
    (sqlh/returning :*))

Then you have a nice place to hook in wrapping values in [:lift ..] to ensure all your values -- including your JSON -- gets lifted out as parameters because you can map over rows and replace every value v with [:lift v]. Nice to have it all in one place so no one can misuse it.

Given that you have ->snake_case on table/column, I'm a bit surprised you're not converting ->kebab-case in the builder? (which would be next.jdbc/unqualified-snake-kebab-opts out of the box)

Yes, for safety.

[:lift 42] and [:lift {:foo "bar"}] are "the same" to HoneySQL: they are both values to be lifted out as parameters, so both produce ? and put the value into the parameter list.