API with NestJS #53. Implementing soft deletes with PostgreSQL and TypeORM

October 25, 2021

This entry is part 53 of 180 in the API with NestJS

In this series, we’ve implemented deleting functionalities for various entities. So far, it has always meant removing records permanently from our database. Instead, we can perform soft deletes. When we delete a record using a soft delete, we only mark it as deleted.

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

Soft deletes with PostgreSQL

To implement soft deletes straightforwardly, we can use a boolean flag. Let’s implement a straightforward example with the users table.

CREATE TABLE users (

id serial PRIMARY KEY,

email text UNIQUE,

is_deleted boolean DEFAULT false

)

For starters, let’s insert a user into our database. Thanks to using the DEFAULT keyword, all our users are not deleted by default.

INSERT into users (

) VALUES (

'marcin@wanago.io'

)

If we want to perform a soft delete on the above record, we no longer use the DELETE statement. Instead, we perform an UPDATE.

UPDATE users

SET is_deleted = true

WHERE email = 'marcin@wanago.io'

Now we need to take the is_deleted column into account when performing various other queries. A good example is a query to get the list of all the users.

1 2	SELECT * from users WHERE is_deleted = false

Advantages and disadvantages of soft deletes

The obvious advantage of soft deletes is that we can always easily restore the data we’ve previously deleted. We might think we could also achieve that with frequent backups, but soft deletes could help us achieve a better user experience. For example, we could create an undo button in our application that changes the is_deleted flag to false.

Still, we definitely should create backups and make sure they work.

A significant disadvantage of soft deletes is that we always need to keep them in mind when performing various queries. If we use Object-Relational Mapping such as TypeORM, it makes our work a bit easier. However, we still need to be aware of the additional effort PostgreSQL needs to make to filter out records where is_deleted equals true.

Even if we expect is_deleted to be false in most of our queries, this might not always be the case. A significant advantage of the soft deletes is the fact that we can still fetch the deleted records. This might come in handy when we want to generate some report, for example. Thanks to soft deletes, we can include all of the records.

There is also an important catch in our example with the users table above. When we mark the email column as unique, it works in a predictable way when we decide to use the regular DELETE statements. This is not that straightforward with soft deletes. PostgreSQL takes the records with is_deleted set to true into account when checking if the unique constraint is violated.

Soft deletes can come in handy when dealing with relationships. For example, in this series, we associated posts with users through a many-to-one relationship. Because of that, the posts table has a column called author_id used to save the author of that post. Trying to perform a hard delete on a user that is an author of a post leads to a foreign constraint violation. This does not happen with soft deletes.

We could deal with the above issue using cascade deletes, but this would delete the posts also.

Soft deletes with TypeORM

The TypeORM community anticipated the soft delete feature very much. To start using this feature, we need to use the @DeleteDateColumn decorator.

category.entity.ts

import { Column, DeleteDateColumn, Entity, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';

import Post from '../posts/post.entity';

@Entity()

class Category {

@PrimaryGeneratedColumn()

public id: number;

@Column()

public name: string;

@ManyToMany(() => Post, (post: Post) => post.categories)

public posts: Post[];

@DeleteDateColumn()

public deletedAt: Date;

}

export default Category;

The above is because TypeORM stores the deletion date instead of a simple boolean flag. Thanks to that, we have an additional piece of information stored that might come in handy.

To perform the soft delete, we need to use the softDelete method instead of delete.

categories.service.ts

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

import Category from './category.entity';

import { InjectRepository } from '@nestjs/typeorm';

import { Repository } from 'typeorm';

import CategoryNotFoundException from './exceptions/categoryNotFound.exception';

@Injectable()

export default class CategoriesService {

constructor(

@InjectRepository(Category)

private categoriesRepository: Repository<Category>

) {}

async deleteCategory(id: number): Promise<void> {

const deleteResponse = await this.categoriesRepository.softDelete(id);

if (!deleteResponse.affected) {

throw new CategoryNotFoundException(id);

}

// ...

}

When we do the above, TypeORM sets the deletedAt column to the current date.

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

Performing queries

Thanks to doing the above, we can check if a record has been soft-deleted when performing other queries.

getAllCategories(): Promise<Category[]> {

return this.categoriesRepository.find({ relations: ['posts'] });

}

Thankfully, TypeORM does that for us out of the box when we use the find() method. Under the hood, it checks if the deletedAt column has the NULL value.

Still, we might want to allow the users to view a deleted category if they know the id, for example. Our /categories/[id] endpoint by default will respond with 404 Not Found for soft-deleted records, though.

Thankfully, we can explicitly tell TypeORM not to filter out soft-deleted records using the withDeleted argument.

async getCategoryById(id: number): Promise<Category> {

const category = await this.categoriesRepository.findOne(

id,

{

relations: ['posts'],

withDeleted: true

}

);

if (category) {

return category;

}

throw new CategoryNotFoundException(id);

}

When we do the above, we can notice that the deletedAt property is now set.

Restoring deleted records

With soft deletes, we can very easily restore the deleted records. TypeORM allows us to do that with the restore method.

async restoreDeletedCategory(id: number) {

const restoreResponse = await this.categoriesRepository.restore(id);

if (!restoreResponse.affected) {

throw new CategoryNotFoundException(id);

}

When we do the above, TypeORM sets the value of deletedAt back to null.

Summary

In this article, we’ve learned the concept of soft delete. While doing so, we wrote some SQL code. Understanding how the soft delete functions under the hood is essential to grasping its advantages and disadvantages better. Even though TypeORM can help us a lot in implementing soft deletes, it comes with some drawbacks. After going through them in this article, it might appear that we need a pretty good reason to use soft deletes. This is because it is bound to make our database requires more space and take a toll on the performance. Even so, it has its uses and might come in handy in some cases.

Series Navigation<< API with NestJS #52. Generating documentation with Compodoc and JSDocAPI with NestJS #54. Storing files inside a PostgreSQL database >>