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.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.npx knex migrate:make add_comments_table 20221029170345_add_comments_table.ts import { Knex } from 'knex'; export async function up(knex: Knex): Promise { 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 { 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