pg-promise

PostgreSQL interface for Node.js

About
Support & Sponsorship
Documentation
Contributing
[Usage]
- Methods
- Query Formatting
  - [Index Variables]
  - [Named Parameters]
    - [Nested Named Parameters]
- Formatting Filters
  - [SQL Names]
    - [Alias Filter]
  - [Raw Text]
  - [Open Values]
  - [JSON Filter]
  - [CSV Filter]
- [Custom Type Formatting]
  - [Explicit CTF]
  - [Symbolic CTF]
- [Query Files]
- [Tasks]
  - [Conditional Tasks]
- [Transactions]
  - [Nested Transactions]
    - [Limitations]
  - [Configurable Transactions]
  - [Conditional Transactions]
- [Library de-initialization]

About

Built on top of [node-postgres], this library adds the following:

Automatic connections
Automatic transactions
Powerful query-formatting engine + query generation
Declarative approach to handling query results
Global events reporting for central handling
Extensive support for external SQL files
Support for all promise libraries

At its inception in 2015, this library was only adding promises to the base driver, hence the name pg-promise. And while the original name was kept, the library's functionality was vastly extended, with promises now being only its tiny part.

Support & Sponsorship

I do free support here and on StackOverflow.

And if you want to help this project, I can accept Bitcoin: 1yki7MXMkuDw8qqe5icVdh1GJZSQSzKZp

Documentation

Chapter [Usage] below explains the basics you need to know, while the [Official Documentation] gets you started, and provides links to all other resources.

Contributing

Please read the [Contribution Notes] before opening any new issue or PR.

Usage

Once you have created a [Database] object, according to the steps in the [Official Documentation], you get access to the methods documented below.

Methods

All query methods of the library are based off generic method [query].

You should normally use only the derived, result-specific methods for executing queries, all of which are named according to how many rows of data the query is expected to return, so for each query you should pick the right method: [none], [one], [oneOrNone], [many], [manyOrNone] = [any]. Do not confuse the method name for the number of rows to be affected by the query, which is completely irrelevant.

By relying on the result-specific methods you protect your code from an unexpected number of data rows, to be automatically rejected (treated as errors).

There are also a few specific methods that you will often need:

[result], [multi], [multiResult] - for verbose and/or multi-query results;
[map], [each] - for simpler/inline result pre-processing/re-mapping;
[func], [proc] - to simplify execution of SQL functions/procedures;
[stream] - to access rows from a query via a read stream;
[connect], [task], [tx] + [txIf] - for shared connections + automatic transactions, each exposing a connected protocol that has additional methods [batch], [page] and [sequence].

The protocol is fully customizable / extendable via event [extend].

IMPORTANT:

The most important methods to understand from start are [task] and [tx]/[txIf] (see [Tasks] and [Transactions]). As documented for method [query], it acquires and releases the connection, which makes it a poor choice for executing multiple queries at once. For this reason, [Chaining Queries] is a must-read, to avoid writing the code that misuses connections.

[Learn by Example] is a beginner's tutorial based on examples.

Query Formatting

This library comes with embedded query-formatting engine that offers high-performance value escaping, flexibility and extensibility. It is used by default with all query methods, unless you opt out of it entirely via option pgFormatting within [Initialization Options].

All formatting methods used internally are available from the [formatting] namespace, so they can also be used directly when needed. The main method there is [format], used by every query method to format the query.

The formatting syntax for variables is decided from the type of values passed in:

[Index Variables] when values is an array or a single basic type;
[Named Parameters] when values is an object (other than Array or null).

ATTENTION: Never use ES6 template strings or manual concatenation to generate queries, as both can easily result in broken queries! Only this library's formatting engine knows how to properly escape variable values for PostgreSQL.

Index Variables

The simplest (classic) formatting uses $1, $2, ... syntax to inject values into the query string, based on their index (from $1 to $100000) from the array of values:

await db.any('SELECT * FROM product WHERE price BETWEEN $1 AND $2', [1, 10])

The formatting engine also supports single-value parametrization for queries that use only variable $1:

await db.any('SELECT * FROM users WHERE name = $1', 'John')

This however works only for types number, bigint, string, boolean, Date and null, because types like Array and Object change the way parameters are interpreted. That's why passing in index variables within an array is advised as safer, to avoid ambiguities.

Named Parameters

When a query method is parameterized with values as an object, the formatting engine expects the query to use the Named Parameter syntax $*propName*, with * being any of the following open-close pairs: {}, (), <>, [], //.

// We can use every supported variable syntax at the same time, if needed:
await db.none('INSERT INTO users(first_name, last_name, age) VALUES(${name.first}, $<name.last>, $/age/)', {
    name: {first: 'John', last: 'Dow'},
    age: 30
});

IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings, as those have no knowledge of how to format values for PostgreSQL. Inside ES6 template strings you should only use one of the 4 alternatives - $(), $<>, $[] or $//. In general, you should either use the standard strings for SQL, or place SQL into external files - see [Query Files].

Valid variable names are limited to the syntax of open-name JavaScript variables. And name this has special meaning - it refers to the formatting object itself (see below).

Keep in mind that while property values null and undefined are both formatted as null, an error is thrown when the property does not exist.

this reference

Property this refers to the formatting object itself, to be inserted as a JSON-formatted string.

await db.none('INSERT INTO documents(id, doc) VALUES(${id}, ${this})', {
    id: 123,
    body: 'some text'    
})
//=> INSERT INTO documents(id, doc) VALUES(123, '{"id":123,"body":"some text"}')

Nested Named Parameters

[Named Parameters] support property name nesting of any depth.

Example

const obj = {
    one: {
        two: {
            three: {
                value1: 123,
                value2: a => {
                    // a = obj.one.two.three
                    return 'hello';
                },
                value3: function(a) {
                    // a = this = obj.one.two.three
                    return 'world';
                },
                value4: {
                    toPostgres: a => {
                        // Custom Type Formatting
                        // a = obj.one.two.three.value4
                        return a.text;
                    },
                    text: 'custom'
                }                
            }
        }
    }
};
await db.one('SELECT ${one.two.three.value1}', obj); //=> SELECT 123
await db.one('SELECT ${one.two.three.value2}', obj); //=> SELECT 'hello'
await db.one('SELECT ${one.two.three.value3}', obj); //=> SELECT 'world'
await db.one('SELECT ${one.two.three.value4}', obj); //=> SELECT 'custom'

The last name in the resolution can be anything, including:

the actual value (basic JavaScript type)
a function that returns:
- the actual value
- another function
- a [Custom Type Formatting] object
a [Custom Type Formatting] object that returns:
- the actual value
- another [Custom Type Formatting] object
- a function

i.e. the resolution chain is infinitely flexible, and supports recursion without limits.

Please note, however, that nested parameters are not supported within the [helpers] namespace.

Formatting Filters

By default, all values are formatted according to their JavaScript type. Formatting filters (or modifiers), change that, so the value is formatted differently.

Note that formatting filters work only for normal queries, and are not available within [PreparedStatement] or [ParameterizedQuery], because those are, by definition, formatted on the server side.

Filters use the same syntax for [Index Variables] and [Named Parameters], following immediately the variable name:

With Index Variables

await db.any('SELECT $1:name FROM $2:name', ['price', 'products'])
//=> SELECT "price" FROM "products"

With Named Parameters

await db.any('SELECT ${column:name} FROM ${table:name}', {
    column: 'price',
    table: 'products'    
});
//=> SELECT "price" FROM "products"

The following filters are supported:

:name / ~ - [SQL Names]
- :alias - [Alias Filter]
:raw / ^ - [Raw Text]
:value / # - [Open Values]
:csv / :list - [CSV Filter]
:json - [JSON Filter]

SQL Names

When a variable name ends with :name, or shorter syntax ~ (tilde), it represents an SQL name or identifier, to be escaped accordingly:

Using ~ filter

await db.query('INSERT INTO $1~($2~) VALUES(...)', ['Table Name', 'Column Name']);
//=> INSERT INTO "Table Name"("Column Name") VALUES(...)

Using :name filter

await db.query('INSERT INTO $1:name($2:name) VALUES(...)', ['Table Name', 'Column Name']);
//=> INSERT INTO "Table Name"("Column Name") VALUES(...)

Typically, an SQL name variable is a text string, which must be at least 1 character long. However, pg-promise supports a variety of ways in which SQL names can be supplied:

A string that contains only * (asterisks) is automatically recognized as all columns:

await db.query('SELECT $1:name FROM $2:name', ['*', 'table']);
//=> SELECT * FROM "table"

An array of strings to represent column names:

await db.query('SELECT ${columns:name} FROM ${table:name}', {
    columns: ['column1', 'column2'],
    table: 'table'
});
//=> SELECT "column1","column2" FROM "table"

Any object that's not an array gets its properties enumerated for column names:

const obj = {
    one: 1,
    two: 2
};

await db.query('SELECT $1:name FROM $2:name', [obj, 'table']);
//=> SELECT "one","two" FROM "table"

In addition, the syntax supports this to enumerate column names from the formatting object:

const obj = {
    one: 1,
    two: 2
};

await db.query('INSERT INTO table(${this:name}) VALUES(${this:csv})', obj);
//=> INSERT INTO table("one","two") VALUES(1, 2)

Relying on this type of formatting for sql names and identifiers, along with regular variable formatting protects your application from [SQL injection].

Method [as.name] implements the formatting.

Alias Filter

An alias is a simpler, less-strict version of :name filter, which only supports a text string, i.e. it does not support *, this, array or object as inputs, like :name does. However, it supports other popular cases that are less strict, but cover at least 99% of all use cases, as shown below.

It will skip adding surrounding double quotes when the name is a same-case single word:

await db.any('SELECT full_name as $1:alias FROM $2:name', ['name', 'table']);
//=> SELECT full_name as name FROM "table"

It will automatically split the name into multiple SQL names when encountering ., and then escape each part separately, thus supporting auto-composite SQL names:

await db.any('SELECT * FROM $1:alias', ['schemaName.table']);
//=> SELECT * FROM "schemaName".table

For more details see method [as.alias] that implements the formatting.

Raw Text

When a variable name ends with :raw, or shorter syntax ^, the value is to be injected as raw text, without escaping.

Such variables cannot be null or undefined, because of the ambiguous meaning in this case, and those values will throw error Values null/undefined cannot be used as raw text.

const where = pgp.as.format('WHERE price BETWEEN $1 AND $2', [5, 10]); // pre-format WHERE condition
await db.any('SELECT * FROM products $1:raw', where);
//=> SELECT * FROM products WHERE price BETWEEN 5 AND 10

Special syntax this:raw / this^ is supported, to inject the formatting object as raw JSON string.

WARNING:
This filter is unsafe, and should not be used for values that come from the client side, as it may result in [SQL injection].

Open Values

When a variable name ends with :value, or shorter syntax #, it is escaped as usual, except when its type is a string, the trailing quotes are not added.

Open values are primarily to be able to compose complete LIKE/ILIKE dynamic statements in external SQL files, without having to generate them in the code.

i.e. you can either generate a filter like this in your code:

const name = 'John';
const filter = '%' + name + '%';

and then pass it in as a regular string variable, or you can pass in only name, and have your query use the open-value syntax to add the extra search logic:

SELECT * FROM table WHERE name LIKE '%$1:value%')

WARNING:
This filter is unsafe, and should not be used for values that come from the client side, as it may result in [SQL injection].

Method [as.value] implements the formatting.

JSON Filter

When a variable name ends with :json, explicit JSON formatting is applied to the value.

By default, any object that's not Date, Array, Buffer, null or Custom-Type (see [Custom Type Formatting]), is automatically formatted as JSON.

Method [as.json] implements the formatting.

CSV Filter

When a variable name ends with :csv or :list, it is formatted as a list of Comma-Separated Values, with each value formatted according to its JavaScript type.

Typically, you would use this for a value that's an array, though it works for single values also. See the examples below.

Using :csv filter

const ids = [1, 2, 3];
await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [ids])
//=> SELECT * FROM table WHERE id IN (1,2,3)

Using :list filter

const ids = [1, 2, 3];
await db.any('SELECT * FROM table WHERE id IN ($1:list)', [ids])
//=> SELECT * FROM table WHERE id IN (1,2,3)

Using automatic property enumeration:

Enumeration with :csv filter

const obj = {first: 123, second: 'text'};

await db.none('INSERT INTO table($1:name) VALUES($1:csv)', [obj])
//=> INSERT INTO table("first","second") VALUES(123,'text')

await db.none('INSERT INTO table(${this:name}) VALUES(${this:csv})', obj)
//=> INSERT INTO table("first","second")