pg-auto is a wrapper around pg that enforces type safety with queries and defaults to returning Bluebird promises.

Installation

$ npm install pg-auto

Usage

Import pg-auto’s main tools:

const {Pool, sql} = require('pg-auto');

Create a connection pool (the options are the same as pg’s client and pool options):

const db = new Pool({
    host: '/var/run/postgresql',
    database: 'example',
});

Add an error listener to avoid exiting when a pooled client not currently in use encounters an error:

db.on('error', error => {
    console.error('Idle client error:', error);
});

Create queries with the sql template literal tag and run them through the pool:

const language = 'en';
const result = await db.query(sql`SELECT greeting FROM greetings WHERE language = ${language}`);
console.log(result.rows[0].greeting);  // Hello, world!

API

sql`…`

A template literal tag for SQL. Converts interpolated values to query parameters.

sql`…`.concat(sql`…`)

Returns the concatenation of two pieces of SQL. For more complex dynamic queries, consider using a query builder (like Knex or node-sql) instead.

let query = sql`SELECT greeting FROM greetings`;

if (searchPattern) {
    query = query.concat(sql` WHERE language ILIKE '%' || ${searchPattern} || '%'`);
}

await db.query(query);

new Pool([options])

Constructs a connection pool. The options are the same as pg’s client and pool options, and the pool emits the same events as the pg pool.

Pool#query(query)

Runs a query using a client from the pool, returning a Bluebird promise that will resolve to a pg result.

Pool#transaction(action, [options])

Runs the function action in a transaction, passing the client in which the transaction is active as an argument to action. action should return a promise. The transaction will be committed if the returned promise resolves, and rolled back if it rejects.

The available options, which reflect the options to BEGIN, are:

• isolationLevel: Controls the transaction’s isolation level. One of 'read committed', 'repeatable read', or 'serializable'.
• readOnly: Whether the transaction should be read-only.
• deferrable: Whether the transaction should be deferrable.

await db.transaction(async client => {
    await client.query(sql`INSERT INTO a VALUES ('a')`);
    await client.query(sql`INSERT INTO b VALUES ('b')`);
});

Pool#acquire()

For other, non-transaction cases when multiple queries have to be run in the same client. Returns a Bluebird-managed client object that provides a query() and transaction() respectively equivalent to Pool#query and Pool#transaction, and emits the same events as a pg client.

Pool#end()

Closes pool connections and invalidates the pool, like pg.Pool#end().

FAQ

How do I use a variable number of values with IN?

Use an array parameter with the = ANY operator instead:

const names = ['a', 'b', 'c'];

db.query(sql`SELECT id FROM tags WHERE name = ANY (${names})`)

Why isn’t my server exiting?

pg-auto uses a connection pool, which keeps connections alive for a while to avoid the overhead of creating new connections. Use db.end() to close idle connections and allow Node to exit.