Push Data with Long Polling using Node.js, Socket.io and MySQL

You need to push data from your server without the client asking for it, in order to display real time data without a page refresh. One limitation to do this with polling is, your clients have to bash the server with short pauses, asking for a value continuously, however there is an approach around it, by waiting until there really is an update on the database before the server pushes the data to the clients. This approach is called long polling, and it is the way big names like facebook pushes notifications, as well as Gmail displaying new emails without page refreshes etc. In this post, I will keep the text short for you to dig into code straight away.

Server side

The following code is for server.js, our vanilla Node.JS server which allows us to poll the database every second (see POLLING_INTERVAL variable) and only push the response if it is different than previous response. Later on we will see client.html to display the client side of socket.io code we need to push the notification from server.

In this particular example, the business logic is simplified to only display the count of notifications we have from our mysql table, called activity_log, which displays activities taken part on our site by the user, uniquely identified with profile_id column. Obviously, one prerequisite for this to work is to have this table created in your MySql database, with the same columns (notified, profile_id) and values (both columns are int).

var app = require('http').createServer(handler),
io = require('socket.io').listen(app),
fs = require('fs'),
mysql = require('mysql'),
connectionsArray = [],
profile_id,
last_count = 0, //this variable is to check previous count value
connection = mysql.createConnection({
 host: 'localhost',
 user: 'root',
 password: '**********', //put your own mysql pwd
 database: '*******', //put your database name
 port: 3306
}),
POLLING_INTERVAL = 1000,
pollingTimer;

// If there is an error connecting to the database

connection.connect(function(err) {

 // connected! (unless `err` is set)

 console.log(err);

});

// creating the server ( localhost:8000 )

app.listen(8000);

// on server started we can load our client.html page

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);

 });

}

/*

* HERE IT IS THE COOL PART

* This function loops on itself since there are sockets connected 

* to the page. Upon Update it only emits the notification if 

* the value has changed.

* Polling the database after a constant interval

*/

var pollingLoop = function() {

 sql = "SELECT count(id) as c FROM activity_log WHERE notified=0 and (profile_id = '"+profile_id+"')";

 // Doing the database query

 var query = connection.query(sql),

 users = []; // this array will contain the result of our db query

// setting the query listeners

 query

 .on('error', function(err) {

 // Handle error, and 'end' event will be emitted after this as well

 console.log(err);

 updateSockets(err);

 })

 .on('result', function(count) {

 // it fills our array looping on each user row inside the db

 users.push(count); 

 // loop on itself only if there are sockets still connected

 if (connectionsArray.length) {

 pollingTimer = setTimeout(pollingLoop, POLLING_INTERVAL);

 updateSockets({

 users: users,

 count: count.c

 });

 }

 })

};

// creating a new websocket to keep the content updated without any AJAX request

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

//This variable is passed via the client at the time of socket //connection, see "io.connect(..." line in client.html

 profile_id = socket.handshake.query.profile_id;

 console.log('Number of connections:' + connectionsArray.length);

 // starting the loop only if at least there is one user connected

 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) {

 if (last_count != data.count) {

 // adding the time of the last update

 data.time = new Date();

 // sending new data to all the sockets connected

 connectionsArray.forEach(function(tmpSocket) {

 tmpSocket.volatile.emit('notification', data);

 });

 }

 last_count = data.count;

};

Client side

On the client side (see client.html file below), instead of pulling the new data via AJAX, we make use of socket.io to listen to notifications emitted by the server and display the response data in a div. This is enabled by web sockets (socket.io library in this case) and it's a great way to display real-time data on our single page applications.

As stated earlier, if you notice the line where there's a querystring variable called profile_id being sent, this is retrieved via socket.handshake.query.profile_id on server side, via our socket.

<html>
 <head>
 <title>Push notification long polling server streaming on a MySQL db</title>
 <style>
 dd,dt {
 float:left;
 margin:0;
 padding:5px;
 clear:both;
 display:block;
 width:100%;
 }
 dt {
 background:#ddd;
 }
 time {
 color:gray;
 }
 </style>
 </head>
 <body>
 <time></time>
 <div id="container">Loading ...</div>
 <script src="socket.io/socket.io.js"></script>
 <script src="http://code.jquery.com/jquery-latest.min.js"></script>
 <script>
// create a new websocket
 var socket = io.connect('http://localhost:8000/?profile_id=abcde2');
 // on message received we print all the data inside the #container div
 socket.on('notification', function (data) {
 var usersList = "<dl>";
 $.each(data.users,function(index,count){
 usersList += "<dt>" + count.c + "</dt>\n";
 });
 
 usersList += "</dl>";
 $('#container').html(usersList);
 
 $('time').html('Last Update:' + data.time);
 $('sql').html('Last Update:' + data.sql);
 });
 </script>
 </body>
</html>

TLDR: Install nodejs and mysql (and make a db/table as the mysql statement above requires) copy and paste above server (server.js, with mysql and socket.io package dependencies) and client (client.html) codes to experiment with a working prototype of long polling push notifications.

Finally, sorry for the formatting, tabs do disappear when pasted here...