Migrations on Sequelize-CLI for beginners
Tags: sequelize, database, tips, beginners
Published: 2020-04-10 03:48:03

I don't know about you, but I always get confused when I search for something on [sequelize docs](https://sequelize.org/v5/).

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](https://github.com/sequelize/sequelize) on CLI. You have to use another package: [sequelize-cli](https://github.com/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).

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](https://github.com/npm/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. 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. (add 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`. The big 4.

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

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.

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 `.sequelizerc`and 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