Famosa Adegbite

Posted on Jan 12, 2021 • Updated on Sep 17, 2022

CREATE, UPDATE, ADD and DELETE SEQUELIZE MIGRATION

#sequelize #node #postgres #database

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

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.

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

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

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.

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

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

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.

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

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

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

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.

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

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

'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
}