API with NestJS #65. Implementing soft deletes using MikroORM and filters

June 13, 2022

This entry is part 65 of 121 in the API with NestJS

In this article, we look into the filters feature and implement soft deletes. With them, we can mark an entity as deleted without removing it from the database permanently.

You can get the code from this article in this repository.

Introducing MikroORM filters

We can provide various filters when querying data using functions such as find and findOne. For example, let’s query only short posts using the LENGTH function built into PostgreSQL. To do that, we also need $lt to create a smart query condition.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository, expr } from "@mikro-orm/core";

import PostEntity from './post.entity';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

getShortPosts() {

return this.postRepository.find(

{

[expr('length(content)')]: {

$lt: '100',

{

populate: ['author', 'author.address', 'categories'],

);

}

// ...

}

Above, we use the expr helper to bypass a strict type check in the FilterQuery interface built into MikroORM. We need it, because there is no column named length(content).

Instead of the above approach, we can use the @Filter() decorator to predefine filter criteria and attach them to a class.

post.entity.ts

import {

Entity,

Property,

PrimaryKey,

Filter

} from '@mikro-orm/core';

@Entity()

@Filter({

cond: {

'length(content)': {

$lt: 100

}

})

class PostEntity {

@PrimaryKey()

id: number;

@Property()

title: string;

@Property()

content: string;

// ...

}

export default PostEntity;

The cond property works in a similar way to queries in functions such as find and findOne.

Thanks to attaching our short filter, we can now use it in our queries.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository } from "@mikro-orm/core";

import PostEntity from './post.entity';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

getPosts(getOnlyShortPosts?: boolean) {

return this.postRepository.findAll({

populate: ['author', 'author.address', 'categories'],

filters: {

short: getOnlyShortPosts

}

});

}

Adding arguments

Besides simple boolean-based filters, we can pass additional arguments through an object. Let’s make our previous filter more generic by adding the option of specifying the maximum content length.

@Filter({

cond: ({ maximumContentLength }: { maximumContentLength?: number }) => {

if (maximumContentLength === undefined) {

return {};

}

return {

'length(content)': {

$lt: maximumContentLength,

};

})

Thanks to the above, we can now pass the content length when querying posts.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository } from '@mikro-orm/core';

import PostEntity from './post.entity';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

getPosts(maximumContentLength?: number) {

return this.postRepository.findAll({

populate: ['author', 'author.address', 'categories'],

filters: {

maximumContentLength: { maximumContentLength },

});

}

// ...

}

Enabling filters by default

So far, we’ve been creating filters we can use through functions such as find and findOne. Instead of that, we can define filters toggled by default. For example, let’s create a filter that skips posts that have an empty string for content.

@Filter({

cond: () => ({

'length(content)': {

$gt: 0

}

}),

default: true

})

Even though our skipEmptyPosts filter works by default, we can turn it off for a particular query if we need to.

getPosts() {

return this.postRepository.findAll({

populate: ['author', 'author.address', 'categories'],

filters: {

skipEmptyPosts: false

}

});

}

Implementing soft deletes

The idea behind soft deletes is that instead of permanently removing records from the database, we only mark them as deleted. Thanks to doing so, we can always restore the data deleted previously. In addition, it can help us increase the user experience of our application by creating an undo button, for example.

CREATE TABLE posts (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

title text,

content text,

is_deleted boolean DEFAULT false

)

If you want to know more about the identity column above, check out Serial type versus identity columns in PostgreSQL and TypeORM

If we want to mark a post as deleted, we need to change is_deleted to true.

UPDATE posts

SET is_deleted = true

WHERE id = 1

A disadvantage of soft deletes is that we always need to consider the is_deleted column when performing various other queries.

Fortunately, MikroORM will be able to do this for us. Still, it takes some additional computing power to filter out deleted entities.

1 2	SELECT * from posts WHERE is_deleted = false

There is also an important catch with columns marked as unique. Let’s imagine the following scenario:

create a posts table where every title needs to be unique,
insert a post with a given title,
delete the above post with a soft delete,
try to insert a post with the same title again.

Doing the above would result in an error, unfortunately.

Soft deletes with MikroORM

To use MikroORM to achieve soft deletes, we can create a custom decorator with a filter. While on it, we can create a deletedAt column with the deletion date instead of a boolean flag.

If you want to know more about handling dates with PostgrteSQL, check out Managing date and time with PostgreSQL and TypeORM

withSoftDelete.ts

import { Filter } from '@mikro-orm/core';

const WithSoftDelete = (): ClassDecorator => {

return Filter({

cond: {

deletedAt: null,

default: true,

});

};

export default WithSoftDelete;

Thanks to writing deletedAt: null above, we filter out all posts marked as deleted.

post.entity.ts

import {

Entity,

Property,

PrimaryKey,

Index,

} from '@mikro-orm/core';

import WithSoftDelete from '../utils/withSoftDelete';

@Entity()

@WithSoftDelete()

class PostEntity {

@PrimaryKey()

id: number;

@Property()

title: string;

@Property()

content: string;

@Index()

@Property({ nullable: true, type: 'timestamptz' })

deletedAt?: Date;

// ...

}

export default PostEntity;

Please notice that we use the @Index() decorator above on the deletedAt property. Thanks to doing that, we improve the performance of various queries. Since many of our queries will include checking if a post is deleted, creating an index might be a good idea.

If you want to know more about indexes, check out API with NestJS #14. Improving performance of our Postgres database with indexes

We also need to add a new method to our service that sets the value of the deletedAt column.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository } from '@mikro-orm/core';

import PostEntity from './post.entity';

import PostNotFoundException from './exceptions/postNotFound.exception';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

async getPostById(id: number) {

const post = await this.postRepository.findOne({

id,

});

if (!post) {

throw new PostNotFoundException(id);

}

return post;

}

async softDeletePost(id: number) {

const existingPost = await this.getPostById(id);

existingPost.deletedAt = new Date();

await this.postRepository.persistAndFlush(existingPost);

}

// ...

}

Fetching deleted posts

We can modify our filter to accept arguments if we need to fetch the deleted posts or all posts.

withSoftDelete.ts

import { Filter } from '@mikro-orm/core';

interface FilterArguments {

getAll?: boolean;

getOnlyDeleted?: boolean;

}

const WithSoftDelete = (): ClassDecorator => {

return Filter({

cond: ({ getAll, getOnlyDeleted }: FilterArguments = {}) => {

if (getAll) {

return {};

}

if (getOnlyDeleted) {

return {

deletedAt: {

$ne: null,

};

}

return {

deletedAt: null,

};

default: true,

});

};

export default WithSoftDelete;

We need to pass an additional argument to the filter to get a deleted post.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository } from '@mikro-orm/core';

import PostEntity from './post.entity';

import PostNotFoundException from './exceptions/postNotFound.exception';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

async getDeletedPost(id: number) {

const post = await this.postRepository.findOne(

{

id,

{

filters: {

softDelete: {

getOnlyDeleted: true,

);

if (!post) {

throw new PostNotFoundException(id);

}

return post;

}

// ...

}

Restoring deleted posts

The getDeletedPost method we’ve created above can come in handy when restoring a deleted post. We need to set the deletedAt column back to null to do that.

posts.service.ts

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

import { InjectRepository } from '@mikro-orm/nestjs';

import { EntityRepository } from '@mikro-orm/core';

import PostEntity from './post.entity';

import PostNotFoundException from './exceptions/postNotFound.exception';

@Injectable()

export class PostsService {

constructor(

@InjectRepository(PostEntity)

private readonly postRepository: EntityRepository<PostEntity>,

) {}

async getDeletedPost(id: number) {

const post = await this.postRepository.findOne(

{

id,

{

filters: {

softDelete: {

getOnlyDeleted: true,

);

if (!post) {

throw new PostNotFoundException(id);

}

return post;

}

async restorePost(id: number) {

const existingPost = await this.getDeletedPost(id);

existingPost.deletedAt = null;

await this.postRepository.persistAndFlush(existingPost);

return existingPost;

}

// ...

}

Summary

In this article, we’ve gone through the feature of filters in MikroORM. While we might prefer to write the query-related logic in a service, creating reusable filters might be a useful pattern. Besides that, we’ve learned about what soft delete is and what are its advantages and disadvantages. We’ve also created a reusable filter that helps us implement soft deletes with MikroORM.

Series Navigation<< API with NestJS #64. Transactions with PostgreSQL and MikroORMAPI with NestJS #66. Improving PostgreSQL performance with indexes using MikroORM >>

1 Comment

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

bryan-gc

1 year ago

You could add a global filter in the module configuration to get the same behavior of filtering all soft deleted rows. I think the getOnlyDeleted filter could be added too. Anyway, this approach or the one from the post doesn’t work with pivot tables.

entities: ['./dist/**/entities/*.entity.js'],

entitiesTs: ['./src/**/entities/*.entity.ts'],

port: 5432,

type: 'postgresql',

dbName: 'db',

host: 'postgresql',

user: 'user',

password: 'password',

debug: ['query', 'query-params', 'schema', 'info'],

logger: console.log.bind(console),

flushMode: FlushMode.COMMIT,

softDeleteFilter: {

cond: { deleted_at: null },

For example this

const user = await this.userRepository.findOne({

userRefreshTokens: { refresh_token: refreshToken },

where userRefreshTokens is a relation of @OneToMany would execute this query

1	select "u0".* from "user" as "u0" left join "user_refresh_token" as "u1" on "u0"."id" = "u1"."user_id" where "u0"."deleted_at" is null and "u1"."refresh_token" = 'refresh_token' limit 1

I need to add the filter manually to get the same behavior with relationships

userRefreshTokens: { refresh_token: refreshToken, deleted_at: null },

1	select "u0".* from "user" as "u0" left join "user_refresh_token" as "u1" on "u0"."id" = "u1"."user_id" where "u0"."deleted_at" is null and "u1"."refresh_token" = 'refresh_token' and "u1"."deleted_at" is null limit 1