Tutorial: Basics

This is a simple tutorial that will help you get started with pglistend.

Install the package

First of all, install pglistend globally in your system using npm

$ npm install -g pglistend

Setup daemon

Now setup the daemon using pglisten. It will register systemd daemon service your system.

$ sudo pglisten setup-daemon

You should see similar output:

The setup has just enabled the daemon in your system and it will automatically be loaded by default on system startup along with other services.

But since you haven't configured it yet. It's not runnable right now.

Setup test database

But before we configure it let's create a test database in postgresql to try out the LISTEN/NOTIFY thing.

CREATE DATABASE test_pglistend;

Let's create a new table on it.

CREATE TABLE users (
    id serial primary key,
    name varchar,
    email varchar
);

Now that we've created a table, we will create a function that would send notifications to the daemon using postgres NOTIFY.

CREATE OR REPLACE FUNCTION notify_table_update() RETURNS trigger AS
$$
DECLARE
    id bigint;
BEGIN
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        id = NEW.id;
    ELSE
        id = OLD.id;
    END IF;
    --
    -- Send notifications to the clients listening on channel 'update'
    --
    PERFORM pg_notify('update',
        json_build_object(
            'table', TG_TABLE_NAME,
            'id', id,
            'opr', TG_OP
        )::text
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This is a basic function that will trigger NOTIFY to channel 'update'. So, every client listening to this channel will receive the notification. And, note that we are sending the payload encoded in JSON using json_build_object which allows us to send any kind of data.

Now, we'll need to add TRIGGERS on the table to execute this function every time data is manipulated in the table. We'll trigger the notification for all INSERT, UPDATE DELETE operations

DROP TRIGGER users_update ON users;
CREATE TRIGGER users_update AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE notify_table_update();

DROP TRIGGER users_insert ON users;
CREATE TRIGGER users_insert AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE notify_table_update();

DROP TRIGGER users_delete ON users;
CREATE TRIGGER users_delete AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE notify_table_update();

Now we have setup a test database to fire NOTIFY messages everytime data is updated in the database.

Let's configure pglistend to use this database and listen to the channel we've just created.

Configure pglistend

Listener Script

Let's create a listener script in which we'll register handlers the channels we are listening to. Above we have sent the notifications to channel update so we'll register a callback for this channel now. For now we are just doing a console.log whenever we receive a notification.

module.exports = function(h) {
    // Handler for the channel 'update'
    return {
        'update': function(payload) {
            console.log('Invoked with payload', payload);
        }
    };
};

Save this file where ever you want. Here I'll save it to /home/kabir/pglistend-test/listener.js.

Database Connection

Now we'll create a new configuration file for setting up the our database connection.

# postgresql connection
connection:
    user: 'YOUR_USERNAME'
    password: 'YOUR_PASSWORD'
    database: 'test_pglistend'
    port: 5432
    max: 10

# channels to LISTEN to
channels: [update]

# list of listener scripts
scripts:
    # Give the full path to the listener script that you've created.
    - /home/kabir/pglistend-test/listener.js

I have saved it to the same directory as /home/kabir/pglistend-test/db-connection.yml

Make sure you've replaced the YOUR_USERNAME & YOUR_PASSWORD with your database credentials.

And also update the full path to the listener script that we've just created under scripts key. In my case I've added /home/kabir/pglistend-test/listener.js.

Configure connections to load

Now open the main config file /etc/pglistend/config.yml and append the path to the database connection file we've just created under the connections array. In my case I've added /home/kabir/pglistend-test/db-connection.yml.

It should look similar to this:

default:
    connection:
        host: localhost
        port: 5432
        max: 2
        idleTimeoutMillis: 10000

# Include configuration files database connections you want to use
connections:
    - /home/kabir/pglistend-test/db-connection.yml

You may add multiple database connection configs if you need here.

Start the service

Now that we've configured every thing it's time to start the pglistend service.

You can do that using

$ sudo systemctl start pglistend

Testing the notifications

As this is a background process we need to check the logs to make sure it is working as expected.

Now let's check the logs for this service. You can use journalctl for this.

$ journalctl -f -u pglistend

You'll see the following entries as we've just started the service.

Now run the following sql INSERT statements from a different terminal on the database we've just created.

INSERT INTO users(name, email) VALUES('Foo', 'foo@bar.com');
INSERT INTO users(name, email) VALUES('Bar', 'bar@bar.com');

Now immediately go back to the terminal in which we're checking the logs. Now it should logs these notifications instantly.

Again, try manipulating the data in the table using UPDATE, DELETE statements.

UPDATE users SET name = 'Test User' WHERE name = 'Foo';
DELETE FROM users WHERE name = 'Bar';

And you should see additional entries have been logged for the received notifications.

Right now we're just testing this service by reading the logs but you may perform any actions in the listener scripts on receiving notifications from postgres.

Read more about performing custom actions using the listener scripts.