API with NestJS #158. Soft deletes with the Drizzle ORM

July 22, 2024

This entry is part 158 of 177 in the API with NestJS

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.

CREATE TABLE categories (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

name text NOT NULL,

is_deleted boolean DEFAULT false

);

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.

INSERT into categories (

name

) VALUES (

'JavaScript'

)

RETURNING *

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.

UPDATE categories

SET is_deleted = true

WHERE id = 1

RETURNING *

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.

1 2	SELECT * from categories WHERE is_deleted = false

Advantages

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.

database-schema.ts

import {

serial,

text,

integer,

pgTable,

} from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

email: text('email').unique().notNull(),

password: text('password').notNull(),

addressId: integer('address_id')

.unique()

.references(() => addresses.id),

});

// ...

export const databaseSchema = {

users,

// ...

};

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.

database-schema.ts

import { serial, text, pgTable, timestamp } from 'drizzle-orm/pg-core';

export const categories = pgTable('categories', {

id: serial('id').primaryKey(),

deletedAt: timestamp('deleted_at', { withTimezone: true }),

});

// ...

export const databaseSchema = {

categories,

};

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.

categories.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq, sql, and, isNull } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

// ...

async delete(id: number) {

const deletedCategories = await this.drizzleService.db

.update(databaseSchema.categories)

.set({

deletedAt: sql`now()`,

})

.where(

and(

eq(databaseSchema.categories.id, id),

isNull(databaseSchema.categories.deletedAt),

)

.returning();

if (deletedCategories.length === 0) {

throw new NotFoundException();

}

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.

categories.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq, and, isNull } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

getAll() {

return this.drizzleService.db

.select()

.from(databaseSchema.categories)

.where(isNull(databaseSchema.categories.deletedAt));

}

async getById(categoryId: number) {

const category = await this.drizzleService.db.query.categories.findFirst({

with: {

categoriesArticles: {

with: {

article: true,

where: and(

eq(databaseSchema.categories.id, categoryId),

isNull(databaseSchema.categories.deletedAt),

});

if (!category) {

throw new NotFoundException();

}

const articles = category.categoriesArticles.map(({ article }) => article);

return {

id: category.id,

articles,

};

}

// ...

}

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.

categories.service.ts

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

import { CategoryDto } from './dto/category.dto';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq, and, isNull } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

async update(id: number, data: CategoryDto) {

const updatedCategories = await this.drizzleService.db

.update(databaseSchema.categories)

.set(data)

.where(

and(

eq(databaseSchema.categories.id, id),

isNull(databaseSchema.categories.deletedAt),

)

.returning();

if (updatedCategories.length === 0) {

throw new NotFoundException();

}

return updatedCategories.pop();

}

// ...

}

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.

categories.service.ts

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

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

async restore(id: number) {

const restoredCategories = await this.drizzleService.db

.update(databaseSchema.categories)

.set({

deletedAt: null,

})

.where(eq(databaseSchema.categories.id, id))

.returning();

if (restoredCategories.length === 0) {

throw new NotFoundException();

}

return restoredCategories.pop();

}

// ...

}

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.

Series Navigation<< API with NestJS #157. Handling JSON data with PostgreSQL and the Drizzle ORMAPI with NestJS #159. Date and time with PostgreSQL and the Drizzle ORM >>