Javascript Sequelize：使用多个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37078970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sequelize: Using Multiple Databases
提问by dougBTV
Do I need to create multiple instances of Sequelize if I want to use two databases? That is, two databases on the same machine.
如果我想使用两个数据库，是否需要创建多个 Sequelize 实例？即同一台机器上的两个数据库。
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 中：
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.
但这会创建 table customers.stats.interesting_statistics。我需要使用统计数据库中的现有表。
What's the proper way to achieve this? Thanks.
实现这一目标的正确方法是什么？谢谢。
回答by Aramil Rey
You need to create different instances of sequelize for each DB connection you want to create:
您需要为要创建的每个数据库连接创建不同的 sequelize 实例：
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.
从 sequelize 创建的每个实例都有自己的数据库信息(db host, url, user, pass, etc...)，并且这些值不打算更改，因此没有“正确”的方法来创建多个连接与一个续集的实例。
Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database.
Sequelize 将在初始化时设置一个连接池，因此理想情况下您应该只为每个数据库创建一个实例。
One instance per database
每个数据库一个实例
A "common" approach to do this, is having your databases in a config.jsonfile and loop over it to create connections dinamically, something like this maybe:
执行此操作的“常见”方法是将您的数据库放在一个config.json文件中并循环遍历它以动态地创建连接，可能是这样的：
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.
您需要做更多的编码才能启动并运行它，但这是一个总体思路。
回答by Y Talansky
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);
})
}
回答by john ellis
if you are trying to associate objects in the same RDS across multiple databases, you can use schema.
如果您尝试跨多个数据库关联同一 RDS 中的对象，则可以使用schema.
http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-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
这会将数据库名称添加到表名称之前，因此，大概，您的查询将如下所示：
SELECT A.ColA, B.ColB FROM SchemaA.ATable A INNER JOIN SchemaB.BTable B ON B.BId = A.BId
