From example where-col-in example and this answer, WHERE IN clauses should have query with parameters with following syntax
const response = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
where data is an array.
Now, when data is an empty array, it produces the following query
SELECT * FROM users WHERE id IN ()
which is a syntax error.
Consider following statements:
this works
const x = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[1, 2, 3]]);
this does not work
const y = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[]]);
A similar error reported for squel library has answers on how knex and ruby's sequel behaves in such scenario.
Is this a bug or am I doing something wrong? Could there be an alternate syntax which works for both scenarios.
For instance, an alternate query using ANY works for both situations:
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[1, 2, 3]]);
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[]]);
What should be the best way to have WHERE col IN queries which could also handle empty arrays as params?
Common Answer
Is this a bug or am I doing something wrong?
Not a bug, but a flaw for most SQL frameworks. It is very difficult to handle such parameters, so most frameworks just leave the empty list as it is to generate invalid SQL XXX in ().
Could there be an alternate syntax which works for both scenarios.
A simple approach is:
if(data is empty) data = [ -1 ] //fill a non-existing id
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
What about knex or sequel?
They are Query Builder frameworks, so they have chances to generate special SQL to handle empty lists. Popular methods used by Query Builder frameworks to handle WHERE id in () OR ...:
Personally I do not like id!=id :)
For Some Framework
You may check its manual to see if there is some way to handle empty lists, eg: can the framework replace the empty list with a non-existing value?