Value array support

[Eomm]

Starting from the following example, I would like to simplify the query2 code like the query is.

The query output leads to an "[object object]" string replacement, I think it would be good to support an "auto-glue" option to avoid the SQL.glue statement for these simple tasks.

const SQL = require('@nearform/sql')

const ids = [1, 2, 3]

const query = SQL`SELECT * FROM table WHERE id IN (${ids})`
console.log(query.sql)
console.log(query.values)
// SELECT * FROM table WHERE id IN (?)
// [ [ 1, 2, 3 ] ]

const query2 = SQL`SELECT * FROM table WHERE id IN (${SQL.glue(
  ids.map((id) => SQL`${id}`),
  ','
)})`
console.log(query2.sql)
console.log(query2.values)
// SELECT * FROM table WHERE id IN (?,?,?)
// [ 1, 2, 3 ]

[paolochiodi]

Working with array may be difficult and I'm not sure auto-glue is the correct option here, because arrays are actually valid values in sql (i.e. you can have a varchar[]) and you would be left to clue whether the array should be translated to a sql array or not.

A better approach would be to change the query to something similar to the following

const query = SQL`SELECT * FROM table WHERE id = ANY(ARRAY[${ids}])`
console.log(query.sql)
console.log(query.values)
// SELECT * FROM table WHERE id = ANY(ARRAY[?])
// [ [ 1, 2, 3 ] ]

I don't have a test project at hand, @Eomm can you check if something similar to the above works?

[Eomm]

by using mysql8 it does not work as ANY expect a subquery

[Eomm]

Adding this feature would also add SQLite to the supported dbs.

It wants a ? char for each item of the parameter array https://github.com/TryGhost/node-sqlite3/issues/762

[lasmil]

@Eomm , I can try to sort this out, I will sync with you

[simoneb]

@Eomm can you provide some feedback please?

[github-actions[bot]]

🎉 This issue has been resolved in version 1.10.0 🎉

The release is available on:

• npm package

• GitHub release

  Your optic bot 📦🚀