Where Should I Initialize Pg-Promise

Where should I initialize pg-promise

You need to initialize the database connection only once. If it is to be shared between modules, then put it into its own module file, like this:

const initOptions = {
    // initialization options;
};

const pgp = require('pg-promise')(initOptions);

const cn = 'postgres://username:password@host:port/database';
const db = pgp(cn);

module.exports = {
    pgp, db
};

See supported Initialization Options.

UPDATE-1

And if you try creating more than one database object with the same connection details, the library will output a warning into the console:

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

This points out that your database usage pattern is bad, i.e. you should share the database object, as shown above, not re-create it all over again. And since version 6.x it became critical, with each database object maintaining its own connection pool, so duplicating those will additionally result in poor connection usage.

---

Also, it is not necessary to export pgp - initialized library instance. Instead, you can just do:

module.exports = db;

And if in some module you need to use the library's root, you can access it via property $config:

const db = require('../db'); // your db module
const pgp = db.$config.pgp; // the library's root after initialization

UPDATE-2

Some developers have been reporting (issue #175) that certain frameworks, like NextJS manage to load modules in such a way that breaks the singleton pattern, which results in the database module loaded more than once, and produce the duplicate database warning, even though from NodeJS point of view it should just work.

Below is a work-around for such integration issues, by forcing the singleton into the global scope, using Symbol. Let's create a reusable helper for creating singletons...

// generic singleton creator:
export function createSingleton<T>(name: string, create: () => T): T {
    const s = Symbol.for(name);
    let scope = (global as any)[s];
    if (!scope) {
        scope = {...create()};
        (global as any)[s] = scope;
    }
    return scope;
}

Using the helper above, you can modify your TypeScript database file into this:

import * as pgLib from 'pg-promise';

const pgp = pgLib(/* initialization options */);

interface IDatabaseScope {
    db: pgLib.IDatabase<any>;
    pgp: pgLib.IMain;
}

export function getDB(): IDatabaseScope {
    return createSingleton<IDatabaseScope>('my-app-db-space', () => {
        return {
            db: pgp('my-connect-string'),
            pgp
        };
    });
}

Then, in the beginning of any file that uses the database you can do this:

import {getDB} from './db';

const {db, pgp} = getDB();

This will ensure a persistent singleton pattern.

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

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 */
});

How to end 'pg-promise' app

Terminate the application properly at the end of each call

You should absolutely NOT do it, shutting down the whole connection pool after each query is a terrible idea.

Reuse the pgp connection object on subsequent calls

What's the complication? Are you askign how to reuse objects in Node.js?

See also: Where should I initialize pg-promise.

And you should stay away from using method db.connect like that, it is not a good way to use the framework. You should rely on the automatic connections instead ;)

pg-promise update where in custom array

Use CSV Filter for the last part: IN (VALUES $3:csv).

And to make each item in the array format itself correctly, apply Custom Type Formatting:

const data = [{
    startTime: '17:00:00',
    startDayOfWeek: 'U'
  },
  {
    startTime: '16:00:00',
    startDayOfWeek: 'T'
  }];

const values = data.map(d => ({
    toPostgres: () => pgp.as.format('(${startDayOfWeek},(cast(${startTime} as time))', d),
    rawType: true
}));

Now passing in values for $3:csv will format your values correctly:

('U',(cast('17:00:00' as time)),('T',(cast('16:00:00' as time))

---

Related Topics