Sequelize: Using Multiple Databases

Question

Do I need to create multiple instances of Sequelize if I want to use two databases? That is, two databases on the same machine.

If not, what's the proper way to do this? It seems like overkill to have to connect twice to use two databases, to me.

So for example, I have different databases for different functions, for example, let's say I have customer data in one database, and statistical data in another.

So in MySQL:

MySQL [customers]> show databases;
+--------------------+
| Database           |
+--------------------+
| customers          |
| stats              |
+--------------------+

And I have this to connect with sequelize

// Create a connection....
var Sequelize = require('sequelize');
var sequelize = new Sequelize('customers', 'my_user', 'some_password', {
    host: 'localhost',
    dialect: 'mysql',

    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
    logging: function(output) {
        if (opts.log_queries) {
            log.it("sequelize_log",{log: output});
        }
    }

});

// Authenticate it.
sequelize.authenticate().nodeify(function(err) {

    // Do stuff....

});

I tried to "trick" it by in a definition of a model using dot notation

var InterestingStatistics = sequelize.define('stats.interesting_statistics', { /* ... */ });

But that creates the table customers.stats.interesting_statistics. I need to use an existing table in the stats database.

What's the proper way to achieve this? Thanks.

Answer 1

You need to create different instances of sequelize for each DB connection you want to create:

const Sequelize = require('Sequelize'); const userDb = new Sequelize(/* ... */); const contentDb = new Sequelize(/* ... */); 

Each instance created from sequelize has its own DB info (db host, url, user, pass, etc...), and these values are not meant to be changed, so there is no "correct" way to create multiple connections with one instance of sequelize.

From their docs:

Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database.

One instance per database

A "common" approach to do this, is having your databases in a config.json file and loop over it to create connections dinamically, something like this maybe:

config.json

{     /*...*/     databases: {         user: {             path: 'xxxxxxxx'         },         content: {             path: 'xxxxxxxx'         }     } } 

Your app

const Sequelize = require('sequelize'); const config = require('./config.json');  // Loop through const db = {}; const databases = Object.keys(config.databases); for(let i = 0; i < databases.length; ++i) {     let database = databases[i];     let dbPath = config.databases[database];     db[database] = new Sequelize( dbPath ); }  // Or a one liner const db = Object.entries(config).reduce((r, db) => (r[db[0]] = db[1].path) && r, {});  // Sequelize instances: // db.user // db.content 

You will need to do a little bit more coding to get it up and running but its a general idea.

Answer 2

if you are trying to associate objects in the same RDS across multiple databases, you can use schema.

http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-schema

this will prepend the db name to the table name so, presumably, your queries would come out like: SELECT A.ColA, B.ColB FROM SchemaA.ATable A INNER JOIN SchemaB.BTable B ON B.BId = A.BId

Answer 3

Why don't you use raw query? With this you can connect to one database and query the other. See sample code below.

const sequelize = require('db_config');
function test(req, res){
  const qry = `SELECT * FROM db1.affiliates_order co
LEFT JOIN db2.affiliates m ON m.id = co.campaign_id`;
  sequelize.query(qry, null, {  raw: true}).then(result=>{
    console.log(result);
  })
}