CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Sudesh
Sudesh

Reputation: 1169

Migrations Add Column after specified column and index column

I want to add column via migrations after a certain column also index it (Normal mysql index not unique index). Is this possible via sequelize.js via migrations. If yes how and if no any alternatives for this via migrations.

Can we execute custom query in sequelize migrations.

Below is my existing migration file.

'use strict';

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.addColumn('UserDetails', 'user_id', {
      type: Sequelize.INTEGER,
    });
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.removeColumn('UserDetails', 'user_id');
  }
};

Upvotes: 15

Views: 21170

Answers (4)

Nihal Gupta
Nihal Gupta

Reputation: 178

You can add a column after a certain column by using the after keyword of sequelize. Also you can create an indexing using .addIndex.

This is the required migration file code:

'use strict';

module.exports = {
  up: async function (queryInterface, Sequelize) {
    await queryInterface.addColumn('UserDetails', 'user_id', {
      type: Sequelize.INTEGER,
      after: 'existing_column_name'
    });

    await queryInterface.addIndex('UserDetails', ['user_id'], {
      name: 'user_id_index',
      type: 'INDEX',
    });
  },

  down: async function (queryInterface, Sequelize) {
    await queryInterface.removeIndex('UserDetails', 'user_id_index');
    await queryInterface.removeColumn('UserDetails', 'user_id');
  }
};

Hope it helps!!

Upvotes: 0

Bhargav Raviya
Bhargav Raviya

Reputation: 399

Table: items

New Field: is_completed

Adding After: type

First create new migration for add colurm in table

npx sequelize-cli migration:generate --name add_is_completed_to_items

Change in xxxx-add_is_completed_to_items.js file below

'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    await queryInterface.addColumn('items', 'is_completed', {
      type: Sequelize.BOOLEAN,
      allowNull: false,
      defaultValue: false,
      after: 'type'
    });
  },

  async down (queryInterface, Sequelize) {
    await queryInterface.removeColumn('contests', 'is_completed');
  }
};

Then running for migration

npx sequelize-cli db:migrate

Upvotes: 1

Vivek V Dwivedi
Vivek V Dwivedi

Reputation: 2615

You can pass after or before in the options to add column after some specified column.

'use strict';

module.exports = {
up: function (queryInterface, Sequelize) {
return queryInterface.addColumn('UserDetails', 'user_id', {
  type: Sequelize.INTEGER,
  after: "some_column"
});
},

down: function (queryInterface, Sequelize) {
return queryInterface.removeColumn('UserDetails', 'user_id');
}
};

P.S. after option is only supported by MySQL https://github.com/sequelize/sequelize/blob/master/lib/query-interface.js#L495

Upvotes: 62

wielo
wielo

Reputation: 67

As far as I know there is no way of doing it via addColumn function.

But you can do it via raw SQL query:

ALTER TABLE UserDetails CHANGE COLUMN user_id user_id VARCHAR(50) AFTER some_column;

You can run custom SQL queries in migrations like this:

module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.sequelize.query("ALTER TABLE UserDetails CHANGE COLUMN user_id user_id VARCHAR(50) AFTER some_column;")
  },
  down: function(queryInterface, Sequelize) {
    return true;
  }
};

Since you are already using custom SQL you could add the new column via raw SQL and put it after the column you choosed:

ALTER TABLE UserDetails ADD COLUMN user_id VARCHAR(50) AFTER some_column;

Upvotes: 0

Related Questions