NodeJS + MySQL + Socket.IO : Update database

Question

I'm currently looking for a solution about push notifications with NodeJS and MySQL Database.

I want to combine NodeJS with Socket.IO to provide push notifications but the problem is that I don't know how to ask my server to check if there is update from my database.

I already have a "polling" method that works perfectly but it's a little messy and this is not really optimize in terms of server calls and responses.

So the idea is that when user A insert something in my database, all clients who follow him get notified by push (push, not poll.).

This is what I've got for the moment for my server.js :

var app                 = require('http').createServer(handler),
    io                  = require('socket.io').listen(app),
    fs                  = require('fs'),
    mysql               = require('mysql'),
    connectionsArray    = [],
    connection          = mysql.createConnection({
        host        : 'localhost',
        user        : 'root',
        password    : 'root',
        database    : 'nodejs'
    }),
    POLLING_INTERVAL = 5000,
    pollingTimer;
connection.connect(function(err) {
  console.log( err );
});
app.listen(1337);
function handler ( req, res ) {
    fs.readFile( __dirname + '/client.html' , function ( err, data ) {
        if ( err ) {
            console.log( err );
            res.writeHead(500);
            return res.end( 'Error loading client.html' );
        }
        res.writeHead( 200 );
        res.end( data );
    });
}
var pollingLoop = function () {
    var query = connection.query('SELECT notif FROM notifications WHERE id_user=1 AND status=0'),
        users = [];
    .on('error', function(err) {
        console.log( err );
        updateSockets( err );
    })
    .on('result', function( user ) {
        users.push( user );
    })
    .on('end',function(){
        if(connectionsArray.length) {
            pollingTimer = setTimeout( pollingLoop, POLLING_INTERVAL );
            updateSockets({users:users});
        }
    });
};
io.sockets.on( 'connection', function ( socket ) {
    console.log('Number of connections:' + connectionsArray.length);
    if (!connectionsArray.length) {
        pollingLoop();
    }
    socket.on('disconnect', function () {
        var socketIndex = connectionsArray.indexOf( socket );
        console.log('socket = ' + socketIndex + ' disconnected');
        if (socketIndex >= 0) {
            connectionsArray.splice( socketIndex, 1 );
        }
    });
    console.log( 'A new socket is connected!' );
    connectionsArray.push( socket );
});
var updateSockets = function ( data ) {
    data.time = new Date();
    connectionsArray.forEach(function( tmpSocket ){
        tmpSocket.volatile.emit( 'notification' , data );
    });
};

If you have any advice, solutions or something that can be useful, don't hesitate.

Thanks in advance

Answer 1

I can think of two solutions here.

1) Initiate the push from the same request that uploaded the new information. What I mean here is that your flow right now is probably:

1. Get request from client
2. Insert stuff into DB
3. Return to client

With Node you can do more stuff after the response has been sent. For instance 1. Get request from client 2. Insert stuff into DB 3. Return to client 4. Find all subscribers who are subscribing and current signed in 5. Send pushes

2) This approach is more distributed. You can use a pub/sub method for the last steps. Have a different process handling all the pushes, and have your main process (the part that handles the update requests) publish the new content into a queue. This would alert your pushing process everytime there is new content, which is much better than polling your database.

Note that for either of these solutions you will have to find a good way of keeping track of which sockets are connected to which clients, so that you and quickly send messages to a specific client from outside their socket. A quick way of doing this may be to add each user into a room of their own, named after their username or user_id, so you can easily push the update to all participants of the rooms named after subscribed users. If no one is in the room , no requests are sent.