Sling Academy
Home/Node.js/Working with Paranoid Tables in Sequelize

Working with Paranoid Tables in Sequelize

Last updated: December 29, 2023

Introduction

In this tutorial, we will explore the concept of paranoid tables in Sequelize and how to work with them. Paranoid tables allow you to keep records in the database even after they have been deleted, marking them with a deleted timestamp rather than actually removing them. This can be particularly useful for maintaining data integrity and allowing for a soft deletion strategy.

Setting Up

Before we dive into code examples, ensure that you have Sequelize installed and that you’ve set up a connection to your database. Once that’s done, you can go ahead and define your models. Here’s how to define a paranoid model:

const User = sequelize.define('User', {
  // Model attributes...
}, {
  paranoid: true
});

With the paranoid option set to true, Sequelize will automatically add a deletedAt attribute to your model.

Basic Usage of Paranoid Tables

When you delete a record from a paranoid table, Sequelize will not actually remove it from the database. Instead, it will set the deletedAt field to the current timestamp. Here’s how you can delete a record:

User.destroy({
  where: { id: 123 }
});

Retrieving records from a paranoid table will by default exclude entries with a non-null deletedAt. If you want to include the soft-deleted entries, you can use the following query:

User.findAll({
  where: { id: 123 },
  paranoid: false
});

Restoring Soft-Deleted Records

If you need to restore a soft-deleted record, Sequelize provides an easy method to do so:

User.restore({
  where: { id: 123 }
});

This will set the deletedAt attribute back to null, effectively restoring the record.

Advanced Concepts

When dealing with associations, paranoid tables can become a bit tricky. If you have associated models that are also paranoid, you’ll need to handle deletions and restorations carefully to maintain referential integrity. Here’s an example of how to handle cascading restores:

User.restore({
  where: { id: 123 },
  include: [{
    model: Post,
    paranoid: false
  }]
});

This will restore the user and all its associated posts, even the ones that are soft-deleted.

Final Words

In this tutorial, we’ve covered what paranoid tables are and how to work with them using Sequelize. We looked at soft deleting records, excluding and including soft-deleted entries in queries, restoring soft-deleted entries, and working with associations. Implementing paranoid tables can be a powerful feature for your applications, allowing for flexible data management and recovery options without losing historical data.

Next Article: Replication in Sequelize: A complete guide

Previous Article: SequelizeJS: How to reset auto-increment ID after deleting records

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates