NodeJS - How to insert multiple rows to MySQL

Question

I am trying to insert several (hundres/thousands) of records to MySQL database. My problem is that I am not really sure what's the best way how do it. Example:

function saveData(data, callback) {
    var dataLength = data.length,
        saved = [];

    for (var i = 0; i < dataLength; i++) {
        db.query("INSERT INTO table VALUES (?, ?, ?)", data[i], function(err, res) {
           if (err) {
              // ...
           }

           saved.push(res.insertId);
        });
    }
}

The problem is that after all data is saved (save[] contains all IDs) I need to pass this array to a callback. Obviously I can't do it immediately... I have to wait for all queries to be done.

The questions: Should I use for loop or is it better to do it recursively (Next insert will be called after previous is done), should I check if saved.length === dataLength and then return the array or is there some better way how to do it?

as long as everything fits in memory i don't see nothing wrong with your solution — soulcheck
– soulcheck, Commented May 6, 2014 at 18:07
I can not decide whether is better to execute all queries at once and then wait for results or is better to execute just one query, wait for a result and then execute another one. I am using pooling connection so the query is going to be conn.query(...); — 0101
– 0101, Commented May 7, 2014 at 8:23
I am using module node-mysql2. I thought that the queries to a database works as async so I can register (Let's say) ten callbacks a ten just wait for result. Am I wrong? — 0101
– 0101, Commented May 7, 2014 at 8:38
still though then your question is basically about how much load your mysql server can handle — soulcheck
– soulcheck, Commented May 7, 2014 at 8:41

mscdex · Accepted Answer · 2014-05-06 19:13:14Z

2

Here's how you might do it with async:

function saveData(data, cb) {
  async.mapSeries(data, function(queryData, callback) {
    db.query("INSERT INTO table VALUES (?, ?, ?)", queryData, function(err, res) {
      if (err)
        callback(err);
      else
        callback(null, res.insertId);
    });
  }, cb);
  // cb will be passed `err, saved`
}

answered May 6, 2014 at 19:13

mscdex

107k15 gold badges201 silver badges159 bronze badges

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

Collectives™ on Stack Overflow

NodeJS - How to insert multiple rows to MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related