insert multiple rows into mysql through node.js

Question

I want to insert multiple rows into mysql thru node.js mysql module. The data I have is

var data = [{'test':'test1'},{'test':'test2'}];

I am using pool

 pool.getConnection(function(err, connection) {
     connection.query('INSERT INTO '+TABLE+' SET ?', data,   function(err, result) {
          if (err) throw err;
            else {
                console.log('successfully added to DB');
                connection.release();
            }
      });
 });
}

which fails.

Is there a way for me to have a bulk insertion and call a function when all insertion finishes?

Regards Hammer

Possible duplicate of [How do I do a bulk insert in mySQL using node.js](http://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js) — Ivan Schwarz, Aug 16 '16 at 12:28

score 6 · Answer 1 · answered Feb 09 '17 at 15:46

6

You can try this approach as well

lets say that mytable includes the following columns: name, email

var inserts = [];
inserts.push(['name1', 'email1']);
inserts.push(['name2', 'email2']);
conn.query({
sql: 'INSERT into mytable (name, email) VALUES ?',
values: [inserts]
});

This should work

answered Feb 09 '17 at 15:46

Sul Aga

6,142
5
25
37

1

Not suitable of bulk data. – Saqib Omer May 27 '17 at 14:37

score 6 · Answer 2 · answered Jul 27 '21 at 13:25

After coming back to this issue multiple times, I think i've found the cleanest way to work around this.

You can split the data Array of objects into a set of keys insert_columns and an array of arrays insert_data containing the object values.

const data = [
    {test: 'test1', value: 12},
    {test: 'test2', value: 49}
]

const insert_columns = Object.keys(data[0]);
// returns array ['test', 'value']

const insert_data = data.reduce((a, i) => [...a, Object.values(i)], []);
// returns array [['test1', 12], ['test2', 49]]

_db.query('INSERT INTO table (??) VALUES ?', [insert_columns, insert_data], (error, data) => {
    // runs query "INSERT INTO table (`test`, `value`) VALUES ('test1', 12), ('test2', 49)"
    // insert complete 
})

I hope this helps anyone coming across this issues, I'll probably be googling this again in a few months to find my own answer

@Jack Thanks for this solution. also we can use `const insert_data = data.map(element => Object.values(element));` instead of reduce this will do the same. (I found this method in another resource) — DineshMsd, Feb 04 '23 at 06:29

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

You can insert multiple rows into mysql using nested arrays. You can see the answer from this post: How do I do a bulk insert in mySQL using node.js

edited May 23 '17 at 12:18

Community

1
1

answered Jun 22 '14 at 13:35

Ben

5,024
2
18
23

insert multiple rows into mysql through node.js

3 Answers3

Linked