Elegant way of implementing a PATCH API endpoint?

Question

I would like to partially update my user object, as the user has the ability to update any attribute of their choice after user creation.

For example they can decide to provide a boolean host value, dob, and/or telephone number but these attributes are optional.

What is the best way to go about checking to see what fields were provided in the PATCH request and update only those attributes?

User schema:

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT
  uuid_generate_v4(),
  first_name VARCHAR(255) NOT NULL,
  middle_name VARCHAR(255),
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_on TIMESTAMPZ NOT NULL,
  host BOOLEAN NOT NULL DEFAULT FALSE, 
  updated_on TIMESTAMPZ,
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  last_login TIMESTAMPZ,
  telephone VARCHAR(15),
  dob DATE,
);

User Update Controller

const update = async (req, res, next) => {    
  try {
    //1. Destructure the req.body 
    const {host, telephone, dob} = req.body;

    //2. Enter the updated user attributes inside of database
    const updatedUser = await pool.query(
      "UPDATE users SET host = $2, updated_on = NOW() WHERE id = $1 RETURNING *", [
        req.user, host
      ]
    );

    res.status(200).json({message: 'Your profile has been successfully updated'});
  } 
  catch (err) {
    console.error(err.message);
    res.status(500).send("Server Error");  
  }
};
     

Answer 1

I would suggest using COALESCE. You can use it like:

UPDATE users SET host = COALESCE($2, host), updated_on = NOW() WHERE...

This will overwrite the value of host in the database, if it's provided. If it's not, it keeps the value currently assigned.

Source: https://www.postgresqltutorial.com/postgresql-coalesce/