Node.js mysql transaction

Question

Can anyone provide an example of how I could achieve MySQL transactions in Node.js. I am trying to get my head around using the node-mysql driver and node-mysql-queue.

As far are I can tell, using node-mysql-queue greatly reduces the asynchronous nature of Node.js as new queries have to wait until existing ones have completed. To get around this, has anyone attempted to combine node-mysql-queue with node-mysql's connection-pooling capabilities. i.e starting a new mysql connection for each new http request, and starting transaction queues on individual connections?

Danny Harding · Answer 1 · 2020-07-29T16:13:06.247

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

score 22 · Answer 2 · answered Oct 26 '13 at 03:39

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

This doesn't really address the async issue. The connect should be used exclusively for the transaction until the transaction is complete. — bvs, Sep 29 '15 at 18:03
This is not rollbacking the first query result which is inserted. — Prasanna, Nov 27 '21 at 01:36

score 2 · Answer 3 · answered Jun 20 '17 at 05:59

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"

                });
            });

@Splines ...yes this is very old code you can use async and await or Promise.all() !! — Manish, Jun 18 '21 at 11:59
Thanks, yeah just found out that `mysql2` is offering promises [here](https://www.npmjs.com/package/mysql2#using-promise-wrapper) and [this](https://github.com/sidorares/node-mysql2/issues/384#issuecomment-673726520) is a great code sample to use in conjunction with transactions. — Splines, Jun 18 '21 at 12:39

score 1 · Answer 4 · answered Jan 10 '17 at 19:48

I have come up with a solution using a recursive function.

var sql = 'INSERT INTO logs SET data = ?';

// array of rows to insert
var rows = [[/*first row*/], [/*additional row*/]];

connection.beginTransaction(function (err) {

    if (err) { 
        throw err; 
    }

    var insertEachRow = function () {

        var row = rows.shift();

        if (! row) {
            // Done, now commit
            return noMoreRows();
        }

        connection.query(sql, row, function (err, result) {
            if (err) { 
                connection.rollback(function () {
                    throw err;
                });
            }  

            insertEachRow();
        });
    };

    var noMoreRows = function () {
        connection.commit(function (err) {
            if (err) { 
                connection.rollback(function () {
                    throw err;
                });
            }
            console.log('success!');
        });
    };

    insertEachRow();
});

score 0 · Answer 5 · answered Feb 05 '18 at 14:15

0

I found one useful links which uses node js mysql pooling with transaction. Database Connection pooling is always useful. One can check this link

https://github.com/mysqljs/mysql

answered Feb 05 '18 at 14:15

Nirav Shah

307
4
12

score 0 · Answer 6 · answered Nov 18 '19 at 06:57

0

I created a wrapper ORM type thing for this specific purpose, hope it helps SQl-connecton - pool ORM type helper methods

answered Nov 18 '19 at 06:57

Shreyan Mehta

550
5
17

score 0 · Answer 7 · answered Jul 13 '20 at 10:10

0

You can use it with the mysql also, just use this function without any parameters /call back . Make sure you have async function and use these instead

await con.rollback();
await con.beginTransaction();
await con.commit();

Life set.....

answered Jul 13 '20 at 10:10

Saifio

321
2
4

Node.js mysql transaction

7 Answers7

Update

Edit

Linked