Node.js mysql transaction

Node.js mysql transaction

Node.js mysql transaction

Update

See the edit below for async/await syntax


I spent some time writing a generalized version of the transaction example given by node mysql, so I thought I would share it here. I am using Bluebird as my promise library, and used it to promisify the connection object which simplified the asynchronous logic a lot.

const Promise = (bluebird);
const mysql = (mysql);

/**
 * Run multiple queries on the database using a transaction. A list of SQL queries
 * should be provided, along with a list of values to inject into the queries.
 * @param  {array} queries     An array of mysql queries. These can contain `?`s
 *                              which will be replaced with values in `queryValues`.
 * @param  {array} queryValues An array of arrays that is the same length as `queries`.
 *                              Each array in `queryValues` should contain values to
 *                              replace the `?`s in the corresponding query in `queries`.
 *                              If a query has no `?`s, an empty array should be provided.
 * @return {Promise}           A Promise that is fulfilled with an array of the
 *                              results of the passed in queries. The results in the
 *                              returned array are at respective positions to the
 *                              provided queries.
 */
function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            Number of provided queries did not match the number of provided query values arrays
        )
    }

    const connection = mysql.createConnection(databaseConfigs);
    Promise.promisifyAll(connection);
    return connection.connectAsync()
    .then(connection.beginTransactionAsync())
    .then(() => {
        const queryPromises = [];

        queries.forEach((query, index) => {
            queryPromises.push(connection.queryAsync(query, queryValues[index]));
        });
        return Promise.all(queryPromises);
    })
    .then(results => {
        return connection.commitAsync()
        .then(connection.endAsync())
        .then(() => {
            return results;
        });
    })
    .catch(err => {
        return connection.rollbackAsync()
        .then(connection.endAsync())
        .then(() => {
            return Promise.reject(err);
        });
    });
}

If you wanted to use pooling as you suggested in the question, you could easily switch the createConnection line with myPool.getConnection(...), and switch the connection.end lines with connection.release().


Edit

I made another iteration of the code using the mysql2 library (same api as mysql but with promise support) and the new async/await operators. Here is that

const mysql = require(mysql2/promise)

/** See documentation from original answer */
async function transaction(queries, queryValues) {
    if (queries.length !== queryValues.length) {
        return Promise.reject(
            Number of provided queries did not match the number of provided query values arrays
        )
    }
    const connection = await mysql.createConnection(databaseConfigs)
    try {
        await connection.beginTransaction()
        const queryPromises = []

        queries.forEach((query, index) => {
            queryPromises.push(connection.query(query, queryValues[index]))
        })
        const results = await Promise.all(queryPromises)
        await connection.commit()
        await connection.end()
        return results
    } catch (err) {
        await connection.rollback()
        await connection.end()
        return Promise.reject(err)
    }
}

The following transaction example was added to the documentation a month ago:

https://github.com/felixge/node-mysql#transactions

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query(INSERT INTO posts SET title=?, title, function(err, result) {
    if (err) { 
      connection.rollback(function() {
        throw err;
      });
    }

    var log = Post  + result.insertId +  added;

    connection.query(INSERT INTO log SET data=?, log, function(err, result) {
      if (err) { 
        connection.rollback(function() {
          throw err;
        });
      }  
      connection.commit(function(err) {
        if (err) { 
          connection.rollback(function() {
            throw err;
          });
        }
        console.log(success!);
      });
    });
  });
});

Node.js mysql transaction

I am using the following approach. There is an add function in my Model where I am performing database operations.

  add : function (data, callback) {

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

        var user_query = INSERT INTO `calldata`.`users` (`username`, `password`, `enabled`, `accountNonExpired`, `accountNonLocked`, `credentialsNonExpired`) VALUES ( + data.mobile + ,  + sha1(data.password) + , 1, 1, 1, 1);
        connection.query(user_query, function(err, results) {
            if (err) {
                return connection.rollback(function() {
                    throw err;
                });
            }

            var accnt_dtls_query = INSERT INTO `calldata`.`accnt_dtls` (`req_mob_nmbr`, `usr_nme`, `dvce_id`, `mngr_id`, `cmpny_id`, `actve_flg`, `crtd_on`, `usr`) VALUES ( + data.mobile + ,  + data.name + , ,  + data.managerId + ,  + data.companyId + , 1, now(),  + data.mobile+ );

            connection.query(accnt_dtls_query, function(err, results) {
                if (err) {
                    return connection.rollback(function() {
                        throw err;
                    });
                }
                var user_role_query = INSERT INTO `calldata`.`user_roles` (`username`, `ROLE`) VALUES ( + data.mobile + ,  + data.role + );

                connection.query(user_role_query, function(err, result) {
                    if (err) {
                        return connection.rollback(function() {
                            throw err;
                        });
                    }

                    //add an entry to manager table
                    var mngr_dtls_query = INSERT INTO `calldata`.`mngr_dtls` (`mngr_nm`, `cmpny_id`, `crtd_on`, `usr_nm`, `eml_id`) VALUES ( + data.name + , + data.companyId +  , now(),  + data.mobile + ,  + data.mobile + );
                    connection.query(mngr_dtls_query, function(err, result) {
                        if (err) {
                            return connection.rollback(function () {
                                throw err;
                            });
                        }
                        console.log(Changed  + result.changedRows +  results);
                        connection.commit(function (err) {
                            console.log(Commiting transaction.....);
                            if (err) {
                                return connection.rollback(function () {
                                    throw err;
                                });
                            }

                            console.log(Transaction Complete.);
                            connection.end();
                            callback(null, result);
                        });
                    });
                });
            });
        });
    });
    //transaction ends here
}

and calling from controller:

 agentAccountModel.add(data, function(err, results) {
                if(err)
                {
                    res.status(500);
                    res.json({
                        status: 500,
                        message: err
                    });
                }

                res.status(200);
                res.json({
                    status: 200,
                    message: Saved successfully

                });
            });

Related posts on Node.js :

Leave a Reply

Your email address will not be published.