正确删除并创建带有续集的 ENUM? [英] Drop and create ENUM with sequelize correctly?

问题描述

如何在迁移中为 Postgres 正确删除然后重新创建带有续集的 ENUM 类型?例如，此迁移不会删除 enum_Users_status 枚举...因此，在创建/更改 status 值后，任何尝试都将失败.

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')
    },
}

最终我确实设法删除了 down 中的枚举类型，但是随后 up 迁移(应该从Scratch) 失败，说诸如 public.enum_Users_status 枚举类型不存在..

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..

推荐答案

UPDATE:到目前为止，我已经在三个项目中使用了它，所以我决定创建一个 npm 模块:https://www.npmjs.com/package/replace-enum-postgresql.

我制作了一个实用程序来执行此操作，希望对您有所帮助.

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

这是我的示例迁移:

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

这篇关于正确删除并创建带有续集的 ENUM?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！