Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

SequelizeUniqueConstraintError: Validation error on insert

ErrorSequelizeUniqueConstraintError: Validation error

Short answer

Sequelize sends the insert, MySQL rejects it because a unique column or unique index already holds that value, and Sequelize reports it as SequelizeUniqueConstraintError. Print the error object to see which column collided. Then either stop inserting the duplicate, use findOrCreate, or catch the error and answer with a clear message.

  • Applies to Node.js 22, Sequelize 6, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Catch the error and print the whole object. The short message does not name the column; the detail inside the object does.

    try {
      await User.create({ username: 'maaz' });
    } catch (err) {
      if (err.name === 'SequelizeUniqueConstraintError') {
        console.dir(err, { depth: 3 });
      } else {
        throw err;
      }
    }
  2. 2Match what you see, then jump to its fix

    1. 1The printed detail names a column whose value already exists in another row.The value already exists in a unique columnFix
    2. 2Rows with different usernames collide, or the collision happens only for certain combinations.Two columns share one unique key without you meaning itFix
    3. 3The error is rare and appears under load, even though your code checks for the row first.A find-then-create check races with another requestFix

What does the error look like?

  • SequelizeUniqueConstraintError: Validation error
  • Unhandled rejection SequelizeUniqueConstraintError: Validation error

Cause 1 of 3

The value already exists in a unique column

1Check

A column marked unique: true becomes a database constraint. Sequelize states that inserting a second row with the same value throws this error, and that a failed constraint is reported after the SQL has run.

SELECT id FROM Users WHERE username = 'maaz';

2Fix

Decide what a duplicate should mean. For a sign-up form, catch the error and return a 409 with a clear message. For get-or-create logic, use findOrCreate.

try {
  const user = await User.create({ username, email });
  res.status(201).json(user);
} catch (err) {
  if (err.name === 'SequelizeUniqueConstraintError') {
    return res.status(409).json({ error: 'That username is already taken.' });
  }
  throw err;
}

3Verify

Send the same request twice. The first returns 201, the second 409.

curl -i -X POST http://127.0.0.1:3000/users -H 'content-type: application/json' -d '{"username":"maaz","email":"a@example.com"}'
curl -i -X POST http://127.0.0.1:3000/users -H 'content-type: application/json' -d '{"username":"maaz","email":"a@example.com"}'

Confirmed by sequelize.org

Did this fix it?

Cause 2 of 3

Two columns share one unique key without you meaning it

1Check

If you give the same string to unique on several columns, they form one composite unique key. Only the exact combination must be unique, and an index declared in the model options works the same way. A copy-pasted unique name can merge columns by accident.

grep -rn "unique" models/

2Fix

Give each column its own unique: true when each must be unique alone, and use one shared name only when you want the combination to be unique.

username: { type: DataTypes.STRING, unique: true },
email:    { type: DataTypes.STRING, unique: true },
// composite: unique only as a pair
// tenantId: { type: DataTypes.INTEGER, unique: 'tenant_slug' },
// slug:     { type: DataTypes.STRING,  unique: 'tenant_slug' },

3Verify

Insert two rows that differ in one of the columns and confirm both succeed.

node scripts/seed-test.js

Confirmed by sequelize.org

Did this fix it?

Cause 3 of 3

A find-then-create check races with another request

1Check

Two requests can both find nothing and then both insert. findOrCreate guards against this: when you pass no transaction it creates one internally, and it handles the unique violation itself.

grep -rn "findOne\|findOrCreate" src/

2Fix

Replace the separate find and create with findOrCreate, and keep the unique constraint as the final guard.

const [user, created] = await User.findOrCreate({
  where: { email },
  defaults: { username },
});
// created is true when a new row was inserted

3Verify

Fire the same request ten times in parallel and confirm one row exists.

seq 10 | xargs -P10 -I{} curl -s -o /dev/null -w '%{http_code}\n' -X POST http://127.0.0.1:3000/users -H 'content-type: application/json' -d '{"username":"maaz","email":"a@example.com"}'

Confirmed by sequelize.org

Did this fix it?

How do I stop it coming back?

  • Keep the database constraint even when you also check in code: it is the only check that cannot race.
  • Catch SequelizeUniqueConstraintError at the route level and turn it into a 409.
  • Name composite unique keys on purpose, with a clear string.
  • Always handle promise rejections so the error is not unhandled.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.