Node.js MySQL integration Using a connection pool
Example
a. Running multiple queries at same time
All queries in MySQL connection are done one after another. It means that if you want to do 10 queries and each query takes 2 seconds then it will take 20 seconds to complete whole execution. The solution is to create 10 connection and run each query in a different connection. This can be done automatically using connection pool
var pool = mysql.createPool({
connectionLimit : 10,
host : 'example.org',
user : 'bobby',
password : 'pass',
database : 'schema'
});
for(var i=0;i<10;i++){
pool.query('SELECT ` as example', function(err, rows, fields) {
if (err) throw err;
console.log(rows[0].example); //Show 1
});
}
It will run all the 10 queries in parallel.
When you use pool you don't need the connection anymore. You can query directly the pool. MySQL module will search for the next free connection to execute your query.
b. Achieving multi-tenancy on database server with different databases hosted on it.
Multitenancy is a common requirement of enterprise application nowadays and creating connection pool for each database in database server is not recommended. so, what we can do instead is create connection pool with database server and then switch them between databases hosted on database server on demand.
Suppose our application has different databases for each firm hosted on database server. We will connect to respective firm database when user hits the application. Here is the example on how to do that:-
var pool = mysql.createPool({
connectionLimit : 10,
host : 'example.org',
user : 'bobby',
password : 'pass'
});
pool.getConnection(function(err, connection){
if(err){
return cb(err);
}
connection.changeUser({database : "firm1"});
connection.query("SELECT * from history", function(err, data){
connection.release();
cb(err, data);
});
});
Let me break down the example:-
When defining pool configuration i did not gave the database name but only gave database server i.e
{
connectionLimit : 10,
host : 'example.org',
user : 'bobby',
password : 'pass'
}
so when we want to use the specific database on database server, we ask the connection to hit database by using:-
connection.changeUser({database : "firm1"});
you can refer the official documentation here
