INSERT Data in Sequelize Tutorial

Welcome to this practical tutorial on how to insert data into your database using Sequelize. Just as you learned how to run SELECT queries to fetch data, you can also use Sequelize to add new records. Think of this process like planting seeds in a garden – you have several methods to ensure your garden (database) grows exactly the way you want it to.

The SQL INSERT Statement

Before diving into Sequelize, let’s quickly recall how you would insert data using plain SQL. The standard SQL INSERT statement looks like this:

INSERT INTO table_name
VALUES (column1_value, column2_value, column3_value);

For example, to insert a record into a dogs table, you might use:

INSERT INTO dogs (id, dog_name, breed)
VALUES (3, 'Fido', 'Dalmatian');

And to insert multiple records at once:

INSERT INTO dogs (dog_name, breed)
VALUES ('Fido', 'Dalmatian'),
       ('Maggie', 'Golden Retriever'),
       ('Toby', 'Poodle');

Using Sequelize to Insert Data

Sequelize offers several methods to insert data into your database. Let’s assume you have a Dog model defined as follows:

'use strict';
const { Model } = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class Dog extends Model {
    static associate(models) {
      // define association here
    }
  }
  Dog.init({
    dogName: DataTypes.TEXT,
    breed: {
      type: DataTypes.TEXT,
      allowNull: false
    }
  }, {
    sequelize,
    modelName: 'Dog',
  });
  return Dog;
};

Using this model, you can insert data in several ways.

Using build() and save() to Persist a Single Record

The first approach is a two-step process: build() creates an instance of the model (like preparing a seed) without saving it to the database, and save() persists that instance. This method is ideal if you need to modify or validate the instance before saving.

// Generate a new Dog instance using build
const newDog = Dog.build({ dogName: 'Fido', breed: 'Dalmatian' });

// At this point, newDog exists in memory but is not in the database
// You can perform additional operations or validations here if needed

// Save the newDog instance to the database
await newDog.save();

// Now, Fido has been inserted into the dogs table

Think of it like preparing a seed, checking its quality, and then planting it in the garden.

Using create() to Persist a Single Record

If you’re confident that no further manipulation is needed before saving, you can use the create() method. This method is a one-step process that both builds and saves the instance in a single command.

// This single command creates a new Dog instance and saves it to the database
const newDog = await Dog.create({ dogName: 'Fido', breed: 'Dalmatian' });

// Fido is now stored in the dogs table

Using create() is like using an automatic planter that plants your seed immediately.

Using bulkCreate() to Persist Multiple Records

When you need to insert multiple records at once, Sequelize provides the bulkCreate() method. This is particularly useful for seeding your database with a large amount of data in one go.

// Insert multiple Dog records at once using bulkCreate
const newDogs = await Dog.bulkCreate([
  { dogName: 'Fido', breed: 'Dalmatian' },
  { dogName: 'Maggie', breed: 'Golden Retriever' },
  { dogName: 'Toby', breed: 'Poodle' }
]);

// newDogs now contains all the inserted records

BulkCreate is like using a machine that plants many seeds simultaneously, saving you time and effort.

Common Error Messages and How to Handle Them

When inserting data, you might encounter errors. For example, if your table has timestamp columns (createdAt and updatedAt), using bulkInsert without manually providing these values will throw an error. You need to include these timestamps:

await queryInterface.bulkInsert('Dogs', [
  {
    dogName: 'Fido',
    breed: 'Dalmatian',
    createdAt: new Date(),
    updatedAt: new Date()
  }
], {});

Additionally, if your model defines validations (for instance, ensuring that the breed is a boolean, or that a field isn’t empty), errors will be thrown if the data doesn’t meet those validations. With Model.bulkCreate, you can enable validations:

await Dog.bulkCreate([
  {
    dogName: 'Fido',
    breed: 'Dalmatian' // If this doesn't pass validation, an error is thrown
  }
], { validate: true });

Handling these errors gracefully in your application is essential for debugging and maintaining data integrity.

Conclusion

In this tutorial, you learned that Sequelize offers multiple ways to insert data into your database:

build() and save(): A two-step process that allows for manipulation and validation before saving a record.
create(): A one-step method to build and persist a record immediately.
bulkCreate(): An efficient way to insert multiple records at once, ideal for seeding your database.

Each method has its use cases and benefits. For quick, straightforward insertions, create() is ideal. For more controlled insertions where you might need to adjust data or perform additional checks, using build() and then save() gives you that flexibility. And when you need to insert large volumes of data, bulkCreate() is your go-to method.

By understanding these methods, you can choose the best approach for your specific needs, ensuring that your data is inserted accurately and efficiently. Happy coding and planting your data seeds!