title: Node JS Inserting array of objects to mysql database when using transactions
body: <p>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 <strong>transaction</strong>. I have simplified my problem by creating a test project to better explain my problem. </p>
  
  <p>Lets say I have to tables <code>users</code> and <code>orders</code> and the data to be inserted looks like this </p>
  
  <pre><code>var user = {
     name: "Dennis Wanyonyi",
     email: "example@email.com"
  };
  
  var orders = [{
     order_date: new Date(),
     price: 14.99
  }, {
     order_date: new Date(),
     price: 39.99
  }];
  </code></pre>
  
  <p>I want to first insert a <code>user</code> to the database and use the <code>insertId</code> to add the each of the <code>orders</code> 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 <a href="https://github.com/mysqljs/mysql#transactions" rel="noreferrer">node-mysql transactions</a>.</p>
  
  <pre><code>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 &lt; 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!');
            });
          });
         }
        });
       });
  </code></pre>
  
  <p>However I have a problem iterating over the array of <code>orders</code> without having to call <code>connection.commit</code> multiple times within the  for loop</p>