Postgres Listen/Notify for automatic updates

Client Updates with Listen - Notify in PostgreSQL.md

Listen / Notify for automatic object updates - pattern in postgreSQL Presented at the San Francsico PostgreSQL User Group Meetup 8/17/15

by Eric Eslinger eric.eslinger@gmail.com github.com/ericeslinger

This example uses listen/notify calls in PostgreSQL that trigger on row update to pass events from the database to a listening node.js web application that in turn uses socket.io to pass events to connected angular.js applications.

This code is not complete, but written for illustrative purposes. It is Copyright (c) 2015 Eric Eslinger, released under the MIT license.

client.js

// angular.js uses its own eventing model
// this is in the global socket service:
let socket = io();
socket.on('modelUpdate', function(channel, update) {
  $rootScope.$emit(`${channel.type}.${channel.id}`, update);
});

// in the model object
$rootScope.$on(`${this.$type}.${this.$id}`, (evt, delta)=> {
  $rootScope.$apply(() => { 
    delta.keys.forEach((key) => {
      this[key] = delta.updateContents[key];
    });
  });
});

notify.sql

CREATE FUNCTION notify_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $_$
BEGIN
  PERFORM pg_notify('update_watchers',
    $${"eventType":"update", "item":{"type": "$$ || TG_TABLE_NAME
    || $$", "id": $$ || new.id
    -- only pass along the names of the columns that changed, as notify has an 8000 byte limit
    || $$}, "keys": $$|| to_json(akeys(hstore(NEW) - hstore(OLD))) ||$$} $$);
  RETURN new;
END;
$_$;

CREATE TRIGGER profile_update 
AFTER UPDATE ON profiles
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*) -- may not want to notify on all changes
EXECUTE PROCEDURE notify_update();

server.js

function start() {
  pgClient.connect();
  pgClient.query('listen "update_watchers"');
  pgClient.on('notification', handleObjectUpdate);
}

function handleObjectUpdate(mess) {
  TrellisModel.childConstructors[mess.item.type].forge({id: mess.item.id}).fetch()
  .then(function(changedModel) {
    mess.updateContents = {};
    mess.keys.forEach(function(key) {
      mess.updateContents[key] = changedModel.get(key);
    });
    Io.to(`model:${mess.item.type}:${mess.item.id}`).emit('modelUpdate', mess.item, mess);
  });
}