有没有办法使用从mysqljs/mysql库中获取的pool.getConnection()方法,并采用async/await语法?
这个想法是创建一个方法,该方法返回一个连接,可以在各种具有外键约束(顺序查询)的写查询之间传递它,然后释放它,同时还可以从池中获取更多连接以进行各种读取查询(并行)。
这个想法是创建一个方法,该方法返回一个连接,可以在各种具有外键约束(顺序查询)的写查询之间传递它,然后释放它,同时还可以从池中获取更多连接以进行各种读取查询(并行)。
分享我的工作示例:
我使用这个适用于Node.js的Promisified MySQL中间件
阅读本文使用Node.js 8和Async/Await创建MySQL数据库中间件
这是我的database.js
var mysql = require('mysql');
// node -v must > 8.x
var util = require('util');
// !!!!! for node version < 8.x only !!!!!
// npm install util.promisify
//require('util.promisify').shim();
// -v < 8.x has problem with async await so upgrade -v to v9.6.1 for this to work.
// connection pool https://github.com/mysqljs/mysql [1]
var pool = mysql.createPool({
connectionLimit : process.env.mysql_connection_pool_Limit, // default:10
host : process.env.mysql_host,
user : process.env.mysql_user,
password : process.env.mysql_password,
database : process.env.mysql_database
})
// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
console.error('Database connection was closed.')
}
if (err.code === 'ER_CON_COUNT_ERROR') {
console.error('Database has too many connections.')
}
if (err.code === 'ECONNREFUSED') {
console.error('Database connection was refused.')
}
}
if (connection) connection.release()
return
})
// Promisify for Node.js async/await.
pool.query = util.promisify(pool.query)
module.exports = pool
你必须升级 Node 版本至 8.x 或更高版本。
你必须使用异步函数才能使用 await。
示例:
var pool = require('./database')
// node -v must > 8.x, --> async / await
router.get('/:template', async function(req, res, next)
{
...
try {
var _sql_rest_url = 'SELECT * FROM arcgis_viewer.rest_url WHERE id='+ _url_id;
var rows = await pool.query(_sql_rest_url)
_url = rows[0].rest_url // first record, property name is 'rest_url'
if (_center_lat == null) {_center_lat = rows[0].center_lat }
if (_center_long == null) {_center_long= rows[0].center_long }
if (_center_zoom == null) {_center_zoom= rows[0].center_zoom }
_place = rows[0].place
} catch(err) {
throw new Error(err)
}
const pool = mysql.createPool(config);
exports.getConnection = () => {
return new Promise((resolve, reject) => {
pool.getConnection(function (err, connection) {
if (err) {
return reject(err);
}
resolve(connection);
});
});
};
someWhereElse.js:
const db = require('./db');
const wrappingFunction = async () => {
const connection = await db.getConnection();
console.log(connection);
};
wrappingFunction();
似乎手动实现承诺是更好的选择。 我在我的代码中使用了以下内容 -
const mysql = require('mysql');
const config = require('config');
const pool = mysql.createPool(config.get('db.mysql'));
module.exports = {
checkConnection: () => {
return new Promise((resolve, reject) => {
pool.getConnection((err, conn) => {
if (err) {
return reject(err);
}
resolve(conn.release());
});
});
},
pool,
closeConnection: () => pool.end(),
};
之前的答案(使用util.promisify)对我没有用,只有手动实现Promise才起作用:
函数:
async function removeItem (id) {
return new Promise( (resolve) => {
pool.query('DELETE FROM table_name WHERE id=' + id, (error) => {
resolve ({result: !error});
});
});
}
使用方法:
const app = express();
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: 10,
host: 'localhost',
user: 'login',
password: 'pass',
database: 'dbname'
});
app.post("/:id", async (req, res) => {
const answer = await itemRemove(id);
res.send(answer);
});
当然,你需要先将其转换为 Promise,自从 node 8.0.0
版本以后,你可以这样做:
const util = require('util');
async function doSomething() {
const getConnectionAsync = util.promisify(pool.getConnection);
try {
const result = await getConnectionAsync('MASTER');
}catch(err) {
console.log('Oh no');
}
}
如果由于某些原因您无法使用 Node 8 或更高版本,则还有其他方法可以将其 promisify,例如 http://bluebirdjs.com/docs/api/promise.promisify.html
TypeError [ERR_INVALID_ARG_TYPE]:参数“original”的类型必须为函数类型。
- JulianProg分享一下我在代码中经常使用的内容:
//Filename: MySQL.js
module.exports = {
connect: function ()
{
return new Promise((resolve, reject) => {
let pool = Mysql.createPool({ //require configfile.js or just put connection detail here
connectionLimit: config.mysql.connectionLimit,
host: config.mysql.host,
user: config.mysql.user,
password: config.mysql.password,
database: config.mysql.database
});
pool.getConnection((err, connection) =>
{
try
{
if (connection)
{
resolve({"status":"success", "data":"MySQL connected.", "con":pool});
connection.release();
}
}
catch (err)
{
reject({"status":"failed", "error":`MySQL error. ${err}`});
}
resolve({"status":"failed", "error":"Error connecting to MySQL."});
});
});
}
}
然后每当您需要调用连接到MySQL时
//Filename: somefile.js
const useMySQL = require('./path/to/MySQL');
module.exports = {
getSomething: function () {
return new Promise(async (resolve) => {
try
{
let connection = await useMySQL.connect();
con = connection.con;
//Do some query here, then
resolve(`Send some result/handle error`);
}
catch (err)
{
//Handle error if any, log, etc, and eventually
resolve(err);
}
});
}
Promise
API,但它将允许您使用async/await
:promise-mysql
。 - Patrick Roberts