Classes

Column

ColumnSet

TableName

Methods

(static) _TN(path) → {Table}

Parameters:

Source:

• helpers/table-name.js, line 147

See:

• TableName, TemplateStringsArray

Returns:

Type

Table

Example

const {ColumnSet, _TN} = pgp.helpers;

// Using as a regular function:
const cs1 = new ColumnSet(['id', 'name'], {table: _TN('schema.table')});

// Using as a template-tag function:
const schema = 'schema';
const cs2 = new ColumnSet(['id', 'name'], {table: _TN`${schema}.table`});

(static) concat(queries) → {string}

Parameters:

Source:

• helpers/methods/concat.js, line 16

Returns:

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}

Parameters:

Source:

• helpers/methods/insert.js, line 18

See:

• Column, ColumnSet, TableName

Returns:

Type

string

Examples

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

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:

insert(dataSingle, null, 'my-table');
//=> INSERT INTO "my-table"("val","msg") VALUES(123,'hello')

// Column details are required for a multi-row `INSERT`:
const {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 {ColumnSet, insert} = pgp.helpers;

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

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

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

Parameters:

Source:

• helpers/methods/sets.js, line 17

See:

• Column, ColumnSet

Returns:

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 {ColumnSet, sets} = pgp.helpers;

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

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

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

Parameters:

Source:

• helpers/methods/update.js, line 19

See:

• Column, ColumnSet, TableName

Returns:

Type

*

Examples

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

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:

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

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:

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 {ColumnSet, update} = pgp.helpers;

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

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:

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 {ColumnSet, update} = pgp.helpers;

const cs = new ColumnSet(['?id', '?name'], {table: 'tt'}); // no actual update-able columns
const result = 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}

Parameters:

Source:

• helpers/methods/values.js, line 17

See:

• Column, ColumnSet

Returns:

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 {ColumnSet, values} = pgp.helpers;

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

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

Type Definitions

ColumnConfig

Properties:

Source:

• helpers/column.js, line 341

QueryFormat

Properties:

Source:

• helpers/methods/concat.js, line 86

See:

• as.format

initCB(col) → {*}

Parameters:

Source:

• helpers/column.js, line 386

Returns:

Type

*

skipCB(col) → {boolean}

Parameters:

Source:

• helpers/column.js, line 420

Returns:

Type

boolean