Soft deletes with the Drizzle ORM

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.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(), name: text('name').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(), name: text('title').notNull(), 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, name: category.name, 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. The post API with NestJS #158. Soft deletes with the Drizzle ORM appeared first on Marcin Wanago Blog - JavaScript, both frontend and backend.