Skip to content

Learn by Example

Jump to bottom
Vitaly Tomilov edited this page May 26, 2021 · 428 revisions

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

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;
    });
  • 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.
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

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.

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(async t => {
    // t.ctx = task config + state context;
    const user = await t.one('SELECT * FROM users WHERE id = $1', 123);
    return 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.

Simple

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

With Results

db.tx(async t => {
    // `t` and `this` here are the same;
    // this.ctx = transaction config + state context;

    const userId = await t.one('INSERT INTO users(name) VALUES($1) RETURNING id', 'John', a => a.id);
    const eventId = await t.one('INSERT INTO events(code) VALUES($1) RETURNING id', 123, a => a.id);

    return {userId, eventId};
})
    .then(data => {
        console.log(data.userId);
        console.log(data.eventId);
    })
    .catch(error => {
        // error
    });

See method tx.

Nested

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

NOTE: You can use this instead of parameters t1 and t2, when using a regular function.

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. See Data Imports with a complete example.

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:

import QueryStream from 'pg-query-stream';
import JSONStream from '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');

await db.stream(qs, s => {
    s.pipe(JSONStream.stringify()).pipe(process.stdout);
});
//=> resolves with: {processed, duration}

Here's another example, of streaming a query result into a CSV file:

import QueryStream from 'pg-query-stream';
import CsvWriter from 'csv-write-stream';
import {createWriteStream} from 'fs';

const csv = new CsvWriter();
const file = createWriteStream('out.csv');

const qs = new QueryStream('select * from my_table');

await db.stream(query, s => {
    s.pipe(csv).pipe(file);
});
//=> resolves with: {processed, duration}

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

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:

import {createReadStream} from 'fs';

const streamRead = pgp.spex.stream.read;
const rs = 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});
}

await db.tx(t => streamRead.call(t, rs, receiver));

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

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 idleTimeoutMillis) 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:name', '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:name, $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. See also Robust Listeners.
db.connect({direct: true})
    .then(sco => {
        sco.client.on('notification', data => {
            console.log('Received:', data);
            // data.payload = 'my payload string'
        });
        return sco.none('LISTEN $1:name', '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. For a complete example, see Robust Listeners.

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. See CSV Filter.

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

The library automatically formats type Buffer for columns of type bytea.

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 full use of it:

matrix

See complete usage example.

pg-promise

Clone this wiki locally