14

I have a node.js server that connects to a mysql database and opens up a new socket using socket.io. The role of this server is basically to notify any client (user) that connects to it when there's a new message for that user in the database table. The code below only works if the client explicitly emits a 'check_messages' request. How can I change it so that the client is the one that's notified whenever a new message is inserted in the mysql table for that user instead of the client having to explicitly emit a 'check_messages' request?

var app = require('http').createServer().listen(8124);

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'some username',
  password : 'some password',
  database : 'some database'
});

connection.connect();

console.log('Server running at http://127.0.0.1:8124/');

var io = require('socket.io').listen(app);

var prev_id = 0;

io.sockets.on('connection', function (socket) {
  socket.emit('greeting', 'Hello');
  socket.on('check_messages',function(data){
  var uid = data['uid'];
  var q = "SELECT * FROM messages WHERE user_id=" + uid + " ORDER BY id DESC LIMIT 1";
  connection.query(q, function(err, rows, fields) {
      if (err) throw err;
      if (rows[0].id > prev_id){
        socket.emit('new_message',rows[0]);
        prev_id = rows[0].id
      }
    });
  });
});
zavg
  • 10,351
  • 4
  • 44
  • 67
Nirav Bhatia
  • 1,003
  • 3
  • 13
  • 25

2 Answers2

4

If you don't want to do any polling on the database you can use the postgresql database which support listen/notify. You will be notified immediately when there is a modification on the table.

leszek.hanusz
  • 5,152
  • 2
  • 38
  • 56
3

You can run your code in timer's event handler on server.

The code below checks database for new message for every 5 seconds and emits event if necessary

io.sockets.on('connection', function (socket) {
  socket.emit('greeting', 'Hello');
  setInterval(5000,function(data){
  var uid = data['uid'];
  var q = "SELECT * FROM messages WHERE user_id="+uid+" ORDER BY id DESC LIMIT 1";
  connection.query(q, function(err, rows, fields) {
      if (err) throw err;
      if (rows[0].id > prev_id){
        socket.emit('new_message',rows[0]);
        prev_id = rows[0].id
      }
    });
  });
});

As alternative way I think you could implement message queueing using redis with the fast node_redis client. It has built-in pubsub semantics.

Look at Redis. It is fast NoSQL key-value storage which you can use to organize fast message queueing. Use node_redis npm module to communicate with it. Read this reference

zavg
  • 10,351
  • 4
  • 44
  • 67
  • 2
    I thought of that but is there a way to notify the client exactly when the row gets inserted in the table instead of the server checking the database? Meaning, is there some kind of a mechanism that when a row is inserted in the table, a notification is sent to the server and the server in turn sends a notification back to the client? – Nirav Bhatia May 10 '13 at 00:26
  • @user791345 I think you should implement message queueing using redis with the fast node_redis client. It has built-in pubsub semantics. – zavg May 10 '13 at 00:31
  • Can you provide more details? Sorry I'm new to node and its modules. – Nirav Bhatia May 10 '13 at 00:38
  • @user791345 look at http://redis.io/ Redis is fast NoSQL key-value store which you can use to organize fast message queueing. use node_redis (https://github.com/mranney/node_redis) npm module to communicate with it. Read this reference http://stackoverflow.com/questions/4441798/how-to-use-redis-publish-subscribe-with-nodejs-to-notify-clients-when-data-value/4446424#4446424 – zavg May 10 '13 at 00:42
  • I think it's not effective to query every 5 seconds. signalrjs should be more effective. https://www.npmjs.com/package/signalrjs – CMartins Nov 28 '17 at 14:57