Migration on ENUM with postgres dialect: type already exists

[ghost]

Deleting previously existing ENUM column and re-creating it but with a different ENUM results in:

Executing (default): ALTER TABLE "Trackers" DROP COLUMN "type";
Executing (default): CREATE TYPE "enum_Trackers_type" AS ENUM('image', 'javascript', 'snippet'); ALTER TABLE "Trackers" ADD COLUMN "type" "enum_Trackers_type";
Possibly unhandled SequelizeDatabaseError: error: type "enum_Trackers_type" already exists

[janmeier]

That's a postgres issue. Dropping an enum column does not drop the enum itself (since an enum might be used in several tables as I understand it)

[ghost]

@janmeier: Any idea how to correctly edit enum values through migration then?

[ghost]

Using raw query meanwhile, unless someone comes up with a better suggestion.

[laser]

@kytwb

Any idea how to correctly edit enum values through migration then?

I was able to modify an ENUM by:

1. Modifying the array assigned to the values property of the configuration object passed as the second argument to sequelize.define to include a new value
2. Create a migration in which the up simply calls ModelNameHere.sync()

That generated the SQL statements required to modify the ENUM-type to match what I'd expressed in JavaScript code.

Erin

[ghost]

@laser Tried what you said, it does work when you add only one new value at a time.

Executing (default): ALTER TYPE "enum_warehouses_type" ADD VALUE 'postmaster' BEFORE 'amazon'
Executing (default): ALTER TYPE "enum_warehouses_type" ADD VALUE 'amazon' AFTER 'postmaster'
Possibly unhandled SequelizeDatabaseError: "amazon" is not an existing enum label

With amazon and postmaster being the two new values here.

[Victor08]

I am facing the same issue. I was able to bypass it with using raw query

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.sequelize.query("CREATE TYPE \"enum_users_sellerAccountType\" AS ENUM(\'sa\', \'sm\'); ALTER TABLE \"users\" ADD COLUMN \"sellerAccountType\" \"enum_users_sellerAccountType\";");
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.sequelize.query("DROP TYPE \"enum_users_sellerAccountType\"; ALTER TABLE \"users\" DROP COLUMN \"sellerAccountType\";");
  }
};

Is it a bug in sequelize, that causes enum migration to fail?

[delveintechnolabs]

Facing same issue. To add to the problem, sequelize only allows changing default value, allowance of null or the data type. If you want to change some other attribute of a column, you will have to find a hack for it.

[thealmightygrant]

Heads up that if you decide to do raw queries like @Victor08 points out above, the down migration should drop the column first so that nothing depends on the data type, like this:

 down: function (queryInterface, Sequelize) {
    return queryInterface.sequelize.query("ALTER TABLE \"users\" DROP COLUMN \"userType\"; DROP TYPE \"enum_users_userType\";");
  }

[felixfbecker]

We have QueryInterface.dropAllEnums(), I think we should add a simple dropEnum() method.

[kgalli]

@felixfbecker is there an update on the dropEnum() method? This would really be very handy. What is the context of QueryInterface.dropAllEnums()? Is it the whole database or maybe a table?

[felixfbecker]

No, there is no update. Can't answer your second question.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[markelog]

This is still an issue

[mariano-aguero]

This is how I solved it, not the best way but it allows adding a new field with an existing enum without it throwing an error.

queryInterface.addColumn(
            'Users',
            'language', {
                type: Sequelize.ENUM('nl', 'en'),
                allowNull: false,
                defaultValue: 'nl'
            }).then(function (response) {
                console.log(response);
                done();
            }).catch(function (err) {
                return queryInterface.sequelize.query(`ALTER TABLE "Users" ADD COLUMN "language" "public"."enum_Users_language" NOT NULL DEFAULT 'nl';`)
                    .then(function (response) {
                        console.log(response);
                        done();
                    }).catch(function (err) {
                        done();
                    });
            });

[Edudjr]

finally got it working:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query("ALTER TYPE enum_type_name ADD VALUE 'new_value'");
  },

  down: (queryInterface, Sequelize) => {
    var query = 'DELETE FROM pg_enum ' +
      'WHERE enumlabel = \'new_value\' ' +
      'AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = \'enum_type_name\')';
    return queryInterface.sequelize.query(query);
  }
};

change enum_type_name and new_value to fit your needs.

[JustBlackBird]

Any plans for adding QueryInterface.dropEnum() method?

[ajmueller]

@Edudjr that almost worked for me, but I had to make an ever so slight change to your up function:

up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(`ALTER TYPE "enum_type_name" ADD VALUE 'new_value'`);
}

As you can see, I had to wrap my enum name in double quotes; for whatever reason, that's how Sequelize created my enum, so without the double quotes I got a "type does not exist" error. I'm not sure if this is a standard for Sequelize, but I figured I'd post my findings here in case anyone else ran into a similar issue. Also note that due to the use of both single and double quotes, I wrapped the query in tick marks since I'm using a new enough version of Node that supports template strings, thereby avoiding the need to escape any strings.

And for the down function, I made similar changes for readability:

down: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(`
        DELETE 
        FROM
            pg_enum
        WHERE
            enumlabel = 'new_value' AND
            enumtypid = (
                SELECT
                    oid
                FROM
                    pg_type
                WHERE
                    typname = 'enum_type_name'
            )
    `);
}

Strangely enough, enum_type_name here in the pg_type table did not have double quotes in its actual value, unlike the type that was created.

[abelosorio]

See my comment #7151 (comment).

I've written a function to entirely replace the ENUM. It works inside a transaction and supports default value.

Hope you find it helpul.

[theSuiGenerisAakash]

I asked this on SO here and this is something sweet and simple that seems to be doing it

down: (queryInterface, Sequelize) => queryInterface.removeColumn('TableName', 'ColumnName') .then(() => queryInterface.sequelize.query('DROP TYPE "enum_TableName_ColumnName";')); };

[tomprogers]

The best way to modify enum values is to queryInterface.changeColumn:

module.exports = {
  up  : function (queryInterface, Sequelize) {
    return queryInterface
      .changeColumn(tableName, 'status', {
        type: Sequelize.ENUM('a', 'b', 'c', 'd'),
        allowNull: false
      });
  },
  down: function (queryInterface, Sequelize) {
    return queryInterface
      .changeColumn(tableName, 'status', {
        type: Sequelize.ENUM('a', 'b', 'c'),
        allowNull: false
      });
  }
};

[naartjie]

The best way to modify enum values is to queryInterface.changeColumn:

@tomprogers does it work with Postgres?

[jraut]

@naartjie @tomprogers No, it does not seem to work on Postgres.
ERROR: type "tableName_status" already exists

[abelosorio]

@naartjie @tomprogers @jraut is not possible to "change" an enum in Postgres. You have to destroy it and create it again with the new values.

Checkout this issue #7151 and this comment #7151 (comment).

I created this npm package to change enums in Postgres: https://www.npmjs.com/package/sequelize-replace-enum-postgres. Hope it helps.

Good luck!

[robcresswell]

@abelosorio It seems like you've been able to alter ENUMs since at least Postgres 9.1https://www.postgresql.org/docs/9.1/static/sql-altertype.html so I'm not sure why you are saying it isn't possible. Can we get this issue reopened @sushantdhiman ?

[rostismal]

is not fixed

[DaddyWarbucks]

This may be a bit off topic to this particular thread, but I wanted to post my solution to a similar problem incase anyone ends up here like I did.

I was trying to set an ENUM along with allowNull: true, then do a bulk update, and then changeColumn to allowNull: false

await queryInterface.addColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: true // set to true to allow bulk update
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

await queryInterface.bulkUpdate(
        'payments',
        {
          entity_id: 1,
          entity_type: SHIPPER
        },
        {},
        { transaction }
      );

await queryInterface.changeColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: false,
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

^^ This code threw the error enum_payments_entity_type already exists. Even when trying to manually drop it, alter the column, etc, I could never get it to work.
My solution was to set the column as a STRING first, then come back and set it as an ENUM.

await queryInterface.addColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.STRING, // NOTE THIS IS A STRING
          allowNull: true // set to true to allow bulk update
        },
        { transaction }
      );

await queryInterface.bulkUpdate(
        'payments',
        {
          entity_id: 1,
          entity_type: SHIPPER
        },
        {},
        { transaction }
      );

await queryInterface.changeColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: false,
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

I am not very well versed in Sequelize (or in RDMS in general) but this bug kicked my butt. Hopefully if this is helpful to someone. And if this is bad practice, feel free to downvote it and tell me why.

[Dakkers]

still not fixed 😭

[Otavioensa]

I'm still running on the same problem

[Otavioensa]

This issue should definitely be reopened

[papb]

Hello, this thread is very long and was closed twice already... Can someone please provide an up-to-date MVCE for this? Something that I can just copy+paste and run and see the problem. If I can easily reproduce it, I will reopen this issue. Thanks!!

[MaxMls]

i have this problem with code:

module.exports = (sequelize, DataTypes) => {
	const tag = sequelize.define('tag', {
		name: {allowNull: false, type: DataTypes.TEXT},
		type: {allowNull: false, type: DataTypes.ENUM('lang', 'category', 'author')}
	}, {});
	tag.associate = function (models) {
		tag.belongsToMany(models.chapter, {through: 'titlesTags'});

		tag.belongsToMany(models.user, {through: 'tagBlackList'});

	};
	return tag;
};

error:
(node:24616) UnhandledPromiseRejectionWarning: SequelizeDatabaseError: тип "enum_tags_type" уже существует

[papb]

I think issues #2577 and #7649 cover everything mentioned in this issue already.

[RasenGUY]

@kytwb

Any idea how to correctly edit enum values through migration then?

I was able to modify an ENUM by:

1. Modifying the array assigned to the `values` property of the configuration object passed as the second argument to `sequelize.define` to include a new value

2. Create a migration in which the `up` simply calls `ModelNameHere.sync()`

That generated the SQL statements required to modify the ENUM-type to match what I'd expressed in JavaScript code.

Erin

this works thanks for sharing.