pg-promise: Recommended pattern for passing connections to different libraries

Question

This question is for pg-promise, its recommended usage pattern & based on following assumption,

It does-not make sense to create more than a single pgp instance, if they are connecting to same DB(also enforced by the good warning message of "Creating a duplicate database object for the same connection.")

Given: I have 2 individual packages which need DB connection, currently they take connection string in constructor from outside and create connection object inside them, which leads to the warning of duplicate connection object and is fair as they both talk to same DB and there is a possibility for optimisation here(since i am in control of those packages).

Then: To prevent this, i thought of implementing dependency injection, for which i pass a resolve function in libraries constructor which gives them the DB connection object.

Issue: There are some settings which are at top level like parsers and helpers and transaction modes which may be different for each of these packages what is the recommendation for such settings or is there a better patterns to address these issues. EG:

const pg = require('pg-promise');
const instance = pg({"schema": "public"});
instance.pg.types.setTypeParser(1114, str => str);//UTC Date which one library requires other doesnt
const constring = "";
const resolveFunctionPackage1 = ()=>instance(constring);
const resolveFunctionPackage2 = ()=>instance(constring);

To sum up: What is the best way to implement dependency injection for pg-promise?

Answer 1

I have 2 individual packages which need DB connection, currently they take connection string in constructor from outside and create connection object inside them

That is a serious design flaw, and it's is never gonna work well. Any independent package that uses a database must be able to reuse an existing connection pool, which is the most valuable resource when it comes to connection usage. Head-on duplication of a connection pool inside an independent module will use up existing physical connections, and hinder performance of all other modules that need to use the same physical connection.

If a third-party library supports pg-promise, it should be able to accept instantiated db object for accessing the database.

And if the third-party library supports the base driver only, it should at least accept an instantiated Pool object. In pg-promise, db object exposes the underlying Pool object via db.$pool.

what happens when they want to set conflicting typeparsers?

There will be a conflict, because pg.types is a singleton from the underlying driver, so it can only be configured in one way. It is an unfortunate limitation.

The only way to avoid it, is for reusable modules to never re-configure the parsers. It should only be done within the actual client application.

UPDATE

Strictly speaking, one should avoid splitting a database-access layer of an application into multiple modules, there can be a number of problems to follow that.

But specifically for separation of type parsers, the library supports setting custom type parsers on the pool level. See example here. Note that the update is just for TypeScript, i.e. in JavaScript clients it has been working for awhile.

So you still can have your separate module create its own db object, but I would advise that you limit its connection pool size to the minimum then, like 1:

const moduleDb = pgp({
    // ...connection details...

    max: 1, // set pool size to just 1 connection

    types: /* your custom type parsers */
});