Nuns Playing Monopoly

    pg-promise-listener

    1.0.6 • Public • Published

    pg-promise-listener

    Resilient notification listener for pg-promise

    The code in this module is mostly copied from Vitaly Tomilov's example code, which can be found here: https://github.com/vitaly-t/pg-promise/wiki/Robust-Listeners

    Install

    npm install pg-promise-listener --save
    

    Use

    Example:

    const Listener = require('pg-promise-listener');
    const pgp = require('pg-promise')();
     
    const myConnection = pgp({
      host: 'localhost',
      port: 5432,
      database: 'myDatabase',
      user: 'myUser',
      password: 'myPassword',
    });
     
    function messageHandler(message) {
      console.log(message);
    }
     
    const listener = new Listener({
      dbConnection: myConnection,
      onDatabaseNotification: messageHandler,
      channel: 'myChannel',
    });

    Sending notifications from Postgres

    SELECT pg_notify('myChannel''My message');

    Keeping connections alive on AWS

    Thank you @boromisp for reasearching and documenting a keep-alive problem (mostly on AWS).

    Link to write-up: https://github.com/vitaly-t/pg-promise/issues/593

    SelfCheck

    If you do run your servers on AWS, you have probably already implemented a /health endpoint. In order to discover all kinds of problems pg-promise-listener has a selfCheck method. This method returns a promise, which is only resolved after a complete roundtrip to the Postgres server like this:

    1. Tell the database to send a notification with a special message (actually just a guid)
    2. Wait for a message with this guid to arrive from the database server
    3. Resolve the promise with true if the notification arrives within selfCheckTimeout milliseconds (defaults to 2000ms)
    4. Otherwise resolve the promise with false

    N.B. There will be no rejection of the promise, so you won't have to bother with .catch if you "then" the promise, or try..catch if you await the promise.

    In your request handler for the /health endpoint, you could do the following:

    const isReady = await listener.selfCheck();
    if (isReady) {
      res.writeHead(200);
    } else {
      res.writeHead(500);
    }
    return res.end();

    Sending and receiving JSON

    It's very convenient to have a single notification trigger connected to multiple tables. It's also convenient to use JSON as the payload format. In order to do this we need:

    • Dynamically read some field values from the updated/inserted table row.
    • Send the json in a stringified format (pg_notify can only send strings)

    The trigger function

    CREATE OR REPLACE FUNCTION main.tr_notify()
     RETURNS trigger
     LANGUAGE plpgsql
    AS $function$
    DECLARE
        _id int;
        _project_id int;
        _another_field varchar;
        _json jsonb;
        _row jsonb;
    BEGIN
        _row = row_to_json(NEW);
     
        --Try to read field values from the row 
        --By using json functions we can do this in a "late binding"-fashion without 
        --getting errors for non existing fields 
        _id = _row ->> 'id';
        _project_id = _row->> 'project_id';
        _another_field = _row->> 'another_field';
     
        _json = jsonb_build_object(
          'tableName', TG_TABLE_NAME,
          'op', TG_OP,
          'id',_id,
          'projectId', _project_id
          'anotherField', _another_field
        );
     
        --Strip null values from the json to get rid of fields that does not exist in this particular table 
        _json = jsonb_strip_nulls(_json);
     
        --Send the message as a stringified json 
        PERFORM pg_notify('myChannel', _json::varchar);
     
        RETURN NULL;
      END;
    $function$;

    Creating triggers

    Our trigger function can now be used in triggers on multiple tables

    CREATE TRIGGER ai_au__my_table__notify
    AFTER UPDATE OR INSERT ON my_table
    FOR EACH ROW
    EXECUTE PROCEDURE tr_notify();
     
    CREATE TRIGGER ai_au__my_other_table__notify
    AFTER UPDATE OR INSERT ON my_other_table
    FOR EACH ROW
    EXECUTE PROCEDURE tr_notify();

    Parsing JSON in the listener

    As a convenience, the listener can parse your JSON data. Just tell Listener that you want this.

    const listener = new Listener({
      dbConnection: myConnection,
      onDatabaseNotification: messageHandler,
      channel: 'myChannel',
      parseJson: true,
    });

    Output

    The parsed message might look like this after inserting a record in my_table:

    {
      "id": 2,
      "op": "INSERT",
      "projectId": 1,
      "tableName": "my_table"
    }

    Install

    npm i pg-promise-listener

    DownloadsWeekly Downloads

    1

    Version

    1.0.6

    License

    MIT

    Unpacked Size

    11 kB

    Total Files

    5

    Last publish

    Collaborators

    • sgryd