When working on our REST APIs, we usually focus on implementing the four fundamental operations: creating, reading, updating, and deleting (CRUD). Deleting entities is a common feature in many web applications. The simplest way to do it is by permanently deleting rows from the database. In this article, we use Kysely to explore the idea of soft deletes that enable us to keep deleted entities within the database. The purpose of soft deletes The simplest way to add the soft delete feature is through a boolean flag.CREATE TABLE comments ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, content text NOT NULL, article_id int REFERENCES articles(id) NOT NULL, author_id int REFERENCES users(id) NOT NULL, is_deleted boolean DEFAULT false )In the above code, we use the DEFAULT keyword. This means that the is_deleted flag is set to false by default whenever we insert an entity into our database.INSERT into comments ( content, article_id, author_id ) VALUES ( 'An interesting article!', 1, 1 ) RETURNING * We don’t use the DELETE keyword when we want to do a soft delete on the record above. Instead, we don’t delete it permanently. To achieve that, we change the value in the is_deleted column.UPDATE comments SET is_deleted = true WHERE id = 1The important thing is that implementing soft deletes impacts various queries. For instance, we need to consider it when retrieving the list of all entities.SELECT * FROM comments WHERE is_deleted = false Soft delete pros The most apparent benefit of soft deletes is that we can quickly recover the deleted entities. While backups can also do this, soft deletes provide a better user experience. A practical example is an undo button that sets the is_deleted flag back to false. We can also retrieve the deleted records from the database, even though we’ve marked them as removed. This can be helpful when we want to create a report that includes all our records, for instance. Soft deletes can also come in handy when handling relationships. For instance, permanently deleting a record referenced in another table can lead to a foreign constraint violation. This doesn’t occur with soft deletes because we don’t remove the entities from the database. If you want to learn more about constraints, check out API with NestJS #124. Handling SQL constraints with Kysely Soft delete cons A significant drawback of soft deletes is that we have to account for them in all associated queries. If we retrieve our data and overlook filtering by the is_deleted column, we could provide the data the users shouldn’t have access to. Implementing this filtering can also impact our performance. Another important thing to consider is the unique constraint. Let’s examine the users table we defined in one of the earlier parts of this series. 20230813165809_add_users_table.ts import { Kysely } from 'kysely'; export async function up(database: Kysely): Promise { await database.schema .createTable('users') .addColumn('id', 'serial', (column) => { return column.primaryKey(); }) .addColumn('email', 'text', (column) => { return column.notNull().unique(); }) .addColumn('name', 'text', (column) => { return column.notNull(); }) .addColumn('password', 'text', (column) => { return column.notNull(); }) .execute(); } export async function down(database: Kysely): Promise { await database.schema.dropTable('users').execute(); }In the situation mentioned above, we require a unique email for each user. With hard deletes, deleting users would free up their email for others to use. However, with soft deletes, we don’t remove records from the database, so deleting users this way doesn’t make their emails available to others. Implementing soft deletes with Kysely A commonly used approach for soft deletes involves storing the deletion date instead of a simple boolean flag. 20231015202921_add_comments_table.ts import { Kysely } from 'kysely'; export async function up(database: Kysely): Promise { await database.schema .createTable('comments') .addColumn('id', 'serial', (column) => { return column.primaryKey(); }) .addColumn('content', 'text', (column) => { return column.notNull(); }) .addColumn('article_id', 'integer', (column) => { return column.references('articles.id'); }) .addColumn('author_id', 'integer', (column) => { return column.references('users.id'); }) .addColumn('deleted_at', 'timestamptz') .execute(); } export async function down(database: Kysely): Promise { await database.schema.dropTable('users').execute(); } If you’re interested in learning more about dates in PostgreSQL, take a look at Managing date and time with PostgreSQL and TypeORM Besides creating the migration, we also need to design an appropriate interface. commentsTable.ts import { Generated } from 'kysely'; export interface CommentsTable { id: Generated; content: string; author_id: number; article_id: number; deleted_at: Date | null; }We also need to add it to our Tables interface. database.ts import { ArticlesTable } from '../articles/articlesTable'; import { Kysely } from 'kysely'; import { UsersTable } from '../users/usersTable'; import { AddressesTable } from '../users/addressesTable'; import { CategoriesTable } from '../categories/categoriesTable'; import { CategoriesArticlesTable } from '../categories/categoriesArticlesTable'; import { CommentsTable } from '../comments/commentsTable'; export interface Tables { articles: ArticlesTable; users: UsersTable; addresses: AddressesTable; categories: CategoriesTable; categories_articles: CategoriesArticlesTable; comments: CommentsTable; } export class Database extends Kysely {} Besides the above, we must create a model to handle the data returned from the database. comment.model.ts export interface CommentModelData { id: number; content: string; author_id: number; article_id: number; deleted_at: Date | null; } export class Comment { id: number; content: string; authorId: number; articleId: number; deletedAt: Date | null; constructor(commentData: CommentModelData) { this.id = commentData.id; this.content = commentData.content; this.authorId = commentData.author_id; this.articleId = commentData.article_id; this.deletedAt = commentData.deleted_at; } } Creating records To create an entity, we first need to define a Data Transfer Object that validates the data coming from the user. comment.dto.ts import { IsString, IsNotEmpty, IsNumber } from 'class-validator'; export class CommentDto { @IsString() @IsNotEmpty() content: string; @IsNumber() articleId: number; }Please notice that we are not allowing the users to set the value of the deleted_at column. Instead, we want it to be null by default. comments.repository.ts import { BadRequestException, Injectable } from '@nestjs/common'; import { Comment } from './comment.model'; import { CommentDto } from './comment.dto'; import { Database } from '../database/database'; import { PostgresErrorCode } from '../database/postgresErrorCode.enum'; import { isDatabaseError } from '../types/databaseError'; @Injectable() export class CommentsRepository { constructor(private readonly database: Database) {} async create(commentData: CommentDto, authorId: number) { try { const databaseResponse = await this.database .insertInto('comments') .values({ content: commentData.content, author_id: authorId, article_id: commentData.articleId, }) .returningAll() .executeTakeFirstOrThrow(); return new Comment(databaseResponse); } catch (error) { if ( isDatabaseError(error) && error.code === PostgresErrorCode.ForeignKeyViolation ) { throw new BadRequestException('Article not found'); } throw error; } } // ... }Above, we look for the foreign key violations to detect if the user tried to create a comment for an article that does not exist. Deleting records A critical aspect of soft deletes is managing the DELETE method correctly. comments.controller.ts import { ClassSerializerInterceptor, Controller, Delete, Param, UseGuards, UseInterceptors, } from '@nestjs/common'; import FindOneParams from '../utils/findOneParams'; import { CommentsService } from './comments.service'; import { JwtAuthenticationGuard } from '../authentication/jwt-authentication.guard'; @Controller('comments') @UseInterceptors(ClassSerializerInterceptor) export class CommentsController { constructor(private readonly commentsService: CommentsService) {} @Delete(':id') @UseGuards(JwtAuthenticationGuard) async delete(@Param() { id }: FindOneParams) { await this.commentsService.delete(id); } // ... } The query in our repository should correctly set the value for the delete_at column. One approach to achieve this is using the now() function built into PostgreSQL, which provides the current date and time and the timezone. comments.repository.ts import { Injectable, NotFoundException } from '@nestjs/common'; import { Comment } from './comment.model'; import { Database } from '../database/database'; import { sql } from 'kysely'; @Injectable() export class CommentsRepository { constructor(private readonly database: Database) {} async delete(id: number) { const databaseResponse = await this.database .updateTable('comments') .set({ deleted_at: sql`now()`, }) .where('id', '=', id) .where('deleted_at', 'is', null) .returningAll() .executeTakeFirst();