Learn by Example

This tutorial is to be used for quick reference and to help first time users get started right away. For complete overview of the library use the official documentation.

• Basics
  • Simple SELECT
  • Simple INSERT
  • INSERT with Result
  • Functions & Procedures
  • Prepared Statements
  • Parameterized Queries
  • Raw Result
• Parameters
  • Single Parameter
  • Multiple Parameters
  • Named Parameters
  • Parameters-Functions
  • Raw Text
  • Open Values
  • SQL Names
  • Passing Arrays
• Tasks
• Transactions
  • Simple
  • With Results
  • Nested
  • Massive
• Streaming
  • From Database
  • Into Database
  • Mixing Up
• Other Recipes
  • LISTEN / NOTIFY
  • WHERE col IN (values)
  • INSERT Special Numbers
  • INSERT Binary
  • INSERT JSON
• Event Monitoring

Basics

Query method names reflect how many rows the query is expected to return: none, one, many, oneOrNone, manyOrNone = any. Do not confuse it with the number of rows affected by the query, which is a different thing.

There is also a generic query method, plus a few specialized methods: result, task, tx, stream, map, each, func and proc. And while inside a task or transaction, there are additional methods batch, sequence and page.

The protocol is fully customizable / extendable via event extend.

You can use the complete examples for a quick copy'n paste of the code snippets provided here. For all recommended reads see the project's WiKi.

Simple SELECT

• ES5

db.any('SELECT * FROM users WHERE active = $1', [true])
    .then(function(data) {
        // success;
    })
    .catch(function(error) {
        // error;
    });

• ES6

try {
    const users = yield db.any('SELECT * FROM users WHERE active = $1', [true]);
    // success
} 
catch(e) {
   // error
}

• ES7

try {
    const users = await db.any('SELECT * FROM users WHERE active = $1', [true]);
    // success
} 
catch(e) {
    // error
}

See: any

Simple INSERT

db.none('INSERT INTO users(name, active) VALUES($1, $2)', ['John', true])
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });

See: none

INSERT with result

db.one('INSERT INTO users(name, active) VALUES($1, $2) RETURNING id', ['John', true])
    .then(data => {
        console.log(data.id); // print new user id;
    })
    .catch(error => {
        console.log('ERROR:', error); // print error;
    });

See: one

Functions & Procedures

db.func('myFuncName', [123, new Date()])
    .then(data => {
        console.log('DATA:', data); // print data;
    })
    .catch(error => {
        console.log('ERROR:', error); // print the error;
    });

See: func

db.proc('myProcName', [123, new Date()])
    .then(data => {
        console.log('DATA:', data); // print data, if any;
    })
    .catch(error => {
        console.log('ERROR:', error.message || error); // print the error;
    });

See: proc

Prepared Statements

Postgres Prepared Statements serve two purposes:

1. Performance optimization (for complex queries only), via cached execution plan;
2. SQL injection prevention, as data and queries are passed in separately.

Using the syntax supported by node-postgres:

db.one({
    name: 'find-user',
    text: 'SELECT * FROM users WHERE id = $1', // can also be a QueryFile object
    values: [1]
})
    .then(user => {
        // user found;
    })
    .catch(error => {
        // error;    
    });

See also: PreparedStatement, QueryFile.

Parameterized Queries

Using the syntax supported by node-postgres:

db.one({
    text: 'SELECT * FROM users WHERE id = $1', // can also be a QueryFile object
    values: [1]
})
    .then(user => {
        // user found;
    })
    .catch(error => {
        // error;    
    });

See also: ParameterizedQuery, QueryFile.

Raw Result

When you want to access the original Result object for properties like rowCount (number of affected rows) or the fields (column details), use method result(query, values) to bypass the result verification and resolve with the Result object passed from PG.

// delete all inactive users;
db.result('DELETE FROM users WHERE active = $1', false)
    .then(result => {
        // rowCount = number of rows affected by the query
        console.log(result.rowCount); // print how many records were deleted;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

See: result

Parameters

Single Parameter

db.one('SELECT * FROM users WHERE id = $1', 123)
    .then(user => {
        console.log(user); // print user object;
    })
    .catch(error => {
        // error;    
    });

Multiple Parameters

db.any('SELECT * FROM users WHERE created < $1 AND active = $2', [new Date(), true])
    .then(data => {
        console.log('DATA:', data); // print data;
    })
    .catch(error => {
        console.log('ERROR:', error); // print the error;
    });

Named Parameters

Named Parameters are defined using syntax $*propName*, where * is any of the following open-close pairs: {}, (), [], <>, //, so you can use one to your liking, but remember that ${} is also used by ES6 template strings.

db.any('SELECT * FROM users WHERE name = ${name} AND active = $/active/',
    {
        name: 'John',
        active: true
    })
    .then(data => {
        console.log('DATA:', data); // print data;
    })
    .catch(error => {
        console.log('ERROR:', error); // print the error;
    });

Property with name this refers to the formatting object itself, to be injected as a JSON-formatted string (see this reference).

Combinations of different open-close symbols are not allowed.

Parameters-Functions

const account = {
    balance: 123.45,
    expenses: 2.7,
    margin: 0.1,
    total: a => {
        const t = a.balance + a.expenses;
        return a.margin ? (t + t * a.margin / 10) : t;
    }
};

db.none('INSERT INTO activity VALUES(${balance}, ${total})', account)
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });

Functions are good for returning any type of value that needs to be created in-line.

Raw Text

Raw (pre-formatted) text is injected by adding either :raw or symbol ^ to the end of the variable name:

• $1:raw, $2:raw,... or $*propName:raw*
• $1^, $2^,... or $*propName^*

where * is any of: {}, (), [], <>, //.

Unlike a regular variable, the value for a raw-text variable:

• doesn't get inner single-quotes fixed (replaced with two);
• isn't wrapped in single quotes;
• cannot be null;
• cannot be undefined inside array of parameters or as an object property.

Open Values

Open Values simplify string concatenation for escaped values. They are injected by adding either :value or symbol # to the end of the variable name:

• $1:value, $2:value,... or $*propName:value*
• $1#, $2#,... or $*propName#*

where * is any of: {}, (), [], <>, //.

// using variable $1#
db.any('SELECT * FROM users WHERE name LIKE '%$1#%'', 'John')
    .then(data => {
        // success;
    })
    .catch(error => {
        // error;
    });

Unlike a regular variable, an open-value variable:

• isn't wrapped in single quotes;
• cannot be null;
• cannot be undefined inside array of parameters or as an object property.

SQL Names

SQL Names and identifiers should be formatted using either :name or symbol ~ (tilde):

• $1:name, $2:name, or ${propName:name}
• $1~, $2~, or ${propName~}

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

db.any('SELECT ${column~} FROM ${table~}', {
    column: 'Column Name',
    table: 'Table Name'
});
// => SELECT "Column Name" FROM "Table Name"

See also: as.name

Passing Arrays

• as a property of an object:

db.none('INSERT INTO data(point) VALUES(${vector})',
    {
        // 2D array of integers: int[][];
        vector: [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
    })
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });

• as an element inside the array of parameters:

db.none('INSERT INTO data(labels) VALUES($1)',
    [
        // 3D array of strings: text[][][];
        [[['one'], ['two']], [['three'], ['four']]]
    ])
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;    
    });

You can also pass an array as the return result from a function, if the array needs to be created in-line.

Tasks

Tasks are for executing multiple queries against the same connection. Also see method task and Chaining Queries.

db.task(t => {
        // this.ctx = task config + state context;
        return t.one('SELECT * FROM users WHERE id = $1', 123)
            .then(user => {
                return t.any('SELECT * FROM events WHERE login = $1', user.name);
            });
    })
    .then(events => {
        // success;
    })
    .catch(error => {
        // error;
    });

The same example, using ES6 generators:

db.task(function * (t) {
        // this.ctx = task config + state context;
        let user = yield t.one('SELECT * FROM users WHERE id = $1', 123);
        return yield t.any('SELECT * FROM events WHERE login = $1', user.name);
    })
    .then(events => {
        // success;
    })
    .catch(error => {
        // error
    });

See method task.

Transactions

A transaction starts with tx, and considered a task surrounded by BEGIN and COMMIT/ROLLBACK queries. See method tx, Chaining Queries.

Same as for tasks, the callback can be either a simple function or an ES6 generator.

Simple

db.tx(t => {
        // this.ctx = transaction config + state context;
        return t.batch([
            t.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]),
            t.none('INSERT INTO audit(status, id) VALUES($1, $2)', ['active', 123])
        ]);
    })
    .then(data => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

With Results

db.tx(t => {
        // `t` and `this` here are the same;
        // this.ctx = transaction config + state context;
        return t.batch([
            t.one('INSERT INTO users(name) VALUES($1) RETURNING id', 'John'),
            t.one('INSERT INTO events(code) VALUES($1) RETURNING id', 123)
        ]);
    })
    // using .spread(function(user, event)) is best here, if supported;
    .then(data => {
        console.log(data[0].id); // print new user id;
        console.log(data[1].id); // print new event id;
    })
    .catch(error => {
        // error
    });

See method tx.

Nested

db.tx(t1 => {
        // this = t1 = transaction protocol context;
        // this.ctx = transaction config + state context;
        return this.batch([
            t1.none('UPDATE users SET active = $1 WHERE id = $2', [true, 123]),
            t1.none('INSERT INTO audit(status, id) VALUES($1, $2)', ['active', 123]),
            t1.tx(t2 => {
                // t2 = this != t1
                return this.batch([
                    t2.one('INSERT INTO users(name) VALUES($1) RETURNING id', 'John'),
                    t2.one('INSERT INTO events(code) VALUES($1) RETURNING id', 123)
                ]);
            })
        ]);
    })
    .then(data => {
        console.log(data[2][0].id); // print new user id;
        console.log(data[2][1].id); // print new event id;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

NOTE: You can use this instead of parameters t1 and t2.

And if Bluebird is your promise library, you can replace the last then section with:

    .get(2)
    .spread((user, event) => {
        console.log(user.id); // print new user id;
        console.log(event.id); // print new event id;
    })

Please be aware of the limitations in the nested transactions support.

Massive

Use method sequence when executing massive transactions, like bulk inserts, with way over 1,000 records.

function source(index) {   
    // create and return a promise object dynamically,
    // based on the index passed;
    if (index < 100000) {
        return this.any('INSERT INTO test(name) VALUES($1)', 'name-' + index);
    }
    // returning or resolving with undefined ends the sequence;
    // throwing an error will result in a reject;
}

db.tx(t => {
    // `t` and `this` here are the same;
    return this.sequence(source);
})
    .then(data => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

The same example, but using ES6 generators, and with preset limit:

function * source(index) {
    return yield this.any('INSERT INTO test(name) VALUES($1)', 'name-' + index);
}

db.tx(t => {
        // `t` and `this` here are the same;
        return this.sequence(source, {limit: 100000});
    })
    .then(data => {
        // success;
    })
    .catch(error => {
        // error
    });

Note that we use this.any as opposed to this.none only because this.none resolves with nothing, which in this case terminates the sequence.

For super-massive transactions support (millions of queries), see chapter Synchronous Transactions.

UPDATE: Article Performance Boost offers a newer and way better-performing approach.

Streaming

From Database

You can use pg-query-stream - high-performance, read-only query streaming via cursor (doesn't work with pgNative option). Its code example can be re-implemented via pg-promise as follows:

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, s => {
        // initiate streaming into the console:
        s.pipe(JSONStream.stringify()).pipe(process.stdout);
    })
    .then(data => {
        console.log('Total rows processed:', data.processed,
            'Duration in milliseconds:', data.duration);
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

This can be very useful for large data outputs, like table exports. See stream.

Into Database

If you want to stream data into the database, have a look at stream support within the spex library. Here's an example:

const fs = require('fs');
const streamRead = pgp.spex.stream.read;
const rs = fs.createReadStream('primes.txt');

function receiver(_, data) {
    
    function source(index) {
        if (index < data.length) {
            return data[index];
        }
    }

    function dest(index, data) {
        return this.none('INSERT INTO primes VALUES($1)', data);
    }

    return this.sequence(source, dest);
}

db.tx(t => {
    return streamRead.call(t, rs, receiver);
})
    .then(data => {
        console.log('DATA:', data);
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

And you should be aware of the considerations made in the Performance Boost.

Mixing Up

Let's throw in together pg-promise streaming + stream reading within spex and output the list of users into the console. The code example will look like this:

const QueryStream = require('pg-query-stream');
const streamRead = pgp.spex.stream.read;
const qs = new QueryStream('SELECT * FROM users');

function receiver(_, data) {
    console.log('USER:', data[0]);
}

db.stream(qs, s => {
        return streamRead(s, receiver);
    })
    .then(data => {
        // success;
    })
    .catch(function (error) {
        // error;
    });

Other Recipes

LISTEN / NOTIFY

• Temporary listener, using the connection pool: listening will stop when the connection pool releases the physical connection, due to inactivity (see poolIdleTimeout) or a connectivity error.

let sco; // shared connection object

db.connect()
    .then(obj => {
        sco = obj;
        sco.client.on('notification', data => {
            console.log('Received:', data);
            // data.payload = 'my payload string'
        });
        return sco.none('LISTEN $1~', 'my-channel');
    })
    .catch(error => {
        console.log('Error:', error);
    })
    .finally(() => {
        if (sco) {
            sco.done(); // releasing the connection back to the pool
        }
    });

Sending a notification example:

db.none('NOTIFY $1~, $2', ['my-channel', 'my payload string'])
    .then(() => {
        console.log('Notification sent.');
    })
    .catch(error => {
        console.log('NOTIFY error:', error);
    });

• Permanent listener, outside of the connection pool: listening will never stop, unless the physical connection fails, or if you call sco.done() to release it.

db.connect({direct: true})
    .then(sco => {
        sco.client.on('notification', data => {
            console.log('Received:', data);
            // data.payload = 'my payload string'
        });
        sco.client.on('error', e => {
            // you should handle connectivity errors for permanent connections,
            // and to provide a way to re-create the connection when failed.
        });
        return sco.none('LISTEN $1~', 'my-channel');
    })
    .catch(error => {
        console.log('Error:', error);
    });

i.e. the difference is that we use {direct: true} to create a separate Client, plus we never release the connection.

However, we can still save sco and call sco.done() at some point, if we want to shut down the channel. For example, if the physical connection fails, you will need to re-create the connection and set up your listeners again.

See also: connect, UNLISTEN

WHERE col IN (values)

const data = [1, 'two', 3, 'four'];

db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])
    .then(data => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

NOTE: data must be a parameter inside an array.

INSERT Special Numbers

Floating-point types can accept special strings: NaN, +Infinity and -Infinity:

db.none('INSERT INTO test VALUES($1, $2, $3, $4)', [123, NaN, 1/0, -1/0])
    .then(() => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });
// This will execute the following query:
// INSERT INTO test VALUES(123, 'NaN', '+Infinity', '-Infinity')

INSERT Binary

const fs = require('fs');

// read in image in raw format (as type Buffer):
fs.readFile('image.jpg', (err, imgData) => {
    // inserting data into column 'img' of type 'bytea':
    db.none('INSERT INTO images(img) VALUES($1)', imgData)
        .then(() => {
            // success;
        })
        .catch(error => {
            // error;
        });
});

INSERT JSON

const user = {
    name: 'John',
    age: 30,
    active: true
};

• as an array element:

// 'info' column is of type json;
db.none('INSERT INTO users(info) VALUES($1)', [user])
    .then(() => {
        // success;
    })
    .catch(error => {
        console.log('ERROR:', error);
    });

• as an object property:

db.none('INSERT INTO users(info) VALUES(${obj})', {
    obj: user
})
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });

• via this reference:

db.none('INSERT INTO users(info) VALUES(${this})', user)
    .then(() => {
        // success;
    })
    .catch(error => {
        // error;
    });

Event Monitoring

This library provides a flexible event system for you to be able to track every aspect of the query execution, while pg-monitor makes the full use of it:

See complete usage example.