Troubles while trying to bulk insert an array of users in Node

Question

I've been spending a lot of time looking at all the solutions provided on how to do a bulk insert with Node (notably here, here and here), but I'm still not able to insert multiple rows from a list of users.

My array looks like this:

[ [ 'user 1' ], [ 'user 2' ], [ 'user3' ] ]

And my code is the following:

let insert_sql = "INSERT IGNORE INTO followers (username) VALUES (?)"
  await connection.execute(insert_sql, [users], function (err, result) {
    if (err) throw err
    console.log("Number of records inserted: " + result.affectedRows)
  })

But it only inserts all the users in one row... I'm using npm and mysql2 package, may this be the problem's origin?

score 0 · Answer 1 · answered Jan 18 '19 at 06:30

You need to change to .query() so it's building longer query on the client side or use .execute and serialise your insert data to/from single parameter ( for example, using JSON field and stored procedure ) or use matching number of placeholders in query text let insert_sql = "INSERT IGNORE INTO followers (username) VALUES (?, ?, ?)"; await connection.execute(insert_sql, [users]).

Here is my answer to explain difference between execute vs query to "why IN ? does not wark with execute?" question:

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

query vs execute step by step:

query:

format sql on the client: 'SELECT id FROM mytable WHERE id IN (?)' + [ [1,2,3] ] becomes 'SELECT id FROM mytable WHERE id IN (1, 2, 3)' send COM_QUERY command with 'SELECT id FROM mytable WHERE id IN (1, 2, 3)' read fields + rows execute:

send COM_PREPARE command with SELECT id FROM mytable WHERE id IN (?) as query parameter read prepared statement result (id + parameters + result fields if known from query ). For this query there is one parameter ( one ? ). id is a number, usually starts with 1 for every connection ( not unique server-wise, you can't prepare statement in one connection and use in another ) send COM_EXECUTE command using statement id + parameters. Parameters must be simple mysql types, and currently all parameters coerced to strings and serialised as strings (with exception for Buffer parameters, they sent as is). In your example is "please execute stmt with id 1 and one parameter which is a string "1,2,3" Result from prepare step above is cached, with next execute commands with same query only 'COM_EXECUTE' part is performed

I agree that this is confusing but this is how prepared statement work. We should improve this with better documentation and maybe some parameter validation warnings/errors like _"hey, are you sure you want to send your {foo: 'bar'} parameter to prepared statement? It'll be sent as [Object object]!''

Troubles while trying to bulk insert an array of users in Node

1 Answers1