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

October 24, 2022

This entry is part 80 of 121 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.

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.

1	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.

1	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.

1	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 SQL query using JavaScript.

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 getById(id: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT * FROM addresses WHERE id=$1

[id],

);

const entity = databaseResponse.rows[0];

if (!entity) {

throw new NotFoundException();

}

return new AddressModel(entity);

}

async update(id: number, addressData: AddressDto) {

const { street, city, country } = addressData;

// return the entity without updating if no value provided

if (![street, city, country].some((value) => value !== undefined)) {

return this.getById(id);

}

const params: unknown[] = [id];

const setQueryParts: string[] = [];

if (street !== undefined) {

params.push(street);

setQueryParts.push(`street = $${params.length}`);

}

if (city !== undefined) {

params.push(city);

setQueryParts.push(`city = $${params.length}`);

}

if (country !== undefined) {

params.push(country);

setQueryParts.push(`country = $${params.length}`);

}

const databaseResponse = await this.databaseService.runQuery(

UPDATE addresses SET

${setQueryParts.join(', ')}

WHERE id = $1

RETURNING *

params,

);

const entity = databaseResponse.rows[0];

if (!entity) {

throw new NotFoundException();

}

return new AddressModel(entity);

}

export 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.

1	PATCH /addresses/1

Request body:

[

{

"op": "replace",

"path": "/country",

"value": "Canada"

}

]

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.

Series Navigation<< API with NestJS #79. Implementing searching with pattern matching and raw SQLAPI with NestJS #81. Soft deletes with raw SQL queries >>