How do I create a MySQL connection pool while working with NodeJS and Express?

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.

4 Answers

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; }); });
}

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
});

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; }); });
};

You do also can access the Mysql in a similar way by firstly importing the package by entering npm install mysql in the terminal and installing it & initialize it.

const {createPool} = require("mysql");
const pool = createPool({ host : 'localhost', user : 'me', password : 'secret', database : 'my_db'
)};
module.exports = pool;