Unhandled rejection SequelizeDatabaseError: relation "email_unique_idx" already exists

[joncorrin]

What you are doing?

I am trying to update my database with a new ratings column. This is the only edit ive made to the model file to cause this error. I am using sequelize.sync to alter the table.

"use strict";
 models.sequelize.sync({alter: true}).then(function() {
  server.listen(port, function() {
    console.log('Express server listening on port ' + server.address().port);
    });
  server.on('error', onError);
  server.on('listening', onListening);
 });

module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define("User", {
    email: {
      type: DataTypes.STRING,
      unique: true,
      isLowerCase: true,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: true
    },
    image: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    name: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    provider:  {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: 'rent'
    },
    uid: {
      type: DataTypes.STRING,
      allowNull: true,
      defaultValue: null
    },
    ratings: {
      type: DataTypes.ARRAY(DataTypes.INTEGER),
      allowNull: true,
      defaultValue: [5]
    }
  });
  User.associate = function(models) {
    User.hasMany(models.Item, {
      as: 'owner',
      onDelete: "CASCADE"
    });
    User.hasMany(models.Item, {
      as: 'renter',
      onDelete: "CASCADE",
      foreignKey: {
        allowNull: true
      }
    });
  };
  return User;
};

What do you expect to happen?

I'm expecting the function to alter the table for any adjustments I had made.

What is actually happening?

This error is being thrown referring to my email unique attribute which works fine without the new alteration added

Output, either JSON or SQL

Dialect:postgres Database version: 9.6 Sequelize version: 4.3.1

[sushantdhiman]

https://github.com/sequelize/sequelize/issues/7606

[joncorrin]

Still getting an error after updating to 4.29.2

    at Query.formatError (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/sequelize/lib/dialects/postgres/query.js:356:16)
    at query.catch.err (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/sequelize/lib/dialects/postgres/query.js:86:18)
    at tryCatcher (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (/Users/jonathancorrin/Desktop/workspace/zylo/zylo-api/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:789:20)
    at tryOnImmediate (timers.js:751:5)
    at processImmediate [as _immediateCallback] (timers.js:722:5)

[mkozjak]

Still getting an error after updating to 4.31.2.

SequelizeDatabaseError: relation "foo_bar_idx" already exists
    at Query.formatError (/opt/dev/uniqcast/ingest/node_modules/sequelize/lib/dialects/postgres/query.js:357:16)
    at query.catch.err (/opt/dev/uniqcast/ingest/node_modules/sequelize/lib/dialects/postgres/query.js:87:18)
    at tryCatcher (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/promise.js:689:18)
    at Async._drainQueue (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (/opt/dev/uniqcast/ingest/node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:789:20)
    at tryOnImmediate (timers.js:751:5)
    at processImmediate [as _immediateCallback] (timers.js:722:5)

[mkozjak]

In my case the index name was too long because it was created from a few columns. Maybe this should be handled automatically.

[jaydp17]

Taking inspiration from https://github.com/sequelize/sequelize/issues/7606#issuecomment-299333087

You can try the below code before running sync

const queryInterface = sequelize.getQueryInterface();
const tableNames = await queryInterface.showAllTables();
return Bluebird.map(tableNames, async tableName => {
  const constraints = await queryInterface.showConstraint(tableName);
  return Bluebird.map(constraints, constraint => {
    if (constraint.constraintType === 'FOREIGN KEY' || constraint.constraintType === 'UNIQUE') {
      return queryInterface.removeConstraint(tableName, constraint.constraintName);
    }
  });
});