CODEWITHSUNDEEP
mysqlnode.js
Dharmesh
Dharmesh

Reputation: 6003

how to avoid insert duplicate record in node mysql?

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

Upvotes: 2

Views: 7432

Answers (3)

Bhuvanachandu
Bhuvanachandu

Reputation: 175

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

Upvotes: 7

num8er
num8er

Reputation: 19372

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);
      });
  });
};

Upvotes: -1

Cynical
Cynical

Reputation: 9588

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

Upvotes: 1

Related Questions