Skip to content

Instantly share code, notes, and snippets.

@glenjamin
Last active July 15, 2024 09:39
Show Gist options
  • Save glenjamin/8924190 to your computer and use it in GitHub Desktop.
Save glenjamin/8924190 to your computer and use it in GitHub Desktop.
DB transaction from a connection pool in node-mysql
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);
}
})
}
@gladchinda
Copy link

gladchinda commented Jul 21, 2018

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

});

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment