API with NestJS #80. Updating entities with PUT and PATCH using raw SQL queries

JavaScript NestJS SQL

This entry is part 80 of 158 in the API with NestJS

A significant thing to realize when developing a REST API is that HTTP methods are a matter of convention. For example, in theory, we could delete entities with the POST method. However, our job is to create an API that is consistent with the REST standard and works predictably.

Updating existing rows in the database is an important part of working with databases. Since that’s the case, it is worth it to investigate the PUT and PATCH methods closer. In this article, we compare them and implement them with raw SQL queries.


The job of the PUT method is to modify an existing entity by replacing it. Therefore, if the request body does not contain a field, it should be removed from the document.

In one of the previous parts of this series, we defined a table of addresses.

The important thing to notice above is that the , , and columns accept null values.


Above, we can see that this post contains all possible properties. Let’s make a PUT request now.

Request body:


Since our request didn’t contain the and properties, they were set to null.

Implementing the PUT method with SQL

Let’s use the correct decorator in the controller to implement the PUT method with SQL and NestJS.


In our Data Transfer Object, we can point out that all of the properties of the address are optional. Let’s ensure that if the user provides data, it consists of non-empty strings.


In our SQL query, we need to use the keyword.

Fortunately, the node-postgres library makes handling the missing values straightforward. If we provide as a parameter to our query, it converts it to null.


Thanks to how the node-postgres library handles the value, we can simply use the in our parameters array. If, for example, is missing, it saves null in the database.


The PUT method is a valid choice, and it is very common. However, it might not fit every case. One of the significant downsides is that we assume that the client knows all of the details of a particular entity. Since not including a specific property removes it, the user must be careful.

A solution to the above problem can be the PATCH method which allows for a partial modification of an entity. The HTTP protocol introduced PATCH in 2010 and describes it as a set of instructions explaining how to modify a resource. The most straightforward way of interpreting the above is sending a request body with a partial entity.

Request body:


The crucial thing above is that we don’t provide the property, which isn’t removed from our entity. To delete a field, we need to explicitly send null. Thanks to this, we can’t remove a property by accident.

Implementing the PATCH method with SQL

Let’s start by using the decorator in our controller.


There are multiple ways of implementing PATCH with SQL using the keyword. Let’s take a look at this SQL:

Above, we are setting the value for three columns:

  • the  becomes ,
  • the becomes null,
  • the  stays the same.

Using does not set the value of the column to the “street” string. Instead, it sets the column to the value of the column. In consequence, its value remains the same.

We can use the above knowledge to write the following query:

Above, we maintain the value of a particular column if the user does not provide its value. There is one big flaw in the above code, though. The query needs to use all the parameters we provide in the array. Let’s imagine a situation where the user provides only the street.

The method generates the following query:

Above, we can see that we are not using the and parameters. This causes the following error:

error: bind message supplies 4 parameters, but prepared statement “” requires 2

In most cases, not using a certain parameter means a bug in our code. However, since this is not the case, we could fix the issue by using all of the arguments in another way.

Thanks to the above approach, we still use the associated parameter even if the user does not provide a specific value.

Generating the query with JavaScript

If you don’t like the above approach, an alternative is generating the SQL query using JavaScript.

Feel free to create an abstraction over the above approach if you would like to reuse it in multiple different repositories.

An advantage of the above approach is that we generate a query that contains only the columns we want to update. A significant downside is that we create the array unpredictability. For example, we can no longer assume that the value for the column is in the parameter.

JSON Patch

An alternative to the above implementation is sending instructions on how to modify the entity literally. One way to do that is to use the JSON Patch format.

Request body:

To know more, check out the jsonpatch.com page. When implementing it, the fast-json-patch library might come in handy.


In this article, we’ve gone through the PUT and PATCH methods and implemented them in our NestJS project. Both ways have their use cases and prove to be useful. We also compared different approaches to implementing the PATCH method. Each one of them has pros and cons, and it is best to choose one based on a particular case. Knowing the difference between PUT and PATCH and how it affects our SQL queries is a piece of useful knowledge for sure.

Series Navigation<< API with NestJS #79. Implementing searching with pattern matching and raw SQLAPI with NestJS #81. Soft deletes with raw SQL queries >>
Notify of
Inline Feedbacks
View all comments