Node.js MySQL错误处理 [英] Node.js MySQL Error Handling
问题描述
我已经阅读了一些在node.js中使用mysql的示例，并且对错误处理有疑问.
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
});
});
});
这会导致服务器每次出现sql错误时都崩溃.我想避免这种情况，并保持服务器运行.
This causes the server to crash every time there's an sql error. I'd like to avoid that and keep the server running.
我的代码是这样的:
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
});
});
});
我不确定这是否是处理它的最佳方法.我也想知道查询的err块中是否应该有一个connection.release().否则，连接可能会保持打开状态并随着时间的推移逐渐建立.
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.
我已经习惯了Java的try...catch...finally或try-with-resources，在这里我可以干净地"捕获任何错误并最后关闭我的所有资源.有没有一种方法可以传播错误并在一个地方处理所有错误?
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?
推荐答案
我决定使用es2017语法和Babel将其转换为Node 7支持的es2016.
I've decided to handle it using es2017 syntax and Babel to transpile down to es2016, which Node 7 supports.
较新版本的Node.js无需转译即可支持此语法.
Newer versions of Node.js support this syntax without transpiling.
这里是一个例子:
'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块都会关闭数据库连接.
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.
您只需要确保自始至终都在兑现承诺.对于数据库访问，我使用promise-mysql模块而不是普通的mysql模块.对于其他所有内容，我都使用bluebird模块和promisifyAll().
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().
我还有一些自定义的Exception类，可以在某些情况下抛出这些异常类，然后在catch块中检测到它们.根据可以在try块中引发哪些异常，我的catch块可能看起来像这样:
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:
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;
这篇关于Node.js MySQL错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
