CODEWITHSUNDEEP
node.jspostgresqlnode-redreal-time-data
GatoPresto
GatoPresto

Reputation: 108

Node-RED listen PostgreSQL in real-time

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?

Upvotes: 2

Views: 2393

Answers (2)

GatoPresto
GatoPresto

Reputation: 108

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

enter image description here

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.

Upvotes: 2

hardillb
hardillb

Reputation: 59751

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.

Upvotes: 2

Related Questions