MySQL transactions in NodeJS

In a previous post we saw how to access and query MySQL from NodeJS. In this post we will see how we can use MySQL transactions in NodeJS. We will be using the same library as used before, so if you have not installed it go ahead and install it. The library supports simple transaction at the connection level.

$ npm install mysql

We will be using the following database tables for the demo.

CREATE TABLE IF NOT EXISTS `names` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
 
CREATE TABLE IF NOT EXISTS `log` (
  `logid` int(11) default NULL,
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Below is a MySQL transaction script that allows you process a transaction and rollback if a transaction fails. The idea here is simple: insert a new name in the ‘names’ table and get the last insert id, and then insert the id into the ‘log’ table. If any of this fails we rollback the transaction.

 
var mysql = require('mysql');
 
var connection = mysql.createConnection(
    {
      host     : 'localhost',
      user     : 'YOUR_USERNAME',
      password : 'YOUR_PASSWORD',
      database : 'DB_NAME'
    }
);
 
connection.connect(function(err) {
  if (err) {
    console.error('error connecting: ' + err.stack);
    return;
  }
  console.log('connected as id ' + connection.threadId);
});
 
/* Begin transaction */
connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO names SET name=?', "sameer", function(err, result) {
    if (err) { 
      connection.rollback(function() {
        throw err;
      });
    }
 
    var log = result.insertId;
 
    connection.query('INSERT INTO log SET logid=?', log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log('Transaction Complete.');
        connection.end();
      });
    });
  });
});
/* End transaction */

As noted in the NodeJS documentation, beginTransaction(), commit() and rollback() are simply convenience functions that execute the MySQL START TRANSACTION, COMMIT, and ROLLBACK commands respectively. If you check the MySQL nodejs module you will see the code like below for ‘commit’ .

Connection.prototype.commit = function commit(options, callback) {
    if (!callback && typeof options === 'function') {
        callback = options;
        options = {};
    }
    options = options || {};
    options.sql = 'COMMIT';
    options.values = null;
    return this.query(options, callback);
};

Also note the location of connection.end() in the code. We only close the connection when the transaction is complete. Say we close the connection like below.

connection.beginTransaction(function(err) {
  ...
});
 
connection.end();

This will throw ‘Error: Cannot enqueue Query after invoking quit.‘ error. We have to remember that NodeJS executes code asynchronously. While other languages will send a database query and wait for the result to come back, Node.js will not. When you send a database query, Node.js will continue executing the following code, then jump back when the result is available. So in our case connection.end() will execute immediately after beginTransaction even if MySQL transaction is not complete. Although in a larger program the connection end code will most probably be somewhere else so this is not the optimal way to close the connection, but for our small example it will suffice.