Multiple DB connections

[ado-astpos]

Firstly, thank you for a great package, it is always a huge help.

Below is my issue and possible feature request. If there is already a way to do this or another possible solution then please point me in the right direction, I have not found anything yet.

Is your feature request related to a problem? Please describe.
My app connects to one database to authenticate a user then get the IP and other DB info to connect to that users database for the remainder of their session (all databases are identical and would therefore point to the same table models). The issue is that once I connect to a database I am limited to only that connection.

Describe the solution you'd like
Ideally the pool functionality would allow me more control over which connections are active and would allow me to choose which user connects to which database. (I am using a jwt in axios headers)

Why should this be in Sequelize
Adds minor customization to the basic usage that would be beneficial to many.

Describe alternatives you've considered
I am using myDB.sequelize.connectionManager.config.database = newDBName currently for all fields I need to change as middle ware for every route.

Usage example
Would allow me to create new connections if one doesn't exist then connect to the right db for the right user (allowing multiple instances to run concurrently)

Additional context
Thank you!

[papb]

Have you considered instantiating sequelize twice?

const sequelize1 = new Sequelize("postgres://abc:def@ghi.jkl.mno:5432/db1");
const sequelize2 = new Sequelize("postgres://abc:def@ghi.jkl.mno:5432/db2");

I never tested this, but I would guess it should work...

[ado-astpos]

I do something similar (I have 2 configs and 2 database model folders then export 2 different DB objects with relevant names) initially.

I connect the first database and query for the IP and other info for that specific user's database, which I then use to connect to their database (second database model but specific to each user). My problem is that once I am connected to the second database I need a way to manage the connections to different database with the same models so I can verify a user before they are able to access that connection.

[papb]

Actually I didn't really understand your problem... What is the matter of having the same models?

[ado-astpos]

The database schema are the same so I would need the same models to apply

[papb]

The database schema are the same so I would need the same models to apply

I don't see why this prevents you from using what I suggested:

Have you considered instantiating sequelize twice?

const sequelize1 = new Sequelize("postgres://abc:def@ghi.jkl.mno:5432/db1");
const sequelize2 = new Sequelize("postgres://abc:def@ghi.jkl.mno:5432/db2");

I never tested this, but I would guess it should work...

Can you try it? Not 2 configs and 2 database model folders but just one config and one database folder, with the only difference being the two Sequelize instances above.

[ado-astpos]

This way I only get one instance of each database, I need multiple instances of the second database because I need to connect to different IPs at different times.

[papb]

Ah, I get it now... I don't know how to help then, unfortunately... I actually don't have experience with using Sequelize with multiple connections (I tried to help with general ideas but now I think I can't really help further)... Good luck! Hopefully someone else will show up to help.

[ado-astpos]

No worries, thank you for your time and effort!

[ado-astpos]

Found a temporary solution...

'use strict';

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');

const basename = path.basename(__filename);
const config = require(`${__dirname}/../../config/config`);

module.exports = (config1, config2, config3) => {
  const storeDB = {};

  const sequelize = new Sequelize(config1 config2, config3, config.store);
  fs.readdirSync(__dirname)
    .filter(file => file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js')
    .forEach(file => {
      const model = sequelize.import(path.join(__dirname, file));
      storeDB[model.name] = model;
    });

  Object.keys(storeDB).forEach(modelName => {
    if (storeDB[modelName].associate) {
      storeDB[modelName].associate(storeDB);
    }
  });

  storeDB.sequelize = sequelize;
  storeDB.Sequelize = Sequelize;

  return storeDB;
};

I changed the index file to be a function and call it on every route. It seems to create a new instance of my second database while still associating all models.

Any thoughts?

[github-actions]

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[John-Bitner]

My team is dealing with a similar issue. Some details for context.
We have a desktop application as the main part of our program suite. The data for our application is single tenant. Each customer has their own MySQL database in a cluster of servers. Think db1,db2,db3... Thousands. We are building a web application to add new features that make more sense on the web. The web app/Api will use the same single tenant databases.

The basic logic in the db API backend is:

• Create sequelize for each customer on demand and store them in an array with some other information about the customer.
• When a connection is needed, we find it in the array for the particular customer and pass it along for various db methods.

So basically we create our own pool of sequelize instances with one per customer.

The problem we are seeing is the multiple instances of sequelize seem to using the same mysql db connection. When we are running multiple db inserts for multiple customers, we end up with inserts to the wrong customer db. e.g. Customer A db inserts end up in the customer B db. While tracing through the code we've ensured that the correct sequelize instance is found in the array and is passed along for the right customer. We just can't make sense as to why the insert hits the wrong db.

Here is the code for creating and retrieving the connections. Any ideas why this wouldn't work to allow a single sequelize instance per customer db and the connections seem to be mixed?

/**
 * dbpool is an array of objects with the following structure
 * {
 *  host: dbCreds.host,
 *  database: dbCreds.database,
 *  db: db
 * }
 */
let dbpool = []

/**
 * Returns a db for a given host if it exists otherwise
 * create and cache it in the dbpool and return it if found
 * @param dbCreds  The db connection information
 */
const customerdb = async (dbCreds) => {
  let dbObject = dbpool.find((pool) => pool.host === dbCreds.host && pool.database === dbCreds.database)
  if (!dbObject) {
    dbObject = {}
    const db = await createdb(dbCreds, dbObject)
    dbObject = {
      host: dbCreds.host,
      database: dbCreds.database,
      db: db
    }
    dbpool.push(dbObject)
  }
  return dbObject.db // sequelize db object
}

/**
 *
 * @param {*} dbCreds The customer database credentials
 * @param {*} dbObject dbObject to be returned with the attached sequelize db object
 * @returns sequelize db object
 */
const createdb = async (dbCreds, dbObject) => {
  dbObject.sequelize = new Sequelize(dbCreds.database, dbCreds.user, dbCreds.password, {
    host: dbCreds.host,
    dialect: 'mysql',
    logging: process.env.NODE_ENV === 'production' ? false : console.log,
    dialectOptions: { decimalNumbers: true },
    define: {
      hooks: {
        beforeDisconnect(connection) {
          removeFromPool(connection) // remove from pool
        }
      }
    }
  })
  await check_csgivingtransaction(dbCreds)
  const db = initModels(dbObject.sequelize)
  db.sequelize = dbObject.sequelize
  db.Sequelize = dbObject.Sequelize
  return db
}```

 

[hatemjaber]

I'm not seeing where you're looping over the configs to add them to the pool. The other thing that I would recommend is to use an object instead of an array so you can do things like:

dbPool.db1.SomeModel.findAll()
dbPool.db2.SomeModel.findAll()
dbPool.db3.SomeModel.findAll()

Unless I'm missing the point here... You have to have some kind of application logic that would determine which one from the pool to send the query to.

[John-Bitner]

Since this post we've change this and related code and have a new system in place to deal with the multiple databases. Thanks for the information.

[hatemjaber]

@John-Bitner it's an interesting concept using multiple databases... can you share what the new solution is?