Database(cn, dcopt) → {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 can return 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 done() on the connection object.

Method done takes one optional parameter - boolean kill flag, to signal the connection pool that you want it to kill the physical connection. This flag is ignored for direct connections, as they always close when released.

It should not be used just for chaining queries on the same connection, methods task and tx (for transactions) are to be used for that. This method is primarily for special cases, like LISTEN notifications.

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, as a stand-alone Client object, bypassing the connection pool.

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

Note that specifically for direct connections, method done returns a Promise, because those connections are closed physically, which may take time.

WARNING:

Do not use this option for regular query execution, because it exclusively occupies one physical channel, and it 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 (returns a Promise in case of direct connections)
    • 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) {
            // if you pass `true` into method done, i.e. done(true),
            // it will make the pool kill the physical connection.
            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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 database function that returns a table, abbreviating the full syntax of query('SELECT * FROM $1:alias($2:csv)', [funcName, values], qrm).

Parameters:
Name Type Attributes Default Description
funcName string

Name of the function to be executed. When it is not same-case, or contains extended symbols, it is double-quoted, as per the :alias filter, which also supports ., to auto-split into a composite name.
values array | value | function <optional>

Parameters for the function - one value | array of values | function returning value(s).
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 to be returned. 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 can return 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 | function

Query formatting parameter(s), or a function that returns it.

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, and resolves with an array of arrays of rows when successful.

The operation is atomic, i.e. all queries are executed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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
// Get data from 2 tables in a single request:
const [users, products] = await db.multi('SELECT * FROM users;SELECT * FROM products');

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

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

The operation is atomic, i.e. all queries are executed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 1 row to be returned. 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 returned value replaces the original one.

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 be returned. It resolves with the row-object when 1 row is returned, or 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 returned value replaces the original one.

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 stored procedure by name, abbreviating the full syntax of oneOrNone('CALL $1:alias($2:csv)', [procName, values], cb, thisArg).

NOTE: This method uses the new CALL syntax that requires PostgreSQL v11 or later.

Parameters:
Name Type Attributes Description
procName string

Name of the stored procedure to be executed. When it is not same-case, or contains extended symbols, it is double-quoted, as per the :alias filter, which also supports ., to auto-split into a composite SQL name.
values array | value | function <optional>

Parameters for the procedure - one value | array of values | function returning value(s).
cb function <optional>

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

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:

When the procedure takes output parameters, a single object is returned, with properties for the output values. Otherwise, the method resolves with null. And if the value-transformation callback is provided, it overrides the result.

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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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, and resolves 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 | function <optional>

Query formatting parameter(s), or a function that returns it.

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 returned value replaces the original one.

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

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 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 ES7 syntax for the callback:
db.task('my-task', async t {
        // t.ctx = task context object

        const user = await t.one('SELECT id FROM Users WHERE name = $1', 'John');
        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
    });

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

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 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.

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

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 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 ES7 syntax for the callback:
db.tx('my-transaction', async t {
        // t.ctx = transaction context object

        const user = await t.one('INSERT INTO Users(name, age) VALUES($1, $2) RETURNING id', ['Mike', 25]);
        return 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

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 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;
    });