Members

(constant) queryResult :number

Query Result Mask

Binary mask that represents the number of rows expected from a query method, used by generic query method, plus func.

The mask is always the last optional parameter, which defaults to queryResult.any.

Any combination of flags is supported, except for one + many.

The type is available from the library's root: pgp.queryResult.

Type:
  • number
Properties:
Name Type Description
one number

Single row is expected, to be resolved as a single row-object.
many number

One or more rows expected, to be resolved as an array, with at least 1 row-object.
none number

Expecting no rows, to be resolved with null.
any number

many|none - any result is expected, to be resolved with an array of rows-objects.
Source:
See:

Type Definitions

EventContext

This common type is used for the following events: query, receive, error, task and transact.

Properties:
Name Type Description
cn string | object

Set only for event error, and only when the error is connection-related.

It is a safe copy of the connection string/object that was used when initializing db - the database instance.

If the original connection contains a password, the safe copy contains it masked with symbol #, so the connection can be logged safely, without exposing the password.
dc *

Database Context that was used when creating the database object (see Database). It is set for all events.
query string | object

Query string/object that was passed into the query method. This property is only set during events query, receive and error (only when the error is query-related).
client external:Client

pg.Client object that represents the connection. It is set for all events, except for event error when it is connection-related. Note that sometimes the value may be unset when the connection is lost.
params *

Formatting parameters for the query.

It is set only for events query, receive and error, and only when it is needed for logging. This library takes an extra step in figuring out when formatting parameters are of any value to the event logging:

  • when an error occurs related to the query formatting, event error is sent with the property set.
  • when initialization parameter pgFormat is used, and all query formatting is done within the PG library, events query and receive will have this property set also, since this library no longer handles the query formatting.

When this parameter is not set, it means one of the two things:

  • there were no parameters passed into the query method;
  • property query of this object already contains all the formatting values in it, so logging only the query is sufficient.
ctx TaskContext

Task/Transaction Context object.

This property is always set for events task and transact, while for events query, receive and error it is only set when they occur inside a task or transaction.
Source:

TaskContext

Task/Transaction Context used via property ctx inside tasks (methods Database.task and Database.taskIf) and transactions (methods Database.tx and Database.txIf).

Properties context, connected, parent, level, dc, isTX, tag, start, useCount and serverVersion are set just before the operation has started, while properties finish, duration, success and result are set immediately after the operation has finished.

Properties:
Name Type Description
context *

If the operation was invoked with a calling context - task.call(context,...) or tx.call(context,...), this property is set with the context that was passed in. Otherwise, the property doesn't exist.
dc *

Database Context that was passed into the Database object during construction.
isTX boolean

Indicates whether this operation is a transaction (as opposed to a regular task).
duration number

Number of milliseconds consumed by the operation.

Set after the operation has finished, it is simply a shortcut for finish - start.
level number

Task nesting level, starting from 0, counting both regular tasks and transactions.
txLevel number

Transaction nesting level, starting from 0. Transactions on level 0 use BEGIN/COMMIT/ROLLBACK, while transactions on nested levels use the corresponding SAVEPOINT commands.

This property exists only within the context of a transaction (isTX = true).
inTransaction boolean

Available in both tasks and transactions, it simplifies checking when there is a transaction going on either on this level or above.

For example, when you want to check for a containing transaction while inside a task, and only start a transaction when there is none yet.
parent TaskContext

Parent task/transaction context, or null when it is top-level.
connected boolean

Indicates when the task/transaction acquired the connection on its own (connected = true), and will release it once the operation has finished. When the value is false, the operation is reusing an existing connection.
tag *

Tag value as it was passed into the task. See methods task and tx.
start Date

Date/Time of when this operation started the execution.
useCount number

Number of times the connection has been previously used, starting with 0 for a freshly allocated physical connection.
serverVersion string

Version of the PostgreSQL server to which we are connected. Not available with Native Bindings.
finish Date

Once the operation has finished, this property is set to the Data/Time of when it happened.
success boolean

Once the operation has finished, this property indicates whether it was successful.
result *

Once the operation has finished, this property contains the result, depending on property success:

  • data resolved by the operation, if success = true
  • error / rejection reason, if success = false
Source:

Events

connect

Global notification of acquiring a new database connection from the connection pool, i.e. a virtual connection.

However, for direct calls to method Database.connect with parameter {direct: true}, this event represents a physical connection.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
e Object

Event Properties

Properties
Name Type Description
client external:Client

pg.Client object that represents the connection.
dc *

Database Context that was used when creating the database object (see Database).
useCount number

Number of times the connection has been previously used, starting with 0, for a freshly allocated physical connection.

This parameter is always 0 for direct connections (created by calling Database.connect with parameter {direct: true}).
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    connect(e) {
        const cp = e.client.connectionParameters;
        console.log('Connected to database:', cp.database);
    }

};

disconnect

Global notification of releasing a database connection back to the connection pool, i.e. releasing the virtual connection.

However, when releasing a direct connection (created by calling Database.connect with parameter {direct: true}), this event represents a physical disconnection.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
e Object

Event Properties

Properties
Name Type Description
client external:Client

pg.Client object that represents connection with the database.
dc *

Database Context that was used when creating the database object (see Database).
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    disconnect(e) {
       const cp = e.client.connectionParameters;
       console.log('Disconnecting from database:', cp.database);
    }

};

error

Global notification of every error encountered by this library.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
err *

The error encountered, of the same value and type as it was reported.
e EventContext

Event Context Object.
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    error(err, e) {

        if (e.cn) {
            // this is a connection-related error
            // cn = safe connection details passed into the library:
            //      if password is present, it is masked by #
        }

        if (e.query) {
            // query string is available
            if (e.params) {
                // query parameters are available
            }
        }

        if (e.ctx) {
            // occurred inside a task or transaction
        }
      }
};

extend

Extends Database protocol with custom methods and properties.

Override this event to extend the existing access layer with your own functions and properties best suited for your application.

The extension thus becomes available across all access layers:

  • Within the root/default database protocol;
  • Inside transactions, including nested ones;
  • Inside tasks, including nested ones.

All pre-defined methods and properties are read-only, so you will get an error, if you try overriding them.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
obj object

Protocol object to be extended.
dc *

Database Context that was used when creating the Database object.
Source:
See:
Examples
// In the example below we extend the protocol with function `addImage`
// that will insert one binary image and resolve with the new record id.

const initOptions = {

    // pg-promise initialization options...

    extend(obj, dc) {
        // dc = database context;
        obj.addImage = data => {
            // adds a new image and resolves with its record id:
            return obj.one('INSERT INTO images(data) VALUES($1) RETURNING id', data, a => a.id);
        }
    }
};
// It is best to extend the protocol by adding whole entity repositories to it as shown in the following example.
// For a comprehensive example see https://github.com/vitaly-t/pg-promise-demo

class UsersRepository {
    constructor(rep, pgp) {
        this.rep = rep;
        this.pgp = pgp;
    }

    add(name) {
        return this.rep.one('INSERT INTO users(name) VALUES($1) RETURNING id', name, a => a.id);
    }

    remove(id) {
        return this.rep.none('DELETE FROM users WHERE id = $1', id);
    }
}

// Overriding 'extend' event;
const initOptions = {

    // pg-promise initialization options...

    extend(obj, dc) {
        // dc = database context;
        obj.users = new UsersRepository(obj, pgp);
        // You can set different repositories based on `dc`
    }
};

// Usage example:
db.users.add('John', true)
    .then(id => {
        // user added successfully, id = new user's id
    })
    .catch(error => {
        // failed to add the user;
    });

query

Global notification of a query that's about to execute.

Notification happens just before the query execution. And if the handler throws an error, the query execution will be rejected with that error.

Parameters:
Name Type Description
e EventContext

Event Context Object.
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    query(e) {
        console.log('QUERY:', e.query);
    }
};

receive

Global notification of any data received from the database, coming from a regular query or from a stream.

The event is fired before the data reaches the client, and it serves two purposes:

  • Providing selective data logging for debugging;
  • Pre-processing data before it reaches the client.

NOTES:

  • If you alter the size of data directly or through the result object, it may affect QueryResultMask validation for regular queries, which is executed right after.
  • Any data pre-processing needs to be fast here, to avoid performance penalties.
  • If the event handler throws an error, the original request will be rejected with that error.

For methods Database.multi and Database.multiResult, this event is called for every result that's returned. And for method Database.stream, the event occurs for every record.

Parameters:
Name Type Description
e Object

Event Properties

Properties
Name Type Description
data Array.<Object>

Array of received objects/rows.

If any of those objects are modified during notification, the client will receive the modified data.
result external:Result
  • Original Result object, if the data is from a non-stream query, in which case data = result.rows. For single-query requests, Result object is extended with property duration - number of milliseconds it took to send the query, execute it and get the result back.
  • It is undefined when the data comes from a stream (method Database.stream).
ctx EventContext

Event Context Object.
Source:
Example
// Example below shows the fastest way to camelize all column names.
// NOTE: The example does not do processing for nested JSON objects.

const initOptions = {

    // pg-promise initialization options...

    receive(e) {
        camelizeColumns(e.data);
    }
};

function camelizeColumns(data) {
    const tmp = data[0];
    for (const prop in tmp) {
        const camel = pgp.utils.camelize(prop);
        if (!(camel in tmp)) {
            for (let i = 0; i < data.length; i++) {
                const d = data[i];
                d[camel] = d[prop];
                delete d[prop];
            }
        }
    }
}

task

Global notification of a task start / finish events, as executed via Database.task or Database.taskIf.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
e EventContext

Event Context Object.
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    task(e) {
        if (e.ctx.finish) {
            // this is a task->finish event;
            console.log('Duration:', e.ctx.duration);
            if (e.ctx.success) {
                // e.ctx.result = resolved data;
            } else {
                // e.ctx.result = error/rejection reason;
            }
        } else {
            // this is a task->start event;
            console.log('Start Time:', e.ctx.start);
        }
    }
};

transact

Global notification of a transaction start / finish events, as executed via Database.tx or Database.txIf.

The library will suppress any error thrown by the handler and write it into the console.

Parameters:
Name Type Description
e EventContext

Event Context Object.
Source:
Example
const initOptions = {

    // pg-promise initialization options...

    transact(e) {
        if (e.ctx.finish) {
            // this is a transaction->finish event;
            console.log('Duration:', e.ctx.duration);
            if (e.ctx.success) {
                // e.ctx.result = resolved data;
            } else {
                // e.ctx.result = error/rejection reason;
            }
        } else {
            // this is a transaction->start event;
            console.log('Start Time:', e.ctx.start);
        }
    }
};