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.
1CREATE TABLE users (
2 id serial PRIMARY KEY,
3 email text UNIQUE,
4 is_deleted boolean DEFAULT false
5)For starters, let’s insert a user into our database. Thanks to using the DEFAULT keyword, all our users are not deleted by default.
1INSERT into users (
2 email
3) VALUES (
4 'marcin@wanago.io'
5)If we want to perform a soft delete on the above record, we no longer use the DELETE statement. Instead, we perform an UPDATE.
1UPDATE users
2 SET is_deleted = true
3 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.
1SELECT * from users
2 WHERE is_deleted = falseAdvantages 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.
1import { Column, DeleteDateColumn, Entity, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';
2import Post from '../posts/post.entity';
3
4@Entity()
5class Category {
6 @PrimaryGeneratedColumn()
7 public id: number;
8
9 @Column()
10 public name: string;
11
12 @ManyToMany(() => Post, (post: Post) => post.categories)
13 public posts: Post[];
14
15 @DeleteDateColumn()
16 public deletedAt: Date;
17}
18
19export 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.
1import { Injectable } from '@nestjs/common';
2import Category from './category.entity';
3import { InjectRepository } from '@nestjs/typeorm';
4import { Repository } from 'typeorm';
5import CategoryNotFoundException from './exceptions/categoryNotFound.exception';
6
7@Injectable()
8export default class CategoriesService {
9 constructor(
10 @InjectRepository(Category)
11 private categoriesRepository: Repository<Category>
12 ) {}
13
14 async deleteCategory(id: number): Promise<void> {
15 const deleteResponse = await this.categoriesRepository.softDelete(id);
16 if (!deleteResponse.affected) {
17 throw new CategoryNotFoundException(id);
18 }
19 }
20
21 // ...
22}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.
1getAllCategories(): Promise<Category[]> {
2 return this.categoriesRepository.find({ relations: ['posts'] });
3}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.
1async getCategoryById(id: number): Promise<Category> {
2 const category = await this.categoriesRepository.findOne(
3 id,
4 {
5 relations: ['posts'],
6 withDeleted: true
7 }
8 );
9 if (category) {
10 return category;
11 }
12 throw new CategoryNotFoundException(id);
13}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.
1async restoreDeletedCategory(id: number) {
2 const restoreResponse = await this.categoriesRepository.restore(id);
3 if (!restoreResponse.affected) {
4 throw new CategoryNotFoundException(id);
5 }
6}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.