How to format a mysql duplicate entry error?

Question

I'm using express.js with mysql2.

When a duplicate entry error happens on insertion I get the error in the global error handler and that works as expected.

When I log it to the console, the error looks like this:

   Error: Duplicate entry 'todd' for key 'users.username_UNIQUE'
    at PromisePool.execute (C:\Users\YT\Desktop\PORTFOLIO\MERN\social-talk\server\node_modules\mysql2\promise.js:358:22)
    at C:\Users\YT\Desktop\PORTFOLIO\MERN\social-talk\server\models\User.js:45:48 {
  code: 'ER_DUP_ENTRY',
  errno: 1062,
  sqlState: '23000',
  sqlMessage: "Duplicate entry 'todd' for key 'users.username_UNIQUE'"
}

My issue is that I'm not sure how to format this error, because it doesn't give the actual field or key that is duplicate (username), but just this sqlMessage field, which is not very good looking to display to the users in this format.

PS: I'm aware of sequelize, but I'm really interested in how I can do this without an ORM

aleksadjukic aleksadjukic · Accepted Answer · 2021-06-06T13:53:19

I actually came up with a decent solution.

Because the sqlMessage will always be in the same format, I first split the sqlMessage into an array, and then extracted the value entered, and then I also extracted the field, but since the field is in a format "database.field_UNIQUE", I had to extract the field that is in between the "." and "_" characters.

The code looks like this:

if (err.errno === 1062) {
    const errWords = err.sqlMessage.split(" ");
    const entry = errWords[2];
    const fieldDB = errWords[5];
    const formattedField = fieldDB.substring(fieldDB.lastIndexOf(".") + 1, fieldDB.lastIndexOf("_"));
    console.log( `Duplicate entry - ${formattedField}: ${entry}`)
    return res.status(400).json({ error: `Duplicate entry - ${formattedField}: ${entry}` });
}

The result will be:

Duplicate entry - username: 'todd'

How to format a mysql duplicate entry error?

1 Answers