Node.js MySQL Error Handling

Question

I've read several examples for using mysql in node.js and I have questions about the error handling.

Most examples do error handling like this (perhaps for brevity):

app.get('/countries', function(req, res) {

    pool.createConnection(function(err, connection) {
        if (err) { throw err; }

        connection.query(sql, function(err, results) {
            if (err) { throw err; }

            connection.release();

            // do something with results
        });
    });
});

This causes the server to crash every time there's an sql error. I'd like to avoid that and keep the server running.

My code is like this:

app.get('/countries', function(req, res) {

    pool.createConnection(function(err, connection) {
        if (err) {
            console.log(err);
            res.send({ success: false, message: 'database error', error: err });
            return;
        }

        connection.on('error', function(err) {
            console.log(err);
            res.send({ success: false, message: 'database error', error: err });
            return;
        });

        connection.query(sql, function(err, results) {
            if (err) {
                console.log(err);
                res.send({ success: false, message: 'query error', error: err });
                return;
            }

            connection.release();

            // do something with results
        });
    });
});

I'm not sure if this is the best way to handle it. I'm also wondering if there should be a connection.release() in the query's err block. Otherwise the connections might stay open and build up over time.

I'm used to Java's try...catch...finally or try-with-resources where I can "cleanly" catch any errors and close all my resources at the end. Is there a way to propagate the errors up and handle them all in one place?

Answer 1

I've decided to handle it using es2017 syntax and Babel to transpile down to es2016, which Node 7 supports.

Newer versions of Node.js support this syntax without transpiling.

Here is an example:

'use strict';

const express = require('express');
const router = express.Router();

const Promise = require('bluebird');
const HttpStatus = require('http-status-codes');
const fs = Promise.promisifyAll(require('fs'));

const pool = require('./pool');     // my database pool module, using promise-mysql
const Errors = require('./errors'); // my collection of custom exceptions


////////////////////////////////////////////////////////////////////////////////
// GET /v1/provinces/:id
////////////////////////////////////////////////////////////////////////////////
router.get('/provinces/:id', async (req, res) => {

  try {

    // get a connection from the pool
    const connection = await pool.createConnection();

    try {

      // retrieve the list of provinces from the database
      const sql_p = `SELECT p.id, p.code, p.name, p.country_id
                     FROM provinces p
                     WHERE p.id = ?
                     LIMIT 1`;
      const provinces = await connection.query(sql_p);
      if (!provinces.length)
        throw new Errors.NotFound('province not found');

      const province = provinces[0];

      // retrieve the associated country from the database
      const sql_c = `SELECT c.code, c.name
                     FROM countries c
                     WHERE c.id = ?
                     LIMIT 1`;
      const countries = await connection.query(sql_c, province.country_id);
      if (!countries.length)
        throw new Errors.InternalServerError('country not found');

      province.country = countries[0];

      return res.send({ province });

    } finally {
      pool.releaseConnection(connection);
    }

  } catch (err) {
    if (err instanceof Errors.NotFound)
      return res.status(HttpStatus.NOT_FOUND).send({ message: err.message }); // 404
    console.log(err);
    return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message }); // 500
  }
});


////////////////////////////////////////////////////////////////////////////////
// GET /v1/provinces
////////////////////////////////////////////////////////////////////////////////
router.get('/provinces', async (req, res) => {

  try {

    // get a connection from the pool
    const connection = await pool.createConnection();

    try {

      // retrieve the list of provinces from the database
      const sql_p = `SELECT p.id, p.code, p.name, p.country_id
                     FROM provinces p`;
      const provinces = await connection.query(sql_p);

      const sql_c = `SELECT c.code, c.name
                     FROM countries c
                     WHERE c.id = ?
                     LIMIT 1`;

      const promises = provinces.map(async p => {

        // retrieve the associated country from the database
        const countries = await connection.query(sql_c, p.country_id);

        if (!countries.length)
          throw new Errors.InternalServerError('country not found');

        p.country = countries[0];

      });

      await Promise.all(promises);

      return res.send({ total: provinces.length, provinces });

    } finally {
      pool.releaseConnection(connection);
    }

  } catch (err) {
    console.log(err);
    return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message }); // 500
  }
});


////////////////////////////////////////////////////////////////////////////////
// OPTIONS /v1/provinces
////////////////////////////////////////////////////////////////////////////////
router.options('/provinces', async (req, res) => {
  try {
    const data = await fs.readFileAsync('./options/provinces.json');
    res.setHeader('Access-Control-Allow-Methods', 'HEAD,GET,OPTIONS');
    res.setHeader('Allow', 'HEAD,GET,OPTIONS');
    res.send(JSON.parse(data));
  } catch (err) {
    res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message });
  }
});


module.exports = router;

Using async/await along with this try { try { } finally { } } catch { } pattern makes for clean error handling, where you can collect and deal with all your errors in one place. The finally block closes the database connection no matter what.

You just have to make sure you're dealing with promises all the way through. For database access, I use the promise-mysql module instead of plain mysql module. For everything else, I use the bluebird module and promisifyAll().

I also have custom Exception classes that I can throw under certain circumstances and then detect those in the catch block. Depending on which exceptions can get thrown in the try block, my catch block might look something like this:

catch (err) {
  if (err instanceof Errors.BadRequest)
    return res.status(HttpStatus.BAD_REQUEST).send({ message: err.message }); // 400
  if (err instanceof Errors.Forbidden)
    return res.status(HttpStatus.FORBIDDEN).send({ message: err.message }); // 403
  if (err instanceof Errors.NotFound)
    return res.status(HttpStatus.NOT_FOUND).send({ message: err.message }); // 404
  if (err instanceof Errors.UnprocessableEntity)
    return res.status(HttpStatus.UNPROCESSABLE_ENTITY).send({ message: err.message }); // 422
  console.log(err);
  return res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ error: err, message: err.message });
}

pool.js:

'use strict';

const mysql = require('promise-mysql');

const pool = mysql.createPool({
  connectionLimit: 100,
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database',
  charset: 'utf8mb4',
  debug: false
});


module.exports = pool;

errors.js:

'use strict';

class ExtendableError extends Error {
  constructor(message) {
    if (new.target === ExtendableError)
      throw new TypeError('Abstract class "ExtendableError" cannot be instantiated directly.');
    super(message);
    this.name = this.constructor.name;
    this.message = message;
    Error.captureStackTrace(this, this.contructor);
  }
}

// 400 Bad Request
class BadRequest extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('bad request');
    else
      super(m);
  }
}

// 401 Unauthorized
class Unauthorized extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('unauthorized');
    else
      super(m);
  }
}

// 403 Forbidden
class Forbidden extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('forbidden');
    else
      super(m);
  }
}

// 404 Not Found
class NotFound extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('not found');
    else
      super(m);
  }
}

// 409 Conflict
class Conflict extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('conflict');
    else
      super(m);
  }
}

// 422 Unprocessable Entity
class UnprocessableEntity extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('unprocessable entity');
    else
      super(m);
  }
}

// 500 Internal Server Error
class InternalServerError extends ExtendableError {
  constructor(m) {
    if (arguments.length === 0)
      super('internal server error');
    else
      super(m);
  }
}


module.exports.BadRequest = BadRequest;
module.exports.Unauthorized = Unauthorized;
module.exports.Forbidden = Forbidden;
module.exports.NotFound = NotFound;
module.exports.Conflict = Conflict;
module.exports.UnprocessableEntity = UnprocessableEntity;
module.exports.InternalServerError = InternalServerError;