Migrations on Sequelize-CLI for beginners

I don't know about you, but I always get confused when I search for something on sequelize docs.

Another day I found myself looking for a way to add a single column in a running localhost postgres DB.

For some reason, I thought that I would use the same sequelize on CLI. You have to use another package: sequelize-cli. Ta-da!

You don't even need to install sequelize-cli as a project dependency, since it doesn't depend on your project code, it only cares about the database (and connects directly with it).

Setting up

Start by installing the package:

npm install --save-dev sequelize-cli

Suppose you want to connect to a DB named crappydb on localhost. Go somewhere on your project or any folder and initialize sequelize and its...well, initial config files.

npx sequelize-cli init

Yes. npx and not npm (the package manager). Simplifying a lot (don't throw rocks on me), npx downloads (if you don't have) and execute the package you give as an argument, instead of installing with npm, creating a script on package.json and running on CLI (command line interface). For easier understand, the same command with npx and npm:

# executing a command with NPX
1. (CLI) --------------| npx sequelize-cli init

# executing a command with NPM
1. (CLI) --------------| npm install --save-dev sequelize-cli
2. (on package.json)"--| sequelize-cli": "sequelize-cli"
3. (CLI) --------------| npm run sequelize-cli -- init

I think 1 command wins over 3!

Returning to sequelize-cli kingdom. The command generates 4 folders: config, migrations, models and seeders.

config is about how to connect to your db.

migrations is about what to change on your db.

models is about the structure on your db and how to connect with it.

seeders is about...seed data.

By default, the config folder will have a file config.json. Change it according to your db credentials.

"development": {
    "username": "root",
    "password": null,
    "database": "crappydb", # name of your db
    "host": "127.0.0.1", # localhost
    "dialect": "postgres", # mysql, mariadb, sqlite etc
    "operatorsAliases": false
  },

Test the connection with npx sequelize-cli db:migrate:status. On the first command, sequelize will create a table named SequelizeMeta to keep track of which migrations were executed or not. This is how it knows to run or not a migration.

Your first migration

Let's start with the migration (finally !!). Start by creating an empty migration:

npx sequelize-cli migration:generate --name <you decide the name>

Suppose you have a table cats. You're crazy about cats and want to add a field to write about every single cat on your database (I don't judge you). You would:

npx sequelize-cli migration:generate --name add-column-description-on-cats

This generates an empty migration like this:

"use strict"

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');
    */
  },
}

Transform it to this:

"use strict"

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.addColumn("cats", "description", {
      type: Sequelize.DataTypes.TEXT,
    })
  },

  down: (queryInterface, Sequelize) => {
    queryInterface.removeColumn("cats", "description")
  },
}

UP is what/how to DO the changes on your db.

DOWN is what/how to UNDO the changes on your db.

Before applying this migration, could you do something for me?

Run npx sequelize-cli db:migrate:status.

(CLI) ---------| npx sequelize-cli db:migrate:status

# This will probably output this for you

(CLI output) --| down xxxxxxxxxxxxxx-add-column-description-on-cats.js

xxxxxxxx is the timestamp of your migration. Ok, now run the migration and after that run the same command above to check the status:

(CLI) ---------| npx sequelize-cli db:migrate

# your DB is changed

(CLI) ---------| npx sequelize-cli db:migrate:status

# This will probably output this for you

(CLI output) --| up xxxxxxxxxxxxxx-add-column-description-on-cats.js

Did you realize that before applying the migration the status was down and after migration, it becomes up? That's how sequelize-cli and you know if a migration has been applied or not.

sequelize-cli db:migrate command looks on the SequelizeMeta table for any migration files which haven't run yet. Every migration lives on this table and inside migrations/ folder.

Other topics

This is the simplest migration you can create. You can do a bunch of other stuff, like:

• Turn config.json to config.js to work programmatically inside, use ENV variables, etc.
• Use sequelize-cli on different envs: dev, test, and production.
• Create tables, use transactions.
• Connect with databases using SSL.
• Create a file .sequelizercand modify the default CLI folder and options.

However, this is a beginner guide. Maybe for another post :)

bye bye

The end.

Picture Credits: Photo by Markus Spiske on Unsplash