pgpw

Opinionated wrapper around the pg-promise library.

This module provides the following features:

• connection pool singleton for the entire application;
• multiple database objects managed in one place;
• normalization to the native Promise;
• extended error handling with the use of verror;
• stubs API to be used in unit testing.

v5 Breaking Changes

Upgrade pg-promise to v10, as v8 has stopped working in Node.js 14. No API breaking change has been introduced.

Archive documentation: v1, v2, v3, v4.

Installation

$ npm install pgpw --save

Usage

You should initialize database object(s) once per application (e.g., in your application's main function):

const pgpw = require('pgpw');

/**
 * If your application works with two databases,
 * you can initialize them both here
 */

// This database object won't provide any default retry strategy
pgpw.init('db_one', {
  host: 'localhost',
  port: 5432,
  database: 'db_1',
  user: 'app',
  password: ''
});

// This database object will provide the given default retry
// strategy (which can be amended or cancelled by a particular execution)
pgpw.init(
  'db_two',
  {
    host: process.env.DB2_HOST,
    port: process.env.DB2_PORT,
    database: process.env.DB2_NAME,
    user: process.env.DB2_USER,
    password: process.env.DB2_PSW
  },
  {
    initialDelayMs: 200,
    maxAttempts: 3
    exponent: 2
  }
);

Then in a module, you have access to initialized database objects by their names

// module.js
const db1 = require('pgpw')('db_one');
const db2 = require('pgpw')('db_two');

// Execute a function
db1.execFunc('my_func_1', [param1, param2])
  .then(recordset => processReturnedRecordset(recordset))
  .catch(err => handleErrorSomehow(err));

// Execute a function with retry options
db2.execFunc(
  'my_func_1',
  [param1, param2],
  {
    retry: {
      initialDelayMs: 100,
      maxAttempts: 5,
      exponent: 2
    }
  }
)
  .then(recordset => processReturnedRecordset(recordset))
  .catch(err => handleErrorSomehow(err));

// Transaction example
db1.transaction(t => t.batch([
  t.func('my_func_1', [param1, param2]),
  t.func('my_func_2', [param3, param4])
]))
  .then(([ recordset1, recordset2 ]) => {
    processReturnedRecordset(recordset1);
    processReturnedRecordset(recordset2);
  })
  .catch(err => handleErrorSomehow(err));

// Transaction with transaction mode settings and retry options
const transactionMode = {
  isolationLevel: db.isolationLevel.serializable,
  isReadOnly: true,
  isDeferrable: true
};

const options = {
  retry: {
    initialDelayMs: 100,
    maxAttempts: 5,
    exponent: 2
  }
}

db1.transaction(
  transactionMode,
  t => t.batch([
    t.func('my_func_1', [param1, param2]),
    t.func('my_func_2', [param3, param4])
  ]),
  options
)
  .then(([ recordset1, recordset2 ]) => {
    processReturnedRecordset(recordset1);
    processReturnedRecordset(recordset2);
  })
  .catch(err => handleErrorSomehow(err));

API Reference

{Function} pgpw.init(name, cnDetails, [retryOptions])

Initialize a database object. You can initialize only one database object with a given name per application. Otherwise an Error is thrown.

Parameters:

• {String} name - Name of database object for further reference.

• {Object} cnDetails - Database connection details

  • {String} cnDetails.host - Database host. Might be a host name or a valid IPv4/IPv6 address;

  • {Integer} cnDetails.port - Database port;

  • {String} cnDetails.database - Database name;

  • {String} cnDetails.user - Database role/user name;

  • {String} cnDetails.password - User password.

• {Object} retryOptions - Optional description of retry strategy to be applied to every database operation made on the database object being created, by default. It is possible to amend the strategy or cancel it competely for a particular operation via options.retry parameter. If any non-falsey value is provided here, this must be an object of correct shape with each and every property carring a correct value. Otherwise, the function throws a TypeError.

  • {Integer} retryOptions.initialDelayMs - Initial delay in milliseconds for first retry;

  • {Integer} retryOptions.maxAttempts - Maximum amount of retry attempts;

  • {Number} exponent - Multiplicator for calculation of delay for next attempt. E.g., given initialDelayMs = 200 and exponent = 2, the first retry attempt will be made in 200 ms after initial operation failure, the second retry attempt will be made in 200 * 2 = 400 ms after the first attempt, the third retry attempt will be made in 400 * 2 = 800 ms after the second attempt, and so on...

Returns: void.

{Function} pgpw(name)

Returns database object by name.

Parameter:

• {String} name - Database object name.

Returns: {Object}.

Database object (db)

{Function} db.execFunc(name, params, [options])

Execute a single database function in a separate database connection.

Parameters:

• {String} name - Database function name.

• {Array<Any>} params - Database function parameters.

• {Object} [options] - Additional options for execution

  • {Object} [retry] - Retry options which override, for this execution, default retry strategy the database object has been initialised with. For shape of object refer to pgpw.init retryOptions parameter description.

    • If this property is not provided, the default retry strategy (if any) will be applied.

    • If any value, other then boolean false value, is provided here, this must be an object of correct shape with each and every property carring a correct value. Otherwise, the function throws a TypeError.

    • If the boolean false value is provided, default retry strategy will be ignored and no retries will be performed.

Returns: {Promise<Any>} - Data returned by the function.

{Function} db.transaction([transactionMode], implementor, [options])

Parameters:

• {Object} transactionMode - Optional. Transaction mode settings (see details below).

• {Function} implementor(t) - Function that is called to execute tasks. Whithin the function, it is possible to use any type of task provided by the pg-promise library (i.e., t.func(), t.query(), t.task(), etc) including nested transactions (t.tx()). The function must return either

  • t.batch([task1, task2, ...]) - for parallel task execution, or
  • t.sequence(source) - for serial task execution.

• {Object} [options] - Additional options for execution

  • {Object} [retry] - Retry options which override, for this execution, default retry strategy the database object has been initialised with. For shape of object refer to pgpw.init retryOptions parameter description.

    • If this property is not provided, the default retry strategy (if any) will be applied.

    • If any value, other then boolean false value, is provided here, this must be an object of correct shape with each and every property carring a correct value. Otherwise, the function throws a TypeError.

    • If the boolean false value is provided, default retry strategy will be ignored and no retries will be performed.

Returns: {Promise<Any>} - Result of executing the tasks.

Transaction mode settings {Object} transactionMode

This parameter allows to extend the default BEGIN command which starts a transaction.

Properties:

• {db.isolationLevel} isolationLevel - Optional. Transaction isolation level. If not provided, or the provided value doesn't match db.isolationLevel enumeration, defaults to having no effect on transaction mode.

• {Boolean} isReadOnly - Optional. Sets transaction access mode. The exact value true adds BEGIN READ ONLY. The exact value false adds BEGIN READ WRITE. Any other value (including undefined, i.e. the property is not provided) adds nothing to the BEGIN command.

• {Boolean} isDeferrable - Optional. Sets transaction deferrable mode. The exact value true adds BEGIN DEFERRABLE. The exact value false adds BEGIN NOT DEFERRABLE. Any other value (including undefined, i.e. the property is not provided) adds nothing to the BEGIN command. This setting has effect only when isolationLevel = db.isolationLevel.serializable and isReadOnly = true, or else it is ignored.

db.isolationLevel enumeration

• serializable - Adds ISOLATION LEVEL SERIALIZABLE;

• repeatableRead - Adds ISOLATION LEVEL REPEATABLE READ;

• readCommitted - Adds ISOLATION LEVEL READ COMMITTED.

Error handling

The Promise returned by execFunc and transaction methods rejects with the following VErrors:

• DatabaseUnavailable - when connecting to database fails. The error provides the following info properties:

  • address {String} - Database host;
  • port {Number} - Database port.

• UnexpectedError - when any other error occurred.

Stubs API

Stubs API provides for an unified way to stub the methods exposed by the module.

pgpw.stubs([{Object} customStubs])

Returns function to be used in place of "required" pgpw module.

Generally, you don't need to call this function directly - it will be called by the require proxy you use in you test suit (think of proxyquire). When called returns an object with the following keys:

• {Function|Array<Function>} execFunc - default or custom stub/stubs for db.execFunc(...) method;
• {Function|Array<Function>} transaction - default or custom stub/stubs for db.transaction(...) method.

You can pass your custom stubs through the optional parameter customStubs which accepts an object with keys execFunc and transaction. All keys are optional. For every key that is not provided, the default stub is returned. Default stub returns Promise that resolves with null.

If you pass an array of stubs to either execFunc of transaction, these stubs will be executed sequentially on sequential calls to the relevant method in your production code. If your production code calls a method more times that the number of stubs in the array, the last stub is executed on all subsequent calls.

Usage Example

Say you want to unit test the following module:

/* my-module.js */

const db = require('pgpw')('my_db_name');

module.exports = async function retrieveDataFromDb () {
  const [ resultOne, resultTwo ] = await Promise.all([
    db.execFunc('func_1', ['param1', 'param2']),
    db.execFunc('func_2', ['param3', 'param4'])
  ]);

  const [ resultTree, resultFour ] = await db.transaction(t => t.batch([
    t.func('func_3', ['param5']),
    t.func('func_4', ['param6'])
  ]));

  // Logic to test
  if (resultOne.length && resultTwo.length && resultTree.length && resultFour.length) {
    return 'foo';
  } else {
    return 'bar';
  }
};

Test file:

const test = require('tape');
const proxyquire = require('proxyquire').noPreserveCache();
const dbStub = require('pgpw').stubs;

const getSelf = ({
  execFuncStub = [
    () => Promise.resolve([]),
    () => Promise.resolve([''])
  ]
} = {}) => proxyquire('./my-module', {
  'pgpw': dbStub({ execFunc: execFuncStub })
});

test('all queries return data', async t => {
  try {
    const self = proxyquire('./my-module', {
      'pgpw': dbStub({
        execFunc: [
          () => Promise.resolve(['item1', 'item2']),
          () => Promise.resolve(['item3', 'item4'])
        ],
        transaction: () => Promise.resolve([
          ['item5', 'item6'],
          ['item7', 'item8']
        ])
      })
    });

    const result = await self();

    t.equal(result, 'foo', 'should return foo');
    t.end();
  } catch (err) {
    t.end(err);
  }
});