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.
1CREATE TABLE categories (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 name text NOT NULL,
4 is_deleted boolean DEFAULT false
5);Above, we use the DEFAULT keyword. Thanks to that, every time we insert an entity into our database, the is_deleted flag equals false.
1INSERT into categories (
2 name
3) VALUES (
4 'JavaScript'
5)
6RETURNING *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.
1UPDATE categories
2SET is_deleted = true
3WHERE id = 1
4RETURNING *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.
1SELECT * from categories
2WHERE is_deleted = falseAdvantages 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.
1CREATE TABLE categories_posts (
2 category_id int REFERENCES categories(id),
3 post_id int REFERENCES posts(id),
4 PRIMARY KEY (category_id, post_id)
5);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.
1CREATE TABLE users (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 email text NOT NULL UNIQUE,
4 name text NOT NULL,
5 password text NOT NULL,
6 is_deleted boolean DEFAULT false
7)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.
1npx knex migrate:make add_comments_table1import { Knex } from 'knex';
2
3export async function up(knex: Knex): Promise<void> {
4 return knex.raw(`
5 CREATE TABLE comments (
6 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
7 content text NOT NULL,
8 post_id int REFERENCES posts(id) NOT NULL,
9 author_id int REFERENCES posts(id) NOT NULL,
10 deletion_date timestamptz
11 );
12 `);
13}
14
15export async function down(knex: Knex): Promise<void> {
16 return knex.raw(`
17 DROP TABLE comments;
18 `);
19}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.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Delete,
5 Param,
6 UseGuards,
7 UseInterceptors,
8} from '@nestjs/common';
9import FindOneParams from '../utils/findOneParams';
10import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
11import CommentsService from './comments.service';
12
13@Controller('comments')
14@UseInterceptors(ClassSerializerInterceptor)
15export default class CommentsController {
16 constructor(private readonly commentsService: CommentsService) {}
17
18 @Delete(':id')
19 @UseGuards(JwtAuthenticationGuard)
20 delete(@Param() { id }: FindOneParams) {
21 return this.commentsService.delete(id);
22 }
23
24 // ...
25}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3
4@Injectable()
5class CommentsRepository {
6 constructor(private readonly databaseService: DatabaseService) {}
7
8 async delete(id: number) {
9 const databaseResponse = await this.databaseService.runQuery(
10 `
11 UPDATE comments
12 SET deletion_date=now()
13 WHERE id = $1 AND deletion_date=NULL
14 RETURNING *
15 `,
16 [id],
17 );
18 if (databaseResponse.rowCount === 0) {
19 throw new NotFoundException();
20 }
21 }
22
23 // ...
24}
25
26export 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.
1import {
2 ClassSerializerInterceptor,
3 Controller,
4 Get,
5 Param,
6 UseInterceptors,
7} from '@nestjs/common';
8import FindOneParams from '../utils/findOneParams';
9import CommentsService from './comments.service';
10
11@Controller('comments')
12@UseInterceptors(ClassSerializerInterceptor)
13export default class CommentsController {
14 constructor(private readonly commentsService: CommentsService) {}
15
16 @Get()
17 getAll() {
18 return this.commentsService.getAll();
19 }
20
21 @Get(':id')
22 getById(@Param() { id }: FindOneParams) {
23 return this.commentsService.getBytId(id);
24 }
25
26 // ...
27}When fetching the entities, we need to make sure to filter out the records that have the deletion_date.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import CommentModel from './comment.model';
4
5@Injectable()
6class CommentsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getAll() {
10 const databaseResponse = await this.databaseService.runQuery(`
11 SELECT * FROM comments
12 WHERE deletion_date = NULL
13 `);
14 return databaseResponse.rows.map(
15 (databaseRow) => new CommentModel(databaseRow),
16 );
17 }
18
19 async getById(id: number) {
20 const databaseResponse = await this.databaseService.runQuery(
21 `
22 SELECT * FROM comments
23 WHERE id=$1 AND deletion_date = NULL
24 `,
25 [id],
26 );
27 const entity = databaseResponse.rows[0];
28 if (!entity) {
29 throw new NotFoundException();
30 }
31 return new CommentModel(entity);
32 }
33
34 // ...
35}
36
37export 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.
1import {
2 Body,
3 ClassSerializerInterceptor,
4 Controller,
5 Param,
6 Put,
7 UseGuards,
8 UseInterceptors,
9} from '@nestjs/common';
10import FindOneParams from '../utils/findOneParams';
11import JwtAuthenticationGuard from '../authentication/jwt-authentication.guard';
12import CommentsService from './comments.service';
13import CommentDto from './comment.dto';
14
15@Controller('comments')
16@UseInterceptors(ClassSerializerInterceptor)
17export default class CommentsController {
18 constructor(private readonly commentsService: CommentsService) {}
19
20 @Put(':id')
21 @UseGuards(JwtAuthenticationGuard)
22 update(@Param() { id }: FindOneParams, @Body() commentData: CommentDto) {
23 return this.commentsService.update(id, commentData);
24 }
25
26 // ...
27}We want our API to respond with a 404 Not Found error when the user tries to update a record marked as deleted.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import CommentModel from './comment.model';
4import CommentDto from './comment.dto';
5
6@Injectable()
7class CommentsRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async update(id: number, commentDto: CommentDto) {
11 const databaseResponse = await this.databaseService.runQuery(
12 `
13 UPDATE comments
14 SET content = $2, post_id = $3
15 WHERE id = $1 AND deletion_date=NULL
16 RETURNING *
17 `,
18 [id, commentDto.content, commentDto.postId],
19 );
20 const entity = databaseResponse.rows[0];
21 if (!entity) {
22 throw new NotFoundException();
23 }
24 return new CommentModel(entity);
25 }
26
27 // ...
28}
29
30export 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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3
4@Injectable()
5class CommentsRepository {
6 constructor(private readonly databaseService: DatabaseService) {}
7
8 async restore(id: number) {
9 const databaseResponse = await this.databaseService.runQuery(
10 `
11 UPDATE comments
12 SET deletion_date=NULL
13 WHERE id = $1
14 RETURNING *
15 `,
16 [id],
17 );
18 if (databaseResponse.rowCount === 0) {
19 throw new NotFoundException();
20 }
21 }
22
23 // ...
24}
25
26export 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.