Sling Academy
Home/Node.js/Sequelize.js: How to Empty/Truncate a Table

Sequelize.js: How to Empty/Truncate a Table

Last updated: December 29, 2023

Introduction

Sequelize is a popular ORM (Object-Relational Mapper) for Node.js that supports various databases. It provides an abstraction to the SQL database interaction, which allows developers to write less database-specific query language – writing more consistent and easier-to-manage JavaScript code instead. One of the common tasks you might encounter is the need to empty a table – also known as ‘truncating’. In a development or testing environment, you may want to quickly remove all records from a specific table without deleting the table itself. This tutorial guides you through the process of truncating a table using Sequelize, from the basic to the more advanced configurations.

Throughout this tutorial, we’ll assume you have a basic understanding of Node.js and Sequelize, and that Sequelize is already set up to connect to your chosen database.

Getting Started with Truncating Tables

To begin with, let’s cover how to use the simplest form of the ‘truncate’ method in Sequelize on a table.

const {User} = require('./models');
// Assuming 'User' is a model that corresponds to your table...
User.truncate()
 .then(() => console.log('Table truncated successfully!'))
 .catch(err => console.error('Failed to truncate table:', err));

The above code will empty the entire ‘Users’ table by deleting all of its rows. Note that the primary key counter will also be reset, if the database and table support this feature.

Truncate with Options

Sequelize allows you to pass options to the truncate method. For instance, if you’d like to keep the primary key counter unchanged, you can do the following:

User.truncate({cascade: false})
 .then(() => console.log('Table truncated without resetting primary key counter.'))
 .catch(err => console.error('Failed to truncate table:', err));

Cascading options can be specifically handy if models have associated models and you want to ensure those are also affected when you truncate the base model.

Truncating Multiple Tables

In scenarios where multiple tables need to be truncated, you can manage this efficiently using promise handling features like Promise.all.

const {User, Product, Order} = require('./models');
Promise.all([
 User.truncate(),
 Product.truncate(),
 Order.truncate()
]).then(() => console.log('All tables truncated successfully!'))
  .catch(err => console.error('Failed to truncate tables:', err));

This parallel execution might be faster than doing each truncate one by one, as it doesn’t wait for each truncate operation to complete before starting the next one.

Truncating with Hooks

Sequelize also provides a way to run functions called ‘hooks’ automatically before or after certain actions. If you have defined hooks for the truncate action and wish to run those:

User.truncate({hooks: true})
 .then(() => console.log('Table truncated with hooks.'))
 .catch(err => console.error('Failed to truncate table:', err));

By default, hooks are called on truncate, but you can set {hooks: false} to skip executing them.

Conclusion

In this tutorial, we’ve walked through the basics of emptying tables using Sequelize, and looked at some variations to suit more advanced use cases. We covered the simplest form of the truncate method, options to alter its behaviour, handling multiple tables, and using hooks in the truncation process. Truncating via Sequelize adds another level of sophistication to your toolbelt, allowing you to manage your database with JavaScript comfortably. Remember to use this powerful operation with caution in production, as it removes all records from your table.

Happy coding!

Next Article: Sequelize.js: How to Delete a Record by ID

Previous Article: Sequelize.js: How to Rename a Table

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