Modifying an existing sequelize migration

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:

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:

$ sequelize migration:create --name name_of_your_migration

A new migration has been generated for you. Next edit the new migration created.

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:

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

$ sequelize migration:create --name name_of_your_migration

Now edit the migration file and give it to queryInterface with the removeColumn method.

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

Run migration:

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

$ sequelize migration:create --name name_of_your_migration

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

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.

Comments

[vamshinandan]

Hi i am having trouble adding boolean column, i am able to add the column but unable to insert the value into the column
Can you provide an example for it please

[Anayo Samson Oleru]

Hi @Vam, here is an example.

To add a Boolean column(Model):

is_activated: {
  type: DataTypes.BOOLEAN,
  defaultValue: false,
},

To insert a value into the column:

By default, it will always be False, but if you want to change it to true, pass true as the value. I mean a boolean true

example:

User.create({
is_activated: true
});

[Anayo Samson Oleru]

Hi @vam, here is an example.

To add a Boolean column(Model):

is_activated: {
  type: DataTypes.BOOLEAN,
  defaultValue: false,
},

To insert a value into the column:

By default, it will always be False, but if you want to change it to true, pass true as the value. I mean a boolean true

example:

User.create({
is_activated: true
});

[johnny chang]

Thanks, your information. this help me a lot

[Hitarth1]

How to perform soft delete in sequelize?

[Anayo Samson Oleru]

Hi Hitarth1, you need to define a model as a paranoid. You can do that by passing theparanoid: true option when you're defining your model. And make sure timestamps is not false. Paranoid needs timestamp to work.

[rogeraidr]

don't forget to add deletedAt column

[Sahl Khalifa]

Thank you. This was helpful. Keep it up

[Monero Jeanniton]

Thank you

[Amit Yadav]

for sequelize 6.3.0 version -
$ sequelize migration:create --name name_of_your_migration
this command is not working for create a migration file, then you can use-
$ sequelize migration:generate --name name_of_your_migration

[Phillip Harden]

What if all I need to do is change the attribute name, like april, to something different?

[Anayo Samson Oleru]

Hi @phillipiscoding my bad, I didn't get notified about this comment.

To change the attribute name, you use the renameColumn method, this is an example below:

module.exports = {
up: (queryInterface, Sequelize) => {
return Promise.all([
queryInterface.renameColumn('users', 'april', 'new_column_name'),
]);
}

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