I was recently asked by a colleague on how to get started with a Node.js app that uses MySQL. Since I couldn't find a decent tutorial that uses async await with a connection pool and a disposer, I decided to write one up. Without further ado, the following is what the DbCore class could look like:

const mysql = require("mysql");
const Promise = require("bluebird");

Promise.promisifyAll(mysql);
Promise.promisifyAll(require("mysql/lib/Connection").prototype);
Promise.promisifyAll(require("mysql/lib/Pool").prototype);

module.exports = class DbCore {
  constructor({
    dbHost,
    dbUsername,
    dbPassword,
    dbName,
    debugSql = false,
    logger
  }) {
    const dbProperties = {
      host: dbHost,
      user: dbUsername,
      password: dbPassword,
      database: dbName,
      supportBigNumbers: true,
      waitForConnections: true, // waitForConnections: Determines the pool's action when no connections are available and the limit has been reached. If true, the pool will queue the connection request and call it when one becomes available. If false, the pool will immediately call back with an error. (Default: true)
      connectionLimit: 10 // The maximum number of connections to create at once.
    };

    this.pool = mysql.createPool(dbProperties);

    this.pool.on("connection", function(connection) {
      connection.on("enqueue", function(sequence) {
        // if (sequence instanceof mysql.Sequence.Query) {
        if ("Query" === sequence.constructor.name) {
          if (debugSql) {
            logger.debug(sequence.sql);
          }
        }
      });
    });

    logger.info("created db pool");

    this.logger = logger;
  }

  getSqlConnection() {
    return this.pool.getConnectionAsync().disposer(connection => {
      connection.release();
    });
  }

  shutDown() {
    return this.pool.endAsync();
  }

  insert(tableName, sanitizedEntity) {
    return Promise.using(this.getSqlConnection(), connection => {
      const sql = `INSERT INTO ${tableName} SET ?`;
      return connection.queryAsync(sql, sanitizedEntity);
    });
  }

  getByField(tableName, fieldName, fieldValue) {
    return Promise.using(this.getSqlConnection(), connection => {
      const sql = `SELECT * FROM ${tableName} WHERE ${fieldName} = ?`;
      // eslint-disable-next-line no-unused-vars
      return connection.queryAsync(sql, fieldValue).then((rows, cols) => {
        if (rows.length) {
          return rows[0];
        }
        return null;
      });
    });
  }
};

Let's break it down - the class takes as parameters the configuration information and creates the connection pool. Here we configure our connection pool to log the sql statement if the debug mode is enabled, which is very useful for development:

this.pool.on("connection", function(connection) {
  connection.on("enqueue", function(sequence) {
    // if (sequence instanceof mysql.Sequence.Query) {
    if ("Query" === sequence.constructor.name) {
      if (debugSql) {
        logger.debug(sequence.sql);
      }
    }
  });
});

Debugging sql like a pro.

The getSqlConnection is the most important method and here we use bluebird's disposer to ensure that our connection will be released after use when used in conjunction with Promise.using. Note that this also ensures that the connection is release in case of an exception and is similar to Java's finally:

getSqlConnection() {
  return this.pool.getConnectionAsync().disposer(connection => {
    connection.release();
  });
}

Every database connection's ultimate goal.

One could use the DbCore as is or even pass it on to other db classes via composition. Here's a basic example of a DbUser class:

const Promise = require("bluebird");
const DbCore = require("./DbCore");

module.exports = class DbUser {
  constructor(dbCore) {
    this.dbCore = dbCore;
    this.logger = dbCore.logger;
    this.tableName = "user";
  }

  add(user) {
    return this.dbCore.insert(this.tableName, sanitized);
  }

  getByEmail(email) {
    return this.dbCore.getByField(this.tableName, "email", email);
  }

  updateById(user) {
    return Promise.using(this.dbCore.getSqlConnection(), connection => {
      const sql = "UPDATE user SET ? WHERE id = ?";
      return connection.queryAsync(sql, [sanitized, user.id]);
    });
  }
};

Since all the methods return promises, using async / await is very straightforward as can be seen in the following test:

const expect = require("chai").expect;
const Promise = require("bluebird");

const DbCore = require("./DbCore");
const DbUser = require("./DbUser");

describe("DbUser", () => {
  let dbCore = null;
  let dbUser = null;

  before(() => {
    dbCore = new DbCore({ ... }); // TODO: fill in with your params
    dbUser = new DbUser(dbCore);
  });

  after(async () => {
    return Promise.using(dbCore.getSqlConnection(), connection => {
      return connection.queryAsync("DELETE FROM user");
    }).then(() => {
      return dbCore.shutDown();
    });
  });

  it("should add and get a user", async () => {
    // given
    const user = {
      email: "abc@abc.com",
      name: "snoop"
    };

    // when & then
    const addResult = await dbUser.add(user);
    expect(addResult.affectedRows).to.equal(1);

    const retrieved = await dbUser.getByEmail("abc@abc.com");
    expect(retrieved).to.deep.equal(user);
  });
});

Please note that the sample code above does not cover data sanitization and validation. It is highly recommended to sanitize data before it gets inserted into the database and many different strategies exist here. I can highly recommend joi for object schema validation. Anyways, I hope that this tutorial helped you in getting started with MySQL with Node.js.