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!');      });    });  });});

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