pg-promise v5 vs v6

[vitaly-t]

This is a memo for choosing between version 5.x and 6.x of pg-promise.

---

Version 5.x is fixed to the true-and-tried version 5.1 of the driver, and is 100% stable.

$ npm install pg-promise

Version 6.x (currently v6.0.26), is now also stable, even though it remains to be Pre-Release.

$ npm install pg-promise@6

It uses the very latest version of the driver, which brings a number of improvements and fixes:

• Huge (10x) performance improvement when reading large bytea columns
• Introduction of the new independent connection pool, which allows finer connection control
• etc...

For example, instead of a 1-pool approach, you can split the default 10 maximum connections like this:

// In version 6.x each db object automatically gets its own connection pool:

const db1 = pgp({...poolSize: 8}); // for High-Priority API calls
const db2 = pgp({...poolSize: 2}); // for Low-Priority API calls

And even though there were some bugs introduced in v6.x of the driver also, the ones that still remain appear to be minor.

Also, version 6.x is 100% backward compatible with version 5.x.

And if you want to share the connection pool with another module, only version 6.x can do it. Each db object has its own connection pool, which it exposes as db.$pool.

[vitaly-t]

Closing, as version 6 has been officially released.

[RomeHein]

Hey @vitaly-t,
I'm having an issue to move from 5.9.7 (last of the 5.x) to v6.
When I run my code with the v5, everything works fine. But when I update to v6, I got this kind of issues:

Relation "project" does not exist error

Here is an exemple of where I got that issue:

static find(projectIdentifier) {
        if (!projectIdentifier) {
            return Promise.reject(new Error('Project informations needed to connect to the db'));
        }
        return connector.db.task(t => {
            return t.batch([
                t.oneOrNone(sql.project.findById, projectIdentifier),
                t.manyOrNone(sql.project.participantsByProjectId, projectIdentifier)
            ]);
        })
            .then(data => {
                var project = null;
                if (data[1]) {
                    project = new Project(data[1]);
                    if (data[2] && data[2].length > 0) {
                        data[2].forEach(function (participant) {
                            project.participants.push(new User(participant));
                        })
                    }
                }
                return project;
            })
            .catch(error => {
                console.log(error);
                return error;
            });
    }

and here is the sql file for sql.project.findById:

SELECT project_id,project_name,project_description,project_creator as creator_id,user_name as creator_name,project_channel,project_state AS state_id,state_name FROM Projects 
INNER JOIN Users ON Users.user_id = Projects.project_creator 
INNER JOIN States ON project_state = state_id
WHERE Projects.project_id = $1;

I'm aware that this issue is due to the case sensitiveness of pg, but I'm not sure the way I'm creating my tables is wrong:
CREATE TABLE if not exists Projects (...);
Note that I'm not using the double quote around the table name.
Is there something I'm missing to update to v6? Is it the new node-postgres driver?
Btw I always wanted to say how I found your job amazing. Honestly, if I could be half as good as you are one day, it would be great!

[vitaly-t]

@RomeHein You should never create a table with open name like Projects. You should either create it as projects or as "Projects". And then query against it in the same way.

Anyway, the error is database-level, not library-level.

[RomeHein]

Ok, it's a bit weird though, cause when I run the sql command directly against the db it works fine, same when using v5, but not with the v6.
Anyway, you are right, I should change for projects.
I've read couple posts on stackoverfow saying that using double quotes was a bad idea.
Thanks for your answer, much appreciated.