Let's say we are working on a web application that stores students information such as their name, email, and password. As the application grows and evolves, we may need to change the way this information is stored in the database. For example, we may want to add a new field to store the student's age or change the datatype of a column. This is where migrations come in, database migrations are files that can be tracked and used to create and update table schema in our database. When working with a large team or project, migrations make sharing and tracking table updates much easier.

While seeders are useful in cases where we need to populate the database with initial data, such as default settings or sample data for testing purposes. Seeders are scripts that define the initial data to be inserted into the database. They help us quickly set up the database with the data we need to start working on our application.

Overall, migrations and seeders are important tools for managing changes to the database schema and initial data in a Node.js application. In this article, we'll look at how to create database tables and store data in them using Sequelize migrations and seeders.

Prerequisites

Before delving into this article, readers should have the following.

• a good understanding of Node.js and Sequelize
• install PostgreSQL in your machine

Setting up Sequelize Application

To begin we will be creating a student-register application using Sequelize and Node.js.

Create a new folder student-register this is where our application will live. Firstly we will initiallize Node.js in our application by running the following command.

npm init -y

Next, install all the required dependencies for our application by running the following command in the application terminal:

npm install sequelize pg pg-hstore

Install npm package Sequelize-CLI globally.

npm install -g sequelize-cli

The above command installs

• pg: this is a Node.js nonblocking PostgreSQL client. pg provides a collection of Node.js modules responsible for creating applications database connection.

• pg-hstore: this implements the hstore datatype for storing sets of key-value pairs within a single PostgreSQL value.

• sequelize: A Node.js ORM tool.

• sequelize-cli: Using the terminal in Windows, MacOS, or Linux. sequelize-cli can be used to create a database and generate models, migrations, and seed files quickly. We installed the CLI tool globally because it is used from the command line.

Before we can use the Sequelize features, we must first initialize our application.

Run the code below to load Sequelize configurations into our student-register application.

sequelize init

This command will generate four new folders in our application directory:

├── config
│   └── config.json
├── migrations
├── models
│   └── index.js
├── seeders
└── package.json

Configure Database

The config folder contains a config.json file, which is used to connect our database to the application using the credentials we provided. For this application, we will be making use of a Postgres database.

We can configure different environments using the generated config/config.json file (e.g. test, development, and production).

• development default credentials are used here
• test test environment credentials
• production for production environment

We will focus on using the development environment

Change the development settings in config/config.json to correspond to your Postgres database setup.

Copy and Paste the below code in the config/config.json file. Change the value #yourDatabasePassword to your Postgres DB password:

{
  "development": {
    "username": "postgres",
    "password": #yourDatabasePassword,
    "database": "student-register",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres",
},
 "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres",
  },
}

Ensure that you create a student-register database in your Postgres DB and that you've entered the correct credentials and dialect, at the very least for the development credential.

Creating Sequelize Model and Migration

Once our database credentials and dialect are defined in the config.json file, we can now create a Sequelize model and migration for our application.

The Sequelize model allows us to connect to, and query the database table from our application, whereas the Sequelize migration creates a table or updates an existing table on the database server.

Using Sequelize-cli command: model:generate we can create both models and migrations at the same time.

The model:generate command will generates two files:

• a model file
• and a corresponding migration file.

The migration file created is a simple JavaScript file with two functions:

• up(): this function defines the table name and its columns and,
• down(): function which simply drops the table or undo what the up() function has done.

The model:generate command has a couple handy options flags

• --name: table name
• --attributes: these let us define the table's properties and datatypes. We can add more than one attribute by separating them with a comma ,.

Now we are going to create a model and migration file for our student-register database. By running the following command in the terminal:

sequelize model:generate --name Student --attributes first_name:string,last_name:string,course:text

The above command will give the following output:

New model was created at /student-register/models/student.js .
New migration was created at /student-register/migrations/20230306004515-create-student.js .

A student model file will be created for us in the /models directory as well as a migration ...-create-student file in the /migrations directory.

Let us now examine the migration file. If you look closely at the file name, you will see the timestamp of when the migration file was created. This is done on purpose so that Sequelize can run migration files in the order in which they were created.

The migration files keep track of the state of the database table schemas, and new migrations should be created whenever your models are changed. When making changes to your models, always commit related migration files.

The following is the skeleton of our student migration file:

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable('Students', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      first_name: {
        type: Sequelize.STRING
      },
      last_name: {
        type: Sequelize.STRING
      },
      course: {
        type: Sequelize.TEXT
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('Students');
  }
};

queryInterface used in the code above is an instance of the QueryInterface class that Sequelize uses to send instructions to the database. We can see the full list of available methods for the QueryInterface class in the Sequelize documentation.

Above we will also notice Sequelize migration added two new columns createdAt and updatedAt. The timestamp of when a new row was inserted is stored in the createdAt column, while the timestamp of when the row was last updated is stored in the updatedAt column.

Our student model and migration file are all set and ready for use.

N:B: Sequelize solely uses Model files to represent its tables. While migration files show all modifications to that model. Think of migrations as a log or a commit for database changes.

Running Sequelize Migrations

Up to this point, nothing has been added to our database. The Student model now has the necessary model and migration files. To create the Student model (table) in the database, we will use Sequelize-CLI command db:migrate

sequelize-cli db:migrate

This command will do the following:

• Checks for (or creates) a table called SequelizeMeta in the database. This table records which migrations have been performed on the current database.
• It will then search for migration files that are yet to be executed. This is accomplished by comparing the migration file name in our application to the SequelizeMeta table content. In our case, it will run the XXXXXXXXXXXXXX-create-student.js migration, which we created in the last session.
• Next, it creates a database table called 'Student' with all of the columns specified in the migration file.

The output of the db:migrate command should look like this:

Loaded configuration file "config/config.json".
Using environment "development".
== 20230306004515-create-student: migrating =======
== 20230306004515-create-student: migrated (0.036s)

Every time we execute db:migrate the up function is called.

And if we ever want to reverse the migrations, we may invoke the down function by calling db:migrate:undo

db:migrate:undo command will undo the last migration performed on the database.

We can undo all migrations by adding the :all flag as shown below:

sequelize-cli db:migrate:undo:all

We can also revert to a specific migration by passing the migration file name after the --to flag:

sequelize-cli db:migrate:undo:all --to [xxxxxxxx]-create-user.js

Our database table has been created and is now ready for use.

Updating Sequelize Table Using Migration

Sequelize tables are simple to update using migrations. We can add new columns, update existing columns, or delete existing columns from our database table.

To change the table structure without creating a new table, we can generate new migration files. To modify our table columns, we will use the Sequelize CLI sequelize migration:generate command.

Let's this by adding an email field to our Student model:

Run:

sequelize-cli migration:generate --name add-email-to-student

The output of the above command would look like this:

migrations folder at "/student-register/migrations" already exists.
New migration was created at /student-register/migrations/20230306014640-add-email-to-student.js .

The above command will generate a new migration file that contains empty up() and down() functions.

Here's how to add an email column to our Student table, Insert the following code into the newly generated migration file:

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add altering commands here.
     *
     * Example:
     * await queryInterface.createTable('users', { id: Sequelize.INTEGER });
     */
     await queryInterface.addColumn("Students", "email", Sequelize.STRING);
  },

  async down (queryInterface, Sequelize) {
    /**
     * Add reverting commands here.
     *
     * Example:
     * await queryInterface.dropTable('users');
     */
    await queryInterface.removeColumn("Students", "email");
  }
};

Here, we're updating our database table using the addColumn and removeColumn methods of the QueryInterface class.

Before we run our migration file to update our student table structure we need to also update the Student model with the new field.

To do that replace the code in the Student model file models/student.js with the below code:

"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Student extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  }
  Student.init(
    {
      first_name: DataTypes.STRING,
      last_name: DataTypes.STRING,
      course: DataTypes.TEXT,
      email: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "Student",
    }
  );
  return Student;
};

Next, Run the new migration file using:

sequelize db:migrate

When we look at our database, we can see that an email field has been added to our Student table.

Creating Sequelize Seeder

When working on databases as a team, it is critical that everyone is working with the same data. It's also possible that we have data, like admin accounts, that we wish to add to the database initially. This can be done using Seeders.

Migrations is in charge of database structure, whereas Seeders is in charge of providing initial data to the database. It does this in roughly the same way.

We can quickly produce and maintain seed files using the sequelize seed:generate command. It creates a seeder file. The resulting file uses the same naming convention as the migration file.

We're going to make a seed file for our student table. This will add data to the student table.

To generate a seeder file, execute the following command:

sequelize-cli seed:generate --name add-student

Next, edit the file to insert student data into our database. Copy and Paste the following code into the generated seeder file.

"use strict";

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkInsert(
      "Students",
      [
        {
          first_name: "Mike",
          last_name: "Abdul",
          course: "Engineering",
          email: "admin@mike.com",
          createdAt: new Date(),
          updatedAt: new Date(),
        },
      ],
      {}
    );
  },

  async down(queryInterface, Sequelize) {
    return queryInterface.bulkDelete(
      "Students",
      { email: "admin@mike.com" },
      {}
    );
  },
};

Again, we are utilizing the queryInterface and it's bulkInsert method. The first parameter of queryInterface’s bulkInsert method is the name of the table in our postgres db.

To run the student seeder file. We can achieve this by issuing Sequelize-CLI command db:seed:all. On successful execution of this command, it will run all seeder files in our application, and fill the Student table with registered student data from our seeder file.

Hence run the command to do this:

sequelize db:seed:all

Now we can check our database, we will notice a new student record has been created in the database.

We can also undo the seeded data if things go in the wrong direction by running any of the following codes:

sequelize db:seed:undo # Undo most recent seed  
sequelize db:seed:undo:all # Undo all seeds  
sequelize db:seed:undo --seed path/to/seed/file.js # Undo specific seeds

That's it for this article. Great work on learning how to use the Sequelize migration and seeder feature! 👍

I'd love to connect with you on Twitter | LinkedIn | GitHub | Portfolio