Deleting entities is a standard feature in most REST APIs. The most straightforward approach is to remove rows from the database permanently. However, we can use soft deletes to keep the deleted entities in our database. In this article, we learn how to do it using the Drizzle ORM and PostgreSQL.
Introducing soft deletes
To implement soft deletes, we can use a boolean flag to indicate which records are deleted.
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);In the code above, we use the DEFAULT keyword so that the is_deleted flag is automatically set to false whenever a new entity is added to the database.
1INSERT into categories (
2 name
3) VALUES (
4 'JavaScript'
5)
6RETURNING *To perform a soft delete on the record above, we avoid using the DELETE keyword. Instead, we update the is_deleted column to indicate that the record has been deleted without permanently removing it.
1UPDATE categories
2SET is_deleted = true
3WHERE id = 1
4RETURNING *It’s crucial to notice that implementing soft deletes affects various queries. For instance, we need to account for it when fetching the list of all entities.
1SELECT * from categories
2WHERE is_deleted = falseAdvantages
One clear benefit of soft deletes is easily restoring deleted entities, offering a better user experience than using a backup. For example, an undo button can simply reset the is_deleted flag to false. Additionally, we can still access deleted records from the database, which is helpful for generating comprehensive reports, for example.
Soft deletes are also helpful in managing relationships. For example, permanently deleting a record referenced in another table can cause a foreign key constraint violation. This issue is avoided with soft deletes since the records remain in the database.
If you want to know more about constraints, check out API with NestJS #152. SQL constraints with the Drizzle ORM
Disadvantages
A major drawback of soft deletes is the need to account for them in all related queries. Users might access information they shouldn’t if we forget to filter by the is_deleted column when retrieving data. Unfortunately, this additional filtering can also affect performance.
Another factor to consider is the unique constraint. In the previous parts of this series, we created the users table, where each row contains a unique email.
1import {
2 serial,
3 text,
4 integer,
5 pgTable,
6} from 'drizzle-orm/pg-core';
7
8export const users = pgTable('users', {
9 id: serial('id').primaryKey(),
10 email: text('email').unique().notNull(),
11 name: text('name').notNull(),
12 password: text('password').notNull(),
13 addressId: integer('address_id')
14 .unique()
15 .references(() => addresses.id),
16});
17
18// ...
19
20export const databaseSchema = {
21 users,
22 // ...
23};In this scenario, each user must have a unique email. Hard deletes free up the email for reuse, but with soft deletes, the records remain in the database. Therefore, deleted users’ emails are not made available to others.
Soft deletes with the Drizzle ORM
A typical approach for soft deletes is to store the deletion date rather than just using a boolean flag.
1import { serial, text, pgTable, timestamp } from 'drizzle-orm/pg-core';
2
3export const categories = pgTable('categories', {
4 id: serial('id').primaryKey(),
5 name: text('title').notNull(),
6 deletedAt: timestamp('deleted_at', { withTimezone: true }),
7});
8
9// ...
10
11export const databaseSchema = {
12 categories,
13};Deleting entities
When deleting our categories, it’s important to correctly set the value of the deleted_at column. Fortunately, we can use the now() function built into SQL.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq, sql, and, isNull } from 'drizzle-orm';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 // ...
11
12 async delete(id: number) {
13 const deletedCategories = await this.drizzleService.db
14 .update(databaseSchema.categories)
15 .set({
16 deletedAt: sql`now()`,
17 })
18 .where(
19 and(
20 eq(databaseSchema.categories.id, id),
21 isNull(databaseSchema.categories.deletedAt),
22 ),
23 )
24 .returning();
25
26 if (deletedCategories.length === 0) {
27 throw new NotFoundException();
28 }
29 }
30}What’s important is that we use the isNull() to prevent deleting a category that’s already deleted. In a case such as that, we need to throw the NotFoundException.
Fetching entities
We also need to use the isNull function when fetching categories to filter out deleted ones.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq, and, isNull } from 'drizzle-orm';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 getAll() {
11 return this.drizzleService.db
12 .select()
13 .from(databaseSchema.categories)
14 .where(isNull(databaseSchema.categories.deletedAt));
15 }
16
17 async getById(categoryId: number) {
18 const category = await this.drizzleService.db.query.categories.findFirst({
19 with: {
20 categoriesArticles: {
21 with: {
22 article: true,
23 },
24 },
25 },
26 where: and(
27 eq(databaseSchema.categories.id, categoryId),
28 isNull(databaseSchema.categories.deletedAt),
29 ),
30 });
31
32 if (!category) {
33 throw new NotFoundException();
34 }
35
36 const articles = category.categoriesArticles.map(({ article }) => article);
37
38 return {
39 id: category.id,
40 name: category.name,
41 articles,
42 };
43 }
44
45 // ...
46}Thanks to using the isNull function, trying to fetch a category with a given ID that is deleted results in the 404 Not Found Error.
Updating entities
The soft deletes also affect how we update existing entities.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { CategoryDto } from './dto/category.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5import { eq, and, isNull } from 'drizzle-orm';
6
7@Injectable()
8export class CategoriesService {
9 constructor(private readonly drizzleService: DrizzleService) {}
10
11 async update(id: number, data: CategoryDto) {
12 const updatedCategories = await this.drizzleService.db
13 .update(databaseSchema.categories)
14 .set(data)
15 .where(
16 and(
17 eq(databaseSchema.categories.id, id),
18 isNull(databaseSchema.categories.deletedAt),
19 ),
20 )
21 .returning();
22
23 if (updatedCategories.length === 0) {
24 throw new NotFoundException();
25 }
26
27 return updatedCategories.pop();
28 }
29
30 // ...
31}Restoring removed entities
We sometimes might want to restore an entity we removed. Thankfully, that’s straightforward and as simple as setting the delete_at column to null.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async restore(id: number) {
11 const restoredCategories = await this.drizzleService.db
12 .update(databaseSchema.categories)
13 .set({
14 deletedAt: null,
15 })
16 .where(eq(databaseSchema.categories.id, id))
17 .returning();
18
19 if (restoredCategories.length === 0) {
20 throw new NotFoundException();
21 }
22
23 return restoredCategories.pop();
24 }
25
26 // ...
27}Summary
In this article, we implemented soft deletes and weighted their benefits and drawbacks. Soft deletes can improve the user experience by allowing the user to both delete and restore entities. However, they add complexity to our SQL queries. Despite this, soft deletes have applications and can be helpful in certain scenarios. Therefore, knowing how to implement them and when it makes sense is worth knowing.