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:
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.
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.
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 (
delete), the schema and table name and the current and old values of the row are some examples of the JSON object.
json_build_objectfunction is used to create JSON objects from one or more key-value pairs. This function takes any number of arguments (where each is a key-value pair) and returns a JSON object containing the specified key-value pairs.
Finally, we use
pg_notify function to send the notification message to a channel named
db_event, passing the payload variable as a message.
pg_notifyfunction is used to send a notification message to a channel. It takes two arguments – The name of the channel to which the message is to be sent and the message itself.
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.
This creates a new trigger on the PostgreSQL database. The trigger named
user_notify is defined to execute
notify_trigger function in response to
DELETE event on the
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
pg-listen package using the following command:
yarn add express pg pg-listen
index.js file, where we will write our event listeners.
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.
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
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:
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.