pg-query

pg-query

Run queries with node-postgres with less boilerplate. node-posgres is low level by design and rather verbose.

This is a simple abstraction I've spun into a module after implementing something like this in most of my projects.

install

npm install pg-query

use

var query = require('pg-query');

query('SELECT NOW()', function(err, rows, result) {
  assert.equal(rows, result.rows);
});

Notice the callback is called with 3 parameters. First the error argument. Next the rows returned by the query. Finally the full result object which contains the same reference to the rows at result.rows

more examples

var query = require('pg-query');
query.connectionParameters = 'postgres://user:password@host:5432/database';

//accepts optional array of values as 2nd parameter for parameterized queries
query('SELECT $1::text as name', ['brian'], function(err, rows, result) {

});

If a callback is not passed, a promise is returned that will be resolved with [rows, result]

var query = require('pg-query');
query.connectionParameters = 'postgres://user:password@host:5432/database';

//accepts optional array of values as 2nd parameter for parameterized queries
var promise = query('SELECT $1::text as name', ['brian']);
function onSuccess(rows, result) {

}
function onError(error) {

}
promise.spread(onSuccess, onError);

query.first(text, [values], [cb])

Often I find I'm getting a single row by ID. query.first allows for easy composition into async structures by returning the first row of the query or null if there are no rows. Let me show you...

//this is the old way:
var getUserById = function(id, cb) {
  query('SELECT * FROM "user" WHERE id = $1', [id], function(err, rows) {
    cb(err, rows ? rows[0] : null)
  })
}

//this is much easier
var getUserById = function(id, cb) {
  query.first('SELECT * FROM "user" WHERE id = $1', id, cb)
}

//or even this if you're crazy
var getUserById = query.first.bind(query, 'SELECT * FROM "user" WHERE id = $1')

query.first does a bit of argument type checking. If you pass an array as the values argument it is passed to the query unchanged. If you pass anything else truthy it is wrapped inside an array. This makes query.first easier to compose and use.

comments

pg-query is domain aware so your callback will always be called in the correct domain. If you're not using domains it will gracefully ignore them.

pg-query uses whichever version of node-postgres you have installed in your project.

pg-query uses pg.defaults and/or environment variables to connect.

pg-query uses a random pooled database client for each query. If you're using a transaction (eg BEGIN/COMMIT) you need to check out a client from the pool manually. Repeat: DO NOT USE THIS FOR RUNNING TRANSACTIONS

todo

• Accept query object
• Accept anything that responds to .toQuery (node-sql queries, etc)
• Possibly add some way to configure connection parameters

license

MIT