New driver considerations

[vitaly-t]

Since pg-promise is now the underlying driver, I wanted to make a quick list of useful considerations.

Monitoring Queries

As Massive constructor accepts the third parameter to configure pg-promise, it means you have access to lots of useful options that can be set there. More interestingly, one can now attach pg-monitor to the object and see all the queries that Massive executes in a nice way.

Stack Tracing

One of the most important aspects of development with promises - Long Stack Traces, necessary to be able to pinpoint any issue in the application. Without Long Stack Traces it is often impossible. Bluebird is the best library for that today, see Long Stack Traces. You can set Bluebird as the promise library of choice for the pg-promise driver, see option promiseLib.

Version 6.x

Version 6.x of pg-promise has been in active development recently, to allow use of the new pool strategies supported by node-postgres 6.x. And the current Beta 6.0.10 is already quite stable.

One of the best examples of the advantages provided by this version is that you can configure your database layer according to the design / business requirements for the access priority, like this:

var dbLowPriority = pgp({....poolSize: 2});
var dbMediumPriority = pgp({....poolSize: 5});
var dbHighPriority = pgp({....poolSize: 10});

This can really change things in a busy environment, setting the pool size according to the requirements for the load / priority.

[vitaly-t]

Added a memo to help with a possible upgrade.

[vitaly-t]

@dmfay one of the recommended improvements - reuse of the pg-promise configuration for the custom promise library when using its db object.

db object gives you access to the generic promise protocol via db.$config.promise. It is a simplified protocol, with methods as explained in the $config.

Note that support for .all was added in v5.9.6.

This way, whenever a query request is made, you can use the same promise library as configured for pg-promise, rather than suddenly switching to ES6 Promise.

[dmfay]

oh, very cool! That's probably worth including in the release too. I've been trying to do something weird with proxies for the connection pools -- mixed results so far :)

[vitaly-t]

I've been trying to do something weird with proxies for the connection pools -- mixed results so far :)

Are you talking about the use of pg-promise version 6.x?

[dmfay]

I've got a pgp-beta branch where I'm playing around with 6.x, yeah. I thought the least obtrusive way to handle multiple pools would be to treat them similarly to schemas: db.highPriority.myfunction(...), db.lowPriority.mytable.update(...), etc. Since that makes managing attached objects messier, I've been looking into using es6 proxies to forward invocations off the pool object back to db instead.

[vitaly-t]

The latest pg-promise version is 6.0.26.

Within pg-promise architecture you get a new pool with each new db object you create by calling db = pgp(connection).

The idea is to have a separate db and thus a separate pool in the following situations:

• When each db represents a separate database on the same server/port. This is perhaps the least useful separation, because everything still goes through the same IO, and likely to give 0 performance advantage.
• When each db represents a separate database on different servers/ports. This is the most useful separation, because the IO is completely separate, and therefore you get a huge performance boost and real parallelism.
• When each db represents a different API priority for the same database, including a different pool size. This is a virtual pool organization to make sure the priority API calls get access to a larger connection pool.

Hopefully this will help you in making the decision in how to use it best ;)

Also worth noting, pg-promise discourages creating more than one db object with the exact same connection details, producing a warning in the console like this:

WARNING: Creating a duplicate database object for the same connection.
    at Object.<anonymous> (D:\NodeJS\tests\test2.js:14:6)

So currently, if you go for scenario 3, you are expected to at least specify a different pool size. Otherwise, like, why creating it in the first place? :) i mean, it makes no sense having multiple pools of the same size going to the same database.

[vitaly-t]

pg-promise 6.x has been officially released.

[vitaly-t]

@dmfay just curious, did you publish/submit your library somewhere after the 3.0 release? I've noticed a good spike in new stars ⭐️ ⭐️ ⭐️ ⭐️ ⭐️

Mine got stale of lately, so I'm thinking of posting it somewhere 😄

[dmfay]

Just Twitter, when I dropped the release. Looks like you had the same thought there!

[vitaly-t]

Must have been some twitter, it looks really in play 😄, though I suspect most people come from here: http://nodeweekly.com/issues/194

Come to think of it, how did you get it published there? Not by twitting? 😄

Or maybe because it got re-twitted here: https://twitter.com/javascriptdaily

😯 I won't tell anybody 😯 😸

[dmfay]

Good question! I don't know either :)

[vitaly-t]

Version 7 of pg-promise has already been implemented within branch v7.
See the related issue: Version 7.x of the driver.

The remaining bits and pieces are tests + documentation, tons of each.

[chamini2]

Sorry if this is a dumb question, but node-postgres now supports Promises, why not use it now instead of pg-promise?

[dmfay]

node-pg's promise support was cumbersome at best up until about four days ago, which would have made integrating it a hassle for no good reason. And pg-promise does more than just facilitating communication with the database: it handles query file management, named parameters, tasks, transactions, and so on and on. These are all things I could write myself -- eventually -- but Vitaly's implementation is at least as good as anything I could come up with. If I don't waste time reinventing the wheel, I can focus more on improving Massive itself.

[chamini2]

I did not know pg-promise did that many things on top of promisifying node-postgres. Thanks for the answer.

[vitaly-t]

@dmfay what do you think of this type of change for version 7? - vitaly-t/pg-promise#371

[vitaly-t]

@dmfay if you update the driver to v6.5.0, please note the breaking change.

Related question: #411

[vitaly-t]

@dmfay You might like this update: https://github.com/vitaly-t/pg-promise/releases/tag/v.6.10.0

[vitaly-t]

@dmfay

One feature in pg-promise that's been missing (made unavailable) in massive.js - Database Context, something that matters when more than one database needs to be supported, like think master + replicas, for example.

See this question: https://stackoverflow.com/questions/46243680/rebuilding-connections-in-nodejs-pg-promise

It is the second parameter into the Database constructor, the value which is then propagated through the entire interface to provide the database context.

[dmfay]

yeah, multiple databases = multiple instances of Massive for now. I need to get back into that proxy stuff I was trying out for juggling connection pools.

[vitaly-t]

Version 7.0.0 is out. You've got some catching up to do 😉

@dmfay

[dmfay]

I've barely had time to think the last couple months let alone try to keep pace with you. Busy busy 😬

[vitaly-t]

Well, now i'm myself fresh out. I just did a major rewrite of the documentation, no code changes.

And since I'm starting a new job on the 16th, I'm likely to take a very long break away from GitHub.

You get your time to catch up ;)

[dmfay]

congrats, have fun at the new place!

[vitaly-t]

@dmfay Congrats on 2000 stars! 😄

When are you planning to upgrade the pg-promise dependency? 😄

[dmfay]

Thanks! As far as upgrading: the pg-query-stream reversion to the classic style doesn't look like it's going to be changed anytime soon, and I don't want to bump to v5 over streams. Other option is to convert it to a modern stream in Massive, I guess, but that makes me itchy. I'll have to think about it.

[vitaly-t]

Now that you have updated to the latest version of pg-promise, time to consider how you can make your library faster and simpler by using these features:

• multi-result methods multi and multiResult - they are great for performance, executing multiple queries and getting all results back with a single IO operation.
• nested named parameters - can simplify the formatting logic on your side and/or client's 😉

[vitaly-t]

@dmfay Unrelated to the subject,...but, your library can do all that - right? - What library do you use for postgres+jsonb in Node?

If so, you may want to publish an answer ;)

[dmfay]

Thanks! :)

[vitaly-t]

@dmfay

I suggest that you start using fixed versions of all packages. Otherwise, you will exposing yourself to unknown issues that suddenly pop up.

pg-promise just had a bad update - version 8.4.1, which was then replaced by 8.4.2.

I've started using only fixed versions within pg-promise awhile ago 😉

[dmfay]

yeah, I'm going to have to think about that -- inattention to semver has also been an issue :/