sequelize: sync({alter: true}) throws "Too many keys specified"

old sequelize use email:{type: Sequelize.STRING, unique: true} Now, email:{type: Sequelize.STRING, unique:‘email’} this will fix the issue.

Here’s workaround that seems to fix it. If you use the approach documented here, it maintains the same index rather than adding a new one each time sequelize syncs. So instead of: sequelize.define('user', {email: {type: Sequelize.STRING, unique:true}}) use sequelize.define('user', {email: Sequelize.STRING}, {indexes:[{unique:true, fields: ['email']}]})

The “solution” I went with is:

1. Make a backup of your complete database. NOTE: This is important and not just “please always make a backup, bla, bla”, because we’ll be clearing the database
2. Run the server once with this configuration NOTE: This might or will DELETE your data

await sequelize.sync({ alter: true, force: true });

1. Change back to

await sequelize.sync({ alter: false, force: false });
// – or in development – 
await sequelize.sync({ alter: true, force: false });

1. Reimport your backed-up database.

Now you can alter your database another 64 times until you have to do it again.

Hope I wrote it down correctly (can’t reproduce right now if I have all the steps…), please be sure to not test the first time with a production database! This is merely a dirty workaround of course, because I have no idea how this could be fixed…

The approach suggested by @cribcutkiran works for me, working with a MySQL database. Additionally, I used MySQL Workbench to delete the additional indexes created. Running sync repeatedly after that only creates a single instance of each index.

for multiple columns with unique keys,

sequelize.define('user', {
  email: Sequelize.STRING,
  username: Sequelize.STRING
}, {
  indexes: [
    {unique:true, fields:['email']},
    {unique:true, fields:['username']}
  ]
})

You have to go in mysql and drop duplicate indexes in tables / models with some unique keys after errors “Too many keys specified” occurs without drop entire database.

To evitate that error occurs again it’s opportune to define unique field like this with the name of index: email: { type: Sequelize.STRING, allowNull: false, unique: 'email'} so sequelize don’t create a new index name at every alter table.

sequelize-typescript solution

Error code

  @AllowNull(false)
  @Column({
    type: DataType.STRING(36),
    unique: true, // delete this line
  })
  cdkey: string;

Correct code

  @Unique({ name: 'cdkey_unique', msg: 'cdkey_should_be_unique' }) // add this line
  @AllowNull(false)
  @Column({
    type: DataType.STRING(36),
  })
  cdkey: string;

old sequelize use email:{type: Sequelize.STRING, unique: true} Now, email:{type: Sequelize.STRING, unique:‘email’} this will fix the issue.

This is not a solution for the issue.

If we can use the “true” value to make Sequelize handle the keys automatically, why would we need then to type the key name instead?

Also I’m still having this issue too, it’s almost making 3 years. My workaround is to reset the database every time I update it; it’s a very expensive task.

I think u should write migration to fix database, stop using auto sync database 😄

old sequelize use email:{type: Sequelize.STRING, unique: true} Now, email:{type: Sequelize.STRING, unique:‘email’} this will fix the issue.

This is not a solution for the issue.

If we can use the “true” value to make Sequelize handle the keys automatically, why would we need then to type the key name instead?

Also I’m still having this issue too, it’s almost making 3 years. My workaround is to reset the database every time I update it; it’s a very expensive task.

same too.

You have to go in mysql and drop duplicate indexes in tables / models with some unique keys after errors “Too many keys specified” occurs without drop entire database.

To evitate that error occurs again it’s opportune to define unique field like this with the name of index: email: { type: Sequelize.STRING, allowNull: false, unique: 'email'} so sequelize don’t create a new index name at every alter table.

Thank You for this information. issue resolved.

Still an issue in ^6.19.0

I believe this is fixed in Sequelize 7.0.0-alpha.14, we have a bunch of tests to ensure indexes are not re-created by alter if they already exist: https://github.com/sequelize/sequelize/blob/main/test/integration/model/sync.test.js#L180

PR: https://github.com/sequelize/sequelize/pull/14619

You have to go in mysql and drop duplicate indexes in tables / models with some unique keys after errors “Too many keys specified” occurs without drop entire database.

To evitate that error occurs again it’s opportune to define unique field like this with the name of index: email: { type: Sequelize.STRING, allowNull: false, unique: 'email'} so sequelize don’t create a new index name at every alter table.

i thank you for your answer !

old sequelize use email:{type: Sequelize.STRING, unique: true} Now, email:{type: Sequelize.STRING, unique:‘email’} this will fix the issue.

This is not a solution for the issue. If we can use the “true” value to make Sequelize handle the keys automatically, why would we need then to type the key name instead? Also I’m still having this issue too, it’s almost making 3 years. My workaround is to reset the database every time I update it; it’s a very expensive task.

I think u should write migration to fix database, stop using auto sync database 😄

This feature is useful when in development stage. It’s very expensive to migrate the entire database every time something is changing when you’re still developing / designing the database 😕

Still seeing this at sequelize": "^5.19.0

Consider 👍🏼 the initial post as we often sort by the thumbs up emoji to determine what needs to be prioritized.