nodejs, postgres, and bluebird

Question

I have been attempting to use bluebird promises with the pg library, and even found this post but sadly I am too new of a StackOverflow user to simply comment there directly: Manually promisifying pg.connect with Bluebird

Put simply, everything I've tried with the bluebird Promisfy function after doing a cut and paste of that code doesn't pull in any of the query constructors, and maybe I'm misusing the ClientAsync function in the attempts I made, but am hoping this is a quick and easy help as everything I try results in some variation of:

Possibly unhandled TypeError: Object function (err) {
      if(err) {
        pool.destroy(client);
      } else {
        pool.release(client);
      }
    } has no method 'queryAsync'

I dumped the PromisfyAll function result, and sure enough queryAsync isn't present:

Relevant snippet:

Client: { [Function] Query: { [Function] super_: [Object] } },
Query:
  { [Function]
    super_: { [Function: EventEmitter] listenerCount: [Function] } },
pools:
  { all: {},
    Client: { [Function] Query: [Object] },
    getOrCreate: [Function] },
Connection:
 { [Function]
   super_: { [Function: EventEmitter] listenerCount: [Function] } },
types:
 { getTypeParser: [Function],
   setTypeParser: [Function],
   arrayParser: { create: [Function] } },
ClientAsync: { [Function: ClientAsync] __isPromisified__: true },
endAsync: { [Function: endAsync] __isPromisified__: true },
connectAsync: { [Function: connectAsync] __isPromisified__: true },
cancelAsync: { [Function: cancelAsync] __isPromisified__: true },
setMaxListenersAsync: { [Function: setMaxListenersAsync] __isPromisified__: true },
emitAsync: { [Function: emitAsync] __isPromisified__: true },
addListenerAsync: { [Function: addListenerAsync] __isPromisified__: true },
onAsync: { [Function: onAsync] __isPromisified__: true },
onceAsync: { [Function: onceAsync] __isPromisified__: true },
removeListenerAsync: { [Function: removeListenerAsync] __isPromisified__: true },
removeAllListenersAsync: { [Function: removeAllListenersAsync] __isPromisified__: true },
listenersAsync: { [Function: listenersAsync] __isPromisified__: true } }

It finds the relevant functions in the parse, but doesn't promisfy Query: does anyone know how I can trouble-shoot this further or the potential syntax for executing a SQL query with ClientAsync? I have attempted to add the pg query.js file in manually from the information on the Bluebird github page but to no avail.

score 3 · Answer 1 · edited Nov 05 '14 at 13:00

Well turns out it's a difference in the Javascript vs. native bindings library when using Promisfy.

var pg = require('pg');
var Promise = require('bluebird');

var db = Promise.promisifyAll(pg);

var connectionString = "postgres://node:node@localhost:5432/postgres";

db.connectAsync("postgres://node:node@localhost:5432/postgres").spread(function(connection, release) {
  return connection.queryAsync("select * from howdy")
     .then(function(result) {
        console.log("rows", result.rows);
     })
     .finally(function() {
        release();
     });
});

Works, whereas this:

var pg = require('pg').native;
var Promise = require('bluebird');

Breaks with the ugly error message.

I guess I will need to do some benchmarking eventually between the various options (bluebird w/promisfy and JS-bindings vs. C-bindings (libpq) and manual promises.

vitaly-t · Answer 2 · 2015-10-23T08:37:01.907

If you want to get the most out of the Promises/A+ architecture, while joining the PG library and Bluebird together, try pg-promise.

Manual promisification will give almost no benefit that promises can otherwise offer to the database when used properly, like connection management, automatic transactions, etc.

Just to give you an idea, here's how a complete transaction looks with pg-promise, nicely hiding both connection and transaction details:

db.tx(function () {
    return this.batch([
        this.query("update users set active=$1 where id=$2", [true, 123]),
        this.query("insert into audit(status, id) values($1, $2)", ['active', 123])
    ]);
})
    .then(function (data) {
        // success;
    }, function (reason) {
        // error;
    });

And the very same logic through a manually promisified version would be many times longer, and much more complex. In fact, you would still have to do all of the following:

Open connection;
Check connection for being successful;
Execute BEGIN command;
Execute sequence of your queries;
Check if your queries were successful;
Execute either COMMIT or ROLLBACK, based on your queries success;
Check that transaction was closed successfully;
Release connection back to the pool;
Return the result for further processing;

now, consider nested transactions :)

score 0 · Answer 3 · edited May 23 '17 at 12:22

The creator of bluebird answered on related question here Manually promisifying pg.connect with Bluebird. I've modifyed that solution slyghtly.

var Promise = require('bluebird');
var pg = require('pg');
Object.keys(pg).forEach(function (key) {
  var Cls = null;
  try {
    Cls = pg[key];
    if (typeof Cls === 'function') {
      Promise.promisifyAll(Cls.prototype);
      Promise.promisifyAll(Cls);
    }
  } catch (e) {
    console.log(e);
  }
});
Promise.promisifyAll(pg);

here 'pg[key] wrapped up in try-catch block because pg[key] can retrun error when attempt to access pg['native']

nodejs, postgres, and bluebird

3 Answers3