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 :)

can you attach the User.js file to the question? – Suvethan Nantha Apr 18 '18 at 04:15 — Suvethan Nantha, Apr 18 '18 at 04:15
I have added user.js file ! Please check – Ajay P. Apr 18 '18 at 04:17 — Ajay P., Apr 18 '18 at 04:17
check the answer and let me know if you have difficulties – Suvethan Nantha Apr 18 '18 at 04:36 — Suvethan Nantha, Apr 18 '18 at 04:36

score 23 · Answer 1 · edited Jun 03 '22 at 11:20

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.

score 15 · Accepted Answer · edited Jul 13 '18 at 00:45

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

"Promise.all" was missing in my case and throwing an exception. Thanks — rodrigorf, Oct 24 '19 at 19:52
I would recommend using a transaction to group both `addColumn` so if one fails, all rollsback gracefully, currently demonstrated in the docs: https://sequelize.org/v5/manual/migrations.html — Ciro Santilli OurBigBook.com, Sep 03 '21 at 10:42

score 9 · Answer 3 · edited Mar 22 '22 at 15:12

9

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.

edited Mar 22 '22 at 15:12

Sal

302
1
11

answered Aug 15 '21 at 13:50

Cristian Gómez

101
1
2

is it will not reset data of all tables or specific table. if no what is purpose of migrations then ? – Jahangir Hussain Nov 30 '22 at 19:39
@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 – GoldenretriverYT Jan 09 '23 at 10:29

score 0 · Answer 4 · answered Apr 02 '20 at 16:56

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!

score 0 · Answer 5 · edited Mar 21 '23 at 00:45

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

score 0 · Answer 6 · edited Jun 03 '23 at 07:57

0

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

edited Jun 03 '23 at 07:57

Tyler2P

2,324
26
22
31

answered Jun 02 '23 at 21:16

tanmay958

1

How to add column in Sequelize existing model?

6 Answers6

Linked