Promisifying MySQL Transactions with Connection Pool

This is a Node.js example on MySQL transactions with promises (using async / await).

Photo by jesse ramirez on Unsplash

Reason

In order to keep our data atomic while we’re updating tables with multiple queries, MySQL transactions come in handy.

To avoid the callback hell as the example in the document, I used promises with async / await functions to better structure the code.

Code example

util/mysql.js

1. Use MySQL’s connection pool to reuse connections and enhance the performance of executing commands.
2. Promisify query and pool for async / await usages later. (Alternative: use util &bind to promisify pool.query.)

const mysql = require("mysql");
require("dotenv").config();
let dbConfig = {
  connectionLimit: 10, // default 10
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE
};
const pool = mysql.createPool(dbConfig);
const connection = () => {
  return new Promise((resolve, reject) => {
  pool.getConnection((err, connection) => {
    if (err) reject(err);
    console.log("MySQL pool connected: threadId " + connection.threadId);
    const query = (sql, binding) => {
      return new Promise((resolve, reject) => {
         connection.query(sql, binding, (err, result) => {
           if (err) reject(err);
           resolve(result);
           });
         });
       };
       const release = () => {
         return new Promise((resolve, reject) => {
           if (err) reject(err);
           console.log("MySQL pool released: threadId " + connection.threadId);
           resolve(connection.release());
         });
       };
       resolve({ query, release });
     });
   });
 };
const query = (sql, binding) => {
  return new Promise((resolve, reject) => {
    pool.query(sql, binding, (err, result, fields) => {
      if (err) reject(err);
      resolve(result);
    });
  });
};
module.exports = { pool, connection, query };

models/user.js

1. Use the pool we exported to get connection before the transaction.
2. Await all queries as they are promises.
3. Use START TRANSACTION query to start the transaction.
4. Use ROLLBACK query at catch block for error handling.
5. Use COMMIT query whenever you’re ready to commit the transaction (normally at the last query).
6. Release the connection back to the pool after committing the transaction.

const mysql = require("../util/mysql.js");
const errors = require("../util/errors");
const crypto = require("crypto");
async function querySignUp (data) {
  const connection = await mysql.connection();
  try {
    console.log("at querySignUp...");
    await connection.query("START TRANSACTION");
    let usernameNo = await connection.query("SELECT COUNT (*) FROM user_table WHERE user_name = ?", [data.username]);
    if (usernameNo[0]["COUNT (*)"] > 0) {
      throw errors.usernameTakenError;
    };
    let emailNo = await connection.query("SELECT COUNT (*) FROM user_table WHERE email = ?", [data.email]);
    if (emailNo[0]["COUNT (*)"] > 0) {
      throw errors.userEmailTakenError;
    };
    let now = Date.now();
    let hash = crypto.createHash("sha256");
    hash.update(data.email + data.password + now);
    let token = hash.digest("hex");
    // encrypt password
    let passwordHash = crypto.createHash("sha256");
    passwordHash.update(data.password);
    let encryptedPassword = passwordHash.digest("hex");
    let userInfo = {
      user_name: data.username,
      user_password: encryptedPassword,
      email: data.email,
      provider: "native",
      access_expired: now + 30 * 24 * 60 * 60 * 1000, // 30 days
      token,
      points: 0,
      level_id: 1
    };
    await connection.query("INSERT INTO user_table SET ?", userInfo);
    let getUserInfo = await connection.query("SELECT user_table.*, level_table.level_name FROM user_table INNER JOIN level_table ON user_table.level_id = level_table.id WHERE user_table.email = ?", [data.email]);
    await connection.query("COMMIT");
    return userInfo;
  } catch (err) {
    await connection.query("ROLLBACK");
    console.log('ROLLBACK at querySignUp', err);
    throw err;
  } finally {
    await connection.release();
  }
}

Special thanks to Arthur, Ethan and Jimmy at AppWorks School!

Reference

mysqljs/mysql

Example of using transaction with promise connection? · Issue #384 · sidorares/node-mysql2

How to use util.promisify and .bind functions in NodeJs?