Back to Tutorials
8 min read

Soft deletes with raw SQL queries

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…

October 31, 2022

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.

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)
);

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.

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<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;
  `)
}

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.

Soft deletes with raw SQL queries | NestJS.io