Push notification server on a MySQL database

nodejs-mysql

Thanks to the success of my previous post about a Node.js push notification server, now in this post I would go further realizing a server that is able to read from a MySql database and then stream those data via websocket to many client connected on the same page. The idea behind this shit is that we will broadcast our data without having any timer on the client but only a ( kind of )timer on the server.
First of all we must install the modules needed for this example.

This node js module is needed to read a MySQL database

$ npm install mysql@2.0.0-alpha9

And then Socket.io to deal with the HTML5 websocket

$ npm install socket.io

Once we have all the nodejs modules installed we could set the Javascript code to run on the server.
The first part of our code is needed to set up the connection with database and to load the html ( client.hmtl ) page in which we are going to print the data fetched with our MySql queries.

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',
        port        : 3306
    }),
    POLLING_INTERVAL = 3000,
    pollingTimer;

// If there is an error connecting to the database
connection.connect(function(err) {
  // connected! (unless `err` is set)
  console.log( err );
});

// create a new nodejs server ( localhost:8000 )
app.listen(8000);

// on server ready 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 );
    });
}

The remaining part of our code creates recursively a loop that will keep always up to date all the clients connected on the application.
The polling loop keeps pushing data only if there are still clients connected to the application, otherwise it breaks itself since at least a new client socket connection is detected. What it is important is that there is only one ,and no more than one, timer updating all the clients treating all of them at same level without any difference, broadcasting the same data to all of them.

/*
*
* HERE IT IS THE COOL PART
* This function loops on itself since there are sockets connected to the page
* sending the result of the database query after a constant interval
*
*/

var pollingLoop = function () {
   
    // Make the database query
    var query = connection.query('SELECT * FROM users'),
        users = []; // this array will contain the result of our db query


    // set up 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( user ) {
        // it fills our array looping on each user row inside the db
        users.push( user );
    })
    .on('end',function(){
        // loop on itself only if there are sockets still connected
        if(connectionsArray.length) {
            pollingTimer = setTimeout( pollingLoop, POLLING_INTERVAL );

            updateSockets({users:users});
        }
    });

};

// create a new websocket connection to keep the content updated without any AJAX request
io.sockets.on( 'connection', function ( socket ) {
   
    console.log('Number of connections:' + connectionsArray.length);
    // start the polling 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 ) {
    // store the time of the latest update
    data.time = new Date();
    // send new data to all the sockets connected
    connectionsArray.forEach(function( tmpSocket ){
        tmpSocket.volatile.emit( 'notification' , data );
    });
};

The client javascript code is pretty easy to understand: it creates a new websocket connection with the server and prints the data received from the database queries.

<html>
    <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');
        // 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,user){
            usersList += "<dt>" + user.user_name + "</dt>\n" +
                         "<dd>" + user.user_description + "\n" +
                            "<figure> <img class='img-polaroid' width='50px' src='" + user.user_img + "' /></figure>"
                         "</dd>";
        });
        usersList += "</dl>";
        $('#container').html(usersList);
   
        $('time').html('Last Update:' + data.time);
      });
    </script>
    </body>
</html>

I have made this short screencast to show you how to run this example code and so you are not forced to download the project to see how it works ;-)

By the way the code used in this demo is on github and you can download and fork it as you like.

Download

P.S. IMPORTANT
This code could be easily extended and used for any kind of application but it needs to be optimized in order to work also on big websites.

56 Comments

  • Pingback: Nodejs and a simple push notification server | Gianluca Guarini's Blog

  • Ciao Gianluca,
    sono Enrico un ICT Manager di un’azienda nel settore delle spezioni e stavo cercando nel web qualche ispirazione per un mio progetto
    per la gestione delle risorse umane ed ecco apparire:

    “Push notification server streaming on a MySQL database”

    Davvero molto interessante e sembra essere molto azzeccato nel poter essere convertito in un modulo console che dovrebbe loggare
    eventi generati da altri moduli già sviluppati da me ed i miei collaboratori in precedenza.

    Premetto da subito che non sono uno sviluppatore web quindi non sono un guru in materia e da questa considerazione vengo alla mia domanda:

    Avrei la necessità di modificare il codice affinchè si potesse agguingere un pulsante di avvio della console ( una sorta di live view ) che potesse anche essere messa in pausa nella visualizzazione degli eventi generati e che si potesse fare un clear e non visualizzare più i vecchi eventi se già mostrati in precedenza ( per esempio verificando se nel database sia presente un campo che identifica se sia già stato visualizzato ed eventualmente aggiornarlo dopo la scrittura a video.

    Sarebbe anche interessante se si potesse visualizzare immediatamente l’utlimo evento ricevuto ( una sorta di focus oppure come una qualsiasi shell) ovvero che dopo aver riempito la pagina l’ultimo evento in basso fosse sempre visibile.

    Dato la mia inesperienza potresti darmi qualche suggerimento?
    Ritieni che queste mie esigenze necessitino di un cambiamento di codice modesto?

    Indipendentemente da un tuo feedback davvero complimenti per il tuo lavoro.

    Grazie

    • Ciao Enrico,
      innanzitutto sono contento che il mio post ti sia stato d’ispirazione per una tua idea però in secondo luogo mi trovo spiazzato nel doverti aiutare in qualcosa di così complesso poiché da come mi hai confermato sembri non essere esperto riguardo le tecnologie web. In ogni caso credo che per ciò che tu voglia fare non vi servirà modificare di molto il codice originale, in fondo le tue richieste sono abbastanza semplici partendo dall’esempio che mostro nel video. In bocca al lupo per il tuo progetto.

      • Grazie Gianluca dell’incoraggiamento. Diciamo che non ho grande esperienza di javascript e tecnologie realtime ma mi sono spesso cimentato in progetti web.
        Spero in caso possa disturbarti per qualche chiarimento o consiglio.
        Ciao e grazie ancora

  • I’m now not certain where you’re getting your info, however great topic. I needs to spend a while finding out more or working out more. Thanks for fantastic info I used to be on the lookout for this info for my mission.

  • Interessante post, l’ho scoperto per caso… mi sto documentando un pò su nodejs in questo periodo e mi sarà di grande aiuto!
    Complimenti per il lavoro ;)

  • interested post…
    i’m newbie in this case..i want ask something…
    how to install “npm install socket.io” on cpanel ?

    thanks..

    • I don’t think you can install socket.io on cpanel. Get ssh access to your account and then install node.js then install socket.io

  • Thanks a bunch for sharing this with all people you really recognize what you are speaking about! Bookmarked. Please also seek advice from my web site =). We will have a link exchange contract between us

  • Articolo interessante e colgo l’occasione per complimentarmi per questo sito! veramente ben fatto e con tanti articoli utili!

  • Hi Gianluca,

    Just discovered node.js and am realising the awesome potential of it. Your tutorial is fantastic!

    Any idea how it would handle large loads eg. 100,000 plus users?

    Many thanks

    Silby

    • Hi Silby, node.js is just the way to handle all these connections, your question is more regarding the hardware technology you can use to achieve that. Anyway you can always control the number of connections using node.js and blocking all the connections that exceed your limit. Someone has tested successfully 1m connections on a single 15GB rackspace cloud server. Good luck

  • Nice tutorial. By the way how you are using phpmyadmin with node.js?

  • Pingback: Database change notification app - Database Solutions - Developers Q & A

  • Nice tutorial, and help me How to make multi event’s like push the user-online and count user-online ?

  • Hi, thank you for a good example, but i noticed some problems. In the first i see that server makes request every time after inserted data, this you can see at tab Network Google Chrome. Why Rwquest returns only last note?
    And how is critically make every time request to DB? May be better use any DB , for example MongoDB or Redis?
    Can you help me at choosing correct way to resolve it?

    • Mine was just a simple example to show how node.js could easily work even with a MySQL database. I am happy you liked it but as far as I understand your request it seems that you want to see how a DB can be able to trigger events without using with a timer. My approach is the safest because I just broadcast the db data to any user connected to the application without set any listener on the database itself, in this way you do not need to care too much about the amount of connections because all the notifications will be always one-to-all (db-to-clients). Instead if you want to realize custom connections on the db listening the data changes ( INSERT UPDATE DELETE ) you should use a multi thread approach maybe using https://github.com/laverdet/node-fibers and I suggest you to try MongoDB or CouchDB that seem to work pretty well and they could give you better performances

      • Ciao Gianluca,
        recentemente ho ripreso in mano la cosa e mi sono fatto un po’ di background in merito per poter completare il mio progetto.
        Ho solo una domanda alla quale sono certo mi potrai rispondere.

        Ho notato che, dopo l’avvio del socket, quest’ultimo dopo un certo tempo si chiude per il raggiungimento di un timeout.
        Dato che la mia applicazione dovrà mantenere sempre il socket aperto o meglio dovrà aprirlo quando almeo un client è connesso mi chiedevo dove impostare questa possibilità.

        il messaggio visualizzato e del tipo:

        info: transport end (undefined)
        debug: set close timeout for client po6xlsm1c4q_GhkQHO48
        debug: cleared close timeout for client po6xlsm1c4q_GhkQHO48
        debug: cleared heartbeat interval for client po6xlsm1c4q_GhkQHO48
        socket = 0 disconnected
        debug: discarding transport

        Grazie

        • Fai un update alla ultima versione di Socket.io e vedi se va meglio. Che versione di node stai usando? Browser ? OS? Server? nel tuo post non ci sono abbastanza info per aiutarti

          • Ho fatto un aggiornamento alla versione di socket.io
            Node è alla versione 0.10.15
            Browser Firefox 24
            OS Server Debian 7

            Se hai info aggiuntive fammi sapere.
            Nel frattempo vedo come va ora

            Grazie

  • I have tried this code but it does not return my query. Upon loading localhost:8000, I only see “Loading…” and the node console only says this, the debug message comes up every time I reload the page:

    info – socket.io started
    null
    debug – served static content /socket.io.js
    debug – served static content /socket.io.js
    debug – served static content /socket.io.js

    MySQL connection is verified working as is the query I am attempting. Any help? Thanks

    • make sure your mySql server is running on port 3306.. by the way I have released a new version of the example files updating all the dependencies let me know if it works

      • Thanks for following up with me.

        MySQL DB server is verified working, I have several other sites using it. I can run the query against the DB and it returns the rows as expected.

        # node server.js
        info – socket.io started
        null
        debug – served static content /socket.io.js

        I also tried running ‘node debug server.js’:

        # node debug server.js
        c
        < info: socket.io started
        c

        I added a console.log line to fire here, but it never does (correct me if I am doing this wrong):

        var pollingLoop = function () {

        // Doing the database query
        var query = connection.query(‘SELECT * FROM players_info’),
        users = []; // this array will contain the result of our db query
        console.log(query);

        It seems like something just isn’t getting called or I am missing something else. Thanks for your help

          • Hi, I tried this again on a completely different host, freshly cloned the git repository and ran the npm installs, it does not run the query against the database. I have confirmed that it does authenticate/connect to the database, but does not actually run the query here:

            var pollingLoop = function () {

            // Doing the database query
            var query = connection.query(‘SELECT * FROM users’),
            users = []; // this array will contain the result of our db query

            I verified this via tcpdump — no packets get sent when I load this page after the initial db auth/connection. Any ideas? Thanks.

  • Pingback: ECONNREFUSED mysql nodejs | Technology & Programming

  • Hi Gianluca Guarini, its a nice article and is very useful too. I tried it out on my local and have a concern,
    i have a folder on my localhost that have three files client1, client2 and client3.html. I want to push notification only to client1 and client2, eventhough client3 is opened. Now i am getting the push notification on all the three clients.html.

    How to push only to specific files.

  • need help with auction site pushing real time data from the database, can i get your direct email and skype
    great work looks like exactly what we need

  • Hi!
    First of all thanks for these type of code, it’s really very good!
    I want to know if it is any way of make a request from the server to the mysql server and that refresh do not show on the client.html. Just show the changes that a user make on the mysql, only.
    I hope you can help me please.

  • I have spent ages trying to understand node.js with MySql but this was perfect. I have a load of questions that I will try to figure out myself first but firstly, how do I get the script to run on a specific page? It works fine using localhost:8000 but obviously would want to call it from a specific web page.

  • Hi Gianluca Guarini,
    Very Nice Post.
    But I tried this example in MSsql using MSSQL module. It is not working fine.

    Can you help me in MSSQL example.

  • Thanks for your great script bro. I need a help. Suppose node returns ‘you have 3 users’ when i run http://127.0.0.1:8000. Now how can i show this in my header section of my site?

    • Just change this part of the code:

      var updateSockets = function ( data ) {
          // store the time of the latest update
          data.time = new Date();
          // number of the users connected
          data.connections = connectionsArray.length;
          // send new data to all the sockets connected
          connectionsArray.forEach(function( tmpSocket ){
              tmpSocket.volatile.emit( 'notification' , data );
          });
      };

      On the frontend you should be able to print this new value anywhere you want

  • Thanks, its a great tutorial.I need a help. i want to send parameter from client side html file(making a dropdown and on select send data with ajax to server side js) to server side js….. and pass that parameter to sql query…which i have done.Problem here is that this condition is not working with multiple user bcoz on sending paramter to sql query it will send same data to all user (but different user have different condition)
    so how multiple users pass multiple parameter to sql using nodejs………
    Thanks

    • I don’t know how I may help you even because I don’t really understand what you need to do.. anyway your problem is not directly related to my code but I would suggest you to start from something a bit simpler. Good luck

  • These are not push notifications. You are polling. And polling a database is a terrible practise. Please use a message queue.

  • hello sir,

    IS it possible to do Push notification server on a SQL SERVER database…….Meams doing the same process as you do..but use SQL server instead of mysql.

  • Hello,
    I try to run your code, they are so good with large connection with loop,

    I just want to know how can i make select query as per userid,

    i just want to send data with http://yoursiteurl:8000/?user_id=24
    and want to get this id and use in mysql query,
    can you please provide any solution, i try with some codes with url but it stoped the code, can you please help me how can i use this id and make select query as per user.

    sorry for bad english.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>