helpers

Namespace for query-formatting generators, available as pgp.helpers, after initializing the library.

It unifies the approach to generating multi-row INSERT / UPDATE queries with the single-row ones.

See also: Performance Boost.

Properties:
Name Type Description
TableName function

TableName class constructor.
ColumnSet function

ColumnSet class constructor.
Column function

Column class constructor.
insert function

insert static method.
update function

update static method.
values function

values static method.
sets function

sets static method.
concat function

concat static method.
Source:

Classes

Column
ColumnSet
TableName

Methods

(static) concat(queries) → {string}

Formats and concatenates multiple queries into a single query string.

Before joining the queries, the method does the following:

  • Formats each query, if values are provided;
  • Removes all leading and trailing spaces, tabs and semi-colons;
  • Automatically skips all empty queries.
Parameters:
Name Type Description
queries array.<(string|helpers.QueryFormat|QueryFile)>

Array of mixed-type elements:

  • a simple query string, to be used as is
  • a QueryFormat-like object = {query, [values], [options]}
  • a QueryFile object
Source:
Returns:

Concatenated string with all queries.

Type
string
Example
const pgp = require('pg-promise')();

const qf1 = new pgp.QueryFile('./query1.sql', {minify: true});
const qf2 = new pgp.QueryFile('./query2.sql', {minify: true});

const query = pgp.helpers.concat([
    {query: 'INSERT INTO Users(name, age) VALUES($1, $2)', values: ['John', 23]}, // QueryFormat-like object
    {query: qf1, values: [1, 'Name']}, // QueryFile with formatting parameters
    'SELECT count(*) FROM Users', // a simple-string query,
    qf2 // direct QueryFile object
]);

// query = concatenated string with all the queries

(static) insert(data, columnsopt, tableopt) → {string}

Generates an INSERT query for either one object or an array of objects.

Parameters:
Name Type Attributes Description
data object | Array.<object>

An insert object with properties for insert values, or an array of such objects.

When data is not a non-null object and not an array, it will throw TypeError = Invalid parameter 'data' specified.

When data is an empty array, it will throw TypeError = Cannot generate an INSERT from an empty array.

When data is an array that contains a non-object value, the method will throw Error = Invalid insert object at index N.
columns array | helpers.Column | helpers.ColumnSet <optional>

Set of columns to be inserted.

It is optional when data is a single object, and required when data is an array of objects. If not specified for an array of objects, the method will throw TypeError = Parameter 'columns' is required when inserting multiple records.

When columns is not a ColumnSet object, a temporary ColumnSet is created - from the value of columns (if it was specified), or from the value of data (if it is not an array).

When the final ColumnSet is empty (no columns in it), the method will throw Error = Cannot generate an INSERT without any columns.
table helpers.TableName | string | Object <optional>

Destination table.

It is normally a required parameter. But when columns is passed in as a ColumnSet object with table set in it, that will be used when this parameter isn't specified. When neither is available, the method will throw Error = Table name is unknown.
Source:
See:
Returns:

An INSERT query string.

Type
string
Examples
const pgp = require('pg-promise')({
   capSQL: true // if you want all generated SQL capitalized
});

const dataSingle = {val: 123, msg: 'hello'};
const dataMulti = [{val: 123, msg: 'hello'}, {val: 456, msg: 'world!'}];

// Column details can be taken from the data object:

pgp.helpers.insert(dataSingle, null, 'my-table');
//=> INSERT INTO "my-table"("val","msg") VALUES(123,'hello')
// Column details are required for a multi-row `INSERT`:

pgp.helpers.insert(dataMulti, ['val', 'msg'], 'my-table');
//=> INSERT INTO "my-table"("val","msg") VALUES(123,'hello'),(456,'world!')
// Column details from a reusable ColumnSet (recommended for performance):

const cs = new pgp.helpers.ColumnSet(['val', 'msg'], {table: 'my-table'});

pgp.helpers.insert(dataMulti, cs);
//=> INSERT INTO "my-table"("val","msg") VALUES(123,'hello'),(456,'world!')

(static) sets(data, columnsopt) → {string}

Generates a string of comma-separated value-set statements from a single object: col1=val1, col2=val2, ..., to be used as part of a query.

Since it is to be used as part of UPDATE queries, Column properties cnd and skip apply.

Parameters:
Name Type Attributes Description
data object

A simple, non-null and non-array source object.

If it is anything else, the method will throw TypeError = Invalid parameter 'data' specified.
columns array | helpers.Column | helpers.ColumnSet <optional>

Columns for which to set values.

When not specified, properties of the data object are used.

When no effective columns are found, an empty string is returned.
Source:
See:
Returns:
  • comma-separated value-set statements for the data object
  • an empty string, if no effective columns found
Type
string
Examples
const pgp = require('pg-promise')();

const data = {id: 1, val: 123, msg: 'hello'};

// Properties can be pulled automatically from the object:

pgp.helpers.sets(data);
//=> "id"=1,"val"=123,"msg"='hello'
// Column details from a reusable ColumnSet (recommended for performance);
// NOTE: Conditional columns (start with '?') are skipped:

const cs = new pgp.helpers.ColumnSet(['?id','val', 'msg']);

pgp.helpers.sets(data, cs);
//=> "val"=123,"msg"='hello'

(static) update(data, columnsopt, tableopt, optionsopt) → {*}

Generates a simplified UPDATE query for either one object or an array of objects.

The resulting query needs a WHERE clause to be appended to it, to specify the update logic. This is to allow for update conditions of any complexity that are easy to add.

Parameters:
Name Type Attributes Description
data object | Array.<object>

An update object with properties for update values, or an array of such objects.

When data is not a non-null object and not an array, it will throw TypeError = Invalid parameter 'data' specified.

When data is an empty array, it will throw TypeError = Cannot generate an UPDATE from an empty array.

When data is an array that contains a non-object value, the method will throw Error = Invalid update object at index N.
columns array | helpers.Column | helpers.ColumnSet <optional>

Set of columns to be updated.

It is optional when data is a single object, and required when data is an array of objects. If not specified for an array of objects, the method will throw TypeError = Parameter 'columns' is required when updating multiple records.

When columns is not a ColumnSet object, a temporary ColumnSet is created - from the value of columns (if it was specified), or from the value of data (if it is not an array).

When the final ColumnSet is empty (no columns in it), the method will throw Error = Cannot generate an UPDATE without any columns., unless option emptyUpdate was specified.
table helpers.TableName | string | Object <optional>

Table to be updated.

It is normally a required parameter. But when columns is passed in as a ColumnSet object with table set in it, that will be used when this parameter isn't specified. When neither is available, the method will throw Error = Table name is unknown.
options Object <optional>

An object with formatting options for multi-row UPDATE queries.

Properties
Name Type Attributes Default Description
tableAlias string <optional>
 t

Name of the SQL variable that represents the destination table.
valueAlias string <optional>
 v

Name of the SQL variable that represents the values.
emptyUpdate * <optional>

This is a convenience option, to avoid throwing an error when generating a conditional update results in no columns.

When present, regardless of the value, this option overrides the method's behavior when applying skip logic results in no columns, i.e. when every column is being skipped.

By default, in that situation the method throws Error = Cannot generate an UPDATE without any columns. But when this option is present, the method will instead return whatever value the option was passed.
Source:
See:
Returns:

An UPDATE query string that needs a WHERE condition appended.

If it results in an empty update, and option emptyUpdate was passed in, then the method returns the value to which the option was set.

Type
*
Examples
const pgp = require('pg-promise')({
   capSQL: true // if you want all generated SQL capitalized
});

const dataSingle = {id: 1, val: 123, msg: 'hello'};
const dataMulti = [{id: 1, val: 123, msg: 'hello'}, {id: 2, val: 456, msg: 'world!'}];

// Although column details can be taken from the data object, it is not
// a likely scenario for an update, unless updating the whole table:

pgp.helpers.update(dataSingle, null, 'my-table');
//=> UPDATE "my-table" SET "id"=1,"val"=123,"msg"='hello'
// A typical single-object update:

// Dynamic conditions must be escaped/formatted properly:
const condition = pgp.as.format(' WHERE id = ${id}', dataSingle);

pgp.helpers.update(dataSingle, ['val', 'msg'], 'my-table') + condition;
//=> UPDATE "my-table" SET "val"=123,"msg"='hello' WHERE id = 1
// Column details are required for a multi-row `UPDATE`;
// Adding '?' in front of a column name means it is only for a WHERE condition:

pgp.helpers.update(dataMulti, ['?id', 'val', 'msg'], 'my-table') + ' WHERE v.id = t.id';
//=> UPDATE "my-table" AS t SET "val"=v."val","msg"=v."msg" FROM (VALUES(1,123,'hello'),(2,456,'world!'))
//   AS v("id","val","msg") WHERE v.id = t.id
// Column details from a reusable ColumnSet (recommended for performance):

const cs = new pgp.helpers.ColumnSet(['?id', 'val', 'msg'], {table: 'my-table'});

pgp.helpers.update(dataMulti, cs) + ' WHERE v.id = t.id';
//=> UPDATE "my-table" AS t SET "val"=v."val","msg"=v."msg" FROM (VALUES(1,123,'hello'),(2,456,'world!'))
//   AS v("id","val","msg") WHERE v.id = t.id
// Using parameter `options` to change the default alias names:

pgp.helpers.update(dataMulti, cs, null, {tableAlias: 'X', valueAlias: 'Y'}) + ' WHERE Y.id = X.id';
//=> UPDATE "my-table" AS X SET "val"=Y."val","msg"=Y."msg" FROM (VALUES(1,123,'hello'),(2,456,'world!'))
//   AS Y("id","val","msg") WHERE Y.id = X.id
// Handling an empty update

const cs = new pgp.helpers.ColumnSet(['?id', '?name'], {table: 'tt'}); // no actual update-able columns
const result = pgp.helpers.update(dataMulti, cs, null, {emptyUpdate: 123});
if(result === 123) {
   // We know the update is empty, i.e. no columns that can be updated;
   // And it didn't throw because we specified `emptyUpdate` option.
}

(static) values(data, columnsopt) → {string}

Generates a string of comma-separated value groups from either one object or an array of objects, to be used as part of a query:

  • from a single object: (val_1, val_2, ...)
  • from an array of objects: (val_11, val_12, ...), (val_21, val_22, ...)
Parameters:
Name Type Attributes Description
data object | Array.<object>

A source object with properties as values, or an array of such objects.

If it is anything else, the method will throw TypeError = Invalid parameter 'data' specified.

When data is an array that contains a non-object value, the method will throw Error = Invalid object at index N.

When data is an empty array, an empty string is returned.
columns array | helpers.Column | helpers.ColumnSet <optional>

Columns for which to return values.

It is optional when data is a single object, and required when data is an array of objects. If not specified for an array of objects, the method will throw TypeError = Parameter 'columns' is required when generating multi-row values.

When the final ColumnSet is empty (no columns in it), the method will throw Error = Cannot generate values without any columns.
Source:
See:
Returns:
  • comma-separated value groups, according to data
  • an empty string, if data is an empty array
Type
string
Examples
const pgp = require('pg-promise')();

const dataSingle = {val: 123, msg: 'hello'};
const dataMulti = [{val: 123, msg: 'hello'}, {val: 456, msg: 'world!'}];

// Properties can be pulled automatically from a single object:

pgp.helpers.values(dataSingle);
//=> (123,'hello')
// Column details are required when using an array of objects:

pgp.helpers.values(dataMulti, ['val', 'msg']);
//=> (123,'hello'),(456,'world!')
// Column details from a reusable ColumnSet (recommended for performance):

const cs = new pgp.helpers.ColumnSet(['val', 'msg']);

pgp.helpers.values(dataMulti, cs);
//=> (123,'hello'),(456,'world!')

Type Definitions

ColumnConfig

A simple structure with column details, to be passed into the Column constructor for initialization.

Properties:
Name Type Attributes Description
name string

Destination column name + source property name (if prop is skipped). The name must adhere to JavaScript syntax for variables, unless prop is specified, in which case name represents only the column name, and therefore can be any non-empty string.
prop string <optional>

Source property name, if different from the column's name. It must adhere to JavaScript syntax for variables.

It is ignored when it is the same as name.
mod string <optional>

Formatting modifier, as supported by method as.format: ^, ~, #, :csv, :list, :json, :alias, :name, :raw, :value.
cast string <optional>

Server-side type casting. Leading :: is allowed, but not needed (automatically removed when specified).
cnd boolean <optional>

Conditional column flag.

Used by methods update and sets, ignored by methods insert and values. It indicates that the column is reserved for a WHERE condition, not to be set or updated.

It can be set from a string initialization, by adding ? in front of the name.
def * <optional>

Default value for the property, to be used only when the source object doesn't have the property. It is ignored when property init is set.
init helpers.initCB <optional>

Override callback for the value.
skip helpers.skipCB <optional>

An override for skipping columns dynamically.

Used by methods update (for a single object) and sets, ignored by methods insert and values.

It is also ignored when conditional flag cnd is set.
Source:

QueryFormat

A simple structure of parameters to be passed into method as.format exactly as they are, used by helpers.concat.

Properties:
Name Type Attributes Description
query string | value | object

A query string or a value/object that implements Custom Type Formatting, to be formatted according to values.
values array | object | value <optional>

Query-formatting values.
options object <optional>

Query-formatting options, as supported by method as.format.
Source:
See:

initCB(col) → {*}

A callback function type used by parameter init within ColumnConfig.

It works as an override for the corresponding property value in the source object.

The function is called with this set to the source object.

Parameters:
Name Type Description
col *

Column-to-property descriptor.

Properties
Name Type Description
source object

The source object, equals to this that's passed into the function.
name string

Resolved name of the property within the source object, i.e. the value of name when prop is not used for the column, or the value of prop when it is specified.
value *

Property value, set to one of the following:

  • Value of the property within the source object (value = source[name]), if the property exists
  • If the property doesn't exist and def is set in the column, then value is set to the value of def
  • If the property doesn't exist and def is not set in the column, then value is set to undefined
exists boolean

Indicates whether the property exists in the source object (exists = name in source).
Source:
Returns:

The new value to be used for the corresponding column.

Type
*

skipCB(col) → {boolean}

A callback function type used by parameter skip within ColumnConfig.

It is to dynamically determine when the property with specified name in the source object is to be skipped.

The function is called with this set to the source object.

Parameters:
Name Type Description
col *

Column-to-property descriptor.

Properties
Name Type Description
source object

The source object, equals to this that's passed into the function.
name string

Resolved name of the property within the source object, i.e. the value of name when prop is not used for the column, or the value of prop when it is specified.
value *

Property value, set to one of the following:

  • Value of the property within the source object (value = source[name]), if the property exists
  • If the property doesn't exist and def is set in the column, then value is set to the value of def
  • If the property doesn't exist and def is not set in the column, then value is set to undefined
exists boolean

Indicates whether the property exists in the source object (exists = name in source).
Source:
Returns:

A truthy value that indicates whether the column is to be skipped.

Type
boolean