Paranoid Tables in Sequelize ORM, Implementing Soft Delete

Setting up the project and database:

If you want to set up your sequelize project you can check out my previous article: One to Many Relationships in MYSQL Database Using Sequelize ORM.

If you ever browse Reddit and get the URL of a post that was deleted but still works and you can see the post and all the interactions that happened with it before deletion, this is an example of soft deletion. The information is still in the database but will be considered deleted. How do you implement this? Well, we will learn that today using Sequelize ORM.

Sequelize supports a feature called paranoid tables. The specialty about these tables is that when they are told to delete a record they never truly delete it. Instead, they add a special column called deleted, which holds the timestamp of when this deletion request was made.

In the project you have set up we will create a post.js file in the model’s folder. Today we will be interacting with the posts model, although the same can be applied to any schema of your choice. I would recommend you to code along.

In the post.js file we will first create a model for our post. It will include the post title, its author, its content, and an auto-incrementing id. Below is the syntax to do so.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
module.exports = (sequelize, DataTypes) => {
  const Post = sequelize.define("Post", {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    title: {
      type: DataTypes.STRING,
    },
    author: {
      type: DataTypes.STRING,
    },
    content: {
      type: DataTypes.STRING,
    },
  }, {
    paranoid: true
  });
  return Post;
};

If you see the following query as output then you did everything right so far.

Let’s create an API to add the data to our database. Then we will check whether the deletion is working or not.
Write this API in the index.js file right below the line where you import the db.

1
2
3
4
5
6
7
8
app.post('/post', async (req, res) => {
  try {
    const post = await db.Post.create(req.body);
    res.send(post);
  } catch (err) {
    res.send(err.message);
  }
})

Now open postman and send a post request to this URL http://localhost:3000/post with the following body.

1
2
3
4
5
{
  "title": "MY post",
  "author": "Topcoder",
  "content": "My first post here"
}

In response, you should see this result:

1
2
3
4
5
6
7
8
{
  "id": 1,
  "title": "MY post",
  "author": "Topcoder",
  "content": "My first post here",
  "updatedAt": "2021-10-06T05:49:25.317Z",
  "createdAt": "2021-10-06T05:49:25.317Z"
}

I made some more requests and added more entities. This is how the MYSQL workbench looks now.

As you can see, there is a deletedAt column, which means we are on the right track.
Now let’s test our soft delete functionality, in the same way we use normal delete. The following API will do the job:

1
2
3
4
5
6
7
8
9
10
11
12
app.delete('/post/:id', async (req, res) => {
  try {
    await db.Post.destroy({
      where: {
        id: req.params.id
      }
    });
    res.send('deleted');
  } catch (err) {
    res.send(err.message);
  }
});

Now in postman make a delete request to this URL and in params add the id of the post you want to delete.
This is how the request and the subsequent response look.

And, if you check your database this is what you’ll see.

A soft delete has happened. If you’ve come this far then give yourself a pat on the back.
Why was this the right method and how come doing this without an inbuilt feature wouldn’t work?
You can do this by simply adding an extra column yourself and updating the deletedAt value from null to current_timestamp whenever a delete request gets made. Let me give you a reason why all this effort is justified and this is the right way to perform the soft delete.
Let’s make a fetch API to understand this that will fetch all the posts in the database.

1
2
3
4
5
6
7
8
app.get('/post', async (req, res) => {
  try {
    const post = await db.Post.findAll();
    res.send(post);
  } catch (err) {
    res.send(err.message);
  }
});

Now make a get request to this URL http://localhost:3000/post and see the response.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[
  {
    "id": 2,
    "title": "Second Post",
    "author": "Topcoder",
    "content": "My second post here",
    "createdAt": "2021-10-06T05:52:02.000Z",
    "updatedAt": "2021-10-06T05:52:02.000Z",
    "deletedAt": null
    },
  {
    "id": 3,
    "title": "New Post",
    "author": "Topcoder",
    "content": "My another post here",
    "createdAt": "2021-10-06T05:52:12.000Z",
    "updatedAt": "2021-10-06T05:52:12.000Z",
    "deletedAt": null
    }
]

As you can see, the post which was soft deleted was not returned. This is all getting handled by Sequelize so you don’t need to worry about it.

This was all about soft delete. Now what if you really want to delete something from the database? Just add a {force: true} key value pair in your API like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
app.delete('/post/:id', async (req, res) => {
  try {
    await db.Post.destroy({
      where: {
        id: req.params.id
      },
      force: true
    });
    res.send('deleted');
  } catch (err) {
    res.send(err.message);
  }
});

This will delete the record from the database permanently.
Now you can use soft/hard delete wherever you like in your next project.

Have fun ✌