Adding multiple rows at once using Sequelize bulkCreate() method

When you need to insert multiple rows to your SQL database table, you can use the Sequelize bulkCreate() method.

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

The data you want to insert into your table must be passed as an array of objects to the method.

Let’s see an example of the bulkCreate() method in action.

Suppose you have a table named Users with the following description:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int          | NO   | PRI | NULL    | auto_increment |
| firstName | varchar(255) | YES  |     | NULL    |                |
| lastName  | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

To insert many rows into the Users table above, you need to connect to the database and create a model of the table first:

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

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

// Creating the model
const User = sequelize.define("User", {
  firstName: {
    type: Sequelize.STRING
  },
  lastName: {
    type: Sequelize.STRING,
  }
}, {
  timestamps: false
});

Now that you have the model of the table, you can call the bulkCreate() method on the User model as follows:

User.bulkCreate([
  { firstName: "Nathan" },
  { firstName: "Jack" },
  { firstName: "John" },
]).then(() => console.log("Users data have been saved"));

The data you want to insert into your table must be in the form of an array of objects. Each object would represent a single row for your table.

You need to use the object key as the column name, and the object value as the column value as shown above.

When you run the above JavaScript code, the data in the Users table would look as follows:

+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | Nathan    | NULL     |
|  2 | Jack      | NULL     |
|  3 | John      | NULL     |
+----+-----------+----------+

If you want to insert a value to the lastName column as well, the bulkCreate() argument should look as follows:

User.bulkCreate([
  { firstName: "Nathan", lastName: "Sebhastian" },
  { firstName: "Jack", lastName: "Stark" },
  { firstName: "John", lastName: "Snow" },
]).then(() => console.log("Users data have been saved"));

The table data would then look as follows:

+----+-----------+------------+
| id | firstName | lastName   |
+----+-----------+------------+
|  1 | Nathan    | NULL       |
|  2 | Jack      | NULL       |
|  3 | John      | NULL       |
|  4 | Nathan    | Sebhastian |
|  5 | Jack      | Stark      |
|  6 | John      | Snow       |
+----+-----------+------------+

The bulkCreate() method also support several options that you can use to manipulate the behavior of Sequelize when inserting the data.

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

User.bulkCreate(
  [
    { id: 1, firstName: "Nathan", lastName: "Sebhastian" },
    { id: 1, firstName: "Jack", lastName: "Stark" },
    { id: 1, firstName: "John", lastName: "Snow" },
  ],
  {
    ignoreDuplicates: true,
  }
).then(() => console.log("Users data have been saved"));

To define the options, you need to pass a second argument to the method in the form of an object.

The ignoreDuplicates option will cause Sequelize to perform an INSERT IGNORE instead of the regular INSERT statements.

For a full list of options supported by bulkCreate() method, you can visit the documentation.

Sequelize bulkCreate() returns NULL for primary keys

Because 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.

To resolve this issue, you can either define the primary key values manually in each object.

In the example below, the Users table primary key column id value is explicitly defined for each row:

User.bulkCreate([
  { id: 1, firstName: "Nathan", lastName: "Sebhastian" },
  { id: 2, firstName: "Jack", lastName: "Stark" },
  { id: 3, firstName: "John", lastName: "Snow" },
]);

If you don’t want to add the primary key values manually, then you need to assign the AUTO_INCREMENT attribute to the primary key column.

For MySQL, you can read this tutorial: MySQL AUTO_INCREMENT attribute explained

Now you have learned how to insert many rows at once using Sequelize bulkCreate() method. Nice work! 👍