This post shows you how to set up PostgreSQL so it triggers a notify event along with additional information to a listening Node.js script. The script will broadcast this information over a WebSocket to the user's browser, all in real time.

PostgreSQL

PostgreSQL allows you to listen for and broadcast notify events. It is as simple as running:

LISTEN myevent;

Then when...

NOTIFY myevent;

...is called any listeners on the myevent channel will be notified along with any attached message.

Firstly create a database table and a stored procedure to issue a notify event. Then add a trigger so that the stored procedure is called after every insert on the table:

CREATE TABLE realtime(
    id SERIAL NOT NULL PRIMARY KEY,
    title character varying(128)
);

CREATE FUNCTION notify_realtime() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    PERFORM pg_notify('addedrecord', NEW.title);
    RETURN NULL;
END;
$$;

CREATE TRIGGER updated_realtime_trigger AFTER INSERT ON realtime
FOR EACH ROW EXECUTE PROCEDURE notify_realtime();

Here a channel called addedrecord is used to broadcast the title of a newly inserted record.

Next: setup Node.js to listen on addedrecord.

Node.js

The socket.io library is recommended to communicate over WebSockets as it offers the best cross-browser compatibility. Mainly because if the user's browser does not support WebSockets socket.io will fall back to the following options: XHR polling, XHR multipart, Htmlfile, FlashSocket and JSONP polling. This article explains each method.

node-postgres is used to connect to the PostgreSQL database and listen for any notify events on the addedrecord channel. Here is the script:

var io = require('socket.io').listen(9000);
var pg = require ('pg');

var con_string = 'tcp://username:password@localhost/dbname';

var pg_client = new pg.Client(con_string);
pg_client.connect();
var query = pg_client.query('LISTEN addedrecord');

io.sockets.on('connection', function (socket) {
    socket.emit('connected', { connected: true });

    socket.on('ready for data', function (data) {
        pg_client.on('notification', function(title) {
            socket.emit('update', { message: title });
        });
    });
});

The PostgreSQL connection is initialised and listens on the addedrecord channel. When a client connects, the server broadcasts an event named connected along with a message (an object with a variable called connected set to true). Clients listen for this event and act accordingly.

The server expects a connected client to broadcast a ready for data event at some point in the future. On receipt of this and a notify event from PostgreSQL the server will broadcast an update event along with a message, in this case an object containing the newly inserted record's title.

Here is the client side JavaScript to hook this all together:

<script src="inc/socket.io.js"></script>
<script>
    WEB_SOCKET_SWF_LOCATION = 'inc/WebSocketMain.swf';
    var socket = io.connect('http://localhost:9000');
    socket.on('connected', function (data) {
        socket.emit('ready for data', {});
    });
    socket.on('update', function (data) {
        console.log(data.message.payload);
    });
</script>