在使用postgres和umzug时，如何定义用于后遗症迁移的默认模式？

Question

我总是试图运行带有后缀和umzug的迁移到特定的postgresql数据库模式。让我们称它为custom模式。默认情况下，所有查询都会转到public架构。是否有任何方法来定义默认模式来运行所有迁移到的后缀或umzug?

• http://docs.sequelizejs.com/
• https://github.com/sequelize/umzug

背景：

使用以下代码，我能够为迁移中的单个查询定义模式：

// schema defined according to https://sequelize.readthedocs.io/en/latest/docs/migrations/
module.exports = {
  up: async (queryInterface, Sequelize) => {
    return Sequelize.transaction(async transaction => {
      await queryInterface.renameTable({ tableName: 'oldtablename', schema: "custom"}, 'newtablename', { transaction })
    })
  },

  down: async () => {
  }
}

它报告了成功，并使用了正确的模式：

Migration {
  path:
   'path/to/migrations/migration_test.js',
  file: 'migration_test.js',
  options:
   { storage: 'sequelize',
     storageOptions: [Object],
     logging: [Function: bound consoleCall],
     upName: 'up',
     downName: 'down',
     migrations: [Object],
     schema: 'custom' } } ]

但是，我需要的是能够定义默认模式，所有迁移中的所有查询都会运行到这个模式，而不是在我想要运行的每个查询中定义我需要的模式。

我试着搜索、阅读库的文档并到处复制粘贴schema: 'custom'，但到目前为止，除了上面的示例之外，没有其他任何东西起作用。

我使用以下代码运行迁移：

const sequelizeConn = new Sequelize(ENV_DB_URL, {
  schema: 'custom',
  logging: false
})

    const migrator = new Umzug({
      storage: 'sequelize',
      storageOptions: {
        sequelize: sequelizeConn,
        tableName: 'migrations',
        schema: 'custom'
      },
      logging: console.log,
      migrations: {
        params: [
          sequelizeConn.getQueryInterface(),
          sequelizeConn
        ],
        path: `${process.cwd()}/src/database/migrations`,
        pattern: /\.js$/
      }
    })
    migrator.up()

我的umzug是2.2.0，sequelize是5.3.0。migrations表被正确地创建为custom模式，但是迁移仍然在public模式中运行。

当运行未在查询本身中指定架构的迁移时，我会得到以下错误。从错误中我们可以看到模式是未定义的：

{ SequelizeDatabaseError: relation "oldtablename" does not exist
  at Query.formatError (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:354:16)
  at query.catch.err (/usr/src/app/node_modules/sequelize/lib/dialects/postgres/query.js:71:18)
  at tryCatcher (/usr/src/app/node_modules/bluebird/js/release/util.js:16:23)
  at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/bluebird/js/release/promise.js:512:31)
  at Promise._settlePromise (/usr/src/app/node_modules/bluebird/js/release/promise.js:569:18)
  at Promise._settlePromise0 (/usr/src/app/node_modules/bluebird/js/release/promise.js:614:10)
  at Promise._settlePromises (/usr/src/app/node_modules/bluebird/js/release/promise.js:690:18)
  at _drainQueueStep (/usr/src/app/node_modules/bluebird/js/release/async.js:138:12)
  at _drainQueue (/usr/src/app/node_modules/bluebird/js/release/async.js:131:9)
  at Async._drainQueues (/usr/src/app/node_modules/bluebird/js/release/async.js:147:5)
  at Immediate.Async.drainQueues [as _onImmediate] (/usr/src/app/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)
  at process.topLevelDomainCallback (domain.js:120:23)
name: 'SequelizeDatabaseError',
parent:
 { error: relation "oldtablename" does not exist
     at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:554:11)
     at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:379:19)
     at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:119:22)
     at Socket.emit (events.js:189:13)
     at Socket.EventEmitter.emit (domain.js:441:20)
     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: 118,
   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: '420',
   routine: 'RangeVarGetRelidExtended',
   sql:
    'ALTER TABLE "oldtablename" RENAME TO "newtablename";' },
original:
 { error: relation "oldtablename" does not exist
     at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:554:11)
     at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:379:19)
     at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:119:22)
     at Socket.emit (events.js:189:13)
     at Socket.EventEmitter.emit (domain.js:441:20)
     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: 118,
   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: '420',
   routine: 'RangeVarGetRelidExtended',
   sql:
    'ALTER TABLE "oldtablename" RENAME TO "newtablename";' },
sql:
 'ALTER TABLE "oldtablename" RENAME TO "newtablename";' }

Answer 1

这有点让人困惑，但是您需要为连接定义一个搜索路径和方言选项。

const sequelizeConn = new Sequelize(ENV_DB_URL, {
  schema: 'custom',
  logging: false,
  searchPath: 'custom',
  dialectOptions: {
    prependSearchPath: true
}
})