如何在单独的文件中正确创建mysql2连接并处理错误

Question

我试图与node.js和mysql2实现以下连接方法，但缺少一些东西，我得到了各种错误(主要是db.query is not a function)。

关键字如下:

• 重用已创建的连接
• 错误处理
• 连接失败时自动重新连接

ORIGINAL(检查下面的更新):

//dbConnection.js
const mysql = require('mysql2/promise');
const dbConfig = {
host: 'localhost',
user: 'user',
database: 'db',
password: 'pass'
};
var connection;
//Exporting to use it elsewhere
module.exports = async function connect() { 
if (connection){
//so if the connection is already exists use it again
return connection;
}
try {
connection = await mysql.createConnection(dbConfig);    
console.info("Connected to MySql on '" + db_config.host + "'");
return connection;
} catch (error) {
console.error('Error when connecting to MySql:', error);
// auto retry in 5 seconds
setTimeout(connect, 5000);
}
}

//dbRoutes.js
const db = require('./dbConnection.js');
dbRouter.get('/get_user', async (req, res, /* next */) => {
const userId = req.userId;
await db.query(
`SELECT * FROM users WHERE id = ${userId};`,
(err, result) => {
return res.status(200).send({
msg: 'success',
user: result
});
}
);
});

更新:

我能让它工作，但我不确定这是最好的方式，所以我要求任何改进你的想法。

//dbConnection.js
const mysql = require('mysql2/promise');
const db_config = {
host: 'localhost',
user: 'root',
database: 'bln',
password: ''
};
var connection;
const connect = async function () { 
if (connection){
return connection;
}
try {
connection = await mysql.createConnection(db_config);
console.info("Connected to MySql on '" + db_config.host + "'");
return connection;
} catch (error) {
console.error('Error when connecting to db:', error);
connection = undefined;
setTimeout(connect, 5000);
}
}
module.exports = connect;

//dbRoutes.js
const dbc = require('./dbConnection.js');
dbRouter.post('/login', userMiddleware.validateLogin, async (req, res) => {
const db = await dbc();
const [results] = await db.execute(`SELECT * FROM users WHERE email = ?;`, [req.body.email]);
return res.status(401).send({
msg: 'success',
user: result
});
});

Answer 1

正如之前的文章所说，我们得到的是一个promise对象而不是连接本身。因此，只需在次要文件中使用.then()方法获取承诺的解析值即可。

const connectPromise = require('./dbConnection.js');
let db;
connectPromise.then(c => {
db = c;
});

现在我们可以使用db.query 了