Mysql: Passing array into connection.query() only getting first value

[tbaustin]

mysql query module

const sql = `delete from products where id in (?)`
const args =  [ 1199027, 1199028, 1199029 ]
const query = connection.query(sql, args, (err, results, fields) => {
  console.log(results)
  console.log(err)
})

As you can see from the query variable, I am logging the actual sql and it is only grabbing the first value from the array.

Console Logs

QUERY:  delete from products where id in (?)
ARGS:  [ 1199027, 1199028, 1199029 ]
mysql connection.query(...).sql:  delete from products where id in (1199027)

[tbaustin]

So far my only solution was to nest the array of ids to force it into a group. so

const args = [ [ 1199027, 1199028, 1199029 ] ]

Which from the documentation:
Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

so it forced it into a group (1199027, 1199028, 1199029) which seemed to work, but that seems like a work around not a solution

[dougwilson]

The issue here is probably the confusion around the second argument to connection.query: the first argument is a string, which is the query. The second argument is an array, where each index corresponds to each ? in the query string.

If you break this down, here is JavaScript that perhaps explains how this works better:

const sql = `delete from products where id in (?)`
const value_for_first_question_mark =  [ 1199027, 1199028, 1199029 ]
const values = [value_for_first_question_mark /*, value_for_second_question_mark, etc. */]
const query = connection.query(sql, values, (err, results, fields) => {
  console.log(results)
  console.log(err)
})

The value you want to sub into the first question mark (?) is the array [ 1199027, 1199028, 1199029 ], so that is the value that should be at the first index to the values argument. Put another way, here is an example where another ? is added to the query, to show what it would look like if there was more than one:

const sql = `delete from ?? where id in (?)`
const table_name = 'products'
const ids =  [ 1199027, 1199028, 1199029 ]
const values = [table_name, ids]
const query = connection.query(sql, values, (err, results, fields) => {
  console.log(results)
  console.log(err)
})

I hope this helps!

[tbaustin]

That makes it super clear! Sorry I didn't get that before!