API with NestJS #81. Soft deletes with raw SQL queries

October 31, 2022

This entry is part 81 of 121 in the API with NestJS

Removing entities is a very common feature in a lot of web applications. The most straightforward way of achieving it is permanently deleting rows from the database. In this article, we implement soft deletes that only mark records as deleted.

You can find the code from this article in this repository.

The idea behind soft deletes

The simplest way of implementing soft deletes is with a boolean flag.

CREATE TABLE categories (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

name text NOT NULL,

is_deleted boolean DEFAULT false

);

Above, we use the DEFAULT keyword. Thanks to that, every time we insert an entity into our database, the is_deleted flag equals false.

INSERT into categories (

name

) VALUES (

'JavaScript'

)

RETURNING *

When we want to perform a soft delete on the above record, we don’t use the DELETE keyword. Instead, we update the value in the is_deleted column.

UPDATE categories

SET is_deleted = true

WHERE id = 1

RETURNING *

The crucial thing is that implementing soft deletes affects various queries. For example, we need to consider it when getting the list of all entities.

1 2	SELECT * from categories WHERE is_deleted = false

Advantages of soft deletes

The most apparent advantage of soft deletes is that we can quickly restore the entities we’ve deleted. While that’s also possible with backups, soft deletes allow for a better user experience. A good example is an undo button that changes the is_deleted flag back to false.

The convenient thing is that we can fetch the deleted records from the database even though we’ve marked them as removed. This can be useful when we want to generate a report that includes all our records, for example.

If you want to know how to use SQL to generate a report, check out API with NestJS #78. Generating statistics using aggregate functions in raw SQL

Soft deletes might also help us deal with relationships. For example, in this series, we’ve created the categories_posts table.

CREATE TABLE categories_posts (

category_id int REFERENCES categories(id),

post_id int REFERENCES posts(id),

PRIMARY KEY (category_id, post_id)

);

If you want to know more about the above table, check out API with NestJS #74. Designing many-to-one relationships using raw SQL queries

Performing a hard delete on a category that’s referenced in the categories_posts table causes the foreign constraint violation. The above does not happen with soft deletes because we don’t remove the records from the database.

Disadvantages of soft deletes

A significant drawback of implementing soft deletes is that we always need to consider them in various queries. When we implement an endpoint that fetches the data, and we forget to filter by the is_deleted column, the client might access data that shouldn’t be accessible. Having to implement additional filtering might also have an impact on the performance.

Besides the above, we must also watch out for the unique constraint. Let’s create an example by modifying our users table by adding the is_deleted column.

CREATE TABLE users (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

email text NOT NULL UNIQUE,

name text NOT NULL,

password text NOT NULL,

is_deleted boolean DEFAULT false

)

In the above case, we require every email to be unique. With hard deletes, removing users makes their email accessible to others. However, we don’t remove records from the database when we use soft deletes. Because of that, removing users with soft deletes does not make their emails available to use.

If you want to know more about constraints, check out Defining constraints with PostgreSQL and TypeORM

Implementing soft deletes in our NestJS project

A common approach to implementing soft deletes is storing the deletion date instead of using a simple boolean column. Let’s create a migration that adds a new comments table that uses soft deletes.

1	npx knex migrate:make add_comments_table

20221029170345_add_comments_table.ts

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {

return knex.raw(`

CREATE TABLE comments (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

content text NOT NULL,

post_id int REFERENCES posts(id) NOT NULL,

author_id int REFERENCES posts(id) NOT NULL,

deletion_date timestamptz

);

`);

}

export async function down(knex: Knex): Promise<void> {

return knex.raw(`

DROP TABLE comments;

`);

}

If you want to know more about dates in PostgreSQL, check out Managing date and time with PostgreSQL and TypeORM

Deleting entities

The crucial part of implementing soft deletes is handling the DELETE method correctly.

comments.controller.ts

import {

ClassSerializerInterceptor,

Controller,

Delete,

Param,

UseGuards,

UseInterceptors,

} from '@nestjs/common';

import FindOneParams from '../utils/findOneParams';

import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';

import CommentsService from './comments.service';

@Controller('comments')

@UseInterceptors(ClassSerializerInterceptor)

export default class CommentsController {

constructor(private readonly commentsService: CommentsService) {}

@Delete(':id')

@UseGuards(JwtAuthenticationGuard)

delete(@Param() { id }: FindOneParams) {

return this.commentsService.delete(id);

}

// ...

}

The SQL query in our repository should set the value for the deletion_date column correctly. One way to do that is to use the now() function that returns the current date and time with the timezone.

comments.repository.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

@Injectable()

class CommentsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async delete(id: number) {

const databaseResponse = await this.databaseService.runQuery(

UPDATE comments

SET deletion_date=now()

WHERE id = $1 AND deletion_date=NULL

RETURNING *

[id],

);

if (databaseResponse.rowCount === 0) {

throw new NotFoundException();

}

// ...

}

export default CommentsRepository;

Above, we include check if the deletion_date equals NULL to disallow removing the entity if it is already marked as deleted.

Fetching entities

It is crucial to account for the deletion_date column in the rest of our queries. A good example is implementing the GET method.

comments.controller.ts

import {

ClassSerializerInterceptor,

Controller,

Get,

Param,

UseInterceptors,

} from '@nestjs/common';

import FindOneParams from '../utils/findOneParams';

import CommentsService from './comments.service';

@Controller('comments')

@UseInterceptors(ClassSerializerInterceptor)

export default class CommentsController {

constructor(private readonly commentsService: CommentsService) {}

@Get()

getAll() {

return this.commentsService.getAll();

}

@Get(':id')

getById(@Param() { id }: FindOneParams) {

return this.commentsService.getBytId(id);

}

// ...

}

When fetching the entities, we need to make sure to filter out the records that have the deletion_date.

comments.repository.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import CommentModel from './comment.model';

@Injectable()

class CommentsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getAll() {

const databaseResponse = await this.databaseService.runQuery(`

SELECT * FROM comments

WHERE deletion_date = NULL

`);

return databaseResponse.rows.map(

(databaseRow) => new CommentModel(databaseRow),

);

}

async getById(id: number) {

const databaseResponse = await this.databaseService.runQuery(

SELECT * FROM comments

WHERE id=$1 AND deletion_date = NULL

[id],

);

const entity = databaseResponse.rows[0];

if (!entity) {

throw new NotFoundException();

}

return new CommentModel(entity);

}

// ...

}

export default CommentsRepository;

Thanks to the above approach, trying to fetch a record marked as deleted results in the 404 Not Found error.

Updating entities

Using soft deletes can also affect the implementation of our PUT method.

comments.controller.ts

import {

Body,

ClassSerializerInterceptor,

Controller,

Param,

Put,

UseGuards,

UseInterceptors,

} from '@nestjs/common';

import FindOneParams from '../utils/findOneParams';

import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';

import CommentsService from './comments.service';

import CommentDto from './comment.dto';

@Controller('comments')

@UseInterceptors(ClassSerializerInterceptor)

export default class CommentsController {

constructor(private readonly commentsService: CommentsService) {}

@Put(':id')

@UseGuards(JwtAuthenticationGuard)

update(@Param() { id }: FindOneParams, @Body() commentData: CommentDto) {

return this.commentsService.update(id, commentData);

}

// ...

}

We want our API to respond with a 404 Not Found error when the user tries to update a record marked as deleted.

comments.repository.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import CommentModel from './comment.model';

import CommentDto from './comment.dto';

@Injectable()

class CommentsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async update(id: number, commentDto: CommentDto) {

const databaseResponse = await this.databaseService.runQuery(

UPDATE comments

SET content = $2, post_id = $3

WHERE id = $1 AND deletion_date=NULL

RETURNING *

[id, commentDto.content, commentDto.postId],

);

const entity = databaseResponse.rows[0];

if (!entity) {

throw new NotFoundException();

}

return new CommentModel(entity);

}

// ...

}

export default CommentsRepository;

Restoring deleted entities

We might want to restore a deleted entity. Fortunately, this is very easy to achieve by setting the value in the deletion_date column to null.

comments.repository.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

@Injectable()

class CommentsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async restore(id: number) {

const databaseResponse = await this.databaseService.runQuery(

UPDATE comments

SET deletion_date=NULL

WHERE id = $1

RETURNING *

[id],

);

if (databaseResponse.rowCount === 0) {

throw new NotFoundException();

}

// ...

}

export default CommentsRepository;

Summary

In this article, we’ve gone through the idea of soft deletes and discussed their pros and cons. Soft deletes can help us achieve a good user experience associated with deleting entities and restoring them. But unfortunately, they come with the cost of the increased complexity of all of our SQL queries. Even though that’s the case, soft deletes have their use cases and might come in handy.

Series Navigation<< API with NestJS #80. Updating entities with PUT and PATCH using raw SQL queriesAPI with NestJS #82. Introduction to indexes with raw SQL queries >>