Sign Up for Free

RunKit +

Try any Node.js package right in your browser

This is a playground to test code. It runs a full Node.js environment and already has all of npm’s 400,000 packages pre-installed, including pg-live-select with all npm packages installed. Try it out:

var pgLiveSelect = require("pg-live-select")

This service is provided by RunKit and is not affiliated with npm, Inc or the package authors.

pg-live-select v1.0.3

Live updating PostgreSQL SELECT statements

pg-live-select Build Status

NPM Package to provide events when a PostgreSQL SELECT statement result set changes.

Built using the node-postgres NPM package.

Postgres 9.3+ is required.

See also:

Installation

npm install pg-live-select

LivePg Class

The LivePg constuctor requires 2 arguments:

Constructor ArgumentTypeDescription
connStrstringPostgres connection string, ex. postgres://user:pass@host/db
channelstringUnique identifier for this connection. Used as channel for NOTIFY commands as well as prefix for triggers, functions, and views. Must follow SQL naming rules: may only contain a-z, A-Z, 0-9, and _ and not begin with a number.

A single persistent client is used to listen for notifications. Result set refreshes obtain additional clients from the pool on-demand.

var LivePg = require('pg-live-select');

// Instantiate LivePg class
var liveDb = new LivePG('postgres://user:pass@host/db', 'myapp');

// Create a live query
var highScores = liveDb
  .select('SELECT * FROM scores WHERE score > 10')
  .on('update', function(diff, data) {
    // diff: object containing differences since last update
    // data: array of full result set
    // This event will always be called at least once.
    // On initialization with an empty result set, diff.added == []
  });

// Stop query updates
highScores.stop();

// When exiting the application, remove all installed triggers
liveDb.cleanup(function() {
  // Database is now cleaned
});

See complete working example in example/livequery.js.

The LivePg class inherits from EventEmitter, providing error events.

LivePg.prototype.select(query, [params], [triggers])

ArgumentTypeDescription
queryStringSELECT SQL statement
paramsArrayOptionally, pass an array of parameters to interpolate into the query safely. Paramaters in the query are denoted using lt;number>, e.g. $1 corresponds to params[0]. If omitted, triggers may occupy the second argument.
triggersObjectOptionally, specify an object defining invalidation lamdba functions for specific tables. If omitted, the query results will be refreshed on any change to the query's dependent tables.

Returns SelectHandle object.

Trigger object definitions

The triggers argument object contains table names as the object's keys and result set data invalidation functions as values. Each function returns a boolean value determining whether the query results should be refreshed on account of the row that has changed.

  • For INSERT operations, the new row is passed as the argument.
  • For UPDATE operations, the function is called twice: once with the old row as the argument and once with the new row as the argument. If either returns true, the query results are updated.
  • For DELETE operations, the old row is passed as the argument.
// Simple live query with custom trigger
liveDb.select('SELECT * FROM scores WHERE score > $1', [ 10 ], {
  'scores': function(row) {
    return row.score > 10
  }
})

SelectHandle class

The LivePG.prototype.select() method returns an instance of the SelectHandle class that contains a stop() method for terminating updates to a live query.

The SelectHandle class inherits from EventEmitter, providing an update event on each result set change with two arguments: diff and data. diff contains a description of which rows have been added, moved, removed, and copied. data contains an array of the full result set.

An error event is emitted from the SelectHandle for initialization errors.

LivePg.prototype.cleanup(callback)

Remove triggers and trigger function on application exit.

// On Ctrl+C, remove triggers and exit
process.on('SIGINT', function() {
  liveDb.cleanup(process.exit);
});

Getting started with the example

Run the following commands:

# Download dependent packages
$ npm install

# Load sample dataset into Postgres
psql databasename < example/sample-data.sql

# Configure the database connection string
vim example/livequery.js

# Start sample application
node example/livequery.js

Perfoming Tests

Regression tests are performed using the npm test command.

Performance tests may be performed using the node-pg-mem-test application.

License

MIT

Metadata

RunKit is a free, in-browser JavaScript dev environment for prototyping Node.js code, with every npm package installed. Sign up to share your code.
Sign Up for Free