Sling Academy
Home/Node.js/Sequelize.js: How to insert multiple records at once

Sequelize.js: How to insert multiple records at once

Last updated: December 29, 2023

Overview

Sequelize is a popular Node.js ORM for managing relational databases. One common task when interacting with databases is inserting multiple records efficiently. Sequelize provides mechanisms for bulk-insert operations which are far more efficient than inserting records one by one. In this article, we will walk through the process of inserting multiple records into a database using Sequelize.

We will start with basic bulk insertion, explore various options and configurations, handle validations, and conclude with transaction management during bulk inserts. By the end of this tutorial, you will be able to perform multiple record insertions with confidence and efficiency while using Sequelize.

Basic Bulk Insertion

To perform a basic bulk insert with Sequelize, you use the bulkCreate method on a model. The method takes an array of objects, each representing a record you want to insert into the database.

const User = require('./models/user');

// Array of user objects to insert
c onst users = [
    { name: 'Alice', email: '[email protected]' },
    { name: 'Bob', email: '[email protected]' },
    ...
];

User.bulkCreate(users)
    .then(() => console.log('Users have been inserted'))
    .catch(err => console.error('Failed to insert users', err));

This simple example demonstrates the most straightforward approach to inserting multiple records. However, when diving deeper into bulk operations, you will often need to handle more complex scenarios including setting defaults, managing insert conflicts, and paying attention to validation.

Handling Defaults and Override

When performing bulk inserts, you might have fields with default values or fields that you want to override for all records inserted. Sequelize s bulkCreate method provides options to achieve this.

// Assume createdAt and updatedAt are set to default to the current date and time
c onst usersWithDefaults = [...];

User.bulkCreate(usersWithDefaults, {
    fields: ['name', 'email'], // Specify fields to insert to ignore the rest
    def aultFields: ['createdAt', 'updatedAt'] // Fields you want to set defaults
})
    .then() // handle success
    .catch(); // handle error

Advanced: Handling Insert Conflicts

In applications where you need to maintain the uniqueness of certain fields or handle conflicts elegantly when they happen, Sequelize provides options inside bulkCreate to specify this behavior.

// Let's say we want to avoid inserting a user with an email that already exists
User.bulkCreate(users, {
    updateOnD uplicate: ['name'] // Specify the fields to update if a duplicate is found
})
    .then() // Handle success
    .catch(); // Handle error

This will perform an upsert operation. If a record with a duplicate key is found, it will update the specified fields rather than failing to insert.

Validations and Data Integrity

When inserting multiple records, one invalid record can cause the entire operation to fail. Sequelize allows you to manage validations and transactions to ensure data integrity during bulk inserts.

// Perform a bulk insert with validation
User.bulkCreate(users, {validate: true})
    .then() // handle success
    .catch(); // handle error

Bulk Insert with Transactions

Transactions are crucial when you have multiple operations that must succeed or fail as a whole. Sequelize’s transaction support is robust and can be integrated with bulk operations.

const sequelize = require('./your-sequelize-instance');

sequelize.transaction((t) => {
    return User.bulkCreate(users, { transaction: t })
        .then() // handle success
        .catch(); // handle error
});

Performance Considerations

While bulkCreate dramatically improves performance over individual inserts, there are still considerations to keep in mind. These involve index impacts, memory usage, and potential database timeouts, which require tailored solutions based on your application and database setup.

Conclusion

In conclusion, Sequelize’s bulkCreate method is a powerful tool for inserting multiple records at once. It is important to understand its behavior, options, and implications to effectively manage large datasets and ensure the integrity of your database operations. With proper usage and understanding, bulkCreate can be an invaluable asset in your toolkit for working with Sequelize and relational databases.

Next Article: Sequelize: How to Update a Record by ID

Previous Article: Sequelize Upsert: How to insert or update a record in one query

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