Connect to multiple databases

Dear supporters,

We connected to 3 different databases in our system. Unfortunately, they have some tables which have the same name and structure.
For example:
I have 3 databases named like “Salary”, “Employer”, “Finance”

  • In Employer Database
    we have an employer, an address table, etc… An employer will have an address

  • In Salary Database
    we have a salary and an address table, etc… A salary will have an address

  • In Financer Database
    we have a finance and an address table, etc… A finance will have an address

ForestAdmin only shows data of an address table.

I changed my Sequelize model’s name look like
const Addresse = sequelize.define(‘address_employers’)
I changed the name in my relationships as well.

But no luck, ForestAdmin still data of an address of one of three databases only.



Could you please give me your suggestion for my issue?

Thanks in advance,
Cong Le

Welcome @thanhcongptit to the Forest Admin Community ! :wave:

I think you are on the right path but the implementation is not complete :smile:.

Can you show me your models/index.js file please ?
I think the issue might be in this file.

1 Like

Hi Vince,

Thanks for your quick response.

This is my index.js in model folder

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

let databases = [
  {
  name: 'salaries',
  connectionString: process.env.DATABASE_URL_SALARIES
},
 {
  name: 'employers',
  connectionString: process.env.DATABASE_URL 
}, 
{
  name: 'financeurs',
  connectionString: process.env.DATABASE_URL_FINANCEURS
}];

const sequelize = {};
const db = {};
const models = {};

databases.forEach((databaseInfo) => {
  models[databaseInfo.name] = {};

  const isDevelopment = process.env.NODE_ENV === 'development' || !process.env.NODE_ENV;
  const databaseOptions = {
    logging: isDevelopment ? console.log : false,
    pool: { maxConnections: 10, minConnections: 1 },
    dialectOptions: {}
  };

  if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {
    databaseOptions.dialectOptions.ssl = true;
  }

  const connection = new Sequelize(databaseInfo.connectionString, databaseOptions);
  sequelize[databaseInfo.name] = connection;

  fs
    .readdirSync(path.join(__dirname, databaseInfo.name))
    .filter((file) => file.indexOf('.') !== 0 && file !== 'index.js')
    .forEach((file) => {
      try {
        const model = connection.import(path.join(__dirname, databaseInfo.name, file));
        models[databaseInfo.name][model.name] = model;
      } catch (error) {
        console.error('Model creation error: ' + error);
      }
    });

  Object.keys(models[databaseInfo.name]).forEach((modelName) => {
    if ('associate' in models[databaseInfo.name][modelName]) {
      models[databaseInfo.name][modelName].associate(sequelize[databaseInfo.name].models);
    }
  });
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

I uploaded my source code here https://drive.google.com/open?id=1VSPS4F2U_xVc4BxmRBeX5taZAWolkbh9
Could you please take a look and help me know is there any wrong in my code.

Thank you so much!

Hi @thanhcongptit

Thanks to your code I guess I figured out the issue. :pray:

:point_right: Let me try to explain with a full yet simplified example (still highly inspired by yours!) with 2 databases. You could also have a look to the TL;DR at the end of this post.

  1. Database a has 2 tables: user and address.
  2. Database b has 2 tables: company and address.

The schema should look like this:

# Database a
create table address ( id serial constraint address_pk primary key );
create table "user" (
  id serial constraint user_pk primary key,
  address_id integer constraint user_address_id_fk references address 
);
# Database b
create table address ( id serial constraint address_pk primary key );
create table company (
  id serial constraint company_pk primary key,
  address_id integer constraint company_address_id_fk references address 
);

The model folder should look like this (yours is already correct):

Capture d’écran 2020-06-04 à 14.16.56

Then each model file has to be edited :point_down:

models/index.js

Yours is already correct, I just put it there since I hope it may help other readers.

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

let databases = [
  {
    name: 'a',
    connectionString: process.env.DATABASE_URL_A
  },
  {
    name: 'b',
    connectionString: process.env.DATABASE_URL_B
  }];

const sequelize = {};
const db = {};
const models = {};

databases.forEach((databaseInfo) => {
  models[databaseInfo.name] = {};

  const isDevelopment = process.env.NODE_ENV === 'development' || !process.env.NODE_ENV;
  const databaseOptions = {
    logging: isDevelopment ? console.log : false,
    pool: { maxConnections: 10, minConnections: 1 },
    dialectOptions: {}
  };

  if (process.env.DATABASE_SSL && JSON.parse(process.env.DATABASE_SSL.toLowerCase())) {
    databaseOptions.dialectOptions.ssl = true;
  }

  const connection = new Sequelize(databaseInfo.connectionString, databaseOptions);
  sequelize[databaseInfo.name] = connection;

  fs
    .readdirSync(path.join(__dirname, databaseInfo.name))
    .filter((file) => file.indexOf('.') !== 0 && file !== 'index.js')
    .forEach((file) => {
      try {
        const model = connection.import(path.join(__dirname, databaseInfo.name, file));
        models[databaseInfo.name][model.name] = model;
      } catch (error) {
        console.error('Model creation error: ' + error);
      }
    });

  Object.keys(models[databaseInfo.name]).forEach((modelName) => {
    if ('associate' in models[databaseInfo.name][modelName]) {
      models[databaseInfo.name][modelName].associate(sequelize[databaseInfo.name].models);
    }
  });
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

models/a/address.js

You have to define a unique name for the address table (let’s use user_address)

module.exports = (sequelize) => {
  const Address = sequelize.define('user_address', { // Change here.
  }, {
    tableName: 'address',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  Address.associate = (models) => {
    Address.hasMany(models.user, {
      foreignKey: {
        name: 'addressIdKey',
        field: 'address_id',
      },
      as: 'users',
    });
  };
  return Address;
};

models/a/user.js

Then you have to update your user model to specify the name of the model used for relation (models.user_address in this case):

module.exports = (sequelize) => {
  const User = sequelize.define('user', {
  }, {
    tableName: 'user',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  User.associate = (models) => {
    User.belongsTo(models.user_address, { // Change here.
      foreignKey: {
        name: 'addressIdKey',
        field: 'address_id',
      },
      as: 'address',
    });
  };
  return User;
};

models/b/address.js

Define a unique name for the address table in database b (let’s use user_company)

module.exports = (sequelize) => {
  const Address = sequelize.define('company_address', { // Change here.
  }, {
    tableName: 'address',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  Address.associate = (models) => {
    Address.hasMany(models.company, {
      foreignKey: {
        name: 'addressIdKey',
        field: 'address_id',
      },
      as: 'companies',
    });
  };
  return Address;
};

models/b/company.js

Then (last step :sweat_smile:) you have to update your company model to specify the name of the model used for relation (models.company_address in this case):

module.exports = (sequelize) => {
  const Company = sequelize.define('company', {
  }, {
    tableName: 'company',
    timestamps: false,
    schema: process.env.DATABASE_SCHEMA,
  });
  Company.associate = (models) => {
    Company.belongsTo(models.company_address, { // Change here.
      foreignKey: {
        name: 'addressIdKey',
        field: 'address_id',
      },
      as: 'company_address',
    });
  };
  return Company;
};

Then it should work and display this (see below) in Forest Admin interface. I’ve just tried this example and it works! :tada:

TL;DR

Name your address models with two different names in their own files (e.g sequelize.define('company_address') and in the file where they are referenced (e.g Company.belongsTo(models.company_address). Almost everything was already correct on your side. :+1:

Let me know if it fixes your problem.

2 Likes

Thanks for your solution.
My problem has been solved
My code is working fine now.

Thank you so much!

2 Likes