sequelize: sync() with new alter method fails with dataType enum

@GrigoryGraborenko:

This can be fixed by changing line 783 in query-generator from: let sql = 'CREATE TYPE ’ + enumName + ’ AS ’ + values + ‘;’; To: let sql = 'DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = ‘enum_’ + tableName + ‘_’ + attr + ‘’) THEN CREATE TYPE ’ + enumName + ’ AS ’ + values + ‘; END IF; END$$;’

This https://github.com/sequelize/sequelize/issues/7649#issuecomment-316942602 helps. I use this workaround while a new patch version containing the fix is in progress:

// pgEnum-fix.js

const PostgresQueryGenerator = require('sequelize/lib/dialects/postgres/query-generator')

PostgresQueryGenerator.prototype.pgEnum = function(tableName, attr, dataType, options) {
  const enumName = this.pgEnumName(tableName, attr, options);
  let values;

  if (dataType.values) {
    values = `ENUM(${dataType.values.map(value => this.escape(value)).join(', ')})`;
  } else {
    values = dataType.toString().match(/^ENUM\(.+\)/)[0];
  }

  let sql = `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_${ tableName }_${ attr }') THEN CREATE TYPE ${ enumName } AS ${ values }; END IF; END$$;`;
  if (!!options && options.force === true) {
    sql = this.pgEnumDrop(tableName, attr) + sql;
  }
  return sql;
}

Require this script before the first Sequelize require statement:

require('./pgEnum-fix')
const sequelize = require('sequelize')

Hi @papb

I still have a problem. versoin “5.19.8”

Executing (default): ALTER TABLE "providers" ALTER COLUMN "name" SET NOT NULL;ALTER TABLE "providers" ALTER COLUMN "name" DROP DEFAULT;CREATE TYPE "public"."enum_providers_name" AS ENUM('type1');ALTER TABLE "providers" ALTER COLUMN "name" TYPE "public"."enum_providers_name" USING ("name"::"public"."enum_providers_name");

 ERROR  type "enum_providers_name" already exists                                                                           

  at Query.formatError (node_modules/sequelize/lib/dialects/postgres/query.js:366:16)
  at node_modules/sequelize/lib/dialects/postgres/query.js:72:18
  From previous event:
  at Query.run (node_modules/sequelize/lib/dialects/postgres/query.js:64:23)
  at node_modules/sequelize/lib/sequelize.js:645:29

If anyone is still facing this issue, here’s the workaround I’m using.

Instead of doing this:

option: {
  type: Sequelize.DataTypes.ENUM('enum1', 'enum2', 'enum3'),
  allowNull: false  
  defaultValue: 'enum1',
},

I instead do this:

  type: {
    type: Sequelize.DataTypes.STRING,
    allowNull: false,
    defaultValue: 'enum1',
    validate: {
      customValidator: (value) => {
        const enums = ['enum1', 'enum2', 'enum3']
        if (!enums.includes(value)) {
          throw new Error('not a valid option')
        }
      }
    },
  },

There you have it, all the enum validation of DataTypes.ENUM, without the sync problem. 😄

Note: If anybody is motivated to test out this workaround for high-volume queries, please share back whether it has any measurable performance implications.

This is still an issue and we need a proper fix for this

🎉 This issue has been resolved in version 6.29.1 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

The issue still not fixed.

I have an open PR that has changes related to this. I can try to fix there, but just waiting on some feedback before I put more time into https://github.com/sequelize/sequelize/pull/11514

Why isn’t this being fixed?

If anyone is still facing this issue, here’s the workaround I’m using.

Instead of doing this:

option: {
  type: Sequelize.DataTypes.ENUM('enum1', 'enum2', 'enum3'),
  allowNull: false  
  defaultValue: 'enum1',
},

I instead do this:

  type: {
    type: Sequelize.DataTypes.STRING,
    allowNull: false,
    defaultValue: 'enum1',
    validate: {
      customValidator: (value) => {
        const enums = ['enum1', 'enum2', 'enum3']
        if (!enums.includes(value)) {
          throw new Error('not a valid option')
        }
      }
    },
  },

There you have it, all the enum validation of DataTypes.ENUM, without the sync problem. 😄

Note: If anybody is motivated to test out this workaround for high-volume queries, please share back whether it has any measurable performance implications.

Thanks, But then it wouldn’t define it as Enum on the postgres DB, will it have any performence / issues because of that?

And the problem isn’t fixed yet, on version 6.11

still have this issue

I still have this issue

Can someone help with this issue? It has been years now

I’ve this exact issue

This issue, together with #7606 effectively makes the alter option useless for anything beyond unrelated tables with no enums.

This one seems to stem from this bunch of statements:

ALTER TABLE "users" ALTER COLUMN "provider" SET NOT NULL;
ALTER TABLE "users" ALTER COLUMN "provider" SET DEFAULT 'local';
CREATE TYPE "public"."enum_users_provider" AS ENUM('local', 'google', 'facebook');
ALTER TABLE "users" ALTER COLUMN "provider" TYPE "public"."enum_users_provider" USING ("provider"::"public"."enum_users_provider");

Some kind of check should be added to check if the type exists before executing CREATE TYPE.

still have this issue

Hey, is this issue fixed yet? Because there seems to be no comments after March. @skn3

I’m also dealing with this issue.

Also, I had to change @aristov version:

let sql = `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_${ tableName }_${ attr }') THEN CREATE TYPE ${ enumName } AS ${ values }; END IF; END$$;`;

Was changed to this:

let sql = `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_${ tableName.tableName }_${ attr }') THEN CREATE TYPE ${ enumName } AS ${ values }; END IF; END$$;`;

Yes, but you may not like it: Don’t use sync with alter, use migrations instead. Sync with alter is no good in production anyway; might as well get good at migrations early on.

I told you youmay not like it 😃.

Eric

5 years and the same issue? Is there a plan to fix this? let us know please so we can workaround or wait.

I encountered this problem as well. From what I can tell, line 292 on lib/dialects/postgres/query-generator.js (in changeColumnQuery function) is most likely the offending line.

if (attributes[attributeName].startsWith('ENUM(')) {
  /* THIS LINE */ attrSql += this.pgEnum(tableName, attributeName, attributes[attributeName]);
  definition = definition.replace(/^ENUM\(.+\)/, this.pgEnumName(tableName, attributeName, { schema: false }));
  definition += ` USING (${this.quoteIdentifier(attributeName)}::${this.pgEnumName(tableName, attributeName)})`;
}

It’s a blind call to pgEnum (which generates a CREATE TYPE ... AS ENUM(...) statement), without checking if the type’s name has already been taken.

Unfortunately, I don’t know sequelize internals or postgres well enough to suggest changes or come up with a workaround.