Drop and create ENUM with sequelize correctly?

If you want to change/edit type enum without losing data. here is my migration code. hopefully it helps.

queryInterface.changeColumn(
  'table_name',
  'Column_name',
  {
    type: Sequelize.TEXT,
  },
),
queryInterface.sequelize.query('drop type enum_tableName_columnName;')
.then(() => queryInterface.changeColumn(
  'table_name',
  'column_name',
  {
    type: Sequelize.ENUM('value1','value2'),
  },
)),

UPDATE: I have used this in three projects up to now, so I decided to create a npm module: https://www.npmjs.com/package/replace-enum-postgresql.

I made a utility to do this, hope you it this helpful.

utils/replace_enum.js:

'use strict';

/**
 * Since PostgreSQL still does not support remove values from an ENUM,
 * the workaround is to create a new ENUM with the new values and use it
 * to replace the other.
 *
 * @param {String} tableName
 * @param {String} columnName
 * @param {String} defaultValue
 * @param {Array}  newValues
 * @param {Object} queryInterface
 * @param {String} enumName - Optional.
 *
 * @return {Promise}
 */
module.exports = function replaceEnum({
  tableName,
  columnName,
  defaultValue,
  newValues,
  queryInterface,
  enumName = `enum_${tableName}_${columnName}`
}) {
  const newEnumName = `${enumName}_new`;

  return queryInterface.sequelize.transaction((t) => {
    // Create a copy of the type
    return queryInterface.sequelize.query(`
      CREATE TYPE ${newEnumName}
        AS ENUM ('${newValues.join('\', \'')}')
    `, { transaction: t })
      // Drop default value (ALTER COLUMN cannot cast default values)
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            DROP DEFAULT
      `, { transaction: t }))
      // Change column type to the new ENUM TYPE
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            TYPE ${newEnumName}
            USING (${columnName}::text::${newEnumName})
      `, { transaction: t }))
      // Drop old ENUM
      .then(() => queryInterface.sequelize.query(`
        DROP TYPE ${enumName}
      `, { transaction: t }))
      // Rename new ENUM name
      .then(() => queryInterface.sequelize.query(`
        ALTER TYPE ${newEnumName}
          RENAME TO ${enumName}
      `, { transaction: t }))
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            SET DEFAULT '${defaultValue}'::${enumName}
      `, { transaction: t }));
  });
}

and this is my example migration:

'use strict';

const replaceEnum = require('./utils/replace_enum');

module.exports = {
  up: (queryInterface, Sequelize) => {
    return replaceEnum({
      tableName: 'invoices',
      columnName: 'state',
      enumName: 'enum_invoices_state',
      defaultValue: 'created',
      newValues: ['archived', 'created', 'paid'],
      queryInterface
    });
  },

  down: (queryInterface, Sequelize) => {
    return replaceEnum({
      tableName: 'invoices',
      columnName: 'state',
      enumName: 'enum_invoices_state',
      defaultValue: 'draft',
      newValues: ['archived', 'draft', 'paid', 'sent'],
      queryInterface
    });
  }
};

Dropping the ENUM manually in down worked pretty well for me.

module.exports = {
    up: function (queryInterface, DataTypes) {
        queryInterface.createTable('Users', {
            //...
            status: {
                type: DataTypes.ENUM,
                values: [
                    'online',
                    'offline',
                ],
                defaultValue: 'online'
            }
            //...
        })
    },

    down: function (queryInterface) {
        return queryInterface.sequelize.transaction(t => {
            return Promise.all([
                queryInterface.dropTable('Users'),
                queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_Users_status";'),
            ]);
        });
    }
};

Elaborating on shakir ullah's post and a comment on github, here's what worked for me:

module.exports = {
  up: (queryInterface, Sequelize) => {
    // 1. Change the type of the column to string
    return queryInterface.changeColumn('Users', 'status', {
      type: Sequelize.STRING,
    })
    // 2. Drop the enum
    .then(() => {
      const pgEnumDropQuery = queryInterface.QueryGenerator.pgEnumDrop('Users', 'status');
      return queryInterface.sequelize.query(pgEnumDropQuery);
    })
    // 3. Create the enum with the new values
    .then(() => {
      return queryInterface.changeColumn('Users', 'status', {
        type: Sequelize.ENUM,
        values: [
          'online',
          'offline',
        ],
        defaultValue: 'online'
      });
    })
  },

  // Here I made the choice to restore older values but it might not work
  // if rows were inserted with the new enum.
  // What you want to do then is up to you. Maybe lose the enum and keep
  // the column as a string.
  down: (queryInterface, Sequelize) => {
    // Do as above to restore older enum values
    return queryInterface.changeColumn('Users', 'status', {
      type: Sequelize.STRING,
    }).then(() => {
      const pgEnumDropQuery = queryInterface.QueryGenerator.pgEnumDrop('Users', 'status');
      return queryInterface.sequelize.query(pgEnumDropQuery);
    }).then(() => {
      return queryInterface.changeColumn('Users', 'status', {
        type: Sequelize.ENUM,
        values: [
          'older',
          'values',
        ],
        defaultValue: 'older'
      });
    })
  },
}

This way it worked for me:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn(
      'users',  
      'status', 
      {
        type: Sequelize.ENUM,
        values: [
          'online',
          'offline'  
        ],
        defaultValue: 'online',
        allowNull: false,
      }
    )   
  },

  down: async (queryInterface) => {
    await queryInterface.removeColumn('users', 'status')
    .then(queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_users_status";'))
  }
};

If we go further in Shakir ullah's answer we need to use sequelize transaction as we are performing 3 operations in a sequence.

Also, we should use 'DROP TYPE IF EXISTS' instead of 'DROP TYPE'.

Below is the example to alter the enum datatypes without losing the data.

module.exports = {
  up: (queryInterface, Sequelize) =>
    // transaction in migration reference https://sequelize.org/docs/v6/other-topics/migrations/#migration-skeleton
    queryInterface.sequelize.transaction(async (transaction) => {
      // convert enum type to string
      await queryInterface.changeColumn(
        'user',
        'role',
        {
          type: Sequelize.STRING()
        },
        {
          transaction
        }
      );
      // drop that enum type
      await queryInterface.sequelize.query('DROP TYPE IF EXISTS enum_user_role;', {
        transaction
      });
      // again change column type to enum from string
      await queryInterface.changeColumn(
        'user',
        'role',
        {
          type: Sequelize.ENUM('Teacher', 'Student', 'Principal', 'Monitor', 'Security')
        },
        {
          transaction
        }
      );
    }),

  down: (queryInterface, Sequelize) =>
    // using transaction
    queryInterface.sequelize.transaction(async (transaction) => {
      await queryInterface.changeColumn(
        'user',
        'role',
        {
          type: Sequelize.STRING()
        },
        {
          transaction
        }
      );
      await queryInterface.sequelize.query('DROP TYPE IF EXISTS enum_user_role;', {
        transaction
      });
      await queryInterface.changeColumn(
        'user',
        'role',
        {
          type: Sequelize.ENUM(
            'Teacher',
            'Student',
            'Principal',
            'Monitor',
            'Security',
            'Super Admin',
            'Volunteers',
            'Billing',
            'Admin'
          )
        },
        {
          transaction
        }
      );
    })
};