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: "example@email.com"
};

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);

Answer 2

You can use Promise.all functionality of Bluebird for this.

var promiseArray = dataArray.map(function(data){
    return new BluebirdPromise(function(resolve, reject){
        connection.insertData(function(error, response){
            if(error) reject(error);
            else resolve(response);
        }); //This is obviously a mock
    });
});

And after this:

BluebirdPromise.all(promiseArray).then(function(result){
    //result will be the array of "response"s from resolve(response);
    database.commit();
});

This way, you can work all the inserts asyncronously and then use database.commit() only once.

Answer 3

Some kind of task in Node.js are Asynchronous( like I/O , DB and etc..), and there is a lots of LIBS that help to handle it.

but if you want don't use any lib,for iterating an array in JS and use it in an asynchronous functionality its better to implement it as recursive function.

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;
        });
    }
    // console.log(result.insertId) --> do any thing if need with inserted ID 

    var insertOrder = function(nextId) {
        console.log(nextId);
        if ((orders.length - 1) < nextId) {
            connection.commit(function(err) {
                if (err) {
                    return connection.rollback(function() {
                        throw err;
                    })
                }
                console.log(" ok");
            });

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

                insertOrder(nextId + 1);
            });
        }
    }
    insertOrder(0);

});
});

as you can see I rewrite your for loop as a recursive function inside.

Answer 4

I would use the async.each to do the iteration and to fire all the queries in parallel. If some of the queries will fail, the asyncCallback will be called with an error and the program will stop processing the queries. This will indicate that we should stop executing queries and rollback. If there is no error we can call the commit.

I' ve decoupled the code a bit more and split it into functions:

function rollback(connection, err) {
  connection.rollback(function () {
    throw err;
  });
}

function commit(connection) {
  connection.commit(function (err) {
    if (err) {
      rollback(connection, err);
    }

    console.log('success!');
  });
}

function insertUser(user, callback) {
  connection.query('INSERT INTO users SET ?', user, function (err, result) {
    return callback(err, result);
  });
}

function insertOrders(orders, userId, callback) {
  async.each(orders, function (order, asyncCallback) {
    order.user_id = userId;

    connection.query('INSERT INTO orders SET ?', order, function (err, data) {
      return asyncCallback(err, data);
    });
  }, function (err) {
    if (err) {
      // One of the iterations above produced an error.
      // All processing will stop and we have to rollback.
      return callback(err);
    }

    // Return without errors
    return callback();
  });
}

connection.beginTransaction(function (err) {
  if (err) {
    throw err;
  }

  insertUser(user, function (err, result) {
    if (err) {
      rollback(connection, err);
    }

    insertOrders(orders, result.insertId, function (err, data) {
      if (err) {
        rollback(connection, err);
      } else {
        commit(connection);
      }
    });
  });
});

Answer 5

you need to use async library for these kind of operation.

connection.beginTransaction(function(err) {   
if (err) { throw err; }
 async.waterfall([
        function(cb){
            createUser(userDetail, function(err, data){
              if(err) return cb(err);
              cb(null, data.userId);
           });
        },
      function(userid,cb){
        createOrderForUser(userid,orders, function() {
            if(err) return cb(err);
            cb(null);
        });
      }
    ], function(err){
      if (err) 
        retrun connection.rollback(function() {
              throw err;
        });

       connection.commit(function(err) {
        if (err) {
          return connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });     
    }); 
});
var createUser = function(userdetail, cb){ 
//-- Creation of Orders
};
var createOrderForUser = function (userId, orders, cb) {
  async.each(orders, function(order, callback){
 //-- create orders for users
},function(err){
   // doing err checking.
    cb();
  });
};

Answer 6

See if you can write a Stored Procedure to encapsulate the queries, and have START TRANSACTION ... COMMIT in the SP.

The tricky part comes with needing to pass a list of things into the SP, since there is no "array" mechanism. One way to achieve this is to have a commalist (or use some other delimiter), then use a loop to pick apart the list.

Answer 7

        currentLogs = [
 { socket_id: 'Server', message: 'Socketio online', data: 'Port  3333', logged: '2014-05-14 14:41:11' },
 { socket_id: 'Server', message: 'Waiting for Pi to connect...', data: 'Port: 8082', logged: '2014-05-14 14:41:11' }
];

console.warn(currentLogs.map(logs=>[ logs.socket_id , logs.message , logs.data , logs.logged ]));