Adding Postgres triggers capability to Wappler

Wappler Extensions
1

Hello all,

I have a scenario where both Wappler and other services (written in Python & Java) write to the same Postgres database.

In this scenario I'd love for Wappler to know when the database is updated via the other services and to then push real-time status updates to the app UI using the sockets capability in Wappler.

There's a Postgres feature called triggers that can be used to notify a Node.js function when selected tables are updated. The SQL code for creating the trigger function looks like this:

CREATE OR REPLACE FUNCTION notify_change()
RETURNS trigger AS $$
DECLARE
    payload JSON;
BEGIN
    payload = json_build_object(
        'table', TG_TABLE_NAME,
        'operation', TG_OP,
        'data', row_to_json(NEW)
    );
    PERFORM pg_notify('table_update', payload::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

You then attach the trigger to specific actions on a table (the notifications table in this case):

CREATE TRIGGER user_update_trigger
AFTER INSERT OR UPDATE OR DELETE ON notifications
FOR EACH ROW EXECUTE FUNCTION notify_change();

In Node.js the following code connects to the database and receives the trigger updates in real-time:

const http = require('http');
const { Client } = require('pg');
const pgClient = new Client({
    // connection details go here
}); 

pgClient.connect();
pgClient.query('LISTEN table_update');
pgClient.on('notification', (msg) => {
    const payload = JSON.parse(msg.payload);
    console.log('Notification received');
    console.log(payload);
});

The question I would like to pose is: Could the above Node.js code be turned into a Wappler extension/custom-module (I'm not sure what the correct terminology is)? The extension should use the database connection configured in Wappler and provide the received payload as a JSON object which can be used by subsequent actions in the API end-point workflow?

I don't have any experience in building Wappler extensions, and don't really have the time to learn it from scratch. So any pointers on how to do this would be very much appreciated.

2 Likes
2

No takers for this question?

I'd be great to get some steer on the best way to do this. It doesn't look like a lot of code, so hopefully it shouldn't take long to implement.

Anyways, it'd be great to get a little input from anyone who has experience in Wappler extensions.

Thanks!

3

Your idea goes a little above a standard Wappler extension, someone like @patrick would need to clarify if this is possible

If you're looking to hire someone, you can send a message to @tbvgl, he fits the criteria for developing such an unusual extension

Edit: My personal suggestion is to make a separate, auxiliary NodeJS app that calls your Wappler app Server Action to send an update when the data changes, this way it might be easier to automate without performing too many changes to the Wappler project itself

3 Likes
4

Create a js file in the folder extensions/server_connect/sockets

Add the following code, based on your example:

exports.handler = function (io) {
  const { Client } = require('pg');
  const pgClient = new Client({
    // connection details go here
  }); 

  pgClient.connect();
  pgClient.query('LISTEN table_update');
  pgClient.on('notification', (msg) => {
    io.emit('table_update', msg.payload);
  });
};

The server will now emit a websocket event table_update with the payload to the client.

2 Likes
5

Hi Patrick, this is great, thank you.

Is there a way to grab the current database connection from within the code?

Thanks,
Scott

6

I'm afraid not, the database connection is set only in server actions and not available within the server hooks.

7

Best way is to have the database settings in Server Environment variables which you can access using process.env and using $_ENV in expressions which you can use for your database connection of server connect.

2 Likes
8

Great stuff. Thank you Patrick!