How To Add multiple rows using Sequelize bulkCreate() method

This tutorial help to insert multiple rows to your SQL database table using bulkCreate() method. The data you want to insert into your table must be passed as an array of objects to the method.

Sequelize bulkCreate()

The bulkCreate() method allows you to insert multiple records to your database table with a single function call.

When many-to-many relationships exist in a database and a numerous records insert is required to preserve data integrity, the Sequelize method bulkCreate() can be quite useful.

See how the bulkCreate() method is used in the following example. Say 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';

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 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:

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}, ], { ignoreDuplicates: true, } ).then(() => console.log("Employee data have been saved"));

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"));

Originally published at https://www.js-tutorials.com on September 25, 2022.