How to use Migration in Sequelize

Migration

Just like git to manage the changes in our source code, we use migration to manage the changes in our database
Example:- We can understand with an easy example, if we have a project and a team of four people working on that and everyone has to implement different things into the same database and it may be to add more columns of a table of the database and other has to change the name of the table.
to make these all things possible we use Migration

For this we need to Sequelize-cli, this will support in bootstrapping
Let’s start with installing cli

$ npm install --save sequelize-cli

Bootstrapping:
To create an empty project you will need to execute init command

$ npx sequelize-cli init

This will create following folders:-

• config, contains config file, which tells CLI how to connect with database
• models, contains all models for your project
• migrations, contains all migration files
• seeders, contains all seed files

Configuration
we need to connect to the database for that, open configure file and open config/config.json file and setup your credentials
it will look like:

{   
   "development":{
        "username": "root",
        "password": null,
        "database": "database_development",
        "host": "127.0.0.1",
        "dialect": "mysql"   
      },
   "test": {
     "username": "root",
     "password": null,
     "database": "database_test",
     "host": "127.0.0.1",
     "dialect": "mysql"
   },
   "production": {
     "username": "root", 
     "password": null,
     "database": "database_production",
     "host": "127.0.0.1",
     "dialect": "mysql"   
   }
}

Here you have to change your username, password, database name, host, and dialect(‘which database you are using)
by default, it is set to development

Note: If your database doesn’t exist yet, you can just call db:create command. With proper access, it will create that database for you.
or run the below-given command to create the database

$ npx sequelize-cli db:create 

Creating models:
We will use model:generate command. This command requires two options

• name, Name of the model
• attributes, List of model attributes

let’s create a model named Students.

$ npx sequelize-cli model:generate --name Students --attributes firstName:string,lastName:string,email:string,age:integer

This will create a Students file in model folder and
Create a migration file named xxxxxxxxxxxxx-create-user.js in migration folder.

Running Migrations
Until this step, we haven’t inserted anything into the database. We just created the required model and migration files for our first model Students.
now we are actually going to insert them into the database using db:migrate command:

$ npx sequelize-cli db:migrate

This command will execute these steps:

• Will ensure a table called SequelizeMeta in database. This table is used to record which migrations have run on the current database
• Start looking for any migration files which haven’t run yet. This is possible by checking SequelizeMeta table. In this case it will run XXXXXXXXXXXXXX-create-user.js migration, which we created in the last step.
• Creates a table called Students with all columns as specified in its migration file.

Undoing Migrations

Now our table has been created into the database and we can revert back to the previous step by using command db:migrate:undo
run the below-given command to revert back to the previous state:

$ npx sequelize-cli db:migrate:undo

you can revert back to the first step, means we can undo all the migrations by using command db:migrate:undo all

$ npx sequelize-cli db:migrate:undo:all

And we can also revert/undo a particular migration by using its migration name

$ npx sequelize-cli db:migrate:undo --name xxxxxxxxxxxx-migration-fileName.js

And also we can revert back to a particular migration state using the name of that migration

$ npx sequelize-cli db:migrate:undo --to xxxxxxxxxxxx-migration-fileName.js

Migration Skeleton

The following skeleton shows a typical migration file.

module.exports = {
 up: (queryInterface, Sequelize) => {
 /*
  Add altering commands here.
  Return a promise to correctly handle asynchronicity.
  Example:
      return queryInterface.createTable('users', { 
             id: Sequelize.INTEGER   });
  */
 },
 down: (queryInterface, Sequelize) => {
  /*
  Add reverting commands here.
  Return a promise to correctly handle asynchronicity.
  Example:
  return queryInterface.dropTable('users');
  */
 }
};

We can generate this file using migration:generate. This will create xxx-migration-skeleton.js in your migration folder.

$ npx sequelize-cli migration:generate --name migration-skeleton

The passed queryInterface object can be used to modify the database. The Sequelize object stores the available data types such as STRING or INTEGER.
Here the up function is for making a new change and the downfunction is used for undo
Function up or down should return a Promise. Let's look at an example:

Create new Table

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Course', {
        name: Sequelize.STRING
      });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Course');
  }
}

Add a new Column to the existing table

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn(
        'Students',          //tableName
        'Class',             // newColumnName
         Sequelize.STRING    // dataType
     );
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn('Class');
  }
}

Change the name of the existing Column in the table

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.renameColumn(
        'Students',          //table name
        'Class',             // existing column name
        'Student_class'      // new column name
     );
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.removeColumn(
        'Students',         // table name
        'Student_class'     // new column name
        'Class'             // existing column name
      );
  }
}