Code With Wolf

How to Listen To Postgres Notifications With Node.js

September 20, 2022

How to Listen To Postgres Notifications With Node.js

This blog post will demonstrate how to create a notification with postgres and listen to that notification with node.js. This is a great solution when you want to run logic on your server in real-time as data is updated in your postgres database.

Create Node.js Project

We will create a new directory and initialize a node.js project in it. 

mkdir demo
cd demo
npm init -y

For simplicity, since this is a small demo, we will add all the files in this directory.

Setup Containers

First, we wil need a docker-compose.yml file.

In this file we will create two services, postgres and server as well as set up their volumes. 

version: '3.8'
services:
  postgres:
    container_name: postgres
    image: 'postgres:12'
    ports:
      - '5432:5432'
    environment:
      - POSTGRES_USER=pguser
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=pg-notify-db
    volumes:
      - pgdata:/var/lib/postgresql/data
  server:
    container_name: server
    image: 'node:16-alpine'
    build: ./
    tty: true
    ports:
      - '8080:8080'
    environment:
      - PORT=8080
      - PGUSER=pguser
      - PGPASSWORD=password
      - PGDATABASE=pg-notify-db
      - PGHOST=postgres
    volumes:
      - ./server:/server
      - /server/node_modules
    links:
      - postgres
    depends_on:
      - postgres

volumes:
  pgdata: 
  node_modules:

We will also need a Dockerfile for the server. 

FROM node:16-alpine

WORKDIR /

COPY package*.json ./
COPY yarn.lock ./

RUN npm install

COPY . ./

ENV PORT=8080

CMD [ "npm", "start" ]

I like to use a .dockerignore, and I just keep the node_modules in there.

Install NPM Packages

We will need a few npm packages for this demo. 

yarn add express pg pg-listen

Package.json

We will add a start script to our package.json

{
  "name": "pg-notification",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "start": "node index.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.18.1",
    "pg": "^8.8.0",
    "pg-listen": "^1.7.0"
  }
}

Index.js

Now we need our index.js file.

At first, we will just start an express server in the index.js. 

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

(async function () {
  const server = express();
  const port = process.env.PORT || 8080;
  server.listen(port, () => {
    console.log("node.js server listening on port " + port);
  });
})();

Start containers

docker compose up should start the containers. In the server container, there should be a log that says: node.js server listening on port 8080.

Connect to DB and Set Up Notification

Our index.js should now look like this:

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

(async function () {
  const server = express();
  const port = process.env.PORT || 8080;
  server.listen(port, () => {
    console.log("node.js server listening on port " + port);
  });

  const dbClient = new Client({
    host: process.env.PGHOST,
    user: process.env.PGUSER,
    password: process.env.PGPASSWORD,
    database: process.env.PGDATABASE,
    port: 5432,
  });

  await dbClient.connect();

  const tableName = "table_1";
  const notifyFnName = "number_changed_function";
  const triggerName = "number_changed_trigger";
  const eventName = "number_was_added";

  await dbClient.query(`CREATE TABLE IF NOT EXISTS ${tableName}(
    number INT
    )`);

  await dbClient.query(
    `CREATE OR REPLACE FUNCTION ${notifyFnName}()
            RETURNS TRIGGER
            LANGUAGE PLPGSQL
            AS $$
            DECLARE number text;
            BEGIN
            SELECT to_json(NEW.number)::text INTO number;
            PERFORM pg_notify('${eventName}'::text, number);
            RETURN NULL;
            END;
            $$`
  );

  await dbClient.query(
    `CREATE TRIGGER ${triggerName} AFTER INSERT ON ${tableName}
  FOR EACH ROW EXECUTE FUNCTION ${notifyFnName}()`
  );

  //  Create listener for db
  const subscriber = createSubscriber({
    connectionString: `postgres://${process.env.PGUSER}:${process.env.PGPASSWORD}@${process.env.PGHOST}:5432/${process.env.PGDATABASE}`,
  });
  await subscriber.connect();
  await subscriber.listenTo(eventName);

  subscriber.notifications.on(eventName, async (number) => {
    console.log(`${number} was added to the database`);
  });

  //  Add Item to table
  await dbClient.query(
    `INSERT INTO ${tableName} (number) VALUES (${Math.round(Math.random())})`
  );

})();

The index.js will now create a database table called table_1. Then it creates a function that will return a notification called number_was_added whenever a row is added into the table_1 table. This is done because of the CREATE TRIGGER statement.

After that, we use the pg-listen package to set up a subscriber, listen to the number_was_added notification, and run a function that will log the number when a row is added to the database.

Conclusion

So there you have it, not too difficult to listen to postgres notifications with a node.js express server. This can also be done with the pg package, but pg-listen has some useful features built-in such as error handling and reconnecting to the client, etc.

To stop containers, run docker compose down.

© 2022 Code With Wolf