Hacker News new | ask | show | jobs
by idoubtit 1203 days ago
I fear that syntactic sugar creates as many problems as it solves. For instance, one might wish to sort the results by whitelisted column:

    query = sql`select foo from bar where zed = ${p} order by ${col} asc`;
Unless the lib implements a real SQL parser for the right dialect, it will quote each expression in the same way, and will either fail or produce a broken SQL.
1 comments
hn_throwaway_99 1203 days ago
Definitely a lot of misconceptions around how this would work. Just check out something like slonik, https://github.com/gajus/slonik, which is an excellent implementation.

The example you gave actually isn't valid, because what you're doing is generating SQL dynamically, and that doesn't work the way prepared statements work. That is, you can't have a prepared statement like "select foo from bar where zed = ? order by ? asc", because with prepared statements the question marks can only substitute for VALUES, not schema names. So if you wanted to do something like that it slonik, it would fail. With slonik you CAN do dynamic SQL, that is guaranteed to be safe and checked at compile time with TypeScript, because you can nest SQL tagged templates. That is you can do this:

    const colToSortBy = useFoo ? sql`foo` : sql`bar`;
    const query = sql`select col from mytable order by ${colToSortBy}`;
In that case slonik will know how to safely "merge" the parent and child parsed SQL.
link
hnbad 1203 days ago
We actually did the same for ArangoDB (I think we first did this in 2015, I remember being surprised nobody had done something similar for SQL at the time). Here's the JS driver's current implementation of it:

https://github.com/arangodb/arangojs/blob/main/src/aql.ts#L1...

Basically the `aql` template tag returns an object that can also be fed back into it and we also deduplicate arguments to avoid sending redundant data over the wire. There's also an escape hatch via a helper function (`aql.literal`) in cases where you need to insert literals that aren't known at compile time (e.g. you load query filters from a configuration file).

link