Sequelize addColumn migration always referring public schema

Question

I have an Express application with Sequelize as ORM and PostgreSQL as database. Database is setup in such a manner where every tenants in my application will have different schemas.The migration files which exists in my application contains addColumn/removeColumn migrations. But when i am running npx sequelize-cli db:migrate command, i am getting the following error.

ERROR: relation "public.table_name" does not exist

The above error is throwing only for the migration files that contains either addColumn/removeColumn migrations. Also i am nowhere referring to public schema(even deleted the public schema from db). Is there a way to run the migrations against a particular schema(say test_schema) in Sequelize without hardcoding the schema name in the migration files?

Update #2

'use strict';

module.exports = {
  up: async(queryInterface, Sequelize) => {
    try {
      await queryInterface.addColumn('table_name', 'new_field_name', {
        type: Sequelize.INTEGER
      });
      return Promise.resolve();
    } catch (e) {
      return Promise.reject(e);
    }
  },

  down: async(queryInterface, Sequelize) => {
    try {
      await queryInterface.removeColumn('table_name','new_field_name');
      return Promise.resolve();
    } catch (e) {
      return Promise.reject(e);
    }
  }
};

Above is the code for addColumn migration .

Please show an example of a migration with addColumn/removeColumn — Anatoly, Apr 28 '20 at 18:48
@Anatoly i have updated the question with the example migration.Please see. — Neeraj narayanan, Apr 29 '20 at 09:40

Anatoly · Answer 1 · 2020-05-11T11:43:35.320

5

You can use an extended syntax of addColumn/removeColumn like this:

const { tableSchemas } = require('../config.json')
const tableName = 'table_name'
...
module.exports = {
  up: async(queryInterface, Sequelize) => {
    // adding transaction because we have several changes
    await queryInterface.sequelize.transaction(async transaction => {
      for (const tableSchema of tableSchemas) {
        const table = { schema: tableSchema, tableName: tableName }
        await queryInterface.addColumn(table, 'new_field_name', {
          type: Sequelize.INTEGER
        }, { transaction });
      }
    })
  },

  down: async(queryInterface, Sequelize) => {
    // adding transaction because we have several changes
    await queryInterface.sequelize.transaction(async transaction => {
      for (const tableSchema of tableSchemas) {
        const table = { schema: tableSchema, tableName: tableName }
        await queryInterface.removeColumn(table,'new_field_name', { transaction });
      }
    })
  }
};

edited May 11 '20 at 11:43

answered Apr 29 '20 at 15:26

Anatoly

20,799
3
28
42

i am looking for a way without hardcoding the schema name in the migration files – Neeraj narayanan Apr 30 '20 at 05:24
you can import a config file in a migration file and use a schema name from it. – Anatoly Apr 30 '20 at 14:58
i think it is not possible in my case since i need to run migrations against 5 schemas.Please share your thoughts – Neeraj narayanan May 06 '20 at 13:50
Where is a source of 5 schema names? – Anatoly May 06 '20 at 16:14
right now i am hardcoding schema in the config.json file – Neeraj narayanan May 07 '20 at 05:25
And wgere do you wish to store schema names instead of config.json? – Anatoly May 07 '20 at 17:16
in some env files.This is the confusion,how to run that migration against each schema even though we are specifying it in a config/env files – Neeraj narayanan May 09 '20 at 05:56
Do you use a DB storage for storing applied migrations? – Anatoly May 09 '20 at 08:39
No. Not using any – Neeraj narayanan May 09 '20 at 09:38
If you can import env files to your migration file then you just need a for loop to execute 'up' or 'down' content of a migration. – Anatoly May 09 '20 at 09:48
can u suggest how that can be done in the migration file i have included in my Question – Neeraj narayanan May 11 '20 at 05:44
Thank you for the answer.On doing the way you proposed; say i have four schemas, all the 4 migrations will be generated in the same folder right.so i won't be able to run migration against a particular schema if such a requirement came.is there any such options like that ? – Neeraj narayanan May 27 '20 at 17:29
you can try yo create different config sections and link them to a certain NODE environment variable (like NODE_ENV=development) and then set the variable before executing a migration. – Anatoly May 27 '20 at 18:23
not sure if this will still be useful, but preceding table name with schema `schema."table_name"` worked for me. Seuelize version 6.6.2 and postgressql 12. – Nana Koduah Sep 17 '22 at 13:10

Sequelize addColumn migration always referring public schema

1 Answers1