Connecting to Multiple MySQL DBs in Feathers.js using Sequelize

Question

I've been unable to find a documented way of connecting to multiple MySQL databases in Feathers.js using Sequelize. Is there a way to do this? My use case is to be able to insert and get rows of data into multiple DBs from the same action but the DBs won't necessarily be the same schema.

Thanks!

score 5 · Answer 1 · answered Mar 15 '19 at 10:30

I made some local test and it is possible. You need to define 2 different sequelize clients. If you are using the CLI generator and you set up a service based on sequelize you should have a connection string (my example is a mysql db):

a db connection string in the config/default.json

"mysql" : "mysql://user:password@localhost:3306/your_db"
a sequelize.js in the src root folder

In order to create a second sequelize client

create a new connection string in the config/default.json

"mysql2" : "mysql://user:password@localhost:3306/your_db_2"

create a copy of sequelize.js and name it sequelize2.js

const Sequelize = require('sequelize');

module.exports = function (app) {
  const connectionString = app.get('mysql2');
  const sequelize2 = new Sequelize(connectionString, {
    dialect: 'mysql',
    logging: false,
    operatorsAliases: false,
    define: {
      freezeTableName: true
    }
  });
  const oldSetup = app.setup;

  app.set('sequelizeClient2', sequelize2);

  app.setup = function (...args) {
    const result = oldSetup.apply(this, args);

    // Set up data relationships
    const models = sequelize2.models;
    Object.keys(models).forEach(name => {
      if ('associate' in models[name]) {
        models[name].associate(models);
      }
    });

    // Sync to the database
    sequelize2.sync();

    return result;
  };
};

add the new sequelize configuration to your app.js

const sequelize2 = require('./sequelize2');
app.configure(sequelize2);

Then in your model to a second db :

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
  //load the second client you defined above
  const sequelizeClient = app.get('sequelizeClient2');
  //to check if connect to a different db
  console.log ( sequelizeClient )
  //your model
  const tbl = sequelizeClient.define('your_table', {

    text: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  // eslint-disable-next-line no-unused-vars
  tbl.associate = function (models) {
    // Define associations here
    // See http://docs.sequelizejs.com/en/latest/docs/associations/
  };

  return tbl;
};

In order to work you need 2 different services, each one working with a different db. If you want to put or get with a single action you can create a before/after hook in one of the service and call inside the hook the second service. For the get you need to add the result of the second service to your hook result

Connecting to Multiple MySQL DBs in Feathers.js using Sequelize

1 Answers1