Sequelize: Using Multiple Databases
Solution 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.
Observe that, in the examples above, Sequelize refers to the library itself while sequelize refers to an instance of Sequelize, which represents a connection to one database. This is the recommended convention and it will be followed throughout the documentation.
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.
Solution 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
Solution 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);
})
}
dougBTV
For work: Purely penguin. Asterisk. NodeJS. Docker. Recovering PHP developer. For fun: Microcontroller, embedded linux. Fly fishing, Adirondack exploring. Number of cats: 2 SOreadytohelp
Updated on April 30, 2020Comments
-
dougBTV about 4 years
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.
-
dougBTV about 8 yearsExcellent answer, and thank you. Confirmed, works flawlessly. I've gone ahead and created multiple instances, and then I reference those when creating my models, and it works a charm. Appreciate the sanity check to make sure I'm not doing it in a hack-ish fashion.
-
dialox over 7 yearsI have the similar issue on multiple databases access. In my app, there are a single db connection pool, which handles all the db connections onto multiple databases with the same schema. So, I haven't found a perfect solution yet.
-
7H3 IN5ID3R over 6 years@dougBTV Can you share snippet to represent relations between them?
-
Nicolas B. about 6 yearsIs this scalable ? Could this cause any problem if I have over 100 database to handle ? 1000 ? 10 000 ?
-
Manik Mittal almost 6 yearsCan we put query to get data from two databases in Postgres?
-
GavinBelson over 5 yearsThis is interesting because PG uses schemas somewhat differently than MSSQL. I am currently working on a multi db MSSQL and I guess I will see if the schema dot notation can be applied to databases instead.
-
GavinBelson over 5 yearsCan confirm this works, I looped through databases in config.js to get a connection to each.
-
Faris Rayhan over 5 yearsIt was too complicated guys. Not recommended way
-
Nir Alfasi about 5 yearsThe OP asked about querying different DBs not different tables!
-
Y Talansky about 5 years@alfasin this answer includes dbs the syntax is DB.Table where DB is your DB name.
-
Nir Alfasi about 5 yearsThere is no such syntax for db: this is
schema.table
notdb.table
! -
GavinBelson over 4 yearsYes.. I was able to go into different dbs and schemas with the dot notation as long as the models are loaded for each database
-
leo_cape almost 2 yearsHow do you specify which database to use when running an update migration?