node.js postgresql migration sequelize.js

Drop and create ENUM with sequelize correctly?

How to correctly drop and then recreate ENUM type with sequelize for Postgres in migrations? For example this migration doesn't drop enum_Users_status enum... so any attempts to recreate/change status values after they have been once created fail.

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

    down: function (queryInterface) {
        queryInterface.dropTable('Users')
    },
}

Eventually i did manage to delete the enum type inside down, but then up migration (which is supposed to create this status enum from scratch) fails, saying something like public.enum_Users_status enum type doesn't exist..

Solution

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

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