knex - postgres migrations on multiple schemas with same database

Question

I am using postgres,knex and node.js in my project. There in my postgres database , I have two schemas. so when I'm doing knex migrations it works properly, but migration_table and migration_lock_table creates within the default schema.

I want create both migration_table and migration_lock_table within the relevant schema.

knexfile.js

const path = require('path');
const { DATABASE } = require('./configurations');

module.exports = {
   client: 'pg',
   pool: {
   min: 2,
   max: 20,
   idleTimeoutMillis: 500,
},
migrations: {
   directory: path.join(__dirname, "migrations"),
   tableName: 'migrations',
},
connection: {
   host: DATABASE.HOST,
   port: DATABASE.PORT,
   database: DATABASE.NAME,
   user: DATABASE.USER,
   password: DATABASE.PASSWORD,
   searchPath: DATABASE.SCHEMA,
 },
};

create_user_table.js

exports.up = function(knex) {
   return knex.schema.withSchema('shema2').createTable('User', function(table) {
      table.uuid('id').notNullable().primary();
      table.string('name').notNullable().unique();
      table.string('email');
   });
};

  exports.down = function(knex) {
    return knex.schema.withSchema('schema2').dropTable('User');
  };

What I need is, I need to create migration_table and migration_lock table within the schema2. Now both table are created within public schema (the default schema).

How can change the schema?

Answer 1

You might want to check out this post

Multiple postgres schemas in migrations #1138