Where should I initialize pg-promise

Question

I just started to learn nodejs-postgres and found the pg-promise package. I read the docs and examples but I don't understand where should I put the initialization code? I using Express and I have many routes.

I have to put whole initialization (including pg-monitor init) to every single file where I would like to query the db or I need to include and initalize/configure them only in the server.js?

If I initialized them only in the server.js what should I include other files where I need a db query?

In other words. Its not clear to me if pg-promise and pg-monitor configuration/initalization was a global or a local action?

It's also unclear if I need to create a db variable and end pgp for every single query?

var db = pgp(connection);

db.query(...).then(...).catch(...).finally(**pgp.end**);

Answer 1

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.

Answer 2

A "connection" in pgp is actually an auto-managed pool of multiple connections. Each time you make a request, a connection will be grabbed from the pool, opened up, used, then closed and returned to the pool. That's a big part of why vitaly-t makes such a big deal about only creating one instance of pgp for your whole app. The only reason to end your connection is if you are definitely done using the database, i.e. you are gracefully shutting down your app.