sync() with new alter method fails with dataType enum

[skn3]

What you are doing?

'use strict';

const Sequelize = require('sequelize');

//first connect to the database
const database = new Sequelize('database', 'user', 'password', {
    dialect: 'postgres',
    host: '127.0.0.1',
    port: 5432,

    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
});

return database.authenticate()
.then(() => {
    //success connecting,
    console.log('database connected');
    return database;
})
.then(database => {
    const containerModel = database.define('TestContainer', {
        option: {
            type: Sequelize.DataTypes.ENUM('enum1', 'enum2', 'enum3'),
            defaultValue: 'enum1',
            allowNull: false
        },
    });

    return database.sync({
        alter: true,
        force: false,
    });
})
.catch(err => {
    //failed to connect
    console.error(err);
    process.exit(1);
});

What do you expect to happen?

Being able to run the example multiple times without fail

What is actually happening?

On second run, an error occurs:

SequelizeDatabaseError: type "enum_TestContainer_option" already exists

Dialect: postgres Database version: 9.6.2 Sequelize version: git master

[FREEZX]

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.

[skn3]

also related to #7606 and #7915

[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$$;'

I've never contributed to OSS before, so I don't feel that confident about creating a push request. I can figure it out and create one if you think this solution is acceptable. I worry that I wasn't able to use the enumName as generated a few lines up - postgres throws an error if I don't recreate the name without the namespace. Should I also then refactor the pgEnumName function to pull from a new, third function that returns a pure name without the namespace for commonality?

[stale[bot]]

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 🙂

[techsin]

I've this exact issue

[thebeachdev]

I'm getting this issue for my tests, not sure what's causing it.

[Vincz]

Getting the same issue. Enum don't work with sync alter.

[Vardiak]

This error is still occurring with latest version, any idea ?

[papigers]

Bump? I'm getting the same

[IgorSzymanski]

v. 4.38.1 The error is still there in alter: true mode. force: true does the trick in development, but no data is persisted for obvious reasons.

[ekatzenstein]

Yes, this is still an issue.

[ghost]

So how to maintain alters, is there any workaround? Kindly let us know.

[thesciz]

Bump. Still having a similar issue.

[ZTAP0011]

Did anyone find the solution for the issue? I am also facing the same issue.

[radcapitalist]

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

[papb]

Can someone confirm this is still happening? I was thinking that perhaps #11249 might have fixed this...

[rike422]

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

[syy1125]

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.

[bwoodlt]

Still experiencing this issue... any thoughts?

[sturdynut]

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

[bodolawale]

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

[aristov]

@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')

[MichaelLeeHobbs]

I used @aristov pgEnum-fix.js but had to add module.exports = ()=> PostgresQueryGenerator at the end to fix an issue as shown below.

/usr/src/app/node_modules/sequelize/lib/sequelize.js:489 this.importCache[importPath] = defineCall(this, DataTypes); ^ TypeError: defineCall is not a function at Sequelize.import (/usr/src/app/node_modules/sequelize/lib/sequelize.js:489:38)

[zephyrosjyd]

I used @aristov pgEnum-fix.js and @MichaelLeeHobbs comments.

/* eslint-disable func-names */
/* eslint-disable no-restricted-syntax */
/* eslint-disable guard-for-in */
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 {
    // eslint-disable-next-line prefer-destructuring
    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;
};

PostgresQueryGenerator.prototype.changeColumnQuery = function (tableName, attributes) {
  const query = (subQuery) => `ALTER TABLE ${this.quoteTable(tableName)} ALTER COLUMN ${subQuery};`;
  const sql = [];
  for (const attributeName in attributes) {
    let definition = this.dataTypeMapping(tableName, attributeName, attributes[attributeName]);
    let attrSql = '';

    if (definition.includes('NOT NULL')) {
      attrSql += query(`${this.quoteIdentifier(attributeName)} SET NOT NULL`);

      definition = definition.replace('NOT NULL', '').trim();
    } else if (!definition.includes('REFERENCES')) {
      attrSql += query(`${this.quoteIdentifier(attributeName)} DROP NOT NULL`);
    }

    if (definition.includes('DEFAULT')) {
      attrSql += query(`${this.quoteIdentifier(attributeName)} SET DEFAULT ${definition.match(/DEFAULT ([^;]+)/)[1]}`);

      definition = definition.replace(/(DEFAULT[^;]+)/, '').trim();
    } else if (!definition.includes('REFERENCES')) {
      attrSql += query(`${this.quoteIdentifier(attributeName)} DROP DEFAULT`);
    }

    if (attributes[attributeName].startsWith('ENUM(')) {
      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)})`;
    }

    if (definition.match(/UNIQUE;*$/)) {
      definition = definition.replace(/UNIQUE;*$/, '');
      attrSql += query(`ADD UNIQUE (${this.quoteIdentifier(attributeName)})`).replace('ALTER COLUMN', '');
    }

    if (definition.includes('REFERENCES')) {
      definition = definition.replace(/.+?(?=REFERENCES)/, '');
      attrSql += query(`ADD FOREIGN KEY (${this.quoteIdentifier(attributeName)}) ${definition}`).replace('ALTER COLUMN', '');
    } else {
      attrSql += query(`${this.quoteIdentifier(attributeName)} TYPE ${definition}`);
    }

    sql.push(attrSql);
  }

  return sql.join('');
};

module.exports = () => PostgresQueryGenerator;

This is my temporary solution for ENUM and USING errors.

[cskiwi]

Small change to @zephyrosjyd 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$$;`;

should be:

 if (_.isObject(tableName)) {
        normalizedTableName = `${tableName.schema}.${tableName.tableName}`;
  }

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

This is needed when using multiple schemas

[omsi96]

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

[achempak]

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$$;`;

[changesbyjames]

Just confirming that this is still an issue. I'm opting to not use enums for now and enforce restrictions outside of the database, which isn't ideal.

[Dammmien]

That's still an issue on my side too

[dalisoft]

I have same issue, not solution found yet