Modifying an existing sequelize migration
Tags: sequelize, postgres, modifications, sequelizecli
Published: 2020-05-12 18:22:04

---
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/3zqia8o1w1a344x631iy.png
title: Modifying an existing sequelize migration
published: true
description: This article shows how you can modify an existing sequelize migration.
tags: sequelize, postgres, modifications, sequelizecli 
---
If you've just started using sequelize and sequelize CLI in development, you definitely had frequent addition and deletions of columns. And yeah, all these times you directly modified the migrations file, dropped all migrations and re-ran migrations again, so that your changes will take effect.

But I'm glad you're here because you realize that's wrong, or if you're just finding out for the first time, happy for you. In production, you can't just drop all database tables because you updated a migration. That'll be one big mess up, even though there are backups.

Anyways in this article, I'll show you how to update the column, add a new column or columns, and delete an existing column from/in an existing migration.

Here's something you should know and understand, it'll help when you have issues with sequelize modifications. But if you just want a quick solution you can skip this and move to the more meaty part.

**QueryInterface**: A migration is simply a set of instructions using the *queryInterface* method, it's a middleman between you and your database, so there is you and there is the database, you create and customize your table yourself, now you want to load that table into the database, you tell *queryInterface* to take the table you created and put it into the database, with the ***createTable*** command.

Now we don't want to create a new table, we want to modify the existing table. *queryInterface* is still your man, but it doesn't understand all commands, it has its own command it understands, so to communicate with it you have to use its own command, you can tell it to:

**addColumn** if you want to add a new column or columns. This is a sequelize method *queryInterface* uses, to add a new column to a table. It takes the column and the name of the table from you goes into the database, searches for the table name, and adds it.

**changeColumn** if you want to change column, probably the datatype of a column. This is also a sequelize method *queryInterface* uses, to alter a column. It takes the new adjustments you gave to it, the name of the table, and goes into the database, looks for the table and make the adjustment.

**removeColumn** if you want to remove a column, probably if you find it unuseful. This is a sequelize method *queryInterface* uses, to delete a column. It looks for the table and column and makes the action for you, you still have to provide the name of the table and then name of the column.

**createTable** if you are creating a whole new table entirely. I believe you may already be familiar with this. Without this one, I guess you can't add, change or remove a column.

In essence, you have to give *queryInterface* something to add, here's how you can do it. This is where we get down on it, we'll use a project to test.


So if you don't have a project ready-up and you just want to learn this, I made a starter on `Quicksi` you can easily use, everything is set up for you, check it up here: 
https://quicksi-bot.now.sh/docs/en/Node-JS#node-js-pgsequelize

If you're using the starter, kindly follow the *how to get started* to get the starter, then create a *.env* file and add your database URL. Then create a new sequelize migration, that you can make modification on.


(I). Updating a column
Now let's update a column in the migration you have or created from the starter.

So let's say we have a User table, with `firstname`, `lastname`, and `april ` columns. And we want to update the `april` datatype from INTEGER to FLOAT, with an `allowNull` key and a property of `false`. 

User table before:

```bash
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      april: {
        type: Sequelize.INTEGER
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('users');
  }
};
```

*queryInterface* is waiting for you to give it the command, including what it should update and in what table it should it be done.

To give  *queryInterface* something, you need to create a new migration:

Run this command:
```bash
$ sequelize migration:create --name name_of_your_migration
```
A new migration has been generated for you. Next edit the new migration created.

```bash
module.exports = {
  up: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.changeColumn('users', 'april', {
        type: Sequelize.FLOAT,
        allowNull: false
      }),
    ]);
  },

  down: (queryInterface) => {
    return Promise.all([queryInterface.changeColumn('users', 'april')]);
  },
};
```

Then run the migration: ```bash
$ sequelize db:migrate
```

And that's it! Notice we passed in the name of the table and the column in the table we want to update, *queryInterface* takes this and does its job in the Database.


(II). Deleting a column
Let's delete the column you updated on the User table above.

You need to create another migration file.

```bash
$ sequelize migration:create --name name_of_your_migration
```
Now edit the migration file and give it to *queryInterface* with the `removeColumn` method.

```bash
return queryInterface.removeColumn('users', 'april');
```

Run migration: ```bash
$ sequelize db:migrate
```

(III). Adding a new column
Now let's add a new column in the user migration. Let's say we want to add another column called `June`. Create another migration file.

```bash
$ sequelize migration:create --name name_of_your_migration
```

Now edit the migration file and give it to *queryInterface* with the `addColumn` method.

```bash
module.exports = {
  up: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.addColumn('users', 'june', {
        type: Sequelize.STRING,
        allowNull: true,
      }),
    ]);
  },

  down: (queryInterface) => {
    return Promise.all([queryInterface.removeColumn('users', 'june')]);
  },
};

```

Don't forget to update your models with your new changes.

I do hope this explains sequelize modification methods clearer. 
Follow me on twitter: @AnayoOleru

Have a great day and stay safe.