postgresql - Sequelize : Many-To-Many relation between MariaDB and Postgres

Question

小背景故事
这是我的场景，我的 MariaDB 中有一个名为 Location 的数据库，我的 Postgres 中有一个 User 数据库。我想将其中两个链接到我的 Postgres 数据库中。所以我有两个表 users 和 userLocations 。哦，是的，值得注意的是 我只能从 LocationDB 读取(SELECT 和 CREATE VIEW)，并以 root 身份连接到 UserDB。是的，我已经成功地对他们两个进行了 authenticate()。我什至能够从 LocationDB 接收数据，但是现在唯一的问题是在 User 和 Location 之间创建这种关系。底部可用的错误日志。

这是我的模型:models/user.js

// ... Connection to UserDB (uses Postgres)
const UserDB = require('../datasources/user-db')

const User = UserDB.define('user', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
  name: { type: Sequelize.STRING, allowNull: false }
})
module.exports = User

models/location.js

// Connection to LocationDB (uses MariaDB)
const LocationDB = require('../datasources/location-db')

const Location = LocationDB.define('ms_location', {
  id_Location: { type: Sequelize.INTEGER, primaryKey: true },
  name_Location: { type: Sequelize.STRING, allowNull: false }
}, { timestamps: false, freezeTableName: true })
module.exports = Location

models/user-location.js

// ... Connection to UserDB (uses Postgres)
const UserDB = require('../datasources/user-db')

const UserLocation = UserDB.define('userLocation', {
  id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true }
})
module.exports = UserLocation

app.js

const Location = require('./models/location')
const User     = require('./models/user')
const UserLocation = require('./models/user-location')

User.belongsToMany(Location, { through: UserLocation })

这是案例的缩小版本，如果您需要更多信息，请询问，因为我也是 sequelize 的新手

错误日志:(抱歉我没有任何美化它的想法)

{ SequelizeDatabaseError: relation "ms_location" does not exist
    at Query.formatError (<path-to-project>/node_modules/.registry.npmjs.org/sequelize/5.8.6/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)
    at query.catch.err (<path-to-project>/node_modules/.registry.npmjs.org/sequelize/5.8.6/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)
    at tryCatcher (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/promise.js:517:31)
    at Promise._settlePromise (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/promise.js:574:18)
    at Promise._settlePromise0 (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/promise.js:619:10)
    at Promise._settlePromises (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/promise.js:695:18)
    at _drainQueueStep (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/async.js:138:12)
    at _drainQueue (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/async.js:131:9)
    at Async._drainQueues (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/async.js:147:5)
    at Immediate.Async.drainQueues [as _onImmediate] (<path-to-project>/node_modules/.registry.npmjs.org/bluebird/3.5.5/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:705:18)
    at tryOnImmediate (timers.js:676:5)
    at processImmediate (timers.js:658:5)
  name: 'SequelizeDatabaseError',
  parent:
   { error: relation "ms_location" does not exist
       at Connection.parseE (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:602:11)
       at Connection.parseMessage (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:399:19)
       at Socket.<anonymous> (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:121:22)
       at Socket.emit (events.js:189:13)
       at addChunk (_stream_readable.js:284:12)
       at readableAddChunk (_stream_readable.js:265:11)
       at Socket.Readable.push (_stream_readable.js:220:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 107,
     severity: 'ERROR',
     code: '42P01',
     detail: undefined,
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'namespace.c',
     line: '426',
     routine: 'RangeVarGetRelidExtended',
     sql:
      'CREATE TABLE IF NOT EXISTS "userLocations" ("id"  SERIAL , "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "userId" INTEGER REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "msLocationIdLocation" INTEGER REFERENCES "ms_location" ("id_Location") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("userId", "msLocationIdLocation"), PRIMARY KEY ("id"));' },
  original:
   { error: relation "ms_location" does not exist
       at Connection.parseE (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:602:11)
       at Connection.parseMessage (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:399:19)
       at Socket.<anonymous> (<path-to-project>/node_modules/.registry.npmjs.org/pg/7.11.0/node_modules/pg/lib/connection.js:121:22)
       at Socket.emit (events.js:189:13)
       at addChunk (_stream_readable.js:284:12)
       at readableAddChunk (_stream_readable.js:265:11)
       at Socket.Readable.push (_stream_readable.js:220:10)
       at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
     name: 'error',
     length: 107,
     severity: 'ERROR',
     code: '42P01',
     detail: undefined,
     hint: undefined,
     position: undefined,
     internalPosition: undefined,
     internalQuery: undefined,
     where: undefined,
     schema: undefined,
     table: undefined,
     column: undefined,
     dataType: undefined,
     constraint: undefined,
     file: 'namespace.c',
     line: '426',
     routine: 'RangeVarGetRelidExtended',
     sql:
      'CREATE TABLE IF NOT EXISTS "userLocations" ("id"  SERIAL , "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "userId" INTEGER REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "msLocationIdLocation" INTEGER REFERENCES "ms_location" ("id_Location") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("userId", "msLocationIdLocation"), PRIMARY KEY ("id"));' },
  sql:
   'CREATE TABLE IF NOT EXISTS "userLocations" ("id"  SERIAL , "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "userId" INTEGER REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "msLocationIdLocation" INTEGER REFERENCES "ms_location" ("id_Location") ON DELETE CASCADE ON UPDATE CASCADE, UNIQUE ("userId", "msLocationIdLocation"), PRIMARY KEY ("id"));' }

Answer 1

您已经告诉 Sequelize，您有一个名为 user 的表和一个名为 msLocation 的表，并且这两个表通过另一个名为 userLocation 的表相关联。 (或者是 userLocations ？你的代码和错误信息不一致。)

看起来 Sequelize 正在尝试创建 userLocations 表 UserDB 但它失败了，因为作为表定义的一部分，它正在尝试创建对 ms_location 的外键引用:

"msLocationIdLocation" INTEGER REFERENCES "ms_location" ("id_Location")
ON DELETE CASCADE ON UPDATE CASCADE

失败是因为 ms_location 不在 UserDB 中，而是在另一个数据库中。

我不知道 Sequelize 是否真的可以处理跨多个数据库服务器的表，但我对此表示怀疑。创建跨越同一数据库中两个表的查询很容易，Sequelize 只需创建一个连接即可。如果表在两个不同的服务器上，情况就完全不同了。现在 Sequelize 必须运行两个不同的查询并在内存中执行所有连接逻辑。这是一个相当大的电梯。

某些数据库有一种方法可以在一个数据库中创建一个表，该表可以复制或转发到另一个数据库中的表。如果 PostgreSQL 支持，您可以尝试使用它来复制或隐藏 MariaDB 表，这将使 Sequelize 能够将所有表视为 UserDB 的一部分。