Notify Triggers in PostgreSQL

In PostgreSQL, a NOTIFY trigger is the type used to asynchronously notify clients of changes made to a database table. This can be useful for building real-time applications or triggering other processes in response to changes in the database.

The basic syntax for creating a NOTIFY trigger is as follows:

CREATE TRIGGER trigger_name 
AFTER INSERT|UPDATE|DELETE 
ON table_name 
FOR EACH ROW 
EXECUTE FUNCTION pg_notify(channel_name, payload);

Implementing pg_notify using Node.js Client

Let's create a table named table_user with three columns: ID, username, and an implicitly created serial column to auto-increment the ID.

CREATE TABLE table_user (
  id              SERIAL PRIMARY KEY,
  username           VARCHAR(100) NOT NULL,
);

Setting up triggers

Triggers can be added to this table to execute certain actions automatically in response to certain events. For example, a trigger could be added to log all changes made to the table_user table or to enforce certain business rules on the data being inserted or updated in the table.

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $trigger$
DECLARE
  rec table_user;
  dat table_user;
  payload TEXT;
BEGIN

  -- Set record row depending on operation
  CASE TG_OP
  WHEN 'UPDATE' THEN
     rec := NEW;
     dat := OLD;
  WHEN 'INSERT' THEN
     rec := NEW;
  WHEN 'DELETE' THEN
     rec := OLD;
  ELSE
     RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
  END CASE;

  -- Build the payload
  payload := json_build_object('timestamp',CURRENT_TIMESTAMP,'action',LOWER(TG_OP),'db_schema',TG_TABLE_SCHEMA,'table',TG_TABLE_NAME,'record',row_to_json(rec), 'old',row_to_json(dat));

  -- Notify the channel
  PERFORM pg_notify('db_event', payload);

  RETURN rec;
END;
$trigger$ LANGUAGE plpgsql;

The function uses CASE statement to determine the type of event that occurred.

For example, if the trigger was fired by UPDATE event, the record variable rec is set to the new value of the row and data variable dat is set to the old value of the row.

This function builds the notification message payload by using the json_build_object function to create a JSON object containing information about the trigger event. The timestamp of the event, the type of event (insert, update, delete), the schema and table name and the current and old values of the row are some examples of the JSON object.

Finally, we use pg_notify function to send the notification message to a channel named db_event, passing the payload variable as a message.

The function then returns current or old values of the row, depending upon the type of trigger event that occurred.

We will create a new trigger, calling notify_trigger on the trigger change event.

CREATE TRIGGER user_notify AFTER INSERT OR UPDATE OR DELETE 
ON table_user
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

This creates a new trigger on the PostgreSQL database. The trigger named user_notify is defined to execute notify_trigger function in response to INSERT, UPDATE or DELETE event on the table_user table.

Handling incoming notifications

We will now make a listener in Node.js, which will listen for the notification event called from the PostgreSQL trigger.

First, initialize the empty project.

npm init -y

Then install express, pg, pg-listen package using the following command:

yarn add express pg pg-listen

Create index.js file, where we will write our event listeners.

touch index.js

We will use pg-listen library for listening notifications from a PostgreSQL database. This library allows us to set up listeners for specific or all notifications on a database connection.

  const eventName = "db_event";

  //  Create listener for db
  const subscriber = createSubscriber({
    connectionString: `postgres://${DATABASE_USER}:${DATABASE_PASSWORD}@${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE}`,
  });
  await subscriber.connect();
  await subscriber.listenTo(eventName);

Here, createSubscriber function from the pg-listen library is used to create a new subscriber for the db_event channel. The function is passed through the connection string for the database as an argument. The script then uses the listenTo method on the subscriber to listen for notifications on the db_event channel.

  subscriber.notifications.on(eventName, async (data) => {
    console.log(data);
  });

Finally, we end up with an event listener for the db_event channel on the notifications property of the subscriber. This event listener will be called whenever a notification is received on the db_event channel, and it will also log the notification data to the console.

Assembling the codes

The complete code will look like this:

const express = require("express");
const createSubscriber = require("pg-listen");

(async function () {
  const server = express();
  const port = process.env.PORT || 8080;


  const DATABASE_HOST = process.env.DATABASE_HOST;
  const DATABASE_USER = process.env.DATABASE_USER;
  const DATABASE_PASSWORD = process.env.DATABASE_PASSWORD;
  const DATABASE_PORT = process.env.DATABASE_PORT;
  const DATABASE = process.env.DATABASE;


  const eventName = "db_event";

  //  Create listener for db
  const subscriber = createSubscriber({
    connectionString: `postgres://${DATABASE_USER}:${DATABASE_PASSWORD}@${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE}`,
  });
  await subscriber.connect();
  await subscriber.listenTo(eventName);

  subscriber.notifications.on(eventName, async (data) => {
    console.log(data);
  });
    
  server.listen(port, () => {
    console.log("Server listening on port " + port);
  });
})();

Final Outlook

Notify triggers can be a powerful tool for building real-time applications or triggering other processes in response to changes in a database. However, it's important to use them judiciously, as excessive use of notify triggers can cause performance issues in a database system.

For example, consider a scenario where a NOTIFY trigger is used to notify clients of every change made to a database table. If the table is frequently updated, this can lead to a large number of notifications being sent. This can quickly consume system resources and degrade database performance.

Therefore, it's important to consider the frequency and scope of notifications sent via notify triggers and use them only when necessary.

You may want to limit the number of notifications sent by grouping changes together or using a throttling mechanism to limit the rate at which notifications are sent. By using notify triggers thoughtfully, you can take advantage of their power without negatively impacting the performance of your database system.

Thank you for reading! Don't forget to subscribe and leave a comment down below.