CODEWITHSUNDEEP
sqlmigrationsequelize.jspsql
Wizzardzz
Wizzardzz

Reputation: 841

Sequelize migration default for column "" cannot be cast automatically to type "enum"

I am trying to write a migration to add/remove a value to an enum in my psql database as follows:

up : async (queryInterface, Sequelize) => {
  await queryInterface.changeColumn('Messages', 'status', {
    type : Sequelize.STRING
  });

  await queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_Messages_status"');

  await queryInterface.changeColumn('Messages', 'status', {
    type         : Sequelize.ENUM('unread', 'opened', 'expired', 'blocked', 'refused'),
    defaultValue : 'unread'
  });
},

down : async (queryInterface, Sequelize) => {
  await queryInterface.changeColumn('Messages', 'status', {
    type : Sequelize.STRING
  });

  await queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_Messages_status"');

  await queryInterface.changeColumn('Messages', 'status', {
    type         : Sequelize.ENUM('unread', 'opened', 'expired', 'blocked'),
    defaultValue : 'unread'
  });
}

Unfortunately the down method returns: ERROR: default for column "status" cannot be cast automatically to type "enum_Messages_status".

If I try to change the down to:

await queryInterface.changeColumn('Messages', 'status', {
  type   : 'ENUM USING CAST("status" as ENUM)',
  values : [
    'unread',
    'opened',
    'expired',
    'blocked'
  ],
  defaultValue : 'unread'
});

I get another error: ERROR: type "enum" does not exist.

What can I do here? Thanks!

Upvotes: 1

Views: 2128

Answers (2)

Aman Lajpal
Aman Lajpal

Reputation: 1

I think you should be using sequelize db handled transactions here because if one migration fails other two passes then you will not be able to undo migrations

Upvotes: 0

Itai Hanski
Itai Hanski

Reputation: 8680

It appears you can't cast and define the enum at the same command. Also, your cast needs to be to your specific type.

What you can do to handle it with is to split the enum definition and column change into two commands:

// Manually define the new type
await queryInterface.sequelize.query(`CREATE TYPE "enum_Messages_status" AS ENUM('unread', 'opened', 'expired', 'blocked')`);

// Use it to perform the cast
await queryInterface.changeColumn('Messages', 'status', {
  type: `"enum_Messages_status" USING CAST("status" as "enum_Messages_status")`,
});

For the opposite migration you'll need to reverse the order and drop the type.

Upvotes: 4

Related Questions