Why don't the Sequelize migrations work properly with schemas other than public?-CodePudding

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?

CodePudding user response：

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 });
        }
    )