Updating entities with PUT and PATCH using raw SQL queries

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.CREATE TABLE addresses ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, street text, city text, country text )The important thing to notice above is that the street, city, and country columns accept null values.GET /addresses/1 Response: { "id": 1, "street": "Amphitheatre Parkway", "city": "Mountain View", "country": "USA" }Above, we can see that this post contains all possible properties. Let’s make a PUT request now.PUT /addresses/1 Request body: { "id": 1, "country": "USA" } Response: { "id": 1, "street": null, "city": null, "country": "USA" }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. addresses.controller.ts import { Body, Controller, Param, Put, UseGuards } from '@nestjs/common'; import FindOneParams from '../utils/findOneParams'; import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard'; import AddressDto from './address.dto'; import AddressesService from './addresses.service'; @Controller('addresses') export default class AddressesController { constructor(private readonly addressesService: AddressesService) {} @Put(':id') @UseGuards(JwtAuthenticationGuard) update(@Param() { id }: FindOneParams, @Body() addressDto: AddressDto) { return this.addressesService.update(id, addressDto); } // }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. address.dto.ts import { IsString, IsNotEmpty, IsOptional } from 'class-validator'; class AddressDto { @IsString() @IsOptional() @IsNotEmpty() street?: string; @IsString() @IsOptional() @IsNotEmpty() city?: string; @IsString() @IsOptional() @IsNotEmpty() country?: string; } export default AddressDto;In our SQL query, we need to use the UPDATE keyword.UPDATE addresses SET street = NULL, city = NULL, country = 'USA' WHERE id = 1 RETURNING * 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. address.repository.ts import { Injectable, NotFoundException } from '@nestjs/common'; import DatabaseService from '../database/database.service'; import AddressModel from './address.model'; import AddressDto from './address.dto'; @Injectable() class AddressesRepository { constructor(private readonly databaseService: DatabaseService) {} async update(id: number, addressData: AddressDto) { const databaseResponse = await this.databaseService.runQuery( ` UPDATE addresses SET street = $2, city = $3, country = $4 WHERE id = $1 RETURNING * `, [id, addressData.street, addressData.city, addressData.country], ); const entity = databaseResponse.rows[0]; if (!entity) { throw new NotFoundException(); } return new AddressModel(entity); } // ... } export 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.PATCH /addresses/1 Request body: { "id": 1, "street": null, "country": "Canada" } Response: { "id": 1, "street": null, "city": "Mountain View", "country": "Canada" }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. addresses.controller.ts import { Body, Controller, Param, Patch, UseGuards } from '@nestjs/common'; import FindOneParams from '../utils/findOneParams'; import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard'; import AddressDto from './address.dto'; import AddressesService from './addresses.service'; @Controller('addresses') export default class AddressesController { constructor(private readonly addressesService: AddressesService) {} @Patch(':id') @UseGuards(JwtAuthenticationGuard) update(@Param() { id }: FindOneParams, @Body() addressDto: AddressDto) { return this.addressesService.update(id, addressDto); } // ... }There are multiple ways of implementing PATCH with SQL using the UPDATE keyword. Let’s take a look at this SQL:UPDATE addresses SET country = 'USA', city = NULL, street = street WHERE id = 1 RETURNING *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:import { Injectable, NotFoundException } from '@nestjs/common'; import DatabaseService from '../database/database.service'; import AddressModel from './address.model'; import AddressDto from './address.dto'; @Injectable() class AddressesRepository { constructor(private readonly databaseService: DatabaseService) {} async update(id: number, addressData: AddressDto) { const { street, city, country } = addressData; const databaseResponse = await this.databaseService.runQuery( ` UPDATE addresses SET street = ${street !== undefined ? '$2' : 'street'}, city = ${city !== undefined ? '$3' : 'city'}, country = ${country !== undefined ? '$4' : 'country'} WHERE id = $1 RETURNING * `, [id, street, city, country], ); const entity = databaseResponse.rows[0]; if (!entity) { throw new NotFoundException(); } return new AddressModel(entity); } // ... } export 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.{ "street": "Amphitheatre Parkway" }The update method generates the following query:const databaseResponse = await this.databaseService.runQuery( ` UPDATE addresses SET street = $2 city = city country = country WHERE id = $1 RETURNING * `, [id, street, city, country], );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.import { Injectable, NotFoundException } from '@nestjs/common'; import DatabaseService from '../database/database.service'; import AddressModel from './address.model'; import AddressDto from './address.dto'; @Injectable() class AddressesRepository { constructor(private readonly databaseService: DatabaseService) {} async update(id: number, addressData: AddressDto) { const { street, city, country } = addressData; const databaseResponse = await this.databaseService.runQuery( ` WITH used_parameters AS ( SELECT $2, $3, $4 ) UPDATE addresses SET street = ${street !== undefined ? '$2' : 'street'}, city = ${city !== undefined ? '$3' : 'city'}, country = ${country !== undefined ? '$4' : 'country'} WHERE id = $1 RETURNING * `, [id, street, city, country], ); const entity = databaseResponse.rows[0]; if (!entity) { throw new NotFoundException(); } return new AddressModel(entity); } // ... } export 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