Fork me on GitHub

I was doing an upgrade of HoneySQL from 0.9.3 to 2.2.868 to get support for CTEs and RowNumber function and figured I would attempt a bump to latest. I found that the “alternate” version of parameterized queries as outputs are gone (e.g. $1 ) and everything is now ? which our postgres instance doesn’t seem to like (PostgreSQL 10.18)

ERROR:  syntax error at or near ","
LINE 2: ..._guid, update_type, update_data, status) VALUES (?, ?, ?, ?,...
any suggestions on things to look at? Should I rewrite most of the queries to take the params map?


No idea why PG would barf on that -- it's legal SQL syntax and works in the tests for PG. But, yes, the positional $N style parameters are no longer supported -- only ? placeholders (standard SQL) and named parameters (like :?foo and then {:params {:foo 123}} passed in options).


@steven.proctor I'd have to see your format call and possibly how you're using the result of that (`` or next.jdbc call).


(defn insert-transaction
   (insert-transaction record "running"))
  ([record status]
   (-> (insert-into :write_transactions)
       (values [(cond-> record
                  (not (:status record))
                  (assoc :status status))])
that is our basic honey-sql sql generator


and we destructure and send to pg in ClojureScript on Node

(let [[sql & params] (insert-transaction record)]
        (.query client sql (apply array params)))


sql "INSERT INTO write_transactions (id, session_id, on_behalf_of, loan_guid, update_type, update_data, status) VALUES (?, ?, ?, ?, ?, ?, ?)"

params #js ["b953ca66-a030-4f1c-9e6e-50ea239574a3" "<session-id>" "testing-sap" "2e8de942-e954-4b08-88a4-84d38261e33" "update-async" #js {:commands #js [#js ["patch-loan" #js {}]]} "running"]


and taking that sql straight in to postgres barfs too for some reason:

PREPARE fooprep (uuid, varchar, varchar, uuid, varchar, json, varchar) as
  INSERT INTO write_transactions (id, session_id, on_behalf_of, loan_guid, update_type, update_data, status) VALUES (?, ?, ?, ?, ?, ?, ?)


mainly just making sure it is nothing with the honey-sql directly, as it seemed like the ? would work… but wanted to check on the honey-sql side because all the prepared statements docs on postgres docs and such show the $1 style params


also wondering if the ? is handled specially by the JDBC stuff


If you're using Node on the server, you're not using JDBC... right? So what DB connector are you using on the server?


Maybe it doesn't support ? placeholders? (that seems... incredible... to me since this is the basic default for JDBC)


yeah, that is the pg node library


I also just took a peek to see at next.jdbc on the far off-hand chance that the Heart of Gold had marked those code files as cljc…. 😉


next.jdbc is a thin wrapper around JDBC -- very JVM-specific.


yeah, I was expecting that… figured I would check anyway, because it would be nicer to move to something off the node ecosystem to something more properly clojure ;)


I avoid node at all costs -- and js in general. You're the first person to ask about the numbered parameters since the 2.x release 🙂


I would too, but we have AWS Lambdas… and the decision was made because of the JVM startup time… (even though a number of them are driven by SQS queues and are async anyway… ¯\(ツ)/¯ )


if there is something I can do to help with it, feel free to point me in the right direction on the ticket


Part of the problem -- and why I dropped support for it -- is because the parsing/formatting is no longer left-to-right linear so it's very hard to figure out the ordering for parameters in the SQL string now. I'd have to go through every formatter in the whole codebase and ensure that it produces parameters in a strictly linear, left-to-right order which is potentially a lot of work... so don't expect this to get added any time soon...


that part is good to know at least… 😉


Just looking over the code, there are some places where the ? handling is pretty hardcoded in and there's at least one place where the code depends on "?" being a possible whole SQL string... I'll continue to give it some thought but...


Note that, to aid migration, you can use both HoneySQL 1.x (and earlier) alongside 2.x -- they have different group/artifact coordinates and different namespaces so you can use them together and even require both into the same ns (under different aliases) so you can migrate one query at a time if you want.


Is there any syntax for ARRAY() (as opposed to ARRAY[])? {:select [[[(keyword "array ") {:select :* ,,,}] :alias]]} is the best I've found


@jshaffer2112 Which database are you working with?


Not sure if this is better or worse?

dev=> (sql/format {:select [[[:'ARRAY {:select :* :from :foo}] :bar]]})
The quoted in the keyword means "render as-is" so it circumvents the built-in :array behavior.

🙃 1

postgres That is better, thanks


Given that [:array ...] is already special syntax to support PG's ARRAY[..], there's no obvious way to signify an ARRAY() function -- and I suspect the ARRAY[..] syntax is a lot more common? I looked online and couldn't find PG docs for SELECT ARRAY( SELECT .. ) .. -- do you have a link?