How to use Sequelize migration feature

Sequelize provides you with a migration feature to keep track of the changes to your database in a JavaScript file.

By using migrations, you will be able to save instructions that change your database structures and definitions.

A Sequelize migration file is simply a JavaScript with two functions:

• The up() function perform changes to your database
• The down() function has instructions on how to undo the changes

For example, the following migration file will create a new table named Users in your database:

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

The up() function defines the table name and its columns.

The down() function simply drops the table to undo what the up() function has done.

The code above is automatically generated when you use the Sequelize migration feature. Let’s look at how to start using Sequelize migration next.

Start using Sequelize migration

To use the migration feature, you need to install the Sequelize CLI tool named sequelize-cli.

You can install it on your computer using npm or Yarn. Because the CLI tool is used from the command line, you need to install it globally:

npm install sequelize-cli --global
# or
yarn global add sequelize-cli

Alternatively, you can also use the npx command to run sequelize-cli without installing it globally:

npx sequelize-cli --version

The rest of this tutorial will assume you have installed the sequelize-cli tool globally.

To add migrations to your JavaScript project, you need to have sequelize installed on the project first:

npm install sequelize

Once sequelize is installed as a dependency, you can initialize the files with the command below:

sequelize init

The command will generate 4 folders in your JavaScript project:

Created "config/config.json"
Created models folder at "/js-project/models".
Created migrations folder at "/js-project/migrations".
Created seeders folder at "/js-project/seeders".

The config.json file in the config/ folder is used to let sequelize-cli connect to your database. It stores the credentials and dialects for your databases

There are three different credentials that will be used depending on the value of NODE_ENV variable:

• development is the default credential used
• test if you have a test environment
• production for production environment

Make sure that you have the correct credentials and dialect at least for the development credential.

Creating Sequelize model and migration

Once you have the correct credentials and dialects defined in the config.json file, you need to create a Sequelize model and migration.

The Sequelize model allows you to connect to the table later in your JavaScript project, while the migration file generates the table on the database server.

You can do both at once using the sequelize model:create command:

sequelize model:create --name User --attributes firstName:string

The model:create command requires you to pass two arguments:

--name for the model name --attributes for the model attributes (table columns)

The attributes must be in the form of attributeName:attributeType.

You can add more than one attribute by adding a comma , as follows:

--attributes firstName:string,lastName:string,age:integer

Once you run the model:create command, you will have the following output:

New model was created at /js-project/models/user.js .
New migration was created at /js-project/migrations/20220228031636-create-user.js .

Model files will be generated inside the models/ folder, while migration files will be created inside the migrations/ folder.

A migration file created using Sequelize will automatically add the timestamp of when it was created.

It’s time to run the migration file you just created with the db:migrate command:

sequelize-cli db:migrate

The db:migrate output would look as follows:

Loaded configuration file "config/config.json".
Using environment "development".
== 20220228031636-create-user: migrating =======
== 20220228031636-create-user: migrated (0.440s)

When you run the db:migrate command for the first time, a table named SequelizeMeta will be created in your database.

This table keeps records of which migration files have already been executed in your database.

You can undo the migration with the following command:

sequelize-cli db:migrate:undo

The db:migrate:undo command will undo the last migration performed in your database.

You can undo everything by adding the :all command as shown below:

sequelize-cli db:migrate:undo:all

Or you can also revert to a specific migration by passing the migration file name using the --to option:

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

Create the next migration file

At times, you might want to modify the table structure without creating a new table.

To do so, you need to use the migration:create command instead of the model:create command.

For example, suppose you want to add an email field to the user model:

sequelize-cli migration:create --name add-email-to-user

The migration:create command requires you to pass the --name argument for the migration file name.

The output of the command would be as follows:

migrations folder at "/js-project/migrations" already exists.
migration was created at /js-project/migrations/20220228032612-add-email-to-user.js

When you open the migration file, you will see the migration file skeleton with empty up() and down() functions.

Here’s the code to add an email field to the Users table:

'use strict';

module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.addColumn( 'Users', 'email', Sequelize.STRING );
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.removeColumn( 'Users', 'email' );
  }
};

Run the new migration using the same sequelize-cli db:migrate command.

You will see the email field added to the Users table.

The queryInterface is an instance of the QueryInterface class that Sequelize uses to send instructions to your database. You can see the full list of available methods at Sequelize QueryInterface documentation.

And that’s how you use the Sequelize migration feature. You can visit the Sequelize migration documentation for more details (such as how to insert rows into your table using seed files.)

Great work on learning the Sequelize migration feature! 👍