DB transaction from a connection pool in node-mysql

pool-transaction.js

var mysql = require('mysql');

var pool = mysql.createPool('mysql://localhost');

inTransaction(pool, function(db, next) {
    db.query("DELETE * FROM stuff", function(err) {
        if (err) return next(err);

        db.query("INSERT INTO stuff VALUES (1,2,3)", function(err) {
            return next(err);
        });
    });
}, function(err) {

    console.log("All done, transaction ended and connection released");

});

/**
 * Convenience wrapper for database connection in a transaction
 */
function inTransaction(pool, body, callback) {
    withConnection(pool, function(db, done) {

        db.beginTransaction(function(err) {
            if (err) return done(err);

            body(db, finished)
        })

        // Commit or rollback transaction, then proxy callback
        function finished(err) {
            var context = this;
            var args = arguments;

            if (err) {
                if (err == 'rollback') {
                    args[0] = err = null;
                }
                db.rollback(function() { done.apply(context, args) });
            } else {
                db.commit(function(err) {
                    args[0] = err;
                    done.apply(context, args)
                })
            }
        }
    }, callback)
}

/**
 * Convenience wrapper for database connection from pool
 */
function withConnection(pool, body, callback) {
    pool.getConnection(function(err, db) {
        if (err) return callback(err);

        body(db, finished);

        function finished() {
            db.release();
            callback.apply(this, arguments);
        }
    })
}

Great piece of code. Helped me a lot!

I just rewrote the inTransaction function to incorporate withConnection (Makes the code more readable in my opinion)

function inTransaction(pool, body, callback) {
    pool.getConnection(function(err, conn) {
        if (err) return callback(err);

        conn.beginTransaction(function(err) {
            if (err) return done(err);

            body(conn, function(err) {
                // Commit or rollback transaction, then proxy callback
                let args = arguments;

                if (err) {
                    if (err == 'rollback') {
                        args[0] = err = null;
                    }
                    conn.rollback(function() { done.apply(this, args) });
                } else {
                    conn.commit(function(err) {
                        args[0] = err;
                        done.apply(this, args)
                    })
                }
            });

            function done() {
                conn.release();
                callback.apply(this, arguments);
            }
        });
    })
}

I bumped into this piece of code while trying to execute transactions using pool connections and it did a pretty good job in saving the day. However, for my application, I had to do some rewrites. The rewrites includes ES6 rest parameters and arrow functions. I have also made it possible that additional data passed to the callbackFn of body() are not overridden by the error argument.

Here is my modified version:

const poolTransaction = (pool, body, callback) => {

  pool.getConnection((err, conn) => {
    if (err) return callback(err);

    conn.beginTransaction((err) => {
      if (err) return done(err);

      body(conn, (err, ...args) => {
        // Commit or rollback transaction, then proxy callback

        if (err) {
          if (err == 'rollback') {
            args.unshift(null);
          }
          conn.rollback(() => { done(...args) });
        } else {
          conn.commit((err) => {
            args.unshift(err);
            done(...args);
          })
        }
      });

      function done(...args) {
        conn.release();
        callback(...args);
      }
    });

  })

}

module.exports = poolTransaction;

You can now do this:

const mysql = require('mysql');
const poolTransaction = require('./pool-transaction');

const pool = mysql.createPool('mysql://localhost');

const createUser = new Promise((resolve, reject) => {

  const execute = (connection, next) => {
    const user = { ... }; // user data

    connection.query("INSERT INTO `users` SET ?", user, (err, results) => {
      if (err) return next(err);

      const { insertId: user_id } = results;
      
      if (user_id) {
        connection.query("INSERT INTO `users_profiles` SET ?", user_id, (err, results) => {
          return err ? next(err) : next(null, results.affectedRows)
        })
      }
    })
  }

  const end = (err, data) => {
    return err ? reject(err) : resolve(data)
  }

  poolTransaction(pool, execute, end);

});