What is the best practice when we want to create MySql connection in nodejs.
- Create a global connection and use it for all queries
- Open a new connection for each query and close it after the query is done.
-
use an orm like github.com/lcherone/autorm ;pLawrence Cherone– Lawrence Cherone2020-06-01 15:03:49 +00:00Commented Jun 1, 2020 at 15:03
Add a comment
|
2 Answers
Connection pooling will handle your requests automatically.
This article covers the topic and might be helpful - https://medium.com/@mhagemann/create-a-mysql-database-middleware-with-node-js-8-and-async-await-6984a09d49f4
1 Comment
Hike Nalbandyan
Any chance you came across a full middleware with pool configuration?
Define Global connection variable and use it in the whole project.
// only use when you have not setup app.set('con',con);
// global.con;
dbConnection = () => {
// MYSQL database connection start
let con = mysql.createPool('database Config obj here');
// if you want use global.con variable
// con = mysql.createPool(databaseConfig);
con.getConnection((err) => {
if (err) {
//- The server close the connection.
if (err.code === "PROTOCOL_CONNECTION_LOST") {
console.error("/!\\ Cannot establish a connection with the database. /!\\ (" + err.code + ")");
}
//- Connection in closing
else if (err.code === "PROTOCOL_ENQUEUE_AFTER_QUIT") {
console.error("/!\\ Cannot establish a connection with the database. /!\\ (" + err.code + ")");
}
//- Fatal error : connection variable must be recreated
else if (err.code === "PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR") {
console.error("/!\\ Cannot establish a connection with the database. /!\\ (" + err.code + ")");
}
//- Error because a connection is already being established
else if (err.code === "PROTOCOL_ENQUEUE_HANDSHAKE_TWICE") {
console.error("/!\\ Cannot establish a connection with the database. /!\\ (" + err.code + ")");
}
//- Anything else
else {
console.error("/!\\ Cannot establish a connection with the database. /!\\ (" + err.code + ")");
}
setTimeout(dbConnection, 5000);
} else {
// you can also set the con varibale by calling below set method or else use global.con ;
app.set('con', con);
// rest of the code will goes here
}
});
}
dbConnection();
let con = app.get('con');
// => will have connection varibale here
