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 — Ivan Schwarz
– Ivan Schwarz, Commented Aug 16, 2016 at 12:28

Sul Aga · Accepted Answer · 2017-02-09 15:46:52Z

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 9, 2017 at 15:46

Sul Aga

6,9407 gold badges28 silver badges39 bronze badges

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

1 Comment

Saqib Omer Over a year ago

Not suitable of bulk data.

Clippy2k · Accepted Answer · 2021-07-27 13:25:14Z

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)
This method relies on the all the properties in each of data's objects to be enumerated in the same order. See: stackoverflow.com/questions/5525795/…

Community · Accepted Answer · 2017-05-23 12:18:11Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Jun 22, 2014 at 13:35

Ben

5,0842 gold badges20 silver badges23 bronze badges

Collectives™ on Stack Overflow

insert multiple rows into mysql through node.js

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Linked

Related