CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Filipe Ferminiano
Filipe Ferminiano

Reputation: 8801

Unhandled rejection SequelizeUniqueConstraintError: Validation error

I'm getting this error:

Unhandled rejection SequelizeUniqueConstraintError: Validation error

How can I fix this?

This is my models/user.js

"use strict";

module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define("User", {
    id:  { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true},
    name: DataTypes.STRING,
    environment_hash: DataTypes.STRING
  }, {
    tableName: 'users',
    underscored: false,
    timestamps: false
  }

  );

  return User;
};

And this is my routes.js:

app.post('/signup', function(request, response){

        console.log(request.body.email);
        console.log(request.body.password);

        User
        .find({ where: { name: request.body.email } })
            .then(function(err, user) {
                if (!user) {
                        console.log('No user has been found.');

                        User.create({ name: request.body.email }).then(function(user) {
                            // you can now access the newly created task via the variable task
                            console.log('success');
                        });

                } 
            });



    });

Upvotes: 21

Views: 68085

Answers (7)

Hana Hasanah
Hana Hasanah

Reputation: 244

In my case using PostgreSQL, received this error when created using sequelize and didn't find a column defined as a unique constraint in the model used to create.

After tracing, the problem occurred with the id column which should be autoIncrement but when I checked the DB it turned out that the table I was using had an incomplete id, this happened because someone accessed the DB by creating manually but PostgreSQL couldn't handle this automatically afterwards.

To overcome this, you can reset auto increment counter or follow the steps below:

  1. Specify the name of the sequence you want to reset. You can see the name of the sequence by running the query:

SELECT pg_get_serial_sequence('table_name', 'column_name');

  1. After you get the sequence name, use the ALTER SEQUENCE command to reset the auto increment counter. Here is the format of the command:

ALTER SEQUENCE sequence_name RESTART WITH new_value;

  1. Execute the ALTER SEQUENCE command in a PostgreSQL client, such as through psql or through a database management tool that supports SQL command execution.

Upvotes: 1

Ilya Kushlianski
Ilya Kushlianski

Reputation: 968

In case you stumble upon this Validation error from Sequelize: check that the query populating (creating) the table is performed once.

My Nest.js app was not properly set up and executed the same command twice thus violating the unique constraint.

Upvotes: 0

jlh
jlh

Reputation: 4717

When using SQLite as a database, Sequelize (currently 5.21.5) throws SequelizeUniqueConstraintError on every constraint error, even if it has nothing to do with unique indexes. One examle would be inserting NULL into a non-nullable column. So be sure to also check for other types of errors when debugging this exception.

Upvotes: 5

doublesharp
doublesharp

Reputation: 27667

The call to User.create() is returning a Promise.reject(), but there is no .catch(err) to handle it. Without catching the error and knowing the input values it's hard to say what the validation error is - the request.body.email could be too long, etc.

Catch the Promise reject to see the error/validation details

User.create({ name: request.body.email })
.then(function(user) {
    // you can now access the newly created user
    console.log('success', user.toJSON());
})
.catch(function(err) {
    // print the error details
    console.log(err, request.body.email);
});

Update, since it's 2019 and you can use async/await

try {
  const user = await User.create({ name: request.body.email });
  // you can now access the newly created user
  console.log('success', user.toJSON());
} catch (err) {
  // print the error details
  console.log(err, request.body.email);
}

Upvotes: 34

ow3n
ow3n

Reputation: 6627

Building on @Ricardo Machado's answer, if you add unique:true to a model and already have values in an existing table that wouldn't be allowed under this new constraint you will get this error. To fix you can manually delete the rows or delete the table and so Sequelize builds it again.

Upvotes: 4

Ricardo Machado
Ricardo Machado

Reputation: 782

Check in your database if you have an Unique Constraint created, my guess is that you put some value to unique: true and changed it, but sequelize wasn't able to delete it's constraint from your database.

Upvotes: 12

aherocalledFrog
aherocalledFrog

Reputation: 861

I had this issue with my QA database. Sometimes a new record would save to the database, and sometimes it would fail. When performing the same process on my dev workstation it would succeed every time.

When I caught the error (per @doublesharp's good advice) and printed the full results to the console, it confirmed that a unique constraint as being violated - specifically, the primary key id column, which was set to default to an autoincremented value.

I had seeded my database with records, and even though the ids of those records were also set to autoincrement, the ids of the 200-some records were scattered between 1 and 2000, but the database's autoincrement sequence was set to start at 1. Usually the next id in sequence was unused, but occasionally it was already occupied, and the database would return this error.

I used the answer here to reset the sequence to start after the last of my seeded records, and now it works every time.

Upvotes: 8

Related Questions