Question

[vitaly-t]

Was pg-promise in any way insufficient for your needs?

I'm just curious, if anything is considered missing there...

[irakliy81]

pg-promise is an awesome library and i don't think anything is missing from there. the main reason i didn't use it was that my use case is fairly specific. i needed to make the query object a bit more "heavy" so that I could pass a custom result handlers with each query. this functionality is needed for another module that i'm working on (pg-dao). in a way, pg-io is just a helper module for pg-dao. my eventual (and probably a fairly distant) goal for pg-io is to have no dependencies and to contain the code for communication with the database as well.

a couple of things that i've implemented in pg-io, that i haven't seen in other libraries:

1. aggressive inlining of parameters (for parametrized queries) - this should increase performance
2. ability to execute several (named) queries and get a map of results back

by the way - if you have any feedback for me - it is always welcome :)

[vitaly-t]

Custom result handler with each query - doesn't the use of .then(handler) give you the same?

Aggressive inlining of parameters - I honestly do not understand what that means :)

Ability to execute several queries - if you are using node-postgres, I do not understand how you could possibly achieve this, because that library doesn't have support for multiple results. It has been discussed, and the author knows, but as usual, no progress there. See brianc/node-postgres#757

[irakliy81]

Yes - similar results can be achieved with .then(handlers) but the interface I have is specifically designed to bundle query definition and result handling logic together to make code for this specific use case cleaner. Take a look at the following example (it's fairly simplistic but I hope it illustrates the point):

// define a couple of queries
class GetUserByIdQuery {
  constructor(userId, name) {
    this.text = 'SELECT * FROM users WHERE id = ${userId};';
    this.mask = 'object';
    this.name = name;
    this.handler = {
      parse: (row) => {
        // some custom logic for parsing user rows
      }
    };
  }
}

class GetMessagesForUserQuery {
  constructor(userId, name) {
    this.text = 'SELECT * FROM messages WHERE user_id = ${userId};';
    this.mask = 'list';
    this.name = name;
    this.handler = {
      parse: (row) => {
        // some custom logic for parsing message rows
      }
    };
  }
}

// now, use these queries somewhere later in the code
var q1 = new GetUserByIdQuery(1, 'myUser');
var q2 = new GetMessagesForUserQuery(1, 'myUserMessages');

connection.execute([q1, q2]).then((results) => {
  var myUser = results.get(q1.name);
  var myUserMessages = results.get(q2.name);
  // do something with results  
});

All of the above is achievable with pg-then as well - thought, i think the usage part of the above example would not be as streamlined.

Aggressive parameter inlining just means that parametrized queries are transformed into simple text queries whenever possible and safe to do so. So for example 'SELECT * FROM users WHERE id = $1' will be executed as 'SELECT * FROM users WHERE id = 22' when passed in parameter is 22 (it would not be executed as a parametrized query).

Ability to execute several queries - for now, this is just an interface feature when you can pass in multiple query objects into connection.execute() method. At this point, pg-io just executes them sequentially and then aggregates the results for you (as you noted node-postgres does not yet support multi-statement commands). But as soon as node-postgres supports this feature, pg-io can be configured to actually collapse multiple SQL statements into a single database requests (so, the call in the above example conneciton.execute([q1, q2]) would actually result in a single database call).

[vitaly-t]

I wouldn't keep my hopes up about node-postgres starting to support multiple queries. The project's support has been, somewhat limited. I've been tempted to start on rewriting the entire pg core from scratch.

And if node-postgres were to ever support multiple results, so would be pg-promise.

[vitaly-t]

Using pg-promise with support for multiple queries at a time and multiple results, and it all works just fine. Not sure why I thought before that it wouldn't.