How to define default schema for sequelize migrations when using postgres and umzug?

Question

I am trying to always run migrations with sequelize and umzug to a specific postgresql database schema. Lets call it custom schema. By default all queries go to public schema. Is there any way to define the default schema to run all migrations to in sequelize or umzug?

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

Background:

With the following code I am able to define the schema for a single query in a migration:

// 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 () => {
  }
}

And it reports that it is a success and uses the correct schema:

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' } } ]

However what I require is to be able to define the default schema that all queries in all migrations always run to instead of defining the schema I need in every single query I want to run.

I tried searching, reading the documentation of the libraries and copy pasting schema: 'custom' everywhere, but nothing else worked so far except the above example.

I am using the following code to run the migrations:

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()

My umzug is 2.2.0 and sequelize is 5.3.0. The migrations table is correctly created to the custom schema, but migrations still run in public schema.

I get the following error when running migrations that do not specify the schema in the query itself. From the error we can see that the schema is undefined:

{ 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

It's a bit confusing but you need to define a searchpath and dialect option for the connection.

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