pg-table-observer

Observe PostgreSQL table for changes.

Requires PostgresSQL version 9.3 or above.

Usage

var pgp = require('pg-promise')();
 
import PgTableObserver from 'pg-table-observer';
 
const connection = 'postgres://localhost/db';
 
async function start() {
  try {
    let db = await pgp(connection);
 
    let table_observer = new PgTableObserver(db, 'myapp');
 
    async function cleanupAndExit() {
      await table_observer.cleanup();
      pgp.end();
      process.exit();
    }
 
    process.on('SIGTERM', cleanupAndExit);
    process.on('SIGINT', cleanupAndExit);
 
    // Multiple tables can be specified as an array
 
    let handle = await table_observer.notify('test', change => {
      console.log(change);
    });
 
    // Or trigger a callback when a condition is met
 
    let handle = await table_observer.trigger(condition, () => {
      console.log("condition was met")
    });
 
    // ... when finished observing the table
 
    await handle.stop();
 
    // ... when finished observing altogether
 
    await table_observer.cleanup();
  }
  catch(err) {
    console.error(err);
  }
}
 
// Show unhandled rejections
 
process.on('unhandledRejection', (err, p) => console.log(err.stack));
 
start();

constructor(db, channel)

Parameter	Description
db	PostgreSQL database connection
channel	String, PostgreSQL LISTEN/NOTIFY channel to use, must be unique for every observer, database-wide.

let handle = async notify(tables, callback)

Use this method if you want to observe a set of tables for any change.

The callback will be called for each INSERT, DELETE or UPDATE performed on the tables, for each row individually.

Parameter	Description
tables	A string or array of tables to monitor. Table names will be converted to lowercase, and duplicates will be removed.
callback	function(change). Will be called for any change to any of the tables that are being monitored. See below for its fields.

Fields for the change parameter of the callback:

Field	Description
table	String, name of the table that changed. This will always be in lowercase.
insert	For INSERT, true
delete	For DELETE, true
update	For UPDATE, an object that contains the old and new values of each changed column. If a column score changed from 10 to 20, change.update.score.from would be 10 and change.update.score.to would be 20.
row	The row values, for UPDATE, the NEW row values
old	For UPDATE, the OLD row values

Return value

handle: Object with the following fields:

Field	Description
async stop()	async function(). Stop observing the tables.

let handle = async trigger(tables, triggers, callback, [options])

Use this method if you want to take some action when triggered by some change to the tables.

The triggers callback will be called when there is a change to tables. When triggers returns true, the callback will be called.

The following logic prevents excessive callbacks when multiple rows are updated at once:

• When triggers hits, callback is called and a timer is started.
• When triggers hits again before the timer is finished, the callback will be called as soon as the timer finishes.
• Until that happens, no triggers calls will happen.
• Default behavior can be changed with options, see below.

Parameters

Parameter	Description
tables	A string or array of tables to monitor. Table names will be converted to lowercase, and duplicates will be removed.
triggers	function(change). Will be called when a change to tables happens, with the same fields as described above. If this function returns true, the callback
options	An optional object that may be used to change the default behavior as described above. See below for the possible options.

Options parameter:

Field	Description
trigger_first	(default true): related to the 1st step above. When true, behaves as described above. When set to false, when triggers hit and the timer is not yet started, the callback will not be called immediately. Instead the timer is started and the callback will be called when the timer finishes. Use this if you expect many changes in short succession, or if the callback is relatively costly. On a heavy-load production system you may want to set this to false.
trigger_delay	(default 200ms): the time the timer will be set to. You may want to increase this value on heavy-load production system, or when the callback is relatively costly.
reduce_triggers	(default true): related to the 3rd step above. When true, behaves as described above. When false, triggers will be called for every change. The callback will still be called as described after the timer finishes. Use this if you want to keep track of which changes happen to which tables.

Return value

handle: Object with the following fields.

Field	Description
async stop()	async function(). Stop observing the tables.

async cleanup()

Stops observing all tables and frees up resources.