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
IN
?
How do I use a variable number of values with 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.