PATCH /users/:id - Partial Updates

PATCH allows updating only some fields of a user. Unlike PUT which requires all fields, PATCH lets you send just the fields you want to change.

Dynamic Query Building

PATCH is more complex than PUT because we need to build the UPDATE query dynamically based on which fields are provided. If someone sends just {"username": "newname"}, we only update the username. If they send {"email": "new@email.com"}, we only update the email.

Partial Update Endpoint

// Partially update a user
app.patch("/users/:id", async (req, res) => {
  try {
    const userId = Number(req.params.id);
    const { username, email } = req.body;

    // Validate ID
    if (isNaN(userId)) {
      return res.status(400).json({
        error: "Invalid user ID",
      });
    }

    // Check that at least one field is provided
    if (!username && !email) {
      return res.status(400).json({
        error: "At least one field (username or email) is required",
      });
    }

    // Build dynamic UPDATE query based on provided fields
    const fieldsToUpdate = [];
    const values = [];

    if (username) {
      fieldsToUpdate.push("username = ?");
      values.push(username);
    }

    if (email) {
      fieldsToUpdate.push("email = ?");
      values.push(email);
    }

    values.push(userId); // Add ID for WHERE clause

    // Uses UPDATE SET from Lesson 1, but only for provided fields
    const query = `UPDATE users SET ${fieldsToUpdate.join(", ")} WHERE id = ?`;
    const [result]: [ResultSetHeader, any] = await pool.execute(query, values);

    if (result.affectedRows === 0) {
      return res.status(404).json({
        error: "User not found",
      });
    }

    // Get the updated user to return to the frontend
    const [rows] = await pool.execute(
      "SELECT id, username, email FROM users WHERE id = ?",
      [userId]
    );
    const users = rows as User[];
    const user = users[0];

    res.json(user);
  } catch (error) {
    console.error("Database error:", error);
    res.status(500).json({
      error: "Failed to update user",
    });
  }
});

Understanding the PATCH Code

How the Code Works

1. Check what fields to update: We examine the request body to see which fields are provided.
2. Build dynamic UPDATE query: We construct the SQL query based on available fields.
3. SELECT the user back: We query the database to get the complete updated user object.

Validation Difference

• PUT validation: ALL fields required (username AND email must be provided).
• PATCH validation: At least ONE field required (username OR email OR both can be provided).
• We check if (!username && !email) to ensure at least one field exists.

Dynamic Query Construction

• We build arrays fieldsToUpdate and values as we check each field.
• If username is provided: add "username = ?" to the fieldsToUpdate array and the value to the values array.
• If email is provided: add "email = ?" to the fieldsToUpdate array and the value to the values array.
• Final query example: "UPDATE users SET username = ?, email = ? WHERE id = ?" (if both fields are provided).

Understanding the Database Result

const [result]: [ResultSetHeader, any] = await pool.execute(query, values);

Just like PUT, the UPDATE operation returns a ResultSetHeader:

• affectedRows - How many rows were changed (0 means user not found, 1 means success).

Why PATCH Needs the SELECT Query

Unlike POST and PUT where we know all the field values, PATCH only updates some fields. We need the SELECT query because:

• We don’t know the current values of fields we didn’t update.
• Example: If we only update username, we still need to return the current email value.
• The SELECT gives us the complete user object with all current values.

Repo link