Handling PATCH request with Express.js and mysql

Question

Let's assume I have the following routes:

POST /users where all fields are required and this adds a user to my DB, let's say this one gets an id of 1

{
    "firstName": "FirstName",
    "lastName": "LastName",
    "email": "email@example.com",
    "address": "address"
}

I also have route GET /users which shows all users from the DB.

[
    {
        "id": 1,
        "firstName": "FirstName",
        "lastName": "LastName",
        "email": "email@example.com",
        "address": "address"
    }
]

Now, I am trying to have a route for updating a specific user, PATCH /users.

I want to make all the fields optional and send only the fields I update through the PATCH route and the id will be decoded from the JWT token.

It's important to note that the data is shown in a form on the front-end and I want to update the form but only send values which have been updated from the form rather than the whole form. The actual form has over 30 fields.

I know if I use a PUT request, I can send all the values from the form and execute the following:

editUser = async ({ firstName, lastName, email, address }) => {
    let sql = `UPDATE users
    SET firstName = ?, lastName = ?, email=?, address=? WHERE id = ?`;

    const result = await query(sql, [firstName, lastName, email, address, userId ]);

    return result;
}

Now, let's say I update only the email in the form and send it. For example: PATCH /users

{
    "email":"email@gmail.com"
}

That's also easy enough with the following:

editUser = async ({ email }) => {
    let sql = `UPDATE users
    SET email = ? WHERE id = ?`;

    const result = await query(sql, [email, userId]);

    return result;
}

The above would be a good way to PATCH the email only.

So now when I do GET /users I get the following:

[
    {
        "id": 1,
        "firstName": "FirstName",
        "lastName": "LastName",
        "email": "email@gmail.com",
        "address": "address"
    }
]

But how do I handle different cases where I don't know which keys are being sent to the back-end? For example, one set can be as follows: PATCH /users

{
    "lastName": "updatedLastName"
    "email":"email@gmail.com"
}

The above can be any combination of all the different keys in the object in practice. What would be a good way to do generate custom queries depending on the keys and update the rows in my table with only the column which need to be updated?

Jérémie RPK · Answer 1 · 2021-08-30T20:47:14.600

I have no better idea than these:

With a forEach:

const buildPatchQuery = (table, id, data) => {
    if (Object.keys(data).length === 0) return null; // Or return what you want
    let sql = `UPDATE ${table} SET`;
    Object.entries(data).forEach(([key, value]) => {
        const valueToSet = typeof data[key] === 'string' ? `'${value}'` : value;
        sql += ` ${key}=${valueToSet},`;
    });
    sql = sql.slice(0, -1); // Remove last ","
    sql += ` WHERE id=${id};`;
    return sql;
}

With a map:

const buildPatchQuery = (table, id, data) => {
    if (Object.keys(data).length === 0) return null; // Or return what you want
    let query = `UPDATE ${table} SET `;
    query += Object.keys(data).map((key) => {
        const valueToSet = typeof data[key] === 'string' ? `'${data[key]}'` : data[key];
        return `${key}=${valueToSet}`;
    }).join(', ');
    return query + ` WHERE id=${id};`;
}

With a reduce:

const buildPatchQuery = (table, id, data) => {
    if (Object.keys(data).length === 0) return null; // Or return what you want
    let query = `UPDATE ${table} SET`;
    query += Object.entries(data).reduce((acc, [key, value], index, array) => {
        const valueToSet = typeof data[key] === 'string' ? `'${value}'` : value;
        const optionalComma = index < array.length-1 ? ',' : '';
        return `${acc} ${key}=${valueToSet}${optionalComma}`;
    }, '');
    return query + ` WHERE id=${id};`;
}

By using your SQL library:

const buildPatchQuery = (table, id, data) => {
    if (Object.keys(data).length === 0) return null; // Or return what you want
    let sql = `UPDATE ${table} SET`;
    const newValues = [];
    Object.entries(data).forEach(([key, value]) => {
        sql += ` ${key}=?,`;
        newValues.push(value);
    });
    sql = sql.slice(0, -1); // Remove last ","
    sql += ' WHERE id=?;';
    newValues.push(id);
    return query(sql, newValues);
}

Tested with:

console.log(buildPatchQuery('users', 1, {name: 'toto', email: 'truc', age: 18}));
console.log(buildPatchQuery('users', 1, {name: 'toto'}));
console.log(buildPatchQuery('users', 1, {}));

(not tested via the SQL library)

However, do check the data object and secure your route by checking authentication and permissions to avoid SQL injection or any hack!

That's a neat solution. I managed to craft another solution. Would like to have your input on it. Posted it in answers — nTuply, Aug 30 '21 at 20:46
With pleasure ! You can put your code in https://codeshare.io/ if it's for test reasons. — Jérémie RPK, Aug 30 '21 at 20:50
Another solution with your library would be to "SET" all the fields in your query (with "?"), but by settings the current object's value if no new value is provided. — Jérémie RPK, Aug 30 '21 at 20:54
I think this is what I did. I posted my answer here. Would like some input on it. — nTuply, Aug 30 '21 at 20:56

score 3 · Answer 2 · answered Aug 30 '21 at 20:52

I seem to have found a solution to this. This basically leverages the IFNULL in-built function of MySQL. The trick I used here is to simply pass all my parameters as usual. However, I do a quick check to see whether the values are undefined and if so, I turn them to null. This allows me to now use the IFNULL function in my query. What it does is, it checks if I'm trying to update a column in my table with a null value. If so, then it doesn't update it but rather keeps the current value of the column.

In this particular case, my validator (JOI validator) marks all the fields as optional but still requires them to be in a specific format. Furthermore, the prepared statements in the SQL query help against SQL injections.

editUser = async ({
    firstName,
    lastName,
    email,
    address
  }) => {
   
    firstName = firstName ?? null;
    lastName = lastName ?? null;
    email = email ?? null;
    address = address ?? null;

    let sql = `UPDATE ${this.tableName}
        SET firstName = IFNULL(?, firstName), lastName = IFNULL(?, lastName), email=IFNULL(?, email), address = IFNULL(?, address) WHERE userId = ?`;

    let result = await query(sql, [
      firstName,
      lastName,
      email,
      address
    ]);

    return result;
  };

Nice solution, I didn't think about it! – Jérémie RPK Aug 30 '21 at 20:58 — Jérémie RPK, Aug 30 '21 at 20:58

Senthil · Answer 3 · 2021-08-30T20:38:55.023

1

Handle the fields by yourself, because the end users / hackers can override or manipulate based on the keys. Add conditons based on key / switch statement. Do a find to get the entire object. Because otherwise via SQL injections / critical fields can be manipulated via payload. Dynamic place holder can handle the SQL injection attacks, but the end user can over example paidmembership: true etc They will override. Or have a set of key values in an object and allow only those field to be updated dynamicallyn or use sequelize How to update a record using sequelize for node? which is in a ORM form

app.patch('/api/products/:id', (req, res) => {
  const product = products.find(product => product.id === parseInt(req.params.id));
  if (!product) return res.status(404).json({ message: 'Not Found' });

  if(req.body.name) product.name = req.body.name;
  if(req.body.price)  product.price = req.body.price;

  res.json(product);
});

Ref : https://www.tabnine.com/code/javascript/functions/express/Express/patch

edited Aug 30 '21 at 20:38

answered Aug 30 '21 at 20:33

Senthil

2,156
1
14
19

Can't I use a Validator middleware to validate inputs before doing any DB operation? I"m currently using the JOI validator – nTuply Aug 30 '21 at 20:36
Yes, you can do that but the list of fields will keep on changing . THe new way of writing is to use ORM concept for data manipulations Ref : https://stackoverflow.com/questions/8158244/how-to-update-a-record-using-sequelize-for-node – Senthil Aug 30 '21 at 20:40
Well, the validator (JOI) does not allow unknown fields to pass. So if you send a random key it's going to give you an error. But don't the prepared statements in the SQL query help in preventing SQL injection? – nTuply Aug 30 '21 at 20:43
yes, it does handle based on prepared statement. – Senthil Aug 30 '21 at 20:45
I see, thanks! I got around a solution. Would like to get your input on it. – nTuply Aug 30 '21 at 20:47

score 0 · Answer 4 · answered Jan 09 '23 at 04:33

For 2023 Readers
I was making a crud API and found this solution

// importing modules
const express = require("express");
const mysql = require("mysql");

// configuring express server
const app = express();
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// configuring establishing connection with mysql
const connection = mysql.createConnection({
    port: 3306,
    host: "localhost",
    user: "root",
    password: "password",
    database: "myDatabase"
});
connection.connect();

// patch request
app.patch("/update-user/:id", (req, res) => {
    var id = Number(req.params.id);
    var data = req.body;
    connection.query("update table_name set ? where id = ?", [data, id], (err) => {
        if (err) { console.log(err); }
        else {
            console.log("done");
            res.status(200).json("done");
        }
    });
});

// listening on port 3000
app.listen(3000, () => {
    console.log("Listning on 3000");
});

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jan 09 '23 at 08:30

Handling PATCH request with Express.js and mysql

4 Answers4