Sequelize: Connect multiple databases with the same schema

user_8345081 0
Viewed 2757

Dears.

My business logic is very complicated to explain, but in general I can describe it like this: I have a central database, where I save global configuration, and have approx. 22 databases delivered around my country with the same schema. Following the Sequelize documentation, I created a configuration like this at app.js:

global.dbCentral = new Sequelize('mysql://dir:password@localhost:port/dbCentral', {
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },
  operatorsAliases: false
  , timezone: '-03:00' //for writing to database
});
global.dbSala = new Sequelize('mysql://dir:password@localhost:port/defaultDb', {
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },
  operatorsAliases: false
  , timezone: '-03:00' //for writing to database
});

With the central connections, I have no problem. i.e. I have created a model like this:

const Sequelize = require('sequelize');

const Promocion = dbCentral.define('promocion', {
    id: {
        type: Sequelize.INTEGER(10).UNSIGNED,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    titulo: {
        type: Sequelize.TEXT,
        allowNull: true
    },
    descripcion: {
        type: Sequelize.TEXT,
        allowNull: true
    },
    monto: {
        type: Sequelize.FLOAT,
        allowNull: true
    },
    img: {
        type: Sequelize.STRING(45),
        allowNull: true
    },
    activa: {
        type: Sequelize.BOOLEAN,
        allowNull: true
    },
    fecha_inicio: {
        type: Sequelize.DATE,
        allowNull: true
    },
    fecha_fin: {
        type: Sequelize.DATE,
        allowNull: true
    }
}, {
        tableName: 'promocion',
        timestamps: false
    });


Promocion.hasMany(PromocionUsuarioCruciclub, { as: 'promocion_usuario_cruciclub', foreignKey: 'id', targetKey: 'id_promocion' });

module.exports = Promocion;

But I don't know how to set the models that use the changing connections. If I create a dbSala for each sala I have (dbSala1,dbSala2... etc) I need to repeat each model file and it's is impossible to manage.

I thought that the answer was here: Sequelize: Using Multiple Databases but it didn't work on my case.

I am using sequelize 4.43.0

Please, tell me if you need to explain something better

Thanks!

sequelize.js node.js javascript database
2 Answers

You should register all model definitions in each created sequelize instance (i.e. for each connection). Define your models as functions that have sequelize instance as a parameter and return registered model definition. Something like this:

module.exports = (sequelize, DataTypes) => {
  const action = sequelize.define('action', {
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true
    },
// <here some other fields>
  },
    {
      schema: 'public',
      tableName: 'action'
    })

  action.associate = (models) => {
    action.belongsTo(models.resource, { foreignKey: 'resourceId' })
  }

  return action
}

And then use these functions to register models in each sequelize instance.

user_13766180

I had success using transactions. I had a main database, for which I created the models. Then, to use the same models, but with other databases with identical structure, I simple created a transaction based on a different Sequelize instance connected to another database and passed that transaction to the models methods as part of the options parameter.

user_184222120
Related