Node.js mysql2 WHERE id in (?), [ids] returns array with one result

Question

I have a static method getResults(insertIds) inside a class:

static async getResults(insertIds) {
  const [result] = await Driver.pool.execute(
    "SELECT * FROM test WHERE id in (?)",
    [insertIds]
  );
  return result;
}

Driver.pool returns a pool instance that was created with mysql.createPool. I'm using mysql2 library for Node.

When I log the result of MyClass.getResults([6, 8, 10, 12].join(',')), I get an array with a single result (If I don't join the ids with a comma, I get 0 rows). If I run the same query using Query window in the MySQL Workbench, I get all 4 rows back:

SELECT * FROM test WHERE id in (6, 8, 10, 12);

What am I doing wrong? Using Windows.

Edit: this SO link does not answer my question. My issue is different, I pass the array like in the provided link, but it returns only a single element.

I could do that way, but it would be more convenient to use in operator, especially if the query like that works in general. I'd have to count the length of an array and insert ?s in the query string. Would that be acceptable? I know it is generally considered a bad practice to insert string in the queries directly. — semi_92
– semi_92, Commented Mar 6, 2023 at 9:00
Depends on the library. But I think with mysql2 its the follwoing: If you want to replace the ? for the in, you have to pass your parameters as [[insertIds]]. Because if you only pass a flat array, each value in the array replaces one ?. But if one of values in the parameter array is an array itself, the library will expand it accordingly, so that ? will be replaced by the expanded array — derpirscher
– derpirscher, Commented Mar 6, 2023 at 9:01
i think the problem in [6, 8, 10, 12].join(','), because the numbers were converted to be string as well. — Tobok Sitanggang
– Tobok Sitanggang, Commented Mar 6, 2023 at 9:04
When I pass [[insertIds]] to the execute method, it returns an empty array. — semi_92
– semi_92, Commented Mar 6, 2023 at 9:04

semi_92 · Accepted Answer · 2023-03-06 09:50:51Z

2

I found an answer. I should have used pool.query instead of pool.execute. You can read full explanation in this Mysql2 Github issue

This is the way prepared statements work, I think we should document that better

.execute() under the hood is doing prepare + execute commands

answered Mar 6, 2023 at 9:50

semi_92

2771 gold badge6 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Node.js mysql2 WHERE id in (?), [ids] returns array with one result

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related