Why don’t the Sequelize migrations work properly with schemas other than public?

I’m using the Sequelize CLI to perform migrations, and it doesn’t seem to play nice with schemas, outside of “public”. I ran a series of tests, and it appears that even when I provide the schema in the “options” of the Sequelize command, it only works on some and not others.

For example, I can create a table on the “up” portion and it ends up in the proper schema, but fails on the “down” because it can’t find the table in public:

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable("migration_test", {
            name: Sequelize.DataTypes.STRING,
            isBetaMember: {
                type: Sequelize.DataTypes.BOOLEAN,
                defaultValue: false,
                allowNull: false
            }
        }, {
            schema: "profile"
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable("migration_test", {
            schema: "profile"
        });
    }
};

The table is created correctly:

Executing (default): CREATE TABLE IF NOT EXISTS
“profile”.”migration_test” (“name” VARCHAR(255), “isBetaMember”
BOOLEAN NOT NULL DEFAULT false);

However, on “down”:

Executing (default): DROP TABLE IF EXISTS “migration_test”;

The table obviously isn’t deleted, so not super useful. The SequelizeMeta table ends up in “public” schema, which I can live with, if I have to.

I tried addColumn as well, and the “schema” attribute isn’t even recognized:

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.addColumn(
            "migration_test", 
            "test_column_1", 
            Sequelize.STRING(100), {
                schema: "profile"
            }
        );
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.removeColumn("migration_test", "test_column_1", {
            schema: "profile"
        });
    }
};

…throws an error:

Executing (default): ALTER TABLE “public”.”migration_test” ADD COLUMN
“test_column_1” VARCHAR(100);

ERROR: relation “public.migration_test” does not exist

Am I doing something wrong, or is this just the state of things? It seems inconceivable that this wasn’t built to handle pg schemas? Am I better off using something like db-migrate or just writing one-off SQL scripts for db changes, than using this framework?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It’s better to indicate a schema explicitly if it’s other than public:

const table = { schema: SchemaName, tableName: TableName };

module.exports = {
    up: (queryInterface: QueryInterface): Promise<void> => queryInterface.sequelize.transaction(
        async (transaction) => {
            await queryInterface.createTable(table, attributes, { transaction });
        }
    ),

    down: (queryInterface: QueryInterface): Promise<void> => queryInterface.sequelize.transaction(
        async (transaction) => {
            await queryInterface.dropTable(table as any, { transaction });
        }
    )

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0