Node-RED listen PostgreSQL in real-time

Question

I need to listen PostgreSQL on changes in real-time with Node-RED. How can I do this?

I created trigger on new record in the table and notify this to 'changes' channel.

CREATE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify('changes', TG_TABLE_NAME || ',id,' || NEW.id );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watched_table_trigger AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

But I don't know how to listen it from Node-RED. Could you help me please? Maybe I can do it differently?

Answer 1

I found a good solution for yourself with WebSocket. Look at example below:

var pg = global.get('pg'),
    WebSocket = global.get('ws'),
    config = {
        user: 'user',
        password: 'user',
        host: 'somehost',
        port: 1234,
        database: 'somedb'
    },
    client = new pg.Client(config);

client.connect(function(err) {
    if (err) node.error(err);

    client.on('notification', function(msg) {
        node.send(msg);
    });

    var query = client.query("LISTEN changes");
});

delete msg._session;
return msg;

Post your solution, I really want to know more ways to solve this.

Answer 2

Have a look at this previous SO question:

MQTT Client subscribe to PostgreSQL DB Changes

It looks like Postgress supports Python based triggers which could be used to send a MQTT message which Node-RED could easily subscriber to.