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.
PUT
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.
1CREATE TABLE addresses (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 street text,
4 city text,
5 country text
6)The important thing to notice above is that the street, city, and country columns accept null values.
1GET /addresses/11{
2 "id": 1,
3 "street": "Amphitheatre Parkway",
4 "city": "Mountain View",
5 "country": "USA"
6}Above, we can see that this post contains all possible properties. Let’s make a PUT request now.
1PUT /addresses/11{
2 "id": 1,
3 "country": "USA"
4}1{
2 "id": 1,
3 "street": null,
4 "city": null,
5 "country": "USA"
6}Since our request didn’t contain the street and city 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.
1import { Body, Controller, Param, Put, UseGuards } from '@nestjs/common';
2import FindOneParams from '../utils/findOneParams';
3import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
4import AddressDto from './address.dto';
5import AddressesService from './addresses.service';
6
7@Controller('addresses')
8export default class AddressesController {
9 constructor(private readonly addressesService: AddressesService) {}
10
11 @Put(':id')
12 @UseGuards(JwtAuthenticationGuard)
13 update(@Param() { id }: FindOneParams, @Body() addressDto: AddressDto) {
14 return this.addressesService.update(id, addressDto);
15 }
16
17 //
18}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.
1import { IsString, IsNotEmpty, IsOptional } from 'class-validator';
2
3class AddressDto {
4 @IsString()
5 @IsOptional()
6 @IsNotEmpty()
7 street?: string;
8
9 @IsString()
10 @IsOptional()
11 @IsNotEmpty()
12 city?: string;
13
14 @IsString()
15 @IsOptional()
16 @IsNotEmpty()
17 country?: string;
18}
19
20export default AddressDto;In our SQL query, we need to use the UPDATE keyword.
1UPDATE addresses
2SET street = NULL, city = NULL, country = 'USA'
3WHERE id = 1
4RETURNING *Fortunately, the node-postgres library makes handling the missing values straightforward. If we provide undefined as a parameter to our query, it converts it to null.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import AddressModel from './address.model';
4import AddressDto from './address.dto';
5
6@Injectable()
7class AddressesRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async update(id: number, addressData: AddressDto) {
11 const databaseResponse = await this.databaseService.runQuery(
12 `
13 UPDATE addresses
14 SET street = $2, city = $3, country = $4
15 WHERE id = $1
16 RETURNING *
17 `,
18 [id, addressData.street, addressData.city, addressData.country],
19 );
20 const entity = databaseResponse.rows[0];
21 if (!entity) {
22 throw new NotFoundException();
23 }
24 return new AddressModel(entity);
25 }
26
27 // ...
28}
29
30export default AddressesRepository;Thanks to how the node-postgres library handles the undefined value, we can simply use the addressData in our parameters array. If, for example, addressData.street is missing, it saves null in the database.
PATCH
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.
1PATCH /addresses/11{
2 "id": 1,
3 "street": null,
4 "country": "Canada"
5}1{
2 "id": 1,
3 "street": null,
4 "city": "Mountain View",
5 "country": "Canada"
6}The crucial thing above is that we don’t provide the city 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 @Patch() decorator in our controller.
1import { Body, Controller, Param, Patch, UseGuards } from '@nestjs/common';
2import FindOneParams from '../utils/findOneParams';
3import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
4import AddressDto from './address.dto';
5import AddressesService from './addresses.service';
6
7@Controller('addresses')
8export default class AddressesController {
9 constructor(private readonly addressesService: AddressesService) {}
10
11 @Patch(':id')
12 @UseGuards(JwtAuthenticationGuard)
13 update(@Param() { id }: FindOneParams, @Body() addressDto: AddressDto) {
14 return this.addressesService.update(id, addressDto);
15 }
16
17 // ...
18}There are multiple ways of implementing PATCH with SQL using the UPDATE keyword. Let’s take a look at this SQL:
1UPDATE addresses
2SET country = 'USA', city = NULL, street = street
3WHERE id = 1
4RETURNING *Above, we are setting the value for three columns:
- the country becomes 'USA',
- the city becomes null,
- the street stays the same.
Using street = street does not set the value of the street column to the “street” string. Instead, it sets the street column to the value of the street column. In consequence, its value remains the same.
We can use the above knowledge to write the following query:
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import AddressModel from './address.model';
4import AddressDto from './address.dto';
5
6@Injectable()
7class AddressesRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async update(id: number, addressData: AddressDto) {
11 const { street, city, country } = addressData;
12
13 const databaseResponse = await this.databaseService.runQuery(
14 `
15 UPDATE addresses
16 SET
17 street = ${street !== undefined ? '$2' : 'street'},
18 city = ${city !== undefined ? '$3' : 'city'},
19 country = ${country !== undefined ? '$4' : 'country'}
20 WHERE id = $1
21 RETURNING *
22 `,
23 [id, street, city, country],
24 );
25 const entity = databaseResponse.rows[0];
26 if (!entity) {
27 throw new NotFoundException();
28 }
29 return new AddressModel(entity);
30 }
31
32 // ...
33}
34
35export default AddressesRepository;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.
1{
2 "street": "Amphitheatre Parkway"
3}The update method generates the following query:
1const databaseResponse = await this.databaseService.runQuery(
2 `
3 UPDATE addresses
4 SET
5 street = $2
6 city = city
7 country = country
8 WHERE id = $1
9 RETURNING *
10 `,
11 [id, street, city, country],
12);Above, we can see that we are not using the $3 and $4 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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import AddressModel from './address.model';
4import AddressDto from './address.dto';
5
6@Injectable()
7class AddressesRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async update(id: number, addressData: AddressDto) {
11 const { street, city, country } = addressData;
12
13 const databaseResponse = await this.databaseService.runQuery(
14 `
15 WITH used_parameters AS (
16 SELECT $2, $3, $4
17 )
18 UPDATE addresses
19 SET
20 street = ${street !== undefined ? '$2' : 'street'},
21 city = ${city !== undefined ? '$3' : 'city'},
22 country = ${country !== undefined ? '$4' : 'country'}
23 WHERE id = $1
24 RETURNING *
25 `,
26 [id, street, city, country],
27 );
28 const entity = databaseResponse.rows[0];
29 if (!entity) {
30 throw new NotFoundException();
31 }
32 return new AddressModel(entity);
33 }
34
35 // ...
36}
37
38export default AddressesRepository;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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import AddressModel from './address.model';
4import AddressDto from './address.dto';
5
6@Injectable()
7class AddressesRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async getById(id: number) {
11 const databaseResponse = await this.databaseService.runQuery(
12 `
13 SELECT * FROM addresses WHERE id=$1
14 `,
15 [id],
16 );
17 const entity = databaseResponse.rows[0];
18 if (!entity) {
19 throw new NotFoundException();
20 }
21 return new AddressModel(entity);
22 }
23
24 async update(id: number, addressData: AddressDto) {
25 const { street, city, country } = addressData;
26
27 // return the entity without updating if no value provided
28 if (![street, city, country].some((value) => value !== undefined)) {
29 return this.getById(id);
30 }
31
32 const params: unknown[] = [id];
33 const setQueryParts: string[] = [];
34
35 if (street !== undefined) {
36 params.push(street);
37 setQueryParts.push(`street = $${params.length}`);
38 }
39 if (city !== undefined) {
40 params.push(city);
41 setQueryParts.push(`city = $${params.length}`);
42 }
43 if (country !== undefined) {
44 params.push(country);
45 setQueryParts.push(`country = $${params.length}`);
46 }
47
48 const databaseResponse = await this.databaseService.runQuery(
49 `
50 UPDATE addresses SET
51 ${setQueryParts.join(', ')}
52 WHERE id = $1
53 RETURNING *
54 `,
55 params,
56 );
57 const entity = databaseResponse.rows[0];
58 if (!entity) {
59 throw new NotFoundException();
60 }
61 return new AddressModel(entity);
62 }
63}
64
65export default AddressesRepository;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 params array unpredictability. For example, we can no longer assume that the value for the city column is in the $3 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.
1PATCH /addresses/11[
2 {
3 "op": "replace",
4 "path": "/country",
5 "value": "Canada"
6 }
7]To know more, check out the jsonpatch.com page. When implementing it, the fast-json-patch library might come in handy.
Summary
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.