Database

new Database(cn, dcopt) → {Database}

Represents the database protocol, extensible via event extend. This type is not available directly, it can only be created via the library's base call.

IMPORTANT:

For any given connection, you should only create a single Database object in a separate module, to be shared in your application (see the code example below). If instead you keep creating the Database object dynamically, your application will suffer from loss in performance, and will be getting a warning in a development environment (when NODE_ENV = development):

WARNING: Creating a duplicate database object for the same connection.

If you ever see this warning, rectify your Database object initialization, so there is only one object per connection details. See the example provided below.

See also: property noWarnings in Initialization Options.

Note however, that in special cases you may need to re-create the database object, if its connection pool has been shut-down externally. And in this case the library won't be showing any warning.

Parameters:
Name Type Attributes Description
cn string | object

Database connection details, which can be:

  • a configuration object
  • a connection string

For details see Connection Syntax.

The value can be accessed from the database object via property $cn.

dc * <optional>

Database Context.

Any object or value to be propagated through the protocol, to allow implementations and event handling that depend on the database context.

This is mainly to facilitate the use of multiple databases which may need separate protocol extensions, or different implementations within a single task / transaction callback, depending on the database context.

This parameter also adds uniqueness to the connection context that's used in combination with the connection parameters, i.e. use of unique database context will prevent getting the warning about creating a duplicate Database object.

The value can be accessed from the database object via property $dc.

Source:
See:
Returns:
Type
Database
Example
// Proper way to initialize and share the Database object

// Loading and initializing the library:
const pgp = require('pg-promise')({
    // Initialization Options
});

// Preparing the connection details:
const cn = 'postgres://username:password@host:port/database';

// Creating a new database instance from the connection details:
const db = pgp(cn);

// Exporting the database object for shared use:
module.exports = db;

Members

(readonly) $cn :string|object

Database connection, as was passed in during the object's construction.

This is a hidden property, to help integrating type Database directly with third-party libraries.

Type:
  • string | object
Source:
See:

(readonly) $config :object

This is a hidden property, to help integrating type Database directly with third-party libraries.

Properties available in the object:

  • pgp - instance of the entire library after initialization
  • options - the library's Initialization Options object
  • promiseLib - instance of the promise library that's used
  • promise - generic promise interface that uses promiseLib via 4 basic methods:
    • promise((resolve, reject) => {}) - to create a new promise
    • promise.resolve(value) - to resolve with a value
    • promise.reject(reason) - to reject with a reason
    • promise.all(iterable) - to resolve an iterable list of promises
  • version - this library's version
  • $npm (hidden property) - internal module cache
Type:
  • object
Source:
Example
// Using the promise protocol as configured by pg-promise:

const $p = db.$config.promise;

const resolvedPromise = $p.resolve('some data');
const rejectedPromise = $p.reject('some reason');

const newPromise = $p((resolve, reject) => {
    // call either resolve(data) or reject(reason) here
});

(readonly) $dc :*

Database Context, as was passed in during the object's construction.

This is a hidden property, to help integrating type Database directly with third-party libraries.

Type:
  • *
Source:
See:

(readonly) $pool :external:pg-pool

A pg-pool object associated with the database object, as each Database creates its own pg-pool instance.

This is a hidden property, primarily for integrating type Database with third-party libraries that support pg-pool directly. Note however, that if you pass the pool object into a library that calls pool.end(), you will no longer be able to use this Database object, and each query method will be rejecting with Error = Connection pool of the database object has been destroyed.

You can also use this object to shut down the pool, by calling $pool.end().

For more details see Library de-initialization.

Type:
Source:
See:
Example
// Shutting down the connection pool of this database object,
// after all queries have finished in a run-though process:

.then(() => {}) // processing the data
.catch() => {}) // handling the error
.finally(db.$pool.end); // shutting down the pool

Methods

any(query, valuesopt) → {external:Promise.<Array>}

Executes a query that expects any number of rows. This is simply a shorter alias for method manyOrNone.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
See:
Returns:

A promise object that represents the query result:

  • When no rows are returned, it resolves with an empty array.
  • When 1 or more rows are returned, it resolves with the array of rows.
Type
external:Promise.<Array>

connect(optionsopt) → {external:Promise}

Acquires a new or existing connection, depending on the current state of the connection pool, and parameter direct.

This method creates a shared connection for executing a chain of queries against it. The connection must be released in the end of the chain by calling method done() on the connection object.

This is an obsolete, low-level approach to chaining queries on the same connection. A newer and safer approach is via methods task and tx (for transactions), which allocate and release a shared connection automatically.

NOTE: Even though this method exposes a Client object via property client, you cannot call client.end() directly, or it will print an error into the console: Abnormal client.end() call, due to invalid code or failed server connection. You should only call method done() to release the connection.

Parameters:
Name Type Attributes Description
options object <optional>

Connection Options.

Properties
Name Type Attributes Default Description
direct boolean <optional>
false

Creates a new connection directly, through the Client, bypassing the connection pool.

By default, all connections are acquired from the connection pool. If you set this option however, the library will instead create a new Client object directly (separately from the pool), and then call its connect method.

WARNING:

Do not use this option for regular query execution, because it exclusively occupies one physical connection, and therefore cannot scale. This option is only suitable for global connection usage, such as event listeners.

onLost function <optional>

Notification callback of the lost/broken connection, called with the following parameters:

  • err - the original connectivity error
  • e - error context object, which contains:
    • cn - safe connection string/config (with the password hashed);
    • dc - Database Context, as was used during Database construction;
    • start - Date/Time (Date type) when the connection was established;
    • client - Client object that has lost the connection.

The notification is mostly valuable with direct: true, to be able to re-connect direct/permanent connections by calling method connect again.

You do not need to call done on lost connections, as it happens automatically. However, if you had event listeners set up on the connection's client object, you should remove them to avoid leaks:

function onLostConnection(err, e) {
    e.client.removeListener('my-event', myHandler);
}

For a complete example see Robust Listeners.

Source:
See:
Returns:

A promise object that represents the connection result:

  • resolves with the complete Database protocol, extended with:
    • property client of type Client that represents the open connection
    • method done() that must be called in the end, in order to release the connection
    • methods batch, page and sequence, same as inside a Task
  • rejects with a connection-related error when it fails to connect.
Type
external:Promise
Example
let sco; // shared connection object;

db.connect()
    .then(obj => {
        // obj.client = new connected Client object;

        sco = obj; // save the connection object;

        // execute all the queries you need:
        return sco.any('SELECT * FROM Users');
    })
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    })
    .finally(() => {
        // release the connection, if it was successful:
        if (sco) {
            sco.done();
        }
    });

each(query, valuesopt, cb, thisArgopt) → {external:Promise.<Array.<Object>>}

Executes a provided function once per array element, for an array of rows resolved by method any.

It is a convenience method to reduce the following code:

db.any(query, values)
    .then(data => {
        data.forEach((row, index, data) => {
             // process the row
        });
        return data;
    });

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

cb function

Function to execute for each row, taking three arguments:

  • row - the current row object being processed in the array
  • index - the index of the current row being processed in the array
  • data - the array of rows resolved by method any
thisArg * <optional>

Value to use as this when executing the callback.

Source:
See:
Returns:

Resolves with the original array of rows.

Type
external:Promise.<Array.<Object>>
Example
db.each('SELECT id, code, name FROM Events', [], row => {
    row.code = parseInt(row.code);
})
    .then(data => {
        // data = array of events, with 'code' converted into integer
    })
    .catch(error => {
        // error
    });

func(funcName, valuesopt, qrmopt) → {external:Promise}

Executes a query against a database function by its name: SELECT * FROM funcName(values).

Parameters:
Name Type Attributes Default Description
funcName string

Name of the function to be executed.

values array | value <optional>

Parameters for the function - one value or an array of values.

qrm queryResult <optional>
queryResult.any

Query Result Mask.

Source:
See:
Returns:

A promise object as returned from method query, according to parameter qrm.

Type
external:Promise

many(query, valuesopt) → {external:Promise}

Executes a query that expects one or more rows. When the query returns no rows, the method rejects.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
Returns:

A promise object that represents the query result:

Type
external:Promise

manyOrNone(query, valuesopt) → {external:Promise.<Array>}

Executes a query that expects any number of rows.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
See:
Returns:

A promise object that represents the query result:

  • When no rows are returned, it resolves with an empty array.
  • When 1 or more rows are returned, it resolves with the array of rows.
Type
external:Promise.<Array>

map(query, values, cb, thisArgopt) → {external:Promise.<Array>}

Creates a new array with the results of calling a provided function on every element in the array of rows resolved by method any.

It is a convenience method to reduce the following code:

db.any(query, values)
    .then(data => {
        return data.map((row, index, data) => {
             // return a new element
        });
    });

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

cb function

Function that produces an element of the new array, taking three arguments:

  • row - the current row object being processed in the array
  • index - the index of the current row being processed in the array
  • data - the original array of rows resolved by method any
thisArg * <optional>

Value to use as this when executing the callback.

Source:
See:
Returns:

Resolves with the new array of values returned from the callback.

Type
external:Promise.<Array>
Examples
db.map('SELECT id FROM Users WHERE status = $1', ['active'], row => row.id)
    .then(data => {
        // data = array of active user id-s
    })
    .catch(error => {
       // error
    });
db.tx(t => {
    return t.map('SELECT id FROM Users WHERE status = $1', ['active'], row => {
       return t.none('UPDATE Events SET checked = $1 WHERE userId = $2', [true, row.id]);
    }).then(t.batch);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    });
// Build a list of active users, each with the list of user events:
db.task(t => {
    return t.map('SELECT id FROM Users WHERE status = $1', ['active'], user => {
        return t.any('SELECT * FROM Events WHERE userId = $1', user.id)
            .then(events=> {
                user.events = events;
                return user;
            });
    }).then(t.batch);
})
    .then(data => {
        // success
    })
    .catch(error => {
        // error
    });

multi(query, valuesopt) → {external:Promise.<Array.<Array>>}

Executes a multi-query string without any expectation for the return data, to resolve with an array of arrays of rows when successful.

This method was added specifically to give access to multi-query results supported by the pg driver from version 7.x onwards.

Parameters:
Name Type Attributes Description
query string | function | object

Multi-query string to be executed, which can be any of the following types:

  • A non-empty string that can contain any number of queries
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
See:
Returns:
Type
external:Promise.<Array.<Array>>
Example
db.multi('SELECT * FROM users;SELECT * FROM products')
   .spread((users, products) => {
       // we get data from all queries at once
   })
   .catch(error => {
       // error
   });

multiResult(query, valuesopt) → {external:Promise.<Array.<external:Result>>}

Executes a multi-query string without any expectation for the return data, to resolve with an array of the original Result objects when successful.

This method was added specifically to give access to multi-query results supported by the pg driver from version 7.x onwards.

Parameters:
Name Type Attributes Description
query string | function | object

Multi-query string to be executed, which can be any of the following types:

  • A non-empty string that can contain any number of queries
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
See:
Returns:
Type
external:Promise.<Array.<external:Result>>

none(query, valuesopt) → {external:Promise.<null>}

Executes a query that expects no data to be returned. If the query returns any kind of data, the method rejects.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

Source:
Returns:

A promise object that represents the query result:

Type
external:Promise.<null>

one(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Executes a query that expects exactly one row of data. When 0 or more than 1 rows are returned, the method rejects.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

cb function <optional>

Value transformation callback, to allow in-line value change. When specified, the return value replaces the original resolved value.

The function takes only one parameter - value resolved from the query.

thisArg * <optional>

Value to use as this when executing the transformation callback.

Source:
See:
Returns:

A promise object that represents the query result:

Type
external:Promise
Examples
// a query with in-line value transformation:
db.one('INSERT INTO Events VALUES($1) RETURNING id', [123], event => event.id)
    .then(data => {
        // data = a new event id, rather than an object with it
    });
// a query with in-line value transformation + conversion:
db.one('SELECT count(*) FROM Users', [], c => +c.count)
    .then(count => {
        // count = a proper integer value, rather than an object with a string
    });

oneOrNone(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Executes a query that expects 0 or 1 rows, to resolve with the row-object when 1 row is returned, and with null when nothing is returned. When the query returns more than 1 row, the method rejects.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

cb function <optional>

Value transformation callback, to allow in-line value change. When specified, the return value replaces the original resolved value.

The function takes only one parameter - value resolved from the query.

thisArg * <optional>

Value to use as this when executing the transformation callback.

Source:
See:
Returns:

A promise object that represents the query result:

  • When no rows are returned, it resolves with null.
  • When 1 row is returned, it resolves with that row as a single object.
  • When multiple rows are returned, it rejects with QueryResultError:
  • Resolves with the new value, if transformation callback cb was specified.
Type
external:Promise
Example
// a query with in-line value transformation:
db.oneOrNone('SELECT id FROM Events WHERE type = $1', ['entry'], e => e && e.id)
    .then(data => {
        // data = the event id or null (rather than object or null)
    });

proc(procName, valuesopt, cbopt, thisArgopt) → {external:Promise}

Executes a query against a stored procedure via its name: SELECT * FROM procName(values), expecting back 0 or 1 rows. It resolves either with the resulting row-object, or with null when none returned.

The method simply forwards into func(procName, values, queryResult.one|queryResult.none).

Parameters:
Name Type Attributes Description
procName string

Name of the stored procedure to be executed.

values array | value <optional>

Parameters for the procedure - one value or an array of values.

cb function <optional>

Value transformation callback, to allow in-line value change. When specified, the return value replaces the original resolved value.

The function takes only one parameter - value resolved from the query.

thisArg * <optional>

Value to use as this when executing the transformation callback.

Source:
See:
Returns:

It calls func(procName, values, queryResult.one|queryResult.none), and then returns the same result as method oneOrNone.

And if transformation callback cb was specified, it resolves with the new value.

Type
external:Promise

query(query, valuesopt, qrmopt) → {external:Promise}

Base query method that executes a generic query, expecting the return data according to parameter qrm.

It performs the following steps:

  1. Validates and formats the query via as.format, according to the query and values passed in;
  2. For a root-level query (against the Database object), it requests a new connection from the pool;
  3. Executes the query;
  4. For a root-level query (against the Database object), it releases the connection back to the pool;
  5. Resolves/rejects, according to the data returned from the query and the value of qrm.

Direct use of this method is not suitable for chaining queries, for performance reasons. It should be done through either task or transaction context, see Chaining Queries.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Default Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

qrm queryResult <optional>
queryResult.any

Query Result Mask

Source:
Returns:

A promise object that represents the query result according to qrm.

Type
external:Promise

result(query, valuesopt, cbopt, thisArgopt) → {external:Promise}

Executes a query without any expectation for the return data, to resolve with the original Result object when successful.

When receiving a multi-query result, only the last result is processed, ignoring the rest.

Parameters:
Name Type Attributes Description
query string | function | object

Query to be executed, which can be any of the following types:

  • A non-empty query string
  • A function that returns a query string or another function, i.e. recursive resolution is supported, passing in values as this, and as the first parameter.
  • Prepared Statement {name, text, values, ...} or PreparedStatement object
  • Parameterized Query {text, values, ...} or ParameterizedQuery object
  • QueryFile object
values array | value <optional>

Query formatting parameters.

When query is of type string or a QueryFile object, the values can be:

  • a single value - to replace all $1 occurrences
  • an array of values - to replace all $1, $2, ... variables
  • an object - to apply Named Parameters formatting

When query is a Prepared Statement or a Parameterized Query (or their class types), and values is not null or undefined, it is automatically set within such object, as an override for its internal values.

cb function <optional>

Value transformation callback, to allow in-line value change. When specified, the return value replaces the original resolved value.

The function takes only one parameter - value resolved from the query.

thisArg * <optional>

Value to use as this when executing the transformation callback.

Source:
Returns:

A promise object that represents the query result:

  • resolves with the original Result object (by default);
  • resolves with the new value, if transformation callback cb was specified.
Type
external:Promise
Examples
// use of value transformation:
// deleting rows and returning the number of rows deleted
db.result('DELETE FROM Events WHERE id = $1', [123], r => r.rowCount)
    .then(data => {
        // data = number of rows that were deleted
    });
// use of value transformation:
// getting only column details from a table
db.result('SELECT * FROM Users LIMIT 0', null, r => r.fields)
    .then(data => {
        // data = array of column descriptors
    });

stream(qs, initCB) → {external:Promise}

Custom data streaming, with the help of pg-query-stream.

This method doesn't work with the Native Bindings, and if option pgNative is set, it will reject with Streaming doesn't work with Native Bindings.

Parameters:
Name Type Description
qs QueryStream

Stream object of type QueryStream.

initCB Database.streamInitCB

Stream initialization callback.

It is invoked with the same this context as the calling method.

Source:
Returns:

Result of the streaming operation.

Once the streaming has finished successfully, the method resolves with {processed, duration}:

  • processed - total number of rows processed;
  • duration - streaming duration, in milliseconds.

Possible rejections messages:

  • Invalid or missing stream object.
  • Invalid stream state.
  • Invalid or missing stream initialization callback.
Type
external:Promise

task(optionsopt, cb) → {external:Promise}

Executes a callback function with automatically managed connection.

When invoked on the root Database object, the method allocates the connection from the pool, executes the callback, and once finished - releases the connection back to the pool. However, when invoked inside another task or transaction, the method reuses the parent connection.

This method should be used whenever executing more than one query at once, so the allocated connection is reused between all queries, and released only after the task has finished (see Chaining Queries).

The callback function is called with one parameter - database protocol (same as this), extended with methods batch, page, sequence, plus property ctx - the task context object. See class Task for more details.

Parameters:
Name Type Attributes Description
options string | number | Object <optional>

This parameter is optional, and presumed skipped when the first parameter is a function (cb parameter).

When it is of type string or number, it is assumed to be option tag passed in directly. Otherwise, it is expected to be an object with options as listed below.

Properties
Name Type Attributes Description
tag <optional>

Traceable context for the task (see tags).

cb function | generator

Task callback function, to return the result that will determine either success or failure for the operation.

The function can be either the first of the second parameter passed into the method.

It also can be an ES6 generator or an ES7 async function.

Source:
See:
Returns:

A promise object with the result from the callback function.

Type
external:Promise
Examples
db.task('my-task', t => {
        // t.ctx = task context object
        
        return t.one('SELECT id FROM Users WHERE name = $1', 'John')
            .then(user => {
                return t.any('SELECT * FROM Events WHERE userId = $1', user.id);
            });
    })
    .then(data => {
        // success
        // data = as returned from the task's callback
    })
    .catch(error => {
        // error
    });
// using an ES6 generator for the callback:
db.task('my-task', function * (t) {
        // t.ctx = task context object

        const user = yield t.one('SELECT id FROM Users WHERE name = $1', 'John');
        return yield t.any('SELECT * FROM Events WHERE userId = $1', user.id);
    })
    .then(data => {
        // success
        // data = as returned from the task's callback
    })
    .catch(error => {
        // error
    });

taskIf(optionsopt, cb) → {external:Promise}

Executes a conditional task that results in an actual new task, if either condition is met or when it is necessary (on the top level), or else it reuses the current connection context.

The default condition is not in task or transaction, to start a task only if currently not inside another task or transaction, which is the same as calling the following:

db.taskIf({cnd: t => !t.ctx}, cb => {})

It can be useful, if you want to simplify/reduce the task + log events footprint, by creating new tasks only when necessary.

Parameters:
Name Type Attributes Description
options string | number | Object <optional>

This parameter is optional, and presumed skipped when the first parameter is a function (cb parameter).

When it is of type string or number, it is assumed to be option tag passed in directly. Otherwise, it is expected to be an object with options as listed below.

Properties
Name Type Attributes Description
tag <optional>

Traceable context for the task/transaction (see tags).

cnd boolean | function <optional>

Condition for creating a (task), if it is met. It can be either a simple boolean, or a callback function that takes the task context as this and as the first parameter.

Default condition (when it is not specified):

{cnd: t => !t.ctx}
cb function | generator

Task callback function, to return the result that will determine either success or failure for the operation.

The function can be either the first or the second parameter passed into the method.

It also can be an ES6 generator or an ES7 async function.

Source:
See:
Returns:

A promise object with the result from the callback function.

Type
external:Promise

tx(optionsopt, cb) → {external:Promise}

Executes a callback function as a transaction, with automatically managed connection.

When invoked on the root Database object, the method allocates the connection from the pool, executes the callback, and once finished - releases the connection back to the pool. However, when invoked inside another task or transaction, the method reuses the parent connection.

A transaction wraps a regular task into additional queries:

  • it executes BEGIN just before invoking the callback function
  • it executes COMMIT, if the callback didn't throw any error or return a rejected promise
  • it executes ROLLBACK, if the callback did throw an error or return a rejected promise
  • it executes corresponding SAVEPOINT commands when the method is called recursively.

The callback function is called with one parameter - database protocol (same as this), extended with methods batch, page, sequence, plus property ctx - the transaction context object. See class Task for more details.

Note that transactions should be chosen over tasks only where necessary, because unlike regular tasks, transactions are blocking operations, and must be used with caution.

Parameters:
Name Type Attributes Description
options string | number | Object <optional>

This parameter is optional, and presumed skipped when the first parameter is a function (cb parameter).

When it is of type string or number, it is assumed to be option tag passed in directly. Otherwise, it is expected to be an object with options as listed below.

Properties
Name Type Attributes Description
tag <optional>

Traceable context for the transaction (see tags).

mode txMode.TransactionMode <optional>

Transaction Configuration Mode - extends the transaction-opening command with additional configuration.

cb function | generator

Transaction callback function, to return the result that will determine either success or failure for the operation.

The function can be either the first of the second parameter passed into the method.

It also can be an ES6 generator or an ES7 async function.

Source:
See:
Returns:

A promise object with the result from the callback function.

Type
external:Promise
Examples
db.tx('my-transaction', t => {
        // t.ctx = transaction context object
        
        return t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25])
            .then(user => {
                return t.batch([
                    t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'created']),
                    t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'login'])
                ]);
            });
    })
    .then(data => {
        // success
        // data = as returned from the transaction's callback
    })
    .catch(error => {
        // error
    });
// using an ES6 generator for the callback:
db.tx('my-transaction', function * (t) {
        // t.ctx = transaction context object

        const user = yield t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25]);
        return yield t.none('INSERT INTO Events(userId, name) VALUES($1, $2)', [user.id, 'created']);
    })
    .then(data => {
        // success
        // data = as returned from the transaction's callback
    })
    .catch(error => {
        // error
    });

txIf(optionsopt, cb) → {external:Promise}

Executes a conditional transaction that results in an actual transaction (tx), if the condition is met, or else it executes a regular task.

The default condition is not in transaction, to start a transaction only if currently not in transaction, or else start a task, which is the same as calling the following:

db.txIf({cnd: t => !t.ctx || !t.ctx.inTransaction}, cb => {})

It is useful when you want to avoid Nested Transactions - savepoints.

Parameters:
Name Type Attributes Description
options string | number | Object <optional>

This parameter is optional, and presumed skipped when the first parameter is a function (cb parameter).

When it is of type string or number, it is assumed to be option tag passed in directly. Otherwise, it is expected to be an object with options as listed below.

Properties
Name Type Attributes Default Description
tag <optional>

Traceable context for the task/transaction (see tags).

mode txMode.TransactionMode <optional>

Transaction Configuration Mode - extends the transaction-opening command with additional configuration.

cnd boolean | function <optional>

Condition for opening a transaction (tx), if it is met, or a task when the condition is not met. It can be either a simple boolean, or a callback function that takes the task/tx context as this and as the first parameter.

Default condition (when it is not specified):

{cnd: t => !t.ctx || !t.ctx.inTransaction}
reusable boolean | function <optional>
false

When cnd is/returns false, reuse context of the current task/transaction, if one exists. It can be either a simple boolean, or a callback function that takes the task/tx context as this and as the first parameter.

By default, when cnd is/returns false, the method creates a new task. This option tells the method to reuse the current task/transaction context, and not create a new task.

This option is ignored when executing against the top level of the protocol, because on that level, if no transaction is suddenly needed, a new task becomes necessary.

cb function | generator

Transaction/task callback function, to return the result that will determine either success or failure for the operation.

The function can be either the first or the second parameter passed into the method.

It also can be an ES6 generator or an ES7 async function.

Source:
See:
Returns:

A promise object with the result from the callback function.

Type
external:Promise

Type Definitions

streamInitCB(stream)

Stream initialization callback, used by Database.stream.

Parameters:
Name Type Description
stream external:Stream

Stream object to initialize streaming.

Source:
Example
const QueryStream = require('pg-query-stream');
const JSONStream = require('JSONStream');

// you can also use pgp.as.format(query, values, options)
// to format queries properly, via pg-promise;
const qs = new QueryStream('SELECT * FROM users');

db.stream(qs, stream => {
        // initiate streaming into the console:
        stream.pipe(JSONStream.stringify()).pipe(process.stdout);
    })
    .then(data => {
        console.log('Total rows processed:', data.processed,
          'Duration in milliseconds:', data.duration);
    })
    .catch(error => {
        // error;
    });