0

I have a database that has several different schemas, like a multi-tenant architecture, in Postgres 9.5.7.

I've followed the example in this answer to dynamically set the schema for particular queries. All of the context is encapsulated in a class and as far as I can tell I'm calling pgp in the correct way, but I still get the WARNING: Creating a duplicate database object for the same connection. error.

I have a configuration initializer like so: 

// initializer.js

const initOptions = {
  schema(name) {
    return name ? name : 'public'
  }
}
const _config = {
  host: process.env.DBSERVER || nconf.get('database:server') || 'localhost',
  port: process.env.DBPORT || nconf.get('database:port') || '5432',
  user: process.env.DBUSER || nconf.get('database:user') || 'dev',
  password: process.env.DBPASS || nconf.get('database:password') || 'pass1234',
  database: process.env.DBNAME || nconf.get('database:user') || process.env.DBUSER || 'dev'
}

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

// default database
const pgdb = pgp(_config)

module.exports = {
  pgp
  , pgdb
  , _config
}

And the class using it like so:

const { pgp, _config } = require('../db/initializer')

class Tenant {

  constructor(name) {
    this.name = name
    this.db = pgp(_config, name)
  }

  async getMetadata() {
    try {
      const data = await this.db.many(`SELECT * FROM versions`)
      return data
    } catch (err) {
      log.error(`Error getting versions for ${this.name}:`, err)
      return null
    }
  }
}

The stack trace from this warning points to this line in the _config:

host: process.env.DBSERVER || nconf.get('database:server') || 'localhost',

I'm creating a bunch of these classes all at once and on-demand

BrDaHa
  • 5,138
  • 5
  • 32
  • 47
  • Right as I finished posting the question, I realized that I was creating the class, but the connection must be persisting; right after I start the program I don't get the warnings, but when I execute the call (i.e. re-instantiate all the classes) a second time, I get all of the warnings. It probably as something to do with the connection not being released as I was expecting, either because gc hasn't occurred yet or gc's don't clear it – BrDaHa May 09 '19 at 00:09
  • You should check inside the constructor that it doesn't create a new database object with the same connection parameters, because looks like it does at some point, hence the warning. – vitaly-t May 09 '19 at 00:41
  • When you say "same connection parameters", do you mean including the database context string? I pass the exact same object each time, but it seems to be working, since I'm changing the dc parameter each time – BrDaHa May 09 '19 at 00:45
  • 1
    Yes, I'm saying that sometimes you are creating a new [Database](http://vitaly-t.github.io/pg-promise/Database.html) object with connection parameters + dc that already exist. That's why you are getting that warning. – vitaly-t May 09 '19 at 01:21
  • Yeah, I think I’ve solved it by saving the created objects in a map, associated by the database context since they’re unique. I’m not sure this is the best approach since the objects will stick around for as long as the app is alive, and I also have multiple instances of the app running as micro services talking to the same database. Is there a way to have them all share the same connection (per app instance)? – BrDaHa May 09 '19 at 04:48
  • By setting schema on each new connection, you make them exclusive, so you cannot share such connections. To share a connection, you would need to manually set schema yourself, before running any query. – vitaly-t May 09 '19 at 04:50
  • Makes sense. Thanks! – BrDaHa May 09 '19 at 05:42
  • BrDaHa which was the final solution to avoid create multiple connection objects for each tenant? You decided to store the connection objects in a global data structure and look for the connection there before create a new one? @vitaly-t do you have any suggestion? – JuanDM Oct 18 '19 at 14:18
  • I re-architected so that the data I was trying to get via multiple DB instances now lives in a separate micro service, so the calls to get the same data are HTTP calls. I’m not sure there’s a better way than what I did or what vitaly-t suggested. – BrDaHa Oct 23 '19 at 17:37

0 Answers0