how to avoid insert duplicate record in node mysql?

Question

Here I insert user details username,emailid and contactno and userid is primary key of user table.

My query is when i insert duplicate username or emailid and if duplicate record is heard in the table, it does not allow the record to be inserted how it is possible ?

app.js

app.post('/saveNewUserDetails',function(req,res){
  var form = new multiparty.Form();
  form.parse(req, function(err, fields, files){ 
    connection.query("INSERT INTO user (username,emailid,contactno) VALUES (?,?,?)", [fields.username,fields.emailid,fields.contactno] , function (error, results, fields) {
     if (error) throw error; 
      res.send({
        'status':'1',
        'success': 'true',
        'payload': results,
        'message':'New user Is saved'
      });
    });
  });
});

User

userid(primary key)    username    emailid            contactno
  1                     user-1     [email protected]    1234567890 
  2                     user-2     [email protected]    1234444444

Bhuvanachandu · Accepted Answer · 2018-11-13 12:41:51Z

7

for every entry, we can't check the existence of that record in the database because it takes some time.

so we will set username and emailid as UNIQUE fields in your table. so whenever we tried to insert a duplicate element into the table we will get a Duplicate key error.

Now, we will take that error as an advantage to speed up the process by simply handling it.

// pseudo code
// we are simply ignoring it
if( error & error != "ER_DUP_ENTRY" ){
  // do normal error handling
}

this is the fastest solution for avoiding duplicate insertions

answered Nov 13, 2018 at 12:41

Bhuvanachandu

1751 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cynical · Accepted Answer · 2018-11-13 09:15:13Z

1

You should set username and emailid as UNIQUE fields in your CREATE statement.

answered Nov 13, 2018 at 9:15

Cynical

9,6281 gold badge19 silver badges34 bronze badges

4 Comments

Dharmesh Over a year ago

i make username and emailid as UNIQUE but when i insert duplicate record that time i got this kind of error(Error: ER_DUP_ENTRY: Duplicate entry 'user-1' for key 'username' ) how to avoid this error and got message this record is already available

Cynical Over a year ago

That's exactly what it is saying: a duplicate entry error for a UNIQUE field means that the record is already present in your table.

num8er Over a year ago

@Cynical as I understand Amisha wants to check data existence to avoid error thrown. Since question is: how to avoid insert duplicate record in node mysql?

Mark Baijens Over a year ago

Do a select on the username and one on email to check if it exists?

num8er · Accepted Answer · 2018-11-13 09:49:00Z

How it is possible ? - Check for fields that You send to API with records in user table. Make sure they're not exist.

For more detailed information to client-side app I recommend to check database table for record existence before doing insert.

So here is solution for Your issue:

app.post('/saveNewUserDetails',
  async (req,res) => {
    try {
      const {fields} = await parseRequestBody(req);
      const {username, emailid, contactno} = fields;
      console.log('/saveNewUserDetails', 'fields:', fields); // for debug purposes

      const exists = await userExists(username, emailid);
      if (exists) throw new Error('User data already exists');

      const result = await createUser(username, emailid, contactno);
      res.status(201).send({
        'status': '1',
        'success': 'true',
        'payload': result,
        'message': 'New user Is saved'
      });
    }
    catch (error) {
      res.status(400).send({
        'success': false,
        'message': error.message
      }); 
    }
  }); 

const parseRequestBody = request => {
  return new Promise((resolve, reject) => {
    const form = new multiparty.Form();
    form.parse(request, (error, fields, files) => {
      if (error) return reject(error);
      resolve({fields, files});
    });
  });
};

const userExists = (username, emailid) => {
  return new Promise((resolve) => {
    connection.query(
      'SELECT userid FROM user WHERE username = ? OR emailid = ? LIMIT 1',
      [username, emailid],
      (error, result) => {
        if (error) return reject(error);

        if (result && result[0]) {
          console.log('User exists:', result); // for debug purposes
          return resolve(true);
        }

        resolve(false);
      });
  });
};

const createUser = (username, emailid, contactno) => {
  return new Promise((resolve) => {
    connection.query(
      'INSERT INTO user (username, emailid, contactno) VALUES (?, ?, ?)',
      [username, emailid, contactno],
      (error, result) => {
        if (error) return reject(error);

        resolve(result);
      });
  });
};

Collectives™ on Stack Overflow

how to avoid insert duplicate record in node mysql?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related