use mysql to update specific field(s) using expressjs-CodePudding

I am developing a middleware using express.js with mysql(new to mysql) and in my situation I have built this patch method to update the table. but the issue is I dont want to pass the entire field set to update specific fields out of many. so whats the preferred way to do this so that whatever fields I will send in request body those fields should be updated only.

const updateCompany = (req, res, next) => {
  const cid = req.params.cid;

  const {
    company_id,
    company_name,
    company_address_line1,
    company_address_line2,
    company_email,
    company_phone,
    longitude,
    latitude
  } = req.body;

  var myquery = `UPDATE Company_Master SET company_name="${company_name}",company_address_line1="${company_address_line1}",company_address_line2="${company_address_line2}",company_email="${company_email}",company_phone="${company_phone}",longitude=${longitude},latitude=${latitude} WHERE company_id = "${cid}"`

    conn.query(myquery, (err, result) => {
      if (err) {
        console.log("err"   err);
      } else {
        res.status(201).json(req.body);
      }
    })

}

CodePudding user response：

You can do as follows

const updateCompany = (req, res, next) => {
  const cid = req.params.cid;
  let 
    allowedcolumns = ["company_name", "company_address_line1",  ... ], //all columns that can be updated
    stmts = [],
    values = [];
    
  for (let c of allowedcolumns) {
      if (c in req.body) {  //check if there is a value for that column in the request body
          stmts.push(`${c} = ?`),
          values.push(req.body[c]);
      }
  }
  
  if (stmts.length == 0) {
      return res.sendStatus(204); //nothing to do
  }
  
  values.push(cid);
  conn.query(`UPDATE Company_Master SET ${stmts.join(", ")} WHERE company_id = ?`, values, (err, result) => {
    if (err) {
      console.log("err"   err);
      res.sendStatus(400);
    } else {
      res.status(200).json(req.body);
    }
  })
}

allowedcolumns will contain all columns that you are allowed to update via this request. For each of them check, whether there is a value in the request body or not. If yes, add it to the update statements, if not, ignore it (this assumes, the properties in the req.body and the columns in the table have the same name). Furthermore, to create a parameterized query, add the respective value to a values array, that you then can pass to the query.

If you don't have any values, there is nothing to do, so you can immediately return.

Else execute the query (don't forget to also add the cid to the values array). And return the respective status, based on whether there was an error or not.

BTW: 201 is status CREATED. You shouldn use that, if you are updating an already existing entity ...