How to be certain that an SQL QUERY was successful in Express JS-CodePudding

I want to UPDATE my database and then carry out certain functions based on whether the UPDATE was successful or not.

//Query DB to debit user
db.query(`UPDATE users SET wallet=newBalance WHERE userId=${iserId}`, (err, data)=>{
if(err){
return res.send(err)}

//Do something with data only if UPDATE was successful.
console.log('Update was successful');
res.send('wallet debuted Successfully')

})
}

I however realized that the success response was sent even when a wrong userId was provided in the query.

How do I ensure that success response is sent ONLY IF THE UPDATE actually happened?

I have tried using:

if(!results){res.send('Failed to debit wallet')}
else{res.send("Wallet debited Successfully"}

I still got the same problem

CodePudding user response：

If you are using mysql npm package in your NodeJs project then you can use the logic below to determine what happened after running the UPDATE query on a given userId that you have.

//Query DB to debit user
db.query(`UPDATE users SET wallet=newBalance WHERE userId=${iserId}`, (err, data) => {

  if (err) {
     return res.send(err)
  }

  console.log(data);
  
  /**
   * This is returned when update was successful
   * {
   *   "fieldCount": 0,
   *   "affectedRows": 1,
   *   "insertId": 0,
   *   "serverStatus": 2,
   *   "warningCount": 0,
   *   "message": "(Rows matched: 1  Changed: 1  Warnings: 0",
   *   "protocol41": true,
   *   "changedRows": 1
   * }
   */

   /**
    * This is returned when no matching rows are found
    * {
    *  "fieldCount": 0,
    *  "affectedRows": 0,
    *  "insertId": 0,
    *  "serverStatus": 2,
    *  "warningCount": 0,
    *  "message": "(Rows matched: 0  Changed: 0  Warnings: 0",
    *  "protocol41": true,
    *  "changedRows": 0
    * }
    */

   if (data.affectedRows === 0) {
       console.log("Nothing changed in this update");
       // Do something
   }

  // Do something with data only if UPDATE was successful.
  console.log('Update was successful');
  res.send('wallet debuted Successfully')

});

Another approach is to fetch the data first and check if there is anything to update.

So, you would run a fetch query like

select wallet, userId from users where userId = ?

Now, if the above query returns empty list, then the userId is invalid, if userId is valid then you can check for the wallet amount and can update accordingly.