How to use only one DB connection per web socket?

Question

I am using socket.io in my Express.js web app to open sockets for clients that are listening for events that call a SQL query. The server socket looks like this:

socket.on('updates', function(PEMSID){
        setInterval(function(){
            gps_helper.get_gps(PEMSID, function(data){      //query GPS table for marker coordinates
                socket.emit('message', {message: data});
            });
        }, 10000);

So, every ten second the get_gps() function is being called that does long polling to check if any new records have been added to a table. The get_gps() function looks like this:

exports.get_gps = function(PEMSID, callback) {
    var sql = require('msnodesql');
    var connStr = "Driver={SQL Server Native Client 11.0};Server=myHost,1433;Database=myDB;UID=Username;PWD=Password;";
    var mQuery = "EXEC Z_CoordsByPEMS '" + PEMSID + "'";
    sql.open(connStr, function(err,conn){
        if(err)
            return console.error("Could not connect to sql: ", err);
        conn.query(mQuery,function(err,results){
            if (err)
                return console.error("Error running query: ", err);
            callback(results);  //return query results
        });
    });
}

The problem I'm encountering is the get_gps() is opening a new connection to the SQL db everytime it polls for any updates. This is obviouslly causing insane overhead on the server hosting the db and needs to be changed, as the server's CPU eventually reaches maximum capacity and all future queries time out. I'm using the module msnodesql to do the SQL db tasks, but it doesn't appear there's a close() function in the API to close existing connections. I think I'll need to create one global connection and then have all new sockets reference that in order to do their long polling. I'm unsure however how to set up the global connection, if it's even possible, given the asynchronous nature of Express.js/node.js.

If you want one SQL connection per socket.io connection, move the sql.open call into your on('updates', ...) handler and pass the conn object as an argument into get_gps. If you want one connection globally, do the same thing, but move the sql.open to the top of your code and place all your other code inside the connection callback. — apsillers
– apsillers, Commented Nov 5, 2013 at 13:50
@apsillers, Yep, that worked. Please put your answer in an 'answer' so I can give you credit. Thanks! — gjw80
– gjw80, Commented Nov 5, 2013 at 14:00

apsillers · Accepted Answer · 2013-11-05 14:03:57Z

Currently, you're creating a new SQL connection for each query your send. Instead, you've said you want to have one SQL connection per socket.io connection. Create the SQL connection whenever you get an updates request (assuming you only get this message once per socket.io connection):

socket.on('updates', function(PEMSID){
    var sql = require('msnodesql');

    // open a new socket for each `updates` message
    sql.open(connStr, function(err,conn){
        if(err) { return console.error("Could not connect to sql: ", err); }

        // when the connection is made, start the interval
        setInterval(function(){

            // each interval, get_gps, and pass `conn` as an argument
            gps_helper.get_gps(conn, PEMSID, function(data){      //query GPS table for marker coordinates
                socket.emit('message', {message: data});
            });
        }, 10000);
    });
});

Then your get_gps function will accept a conn argument:

exports.get_gps = function(conn, PEMSID, callback) {
    var connStr = "Driver={SQL Server Native Client 11.0};Server=myHost,1433;Database=myDB;UID=Username;PWD=Password;";
    var mQuery = "EXEC Z_CoordsByPEMS '" + PEMSID + "'";

    // no need to open `conn`; it's already made
    conn.query(mQuery,function(err,results){
        if (err)
            return console.error("Error running query: ", err);
        callback(results);  //return query results
    });
}

If you want one global SQL connection, simply move the sql.open call even higher:

// create conn outside of the socket.io message callback
sql.open(connStr, function(err,conn){
    if(err) { return console.error("Could not connect to sql: ", err); }

    io.sockets.on("connection", function(socket) {

        socket.on('updates', function(PEMSID){
            setInterval(function(){
                gps_helper.get_gps(conn, PEMSID, function(data){      //query GPS table for marker coordinates
                    socket.emit('message', {message: data});
                });
            }, 10000);
        });

    });
});

Collectives™ on Stack Overflow

How to use only one DB connection per web socket?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related