The CLI

Installing CLI

Let's start with installing CLI, you can find instructions here. Most preferred way is installing locally like this

$ 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

Before continuing further we will need to tell CLI how to connect to database. To do that let's open default config file config/config.json. It looks something like this

{
  "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"
  }
}

Now edit this file and set correct database credentials and dialect. The keys of the objects(ex. "development") are used on model/index.js for matching process.env.NODE_ENV (When undefined, "development" is a default value.).

Note:If your database doesn't exists yet, you can just call db:create command. With proper access it will create that database for you.

Creating first Model (and Migration)

Once you have properly configured CLI config file you are ready to create your first migration. It's as simple as executing a simple command.

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

$ npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

This will do following

• Create a model file user in models folder
• Create a migration file with name like XXXXXXXXXXXXXX-create-user.js in migrations folder

Note:Sequelize will only use Model files, it's the table representation. On the other hand, the migration file is a change in that model or more specifically that table, used by CLI. Treat migrations like a commit or a log for some change in database.

Running Migrations

Until this step, we haven't inserted anything into the database. We have just created required model and migration files for our first model User. Now to actually create that table in database you need to run 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 last step.
• Creates a table called Users with all columns as specified in its migration file.

Undoing Migrations

Now our table has been created and saved in database. With migration you can revert to old state by just running a command.

You can use db:migrate:undo, this command will revert most recent migration.

$ npx sequelize-cli db:migrate:undo

You can revert back to initial state by undoing all migrations with db:migrate:undo:all command. You can also revert back to a specific migration by passing its name in —to option.

$ npx sequelize-cli db:migrate:undo:all --to XXXXXXXXXXXXXX-create-posts.js

Creating First Seed

Suppose we want to insert some data into a few tables by default. If we follow up on previous example we can consider creating a demo user for User table.

To manage all data migrations you can use seeders. Seed files are some change in data that can be used to populate database table with sample data or test data.

Let's create a seed file which will add a demo user to our User table.

$ npx sequelize-cli seed:generate --name demo-user

This command will create a seed file in seeders folder. File name will look something like XXXXXXXXXXXXXX-demo-user.js. It follows the same up / down semantics as the migration files.

Now we should edit this file to insert demo user to User table.

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [{
        firstName: 'John',
        lastName: 'Doe',
        email: 'demo@demo.com',
        createdAt: new Date(),
        updatedAt: new Date()
      }], {});
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {});
  }
};

Running Seeds

In last step you have create a seed file. It's still not committed to database. To do that we need to run a simple command.

$ npx sequelize-cli db:seed:all

This will execute that seed file and you will have a demo user inserted into User table.

Note:Seeders execution is not stored anywhere unlike migrations, which use the SequelizeMeta table. If you wish to override this please read Storage section

Undoing Seeds

Seeders can be undone if they are using any storage. There are two commands available for that:

If you wish to undo most recent seed

$ npx sequelize-cli db:seed:undo

If you wish to undo a specific seed

$ npx sequelize-cli db:seed:undo --seed name-of-seed-as-in-data

If you wish to undo all seeds

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