How do I create a MySQL connection pool while working with NodeJS and Express?
Solution 1
Just to help some one in future, this worked for me:
I created a mysql connector file containing the pool:
// Load module
var mysql = require('mysql');
// Initialize pool
var pool = mysql.createPool({
connectionLimit : 10,
host : '127.0.0.1',
user : 'root',
password : 'root',
database : 'db_name',
debug : false
});
module.exports = pool;
Later you can simply include the connector in another file lets call it manageDB.js:
var pool = require('./mysqlConnector');
And made a callable method like this:
exports.executeQuery=function(query,callback){
pool.getConnection(function(err,connection){
if (err) {
connection.release();
throw err;
}
connection.query(query,function(err,rows){
connection.release();
if(!err) {
callback(null, {rows: rows});
}
});
connection.on('error', function(err) {
throw err;
return;
});
});
}
Solution 2
You can create a connection file, Let's called dbcon.js
var mysql = require('mysql');
// connect to the db
dbConnectionInfo = {
host: "localhost",
port: "3306",
user: "root",
password: "root",
connectionLimit: 5, //mysql connection pool length
database: "db_name"
};
//For mysql single connection
/* var dbconnection = mysql.createConnection(
dbConnectionInfo
);
dbconnection.connect(function (err) {
if (!err) {
console.log("Database is connected ... nn");
} else {
console.log("Error connecting database ... nn");
}
});
*/
//create mysql connection pool
var dbconnection = mysql.createPool(
dbConnectionInfo
);
// Attempt to catch disconnects
dbconnection.on('connection', function (connection) {
console.log('DB Connection established');
connection.on('error', function (err) {
console.error(new Date(), 'MySQL error', err.code);
});
connection.on('close', function (err) {
console.error(new Date(), 'MySQL close', err);
});
});
module.exports = dbconnection;
Now include this connection to another file
var dbconnection = require('../dbcon');
dbconnection.query(query, params, function (error, results, fields) {
//Do your stuff
});
Solution 3
There is some bugs in Utkarsh Kaushik solution:
if (err), the connection can not be released.
connection.release();
and when it has an err, next statement
.query
always execute although it gets an error and cause the app crashed.when the result is null although query success, we need to check if the result is null in this case.
This solution worked well in my case:
exports.getPosts=function(callback){
pool.getConnection(function(err,connection){
if (err) {
callback(true);
return;
}
connection.query(query,function(err,results){
connection.release();
if(!err) {
callback(false, {rows: results});
}
// check null for results here
});
connection.on('error', function(err) {
callback(true);
return;
});
});
};
Related videos on Youtube
Utkarsh Kaushik
Take an avid reader, implement the concepts of programming and git init the repository of software engineering. Merge the branches of application development, data science and data modelling to the master. Now push the commits related to product management and project architecture. Pour some love for comics. Finally sprinkle the entrepreneurial and intrapreneurial snippets with some ambitions and attitude. Bake for 20 minutes. Then take another 15 minutes to prepare some espresso with enough steamed milk and milk foam . The resultant delicious and extravagant cake along with superb latte is what you would know as Utkarsh Kaushik! Know more at my blog - http://www.utkarsh-kaushik.com/blogs/
Updated on June 10, 2020Comments
-
Utkarsh Kaushik almost 4 years
I am able to create a MySQL connection like this:
var mysql = require('mysql'); var connection = mysql.createConnection({ host : 'localhost', user : 'me', password : 'secret', database : 'my_db' }); connection.connect();
But I would rather like to initiate a pool and use it across my project.
-
Jay Nirgudkar over 7 yearshi.. so i am new to node too so i have some doubts. So the exports function will be in the connector file right?? and to call this, i will have to pass my query as a parameter?
-
Ankur_009 over 6 yearsWhen you are calling 'connect ' cmd or I don't need this?
-
sumitjainjr over 6 yearsIf you want to check your db connection is established or not, then you can un-comment the connect cmd which is already mention in code, otherwise don't need to use.