@mysql/xdevapi won't allow array parameters for WHERE field IN (?) query

Question

I'm using the @mysql/xdevapi npm package with a local installation of mysql-8.0.15-winx64. I'm trying to execute a SQL string with parameters. The query is looking for records where the id is IN an array.

const sql = "SELECT * FROM table WHERE id IN (?)";
const params = [[1,2]];
const result = await session.sql(sql).bind(params).execute();

and I'm getting the following error

Error: Invalid data, expecting scalar
    at SqlResultHandler.BaseHandler.(anonymous function) (C:\...\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:110:19)
    at Array.entry (C:\...\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:87:29)
    at WorkQueue.process (C:\...\@mysql\xdevapi\lib\WorkQueue.js:75:19)
    at Client.handleServerMessage (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:198:21)
    at Client.handleNetworkFragment (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:242:14)
    at TLSSocket.stream.on.data (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:89:36)
    at emitOne (events.js:116:13)
    at TLSSocket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11) info: [Object] } ],

It works if I just pass the sql SELECT * FROM table WHERE id IN (1,2), returning two records.

If I pass a single parameter with a comma separated string, so const params = ["1,2"] then it returns just the first record.

I can't find anything in the documentation to support this, and I can't find a way to work around it.

Answer 1

You need to provide a placeholder for each value in the array. In your case, the SQL statement should eventually be:

const sql = "SELECT * FROM table WHERE id IN (?, ?)";
const params = [1, 2];
const result = await session.sql(sql).bind(params).execute();

You can find more details here.

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js