Node JS Inserting array of objects to mysql database when using transactions

Question

Am using node-mysql to add records to a database but am facing a challenge when the records to be inserted are an array of objects and I need the operation to be a transaction. I have simplified my problem by creating a test project to better explain my problem.

Lets say I have to tables users and orders and the data to be inserted looks like this

var user = {
   name: "Dennis Wanyonyi",
   email: "[email protected]"
};

var orders = [{
   order_date: new Date(),
   price: 14.99
}, {
   order_date: new Date(),
   price: 39.99
}];

I want to first insert a user to the database and use the insertId to add the each of the orders for that user. Am using a transaction since in case of an error, I want to rollback the whole process. Here is how I try to insert all the records using node-mysql transactions.

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO users SET ?', user, function(err, result) {
    if (err) {
      return connection.rollback(function() {
        throw err;
      });
    }


    for (var i = 0; i < orders.length; i++) {

      orders[i].user_id = result.insertId;

        connection.query('INSERT INTO orders SET ?', orders[i], function(err, result2) {
          if (err) {
            return connection.rollback(function() {
              throw err;
            });
          }  
          connection.commit(function(err) {
            if (err) {
              return connection.rollback(function() {
                throw err;
              });
            }
            console.log('success!');
          });
        });
       }
      });
     });

However I have a problem iterating over the array of orders without having to call connection.commit multiple times within the for loop

Answer 1

I would suggest to construct a simple string for multiple row insert query for orders table in the for loop first and then execute it outside the for loop. Use the for loop to only construct the string. So you can rollback the query whenever you want or on error. By multiple insert query string i mean as follows:

INSERT INTO your_table_name
    (column1,column2,column3)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);