How to add column in Sequelize existing model?

Question

I have added a model and a migration file using this command

node_modules/.bin/sequelize model:generate --name User --attributes firstName:string,lastName:string,email:string

Now I wanted to add few more fields like gender and age in to the existing table(model). I changed model manually and fire this command

node_modules/.bin/sequelize db:migrate

But it is responding that "No migrations were executed, database schema was already up to date. "

User.js

'use strict';
module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define('User', {
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    email: DataTypes.STRING
  }, {});
  User.associate = function(models) {
    // associations can be defined here
  };
  return User;
};

Thank you in advance :)

Dolhenty

can you attach the User.js file to the question? – Crossarm

I have added user.js file ! Please check – Dolhenty

check the answer and let me know if you have difficulties – Crossarm

Answer 1

Suvethan's answer is correct, but the migration code snippet has a minor bug. Sequelize migrations expect a promise to be returned, which is noted in a comment in the generated migration skeleton:

Add altering commands here.
Return a promise to correctly handle asynchronicity.

Example:
return queryInterface.createTable('users', { id: Sequelize.INTEGER });

So, returning an array of promises can potentially lead to unexpected results because there's no guarantee that all of the promises will have resolved before moving on to the next migration. For most operations you're unlikely to run into any issues since most things will complete before Sequelize closes the process. But I think it's better to be safe than sorry when it comes to database migrations. You can still leverage the array of promises; you just need to wrap it in a Promise.all call.

Suvethan's example, but with Promise.all:

module.exports = {
  up: function (queryInterface, Sequelize) {
    return Promise.all([
      queryInterface.addColumn(
        'Users',
        'gender',
         Sequelize.STRING
       ),
      queryInterface.addColumn(
        'Users',
        'age',
        Sequelize.STRING
      )
    ]);
  },

  down: function (queryInterface, Sequelize) {
    // logic for reverting the changes
  }
};

Shears

"Promise.all" was missing in my case and throwing an exception. Thanks – Scone

I would recommend using a transaction to group both addColumn so if one fails, all rollsback gracefully, currently demonstrated in the docs: sequelize.org/v5/manual/migrations.html – Dace

Answer 2

In order to add new fields to the table,we should use migration skeleton as shown below.

sequelize migration:create --name Users

Open the migration file and add the below codes

module.exports = {
  up: function (queryInterface, Sequelize) {
    return [ queryInterface.addColumn(
              'Users',
              'gender',
               Sequelize.STRING
             ),
            queryInterface.addColumn(
             'Users',
             'age',
             Sequelize.STRING
          )];
  },

  down: function (queryInterface, Sequelize) {
    // logic for reverting the changes
  }
};

Then just run the migration

node_modules/.bin/sequelize db:migrate

Note: The passed queryInterface object can be used to modify the database. The Sequelize object stores the available data types such as STRING or INTEGER.

Full list of methods in Query Interface

I hope this will help you. If you have any issues let me know.

Crossarm

Link goes to a 404 – Aragats

Answer 3

In your sequelize initialization add alter property in true, and add column or association into your existing model file.

db.sequelize.sync({ force: false, alter: true })

From relevant documentation:

User.sync({ alter: true }) 

This checks what is the current state of the table in the database (which columns it has, what are their data types, etc), and then performs the necessary changes in the table to make it match the model.

Valeryvalerye

is it will not reset data of all tables or specific table. if no what is purpose of migrations then ? – Hypothermia

@JahangirHussain the purpose of migrations is that you can go up, but also down if needed. You also kinda have a history of structural db changes then – Sip

Answer 4

In addition to @Suvethan Nantha's answer that just helped me by the way, ensure you wrap the queries in a Promise.all i.e return **Promise.all**([queryInterface.addColumn(...)]) so that it will return a promise, hence an error might be thrown. Cheers!

Oxbridge

Answer 5

create file migration using this command:

npx sequelize-cli migration:generate --name add_column_name_to_tablename

and you can simply use this in your file migration:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('users', 'new_column', {
      type: Sequelize.STRING,
      allowNull: false,
      defaultValue: ''
    });

    // Menempatkan kolom baru setelah kolom email
    await queryInterface.sequelize.query('ALTER TABLE "users" ADD COLUMN "new_column" AFTER "email";');
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.removeColumn('users', 'new_column');
  }
};

Hoodmanblind

Answer 6

You can just do npx sequelize-cli db:migrate:undo and then you can go and change in the migration file whatever you want if you want add new attributes or if you want to add property and then again do npx sequelize-cli db:migrate it will work

Giraffe