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

can you try with setInterval to recall after some time to get update in database — JayminLimbachiya
– JayminLimbachiya, Commented Nov 27, 2020 at 12:14

Nick Mitchinson · Accepted Answer · 2013-03-16 21:33:17Z

5

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:

Get request from client
Insert stuff into DB
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.

answered Mar 16, 2013 at 21:33

Nick Mitchinson

5,4801 gold badge27 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simon Over a year ago

Thanks for this solutions. I'm going to check both of them to see wich one is going to be more efficient ! The think is that my polling version works perfectly (not really hard) but using NodeJS for poll is a little bit "huge" (I can do poll without NodeJS). If I'm coming with a solution, I'm going to post it here for everyone. thanks again

Nick Mitchinson Over a year ago

The second solution is similar to polling the database, however your application basically says "Hey, there's something new for you now!", instead of you continually asking "Hey, do you have anything for me yet?"

Collectives™ on Stack Overflow

NodeJS + MySQL + Socket.IO : Update database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related