Node js. Proper / Best Practice to create connection

Question

Right now i am creating a very large application in Node JS. I am trying to make my code clean and short (Just like most of the developer). I've create my own js file to handle connection to mysql. Please see code below.

var mysql = require('mysql');

var config = {
    'default' : {
        connectionLimit : process.env.DB_CONN_LIMIT,
        host     : process.env.DB_HOST,
        user     : process.env.DB_USER,
        password : process.env.DB_PASS,
        database : process.env.DB_NAME,
        debug    : false,
        socketPath  : process.env.DB_SOCKET
    }
};

function connectionFunc(query,parameters,callback,configName) {

    configName = configName || "default";

    callback = callback || null;

    parameters = parameters;

    if(typeof parameters == 'function'){

        callback = parameters;

        parameters = [];

    }
    //console.log("Server is starting to connect to "+configName+" configuration");
    var dbConnection = mysql.createConnection(config[configName]);

    dbConnection.connect();

    dbConnection.query(query,parameters, function(err, rows, fields) {
      //if (!err)
        callback(err,rows,fields);
      //else
        //console.log('Error while performing Query.');
    });

    dbConnection.end();
}

module.exports.query = connectionFunc;

I am using the above file in my models, like below :

var database = require('../../config/database.js');

module.exports.getData = function(successCallBack){
    database.query('SAMPLE QUERY GOES HERE', function(err, result){
        if(err) {console.log(err)}
        //My statements here
    });
}

Using this coding style, everything works fine but when i am trying to create a function that will loop my model's method for some reason. Please see sample below :

for (i = 0; i < 10000; i++) {
    myModel.getData(param, function(result){             
        return res.json({data : result }); 
    });
}

It gives me an ER_CON_COUNT_ERROR : Too Many Conenction. The question is why i still get an error like these when my connection always been ended by this dbConnection.end();? I'm still not sure if i am missing something. I am still stuck on this. My connection limit is 100 and i think adding more connection is a bad idea.

Answer 1

Because query data form the database is async.

In your loop the myModel.getData (or more precisely the underling query) will not halt/paus your code until the query is finished, but send the query to the database server and as soon as the database response the callback will be called.

The calling end on dbConnection will not close the connection immediately, it will just mark the connection to be closed as soon as all queries that where created with that connection are finished.

mysql: Terminating connections

Terminating a connection gracefully is done by calling the end() method. This will make sure all previously enqueued queries are still before sending a COM_QUIT packet to the MySQL server.

An alternative way to end the connection is to call the destroy() method. This will cause an immediate termination of the underlying socket. Additionally destroy() guarantees that no more events or callbacks will be triggered for the connection.

But with destroy the library will not wait for the result so the results are lost, destroy is rarely useful.

So with your given code you try to create 10000 connections at one time.

You should only use on connection by task, e.g. if a user requests data using the browser, then you should use one connection for this given request. The same is for timed task, if you have some task that is done in certain intervals.

Here an example code:

var database = require('./config/database.js');

function someTask( callback ) {
   var conn = database.getConnection();

   myModel.getData(conn, paramsA, dataReceivedA)

   function dataReceivedA(err, data) {
       myModel.getData(conn, paramsB, dataReceivedB)
   }

   function dataReceivedB(err, data) {
      conn.end()
      callback();
   }
}

If you want to entirely hide your database connection in your model code. Then you would need to doe something like that:

var conn = myModel.connect();
conn.getData(params, function(err, data) {
   conn.end();
})

How to actually solve this depends only many factors so it is only possible to give you hints here.