How to update only one column and avoid NULL values for others in node-postgres?-CodePudding

Right now, in my API if I only want to update one value like email, other values become NULL and I have to rewrite all the other stuff as well just to update one thing.

The JSON object is this:

{
    "username": "batman"
}

This will be the result:

{
    "message": "User Updated Successfully!",
    "user": {
        "user_id": 44,
        "name": null,
        "username": "batman",
        "email": null,
        "phone": null,
        "website": null
    }
}

So, this obligates me to write the whole JSON object so that other values don't update to NULL:

{
    "name": "batman",
    "username": "batman",
    "email": "[email protected]",
    "phone": "0123456789",
    "website": "batman.com"
}

This is the callback function for the PUT request:

const updateUser = async (req, res) => {
  const id = parseInt(req.params.id);
  const { name, username, email, phone, website } = req.body;
  try {
    const update = await db.query("UPDATE users SET name = $1, username = $2, email = $3, phone = $4, website = $5 WHERE user_id = $6 RETURNING *", [
      name,
      username,
      email,
      phone,
      website,
      id,
    ]);
    res
      .status(200)
      .json({ message: "User Updated Successfully!", user: update.rows[0] });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Something Went Wrong!" });
  }
};

Is there any way to update only one value without the others updating to NULL?

CodePudding user response：

node-postgres doesn't have a query builder (unlike, for example, knex). One solution could be programmatically constructing your query, and adding some validation and hard coding to make sure there isn't any sql injection:

const UPDATABLE_COLUMNS = {
  name: 'name',
  ...
}
const paramObjects = Object.entries(req.body)
const updateQueryArray = paramObjects.map(([key], index) =>  `${UPDATABLE_COLUMNS[key]} = $${index}`)

const queryString = `UPDATE users SET ${updateQueryArray.join(', ')} WHERE userId = $${paramObjects.length   1} RETURNING *`;

const update = await db.query(queryString, [
  ...paramObjects.map(([, value]) => value),
  id,
]);

CodePudding user response：

I managed to solve my problem by following this article's instruction:

Conditional update in PostgreSQL

UPDATE users 
    SET 
        name = COALESCE (NULLIF($1, ''), name),
        username = COALESCE (NULLIF($2, ''), username),
        email = COALESCE (NULLIF($3, ''), email),
        phone = COALESCE (NULLIF($4, ''), phone),
        website = COALESCE (NULLIF($5, ''),
        website) WHERE user_id = $6
RETURNING *;