Skip to main content
Add multiple rows using Sequelize bulkCreate() method

How To Add multiple rows using Sequelize bulkCreate() method

Last Updated: | By: JsTutorials Team JavaScript

This tutorial help to insert multiple rows to your SQL database table using bulkCreate() method. you may often come across scenarios where you need to insert multiple rows into a database table.

Sequelize provides the bulkCreate() method, which allows you to add multiple rows efficiently in a single operation. The data you want to insert into your table must be passed as an array of objects to the method.

You can also read: Delete Your Table Data by ID using Sequelize

What’s Sequelize

Sequelize is a popular Object-Relational Mapping (ORM) library for Node.js that provides an easy and intuitive way to interact with databases.

We will cover the following topics in this tutorial:

  • What is Sequelize Model.
  • How to create a table using Sequelize Model
  • How to create a table using sequelize-cli command
  • Add multiple rows using Sequelize bulkCreate() method

How To Create Table Using Sequelize

The findAll() from a Sequelize model helps to find all data of a table.

What’s Sequelize Model

The foundation of Sequelize is its models. A model is an abstraction that stands in for a database table. It is a class in Sequelize that extends Model.

The model provides Sequelize with information about the item it represents, including the name of the database table and the columns it contains (and their data types).

There are two ways you can create a table using Sequelize:

  • Using the Model.sync() method
  • Using sequelize-cli database migration system

Sequelize create table with sync() method

The Sequelize model you create using the sequelize.define() method comes with a sync() method that you can use to create a table. The table produced by the sync() method will comply to the column specification defined by the model (s).

By default, Sequelize will include the createdAt and updatedAt columns into your table so have added "timestamps: false", prevent to add columns into the table.

Let’s create an employee table using Employee Models:

const Employee = sequelize.define("Employee", {
id: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
employee_name: {
type: Sequelize.STRING,
},
employee_salary: {
type: Sequelize.INTEGER,
},
employee_age: {
type: Sequelize.INTEGER,
},
{
timestamps: false,
}
});
await Employee.sync();

The above code will create the database table if it doesn’t already exist.

The sync() method also accepts two options as described below:

  • sync({ force: true }) – This option force Sequelize to create a table, dropping it first if it already existed
  • sync({ alter: true }) – This option alters the columns and data types of an existing table to match the model

Sequelize create table with migrations

Sequelize also comes with a command line tool called sequelize-cli that provides support for migration of tables.

We’ll follow the following steps to migrate tables in sequelize:

Step 1: You need to install sequelize-cli globally using npm:

npm install -g sequelize-cli

Step 2: Let’s run the init command to generate several folders required by Sequelize:

npx sequelize-cli init

The folder structure:

  • config/ – This folder contains config file that takes your environment into account (production or development)
  • models/ – This folder contains all models for your project
  • migrations/ – This folder contains all migration files for you to run
  • seeders/ – This folder contains all seed files

Let’s, open the config/config.json file and add the right credentials to connect to your database.

Step 3: Create a new migration file with model:generate command. This command requires you to pass the model name and attributes.

npx sequelize-cli model:generate --name Employee --attributes employee_name:string,employee_salary:integer,employee_age:integer

The above command would generate a new migration and model files for the Employee model.

Step 4: Now, We’ll run the migration file with the db:migrate command as follows:

npx sequelize-cli db:migrate

The above command will create an Employee table into your database.

Adding multiple records with bulkCreate() method

The bulkCreate() method allows you to insert multiple records to your database table with a single function call. This method is designed to perform batch inserts.

When many-to-many relationships exist in a database and numerous records insert is required to preserve data integrity, the Sequelize method bulkCreate() can be quite useful. It also reduces the number of database round trips and improves performance.

The syntax for using the bulkCreate() method is as follows:

Model.bulkCreate(records, options)

  • Model: Refers to the Sequelize model representing the database table.
  • records: An array of objects, with each object representing a row to be inserted into the table.
  • options (optional): Additional configuration options for the bulk insert operation, such as transaction management.

Let’s dive into a practical example of the bulkCreate() method. I assumed you have a table called employees with the following information in it:

CREATE TABLE employee (
id int(11) NOT NULL COMMENT 'primary key',
employee_name varchar(100) NOT NULL COMMENT 'employee name',
employee_salary int(11) NOT NULL COMMENT 'employee salary',
employee_age int(11) NOT NULL COMMENT 'employee age'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable demo table';

The above SQL command will create a table into MySQL db.

You must first establish a connection to the database and build a model of the table in order to insert several rows into the employee table above:

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("database", "username", "password", {
  host: "localhost",
  dialect: /* one of "mysql" | "mariadb" | "postgres" | "mssql" */
});

// Creating the model
const Employee = sequelize.define("employee", {
  employee_name: {
    type: Sequelize.STRING
  },
  employee_salary: {
    type: Sequelize.INTEGER,
  },
  employee_age: {
    type: Sequelize.INTEGER,
  }
}, {
  timestamps: false
});

an array of objects. Each object would represent a single row for your table. we’ll use the object key as the column name, and the object value as the column value as like below.

Employee.bulkCreate([
{ employee_name: "Tiger Nixon", employee_salary:320800, employee_age:61},
{ employee_name: "Garrett Winters", employee_salary:170750, employee_age:63},
{ employee_name: "Ashton Cox", employee_salary:86000, employee_age:66},
]).then(() => console.log("Employee data have been saved"));

When you run the above JavaScript code, The data will be inserted into the employee table.

There are other options supported by the bulkCreate() method that let’s you control how Sequelize acts while inserting data. you can get full of options supported by bulkCreate() from the documentation

For example, you can ignore duplicate primary keys with the ignoreDuplicates option:

const emps = [
{ employee_name: "Tiger Nixon", employee_salary:320800, employee_age:61},
{ employee_name: "Garrett Winters", employee_salary:170750, employee_age:63},
{ employee_name: "Ashton Cox", employee_salary:86000, employee_age:66},
  // Add more user objects as needed
];
Employee.bulkCreate(emps, {ignoreDuplicates: true})
  .then(() => {
    console.log('Multiple users added successfully');
  })
  .catch((error) => {
    console.error('Error adding multiple users:', error);
  });

In this example, we define an array called emps, where each object represents an employee with properties like employee_name, employee_salary, and employee_age. We then call the bulkCreate() method on the Employee model and pass in the emps array as an argument. The method executes a single SQL query to insert all the users into the emps table.

Sequelize bulkCreate() returns NULL for primary keys

The bulkCreate() method only performs multiple INSERT statements to your SQL database, it will insert NULL when your primary key column doesn’t have the AUTO_INCREMENT attribute.

You need to assign the AUTO_INCREMENT attribute to the primary key column into the table.
OR

We can also pass the id value as explicitly for each row:

Employee.bulkCreate([
{ id: 1, employee_name: "Tiger Nixon", employee_salary:320800, employee_age:61},
{ id: 2, employee_name: "Garrett Winters", employee_salary:170750, employee_age:63},
{ id: 3, employee_name: "Ashton Cox", employee_salary:86000, employee_age:66},
],
{
ignoreDuplicates: true,
}
).then(() => console.log("Employee data have been saved"));

Leave a Reply

Your email address will not be published. Required fields are marked *