honeysql is only for producing SQL queries, it does not make connections

I have an example here for honeysql + sqlite: https://github.com/babashka/nbb/blob/main/examples/sqlite/honeysql.cljs

It should be similar for postgres

https://node-postgres.com/

I know but come on man, I just want to cut n paste :rolling_on_the_floor_laughing:

I'll work it out

I notice the syntax expected by this pg lib is $1 instead of ? what honeysql uses.

searching around GH I see that this lib has done it before

https://github.com/talented0312/jobsite/blob/3bf03eda55b40c016e613ad184b415d2623216f2/src/district/server/async_db.cljs

so I'll take it from there

Yeah, the Node PG lib doesn't support regular ? placeholders -- and HoneySQL does not produce the numbered parameters. There's a ticket in the backlog for HoneySQL but it turns out to be extremely tricky with the current machinery -- although I have some ideas about making it work. HoneySQL v1 used to support the numbered parameters but almost no one was using it and since it was really handled "under the hood" it didn't matter much.

I wonder how that async_db.cljs example works then

It uses HoneySQL v1 not v2. And it uses the PG extensions library for HoneySQL v1.

oh right

Perhaps it's not difficult to hack around this: replace each ? with a $ + an incremental number

In SQL ?? is the escaped version of a ? when you don't want a placeholder and PG has (at least) three four operators that include a ?

So you can "hack around" it for simple cases if you know what your SQL is going to contain 🙂

hehe yeah...

Have I said lately just how much I dislike SQL? And how much I particularly like PostgreSQL's extensions to SQL? 😆

I heard your podcast :)

I should go listen to myself rant... maybe...

are you suggesting that it's not recommended @U04V70XH6?

my uses cases are pretty simple tbh

so strings are likely good enough

@U04V5V0V4 Perhaps this also works:

{:select :$1 :from :$2}

{:select :$1 :from :$2 :where [[:= :x :$3]]}

and then you're own your own for passing those arguments in the right order

Maybe I'll try to set aside a day or two to add support for it 😞 I sort of know how to go about it (the complication is that you can't guarantee formatting actually happens strictly left-to-right -- so the actual parameters potentially need to be reordered at the end to match the order the $n were actually embedded in the final string).

So is this $n syntax coming from the library or is it actually a postgres syntax?

PostgreSQL supports both ? in SQL strings and $n -- presumably at the JDBC driver level.

"all" JDBC drivers support ?. But not all PG drivers across all platforms support ?.

so JDBC does the ? => $n translation?

I'm not sure what you mean by "translation" there.

For JDBC, generally you provide a SQL string, with a ? placeholder in the string for each parameter for the statement, in the same matching order.

if postgres expects $n syntax, but JDBC supports writing ?, does JDBC driver translate this before passing it to PG? Or do drivers convert the string + arguments into something else at the driver level?

PG's driver supports ? for JDBC.

I don't know that drivers parse the SQL string (I find that extremely unlikely but, hey, databases and drivers are weird). The generic JDBC docs talk about using ?.

Under the hood, how each JDBC driver talks to its specific database tends to be proprietary. So "anything" could be happening there.

After skimming a little bit, my assumption now is that PG only supports the $n syntax natively and JDBC drivers do the ? to $n translation to offer a uniform interface

So it's entirely possible that PG's underlying ... yeah, I was just about to say that and that's probably why the PG node driver doesn't bother and just forces users to put $n in their SQL strings.

HoneySQL currently "assumes" JDBC even tho' it is technically a clj/s library.

Does JavaScript have JDBC? 🥴

Looks like the go-to querybuilder of the node-universe (KnexJS) does in its postgres adapter a simple regex replace: https://github.com/knex/knex/blob/master/lib/dialects/postgres/index.js#L140

And what of SQL like this? "SELECT ' ? ' AS answer" To be fair, Apache Derby and HSQLDB both throw exceptions trying to parse this but all the other DBs/drivers I test next.jdbc against are happy with this SQL.

also the sqlite go library seemed to accept the ? output from honeysql