Pg-promise: Question re: WARNING: Creating a duplicate database object for the same connection

[CalebEverett]

Is the right way to set this up in an Express app to create a separate db module per the example and then require that in each set of routes? I've done that, but am still getting the warnings. Do I need a separate database object for each set of routes or can you recommend otherwise? Thanks,

[vitaly-t]

You should create a single database object per connection details, and then reuse it.

From the start-up instructions:

You should create only one global/shared db object per connection details.

[vitaly-t]

See related questions:

• http://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise
• http://stackoverflow.com/questions/36120435/verify-database-connection-with-pg-promise-when-starting-an-app

Or the whole category: http://stackoverflow.com/questions/tagged/pg-promise

[vitaly-t]

Oh, and complete forgot: WARNING: Creating a duplicate database object for the same connection.

That should do it :smile:

[CalebEverett]

I was going off of those, but missed a module I was pulling into one of my route files. Thanks again

[vitaly-t]

@CalebEverett

See the changes brought with v.5.1.0

[thenikso]

I've a followup on this. I'm using pg-promise both in my app and on an integration test wrapper that initialize the test database to then import and call the app.

I get this warning which I believe it's not a problem and I can safely ignore it. However do you have any further best practice regarding this kind of scenarios?

[vitaly-t]

So, your intergration test and the application are using separate configurators for the database, while connecting to the same database. Is it really necessary? Why not place it in a shared module? That would get rid of the warning :wink:

Otherwise, in special cases you can disable all the warnings by using option noWarnings.

[MitMaro]

We have a distributed microservice like system, that sometimes runs several independant services within the same node process, with an unknown set of database connections. This meant that explicitly using a shared module wasn't a viable solution. Instead we ended up creating a factory that would use a hash of the configuration to return a cached instance. Looks something like this:

const pgPromise = require('pg-promise')(/*initialization options*/);
const cache = new Map();
module.exports = function  databaseConfig) {
    const dbKey = JSON.stringify(databaseConfig, Object.keys(databaseConfig).sort());
    if (cache.has(dbKey)) {
        return cache.get(dbKey);
    }
    const instance = pgPromise(databaseConfig);
    cache.set(dbKey, instance);
    return instance;
};

edit: Fixed a mistake I made while breaking out the example. Thanks @vitaly-t for pointing it out.

[vitaly-t]

@MitMaro is this module supposed to return an initialized pgp variable or a new db instance?

If it is the former, it is fine, if it is the latter - it won't work, because the library's initialization call is missing, i.e. var pgp = require('pg-promise')(/*initialization options*/), not just var pgp = require('pg-promise').

By the look of it is, it is the latter, that's why I asked.

And if you do have a special use-case, then as I commented earlier, you can use option noWarnings :wink:

var pgp = require('pg-promise')({
   noWarnings: true
});
var db = pgp(connectionDetails);

[MitMaro]

I made a mistake when I pulled out the example from the codebase. It's a database connection, not the pg-promise instance. Our actual code is a bit more complex. I've edited the example.

I was initially suppressing the warning but wanted to use a shared connection pool when possible. I didn't think to check to see if pg-promise reused an existing connection pool on the same connection parameters, but my guess is that it does not.

[vitaly-t]

pg-promise uses just one connection pool, as per node-postgres v5.1 used underneath.

[MitMaro]

Interesting. I think I found a bug in either pg-promise or node-postgres then. I will do up a test case and report it.

[benoittgt]

Hello

Thanks for pg-promise ! Love using it.

I have very simple code (that run on an AWS lambda) but I get THE warning :

undefined WARNING: Creating a duplicate database object for the same connection.
at exports.handler (/var/task/index.js:20:14)

Here is my code :

'use strict';
const config = require('./config_from_env');
const connParams = `pg://${config.user}${config.password}@${config.host}/${config.database}`;
const pgp = require('pg-promise')();

const moveTravels = function(travelType) {
  return `
      BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      -- 2 query...
      COMMIT;`;
};

exports.handler = function(event, context) {
  const db = pgp(connParams);

  return db.tx(function (t) {
    return t.batch([
      t.none(moveTravels('arrivals')),
      t.none(moveTravels('departures'))
    ]);
  })
    .then(function () {
      return context.succeed(`Successfully moved`);
    })
    .catch(function (error) {
      return context.fail(`Failed to run queries : ${JSON.stringify(error)}`);
    });
};

Why I have the warning ? I know it's friday but I don't see duplicated database object.

[vitaly-t]

this is designed to be called more than once:

exports.handler = function(event, context) {
  const db = pgp(connParams);

which results in a repeated creation of the same db object more than once, hence the warning.

Also, on another note, your code shouldn't have things like:

  return `
      BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
      -- 2 query...
      COMMIT;`;

The library can do such things automatically, while guaranteeing correctness.

[benoittgt]

Thanks a lot with your comment I just discover : http://vitaly-t.github.io/pg-promise/txMode.TransactionMode.html

this is designed to be called more than once:
But this handler is called only once (in my case every 4 min)

Thanks again

[vitaly-t]

in my case every 4 min

that's not only once :)

[vitaly-t]

@benoittgt see this: http://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise

[vitaly-t]

@lakesare what is this about?

[RomeHein]

Hello!
I'm currently trying to implement authentification on my API. I was wondering how could I manage different user connections to the db?
This is how I currently create the connection:

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

let instance = null;

module.exports = class Connector {

  constructor(user,psw) {

    if (!instance || (user && psw)) {
      instance = this;

      this.config = {
        user: user || process.env.boUser,
        database:process.env.boDatabase, 
        password: psw || process.env.boUserPassword, 
        host:process.env.boHost, 
        port:process.env.boPort,
      };

      this.db = pgp(this.config);
    }

    return instance;
  }

}

And I use it like so (Connector is a global variable):

module.exports = class Pack {
    ...
    static all() {
        return Connector.db.tx(t => {
            return t.batch([t.manyOrNone(sql.pack.findAll)]);
        })
        .then(data => {
            if (data[0].constructor === Array && data[0].length > 0) {
                return Pack.parseArray(data[0]);
            }
            return null;
        });
    }
}

I'm a bit lost and don't get how I can create multiple connections.
Let's say my API receive 10 requests from 10 different users, with different access rights on the db, how can I manage that on the API side?
I feel like my pattern is not right.

[vitaly-t]

@RomeHein You would have to create and keep a separate db object, but that's not a great idea, because if it is the same server, the valuable IO connections would be consumed, and not reused across users.

[RomeHein]

Ok thanks for your quick answer!
I'll move the authentification logic to the API, and keep only one user to perform the IO connections to the db.
Thx again

[oliversalzburg]

@vitaly-t Is there a way to suppress the warning?

I'm encountering this during automated testing. I have a primary connection in application code and I'm establishing another connection with identical parameters in my test code. I manipulate the database through the test connection to set up state, then run my test which causes application code to mutate the database state through the primary connection, then assert state through the test connection.

I explicitly do not want to share the database connection between application and test context. I have no particular reason for this other than trying to keep both contexts separate from each other. I felt like that is just cleaner and a better approach, because I don't like sharing entities between test and business contexts during testing.

Please let me know your thoughts on this. Thanks

[vitaly-t]

@oliversalzburg Initialization option noWarnings can switch the warning off.

[ild-dsv]

Hi @vitaly-t,

After reading through pg-promise's official document and your explanations on StackOverflow as well, when we're working with pg-promise with Stack:

• AWS Lambda
• Serverless-offline (or on production)
• Serverless-webpack
  I still get this warning WARNING: Creating a duplicate database object for the same connection.

My approach:
Only create data object once then import to lambda function when interactive with database (Our database is using PostgreSQL)

How to reproduce:
utils/db.js

import pgPromise from 'pg-promise'
import Promise from 'bluebird'
import pgPromise from 'pg-promise'
const initOptions = {
  promiseLib: Promise,
  // noWarnings: true,
  capSQL: true,
}
const pgp = pgPromise(initOptions)

const dbObject = pgp(process.env.DATABASE_URL)

function disconnect(db) {
  return db ? db.$pool.end() : null
}

export { dbObject, disconnect }

lambaFunc.js

import { dbObject, disconnect } from './utils/db'

export const handler = async (event, context) => {
  if (event.source === 'serverless-plugin-warmup') {
    return 'Lambda is warm!'
  }

  const dbClient = dbObject

  const response = {
    statusCode: 200,
  }
 // we can do something with dbClient, however, even I don't do anything, just do like this, it also throws the warning
  disconnect(dbClient)

  return response
}

Do you have any idea about this?

Is it possible because of serverless-webpack?

[vitaly-t]

@ild-dsv All I can say is that somehow, somewhere you are creating a duplicate database object. I do not personally use Lambda, but I hear there is a lot of connection-related problems with it.

You can always debug it yourself - hack the library code and make it throw an error with stack upon a second call, to show where the second call is coming from.

[ild-dsv]

@ild-dsv, thanks for your suggestion. It's really useful for me to track this warning. Basically, I think the main error of my case, it can come from the serverless-webpack plugin. It freshly forces reinitializing our dbObject during the compiling time. I tried to use dynamic import to avoid this. However, for my case, It needs a little help with new implements from serverless-webpack.

Anyway, I really like pg-promise and also give a thumb up for your help. Thank you. @vitaly-t

[LRagji]

@vitaly-t Thanks for this package its really awesome, just a follow up Q on this warning part, it this warning now an anti-pattern? cause as per latest Aurora AWS RDS PG docs(some where i read) it says app shld always try to create new connections often as the connection string is load balanced pg cluster especially readers, which means i may create a new object for let say background report generation and other connection for normal app queries using the same connection string how valid is the warning in this case?

[PaulKushch]

Anyone maybe use next.js? I followed the proposed pattern at https://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise but I get the same warning as you guys, "creating a duplicate connection...". What you think about the solution below (i.e. pool of 1 with serverless):

const connectionString = `postgresql://${process.env.DB_USER}:${process.env.DB_PASSWORD}@${process.env.DB_HOST}:${process.env.DB_PORT}/${process.env.DB_DB}`;
const cn = {
    capSQL: true,
};

const dbCn = {
    connectionString,
    max: 1,
};

// Use a symbol to store a global instance of a connection, and to access it from the singleton.
const DB_KEY = Symbol.for("MyApp.db");
const PGP_KEY = Symbol.for("MyApp.pgp");
const globalSymbols = Object.getOwnPropertySymbols(global);
const hasDb = globalSymbols.indexOf(DB_KEY) > -1;
if (!hasDb) {
    global[PGP_KEY] = require("pg-promise")(cn);
    global[DB_KEY] = global[PGP_KEY](dbCn);
}

// Create and freeze the singleton object so that it has an instance property.
const singleton = {};
Object.defineProperty(singleton, "db", {
    get() {
        return global[DB_KEY];
    },
});
Object.defineProperty(singleton, "pgp", {
    get() {
        return global[PGP_KEY];
    },
});
Object.freeze(singleton);

module.exports = {
    pgp: singleton.pgp,
    db: singleton.db,
};

[vitaly-t]

@PaulKushch The warning includes the line where the repeated initialization occurs, so start from there.

[PaulKushch]

@vitaly-t Yes, now the warning is not present, please see my code above. If I dont use singleton pattern, then the warning is there, with singleton pattern no warning. I use next.js... But do you think it is a reasonable solution? Best regards

[vitaly-t]

Th reasonable solution is the one you quoted from StackOverflow :)

[PaulKushch]

@vitaly-t In nextjs that solution gives warning. Like this guy mentions https://www.codeoftheprogrammer.com/2020/01/16/postgresql-from-nextjs-api-route/

[vitaly-t]

That's an odd one, since NodeJS loads each module just once, so it is expected to be a singleton based on that. Not sure why an extra singleton implementation is needed. That Next.js must be doing something abnormal, because I haven't seen such issue anywhere else.

[PaulKushch]

@vitaly-t Yes I agree, I asked at next js repo. Anyway, thanks for the great library :)