CREATE, UPDATE, ADD and DELETE SEQUELIZE MIGRATION
Tags: sequelize, node, postgres, database
Published: 2021-01-12 14:17:06

Did you have an existing sequelize that you wish to make changes like adding more columns, delete or modify a particular column. Have you been deleting/dropping all the migration completely and re-run migrations all over again for changes to reflect?

If you’ve been doing this before or you are new to sequelize/sequelize cli, dropping database tables in production for the sake of modification is totally wrong.

So glad you found this article, we talk more about how to create a new migration, add new column, update and delete existing column.

But you should have a basic understanding of **node**, **sequelize** and **configuration**, you can visit [Sequelize site](https://sequelize.org/master/manual/migrations.html) for more information and details. 

## Create the First Model:
Once we have properly configured CLI config file, creating our first migration It's as simple as executing a simple command.

**Step I:** Use `model: generate` command which will require two options `name` and `attributes`. The `name` is the name of the model (E.g. `User`, `Employee`, etc.) while `attributes` is the list of model attributes.  Assuming we have User model, we can have `firstName`, `lastName`, and more as the attributes.

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

This will create a model file user in models folder and create a migration file with a name like `XXXXXXXXXXXXXX-create-user.js` in migrations folder.
**Our `models/user.js` generated.**
```javascript
'use strict';
const {
  Model, Sequelize
} = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    'User',
    {
      id: {
        type: Sequelize.INTEGER,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
      },
      firstName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      lastName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      
    }, {
    sequelize,
    modelName: 'User',
  });
  
  return User
}
```
`migrations/xxxxxxxxxxxx-create-user.js` generated alongside.

```javascript 
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING
      },
      lastName: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING,
      },
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```

**Step II:** Run `migration command` in order to create a table in database we need. Without this, we haven't inserted anything into the database. We have just created the required model and migration files for our first model User.
```javascript
npx sequelize-cli db:migrate
```
> **Note:** Before running the migration command, we can make changes to our User model and migration, most especially attribute.

At this point, we have created a user model and migration successfully. 

Let's now assumed we’ve started inputting data to the database and want to make changes or modify the database which is our major focus in this article.

## 1.)	Update a column:

Now let's update a column in the migration we have created already.

From the previous model, we have User table, with `id`, `firstName`, `lastName`, and `email` columns. And we want to update the `id` datatype from `INTEGER` to `UUID`.

`queryInterface` in migration will be waiting for a new command, including what it should update and in what table should it be done. In that case, we would have to create or generate a new migration.

**Step I:** Run a `migration command` to create a new migration file.
```javascript
$ npx sequelize-cli migration:create --name migration_file_name
```
**Step II:** A new migration file has been generated for us. Next, let's edit the new migration file created.
```javascript
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.changeColumn('Users', 'id', {
      type: Sequelize.UUID,
      defaultValue: Sequelize.UUIDV4
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```
> **Note:** Take note that `queryInterface.createColumn` from our initial migration file has change to `queryInterface.changeColumn` in order for us to update the file.

**Step III:** Run migration command in order to make changes we are about to update in database table we have already created.
```javascript
npx sequelize-cli db:migrate
```
By doing this, a notice will be passed to the database in the name of the table and column in the table we want to update, queryInterface will execute the command in the Database.

**Step IV:** We have to modify our user model (`models/user.js`), by implementing the changes we made in our database by replacing the `INTEGER` to `UUID`, and add `defaultValue` to `Sequelize.UUIDV4`.

In `models/user.js` file.
```javascript
'use strict';
const {
  Model, Sequelize
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    'User',
    {
      id: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
      },
      firstName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      lastName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    }, {
    sequelize,
    modelName: 'User',
  }); 
  return User
}
```
## 2.)	Add new column:
Now let's add a new column in the migration we have created already.

From the previous model, we have User table, with `id`, `firstName`, `lastName`, and `email` columns. And we want to add `username` column to the table in the database.

`queryInterface` in migration will be waiting for the new command, including what it should add and in what table it should add it. In that case, we would have to create or generate a new migration:

**Step I:** Run a migration command to create a new migration file.
```javascript
$ npx sequelize-cli migration:create --name migration_file_name
```
**Step II:** A new migration file has been generated for us. Next, let's edit the new migration file created.
```javascript
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'username', {
      type: Sequelize.STRING,
      allowNull: true,
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```
> **Note:** Take note that `queryInterface.createColumn` from our initial migration file or the update migration file `queryInterface.changeColumn` has change to `queryInterface.addColumn` in order for us to add new column to our database table.

**Step III:** Run `migration command` in order to make changes we are about to make in database table we have already created.
```javascript
npx sequelize-cli db:migrate
```
By doing this, a new column will be added to the database.

Modify user model (`models/user.js`), by adding the new column `username` and its attribute.
In `models/user.js`.
```javascript
'use strict';
const {
  Model, Sequelize
} = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    'User',
    {
      id: {
        type: Sequelize.UUID,
 defaultValue: Sequelize.UUIDV4,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
      },
      firstName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      lastName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
	username: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    }, {
    sequelize,
    modelName: 'User',
  });
  
  return User
}
```
## 3.)	Delete existing column:
Now let's delete existing column in the migration we have created already.

Our database table should have `id`, `firstName`, `lastName`, `username`, and `email` columns. Let's delete the `username` column in the database table.

We are going to repeat the same steps from previous explanation with a modification in our migration file that will change to `queryInterface.removeColumn`.
```javascript
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('Users', 'username');
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};
```
Then run the migration command and don’t forget to modify user model (`models/user.js`), by removing `username` column and its attribute.

In `models/user.js`.
```javascript
'use strict';
const {
  Model, Sequelize
} = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    'User',
    {
      id: {
        type: Sequelize.UUID,
 defaultValue: Sequelize.UUIDV4,
        autoIncrement: true,
        allowNull: false,
        primaryKey: true
      },
      firstName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      lastName: {
        type: DataTypes.STRING,
        allowNull: false,
      },
	email: {
        type: DataTypes.STRING,
        allowNull: false,
      },
    }, {
    sequelize,
    modelName: 'User',
  });
  
  return User
}
```
Have a great day and stay safe

Happy Coding

Follow us on Twitter [@FamosaAdegbite](https://twitter.com/FamosaAdegbite)