All geek questions in one place

Node.js MySQL Error Handling

I read several examples of using mysql in node.js and I have questions about error handling.

In most examples, this error handling (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 an sql error occurs. I would like to avoid this and keep the server running.

My code looks 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 this. I am also wondering if there should be connection.release()in the request block err. Otherwise, the connections may remain open and build up over time.

I use Java try...catch...finallyor try-with-resources, where I can "cleanly" catch any errors and close all my resources at the end. Is there a way to propagate errors and handle them all in one place?

+12
5

, es2017 Babel, es2016, Node.

Node.js .

:

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

async/await try { try { } finally { } } catch { } pattern , . finally , .

, promises . promise-mysql mysql . bluebird promisifyAll().

, , catch. , try, catch :

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;
+11

- async.series .

const mysql = require('mysql') 
const async = require('async')

async.series([
  function (next) {
    db = mysql.createConnection(DB_INFO)
    db.connect(function(err) {
      if (err) {
        // this callback/next function takes 2 optional parameters: 
        // (error, results)
        next('Error connecting: ' + err.message)
      } else {
        next() // no error parameter filled => no error
      }
    })
  },
  function (next) {
     var myQuery = ....
     db.query(myQuery, function (err, results, fields) {
       if (err) {
         next('error making the query: ' + err.message)
         return // this must be here
       }
       // do something with results
       // ...
       next(null, results) // send the results
     })
   },
   function (next) {
     db.close()
   }], 
   //done after all functions were executed, except if it was an error 
   function(err, results) {
     if (err) {
       console.log('There was an error: ', err)
     }
     else {
       //read the results after everything went well
       ... results ....
     }
   })
+1

, - . , , , , - .

var queryString = "SELECT * FROM notification_detail nd LEFT JOIN notification n ON nd.id_notification = n.uuid WHERE login_id = ?  id_company = ?;";
var filter = [loginId, idCompany];

var query = connection.query({
    sql: queryString,
    timeout: 10000,
}, filter );

query
  .on('error', function(err) {
   if (err) {
      console.log(err.code);
      // Do anything you want whenever there is an error.
      // throw err;
   } 
})
.on('result', function(row) {
  //Do something with your result.
})
.on('end', function() {
  connection.release();
});

, .

var query = connection.query({
sql: queryString, 
timeout: 10000,
}, function(err, rows, fields) {
    if (err) {
      //Do not throw err as it will crash the server. 
      console.log(err.code);
    } else {
      //Do anything with the query result
    } 
    connection.release()
});
0

MySQL. , , , , . , MySQL.

connect: function ()
    {
        return new Promise((resolve, reject) => {
            let pool = Mysql.createPool({
                connectionLimit: config.mysql.connectionLimit,
                host: config.mysql.host,
                user: config.mysql.user,
                password: config.mysql.password,
                database: config.mysql.database
            });

            pool.getConnection((err, con) =>
            {
                try
                {
                    if (con)
                    {
                        con.release();
                        resolve({"status":"success", "message":"MySQL connected.", "con":pool});
                    }
                }
                catch (err)
                {
                    reject({"status":"failed", "error":'MySQL error. ${err}'});
                }
                resolve({"status":"failed", "error":"Error connecting to MySQL."});
            });
        });
    }

MySQL Package Used: https://www.npmjs.com/package/mysql

Native Promise async / await ES2017

0
source

To handle specific cases of error handling that were returned from the sql connection, you can look at the 'error' object returned from the callback.

So..

const mysql = require('mysql') 

let conn = mysql.createConnection(connConfig)

conn.query(query, function(error, result, fields){
    if (error){
        console.log(typeof(error));
        for(var k in error){
            console.log('${k}: ${error[k]}')
        }
}

the console.log statement in the above for loop will output something like:

an object

code: ER_TABLE_EXISTS_ERROR
errno: 1050
sqlMessage: Table 'table1' already exists
sqlState: 42S01
index: 0
sql: CREATE TABLE table1 (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
City varchar(255)
);

using these keys you can pass values ​​to the handler

0
source

Source: https://habr.com/ru/post/1658260/

More articles:


All Articles