Cli: Easy way to select and update values on migration

[gaprl]

Let's suppose I have a model User with the following schema:

{
  addressStreet: DataTypes.STRING,
  addressCity: DataTypes.STRING,
  addressState: DataTypes.STRING,
  addressZip: DataTypes.STRING
}

And I'd like to change it to:

{
  address: { type: DataTypes.STRING, allowNull: false },
}

Ok. All good. I'll just create a migration to create and drop the columns.

The problem is, I need to migrate the data from the old schema to the new one. It may be a stupid question or maybe I am just missing a step here but is there an easy way of doing it instead of creating SELECT/UPDATE queries with queryInterface.sequelize.query?

I could load the model User and use it's findAll function but at the time the migration is running all the old fields (addressStreet, addressCity, etc) are not accessible anymore. :(

I'd be glad if anyone can give me some light on this. Thanks in advance!

_Edit: @nichdiekuh had a similar problem about a year ago with sequelize/sequelize#2050. I was wondering if by now we have a better solution._

[janmeier]

Unfortunately there is no better way - All the ORM fancyness is tied directly to models, and as you mention yourself, the model is not available / not in the right state during the migration.

A very WET way would be to define two temp models in the migrations representing to and from state of the table - the best current, but definitely not an ideal solution

[owencm]

I have a related issue. I currently have an optional BOOLEAN field and would like to change it to default to false and set all currently null options to false.

Is there an example of making such a modification of existing DB values in the same migration system?

Edit: for any future people reading with a similar question, here was my eventual solution:

module.exports = {
  up: function (queryInterface, Sequelize) {
    queryInterface.sequelize.query('UPDATE `SQPlans` AS `SQPlan` SET `SQPlan`.`cancelled` = FALSE WHERE `SQPlan`.`cancelled` IS NULL;')
  },

  down: function (queryInterface, Sequelize) {
    queryInterface.sequelize.query('UPDATE `SQPlans` AS `SQPlan` SET `SQPlan`.`cancelled` = NULL WHERE `SQPlan`.`cancelled` = FALSE;')
  }
};

[reneolivo]

Thanks @owencm!!! I personally had to do something like this to "bulk" update a few records:

const records = [
     { id: 1, field_name: 'field value' },
     { id: 2, field_name: 'field value' },
     { id: 3, field_name: 'field value' },
];

module.exports = {
  up: function(queryInterface, Sequelize) {
    const promises = records.map((r) => queryInterface.sequelize.query(`
      UPDATE table_name
      SET field_name = :field_name
      WHERE id = :id
    `, {
        replacements: r
    });

    return Promise.all(promises);
  },

  //.....
};

[fostahgix]

I had a similar situation, here is the migration I created, if it helps anyone:

```'use strict';

module.exports = {
up: async (queryInterface, Sequelize) => {
return [
await queryInterface.addColumn('jobs', 'matterInfo', {
type: Sequelize.STRING(150),
defaultValue: ''
}),
await queryInterface.sequelize.query(
'UPDATE jobs SET matterInfo = CONCAT( matterNumber, " / ", matterName);'
),

  // remove unused fields
  await queryInterface.removeColumn('jobs', 'matterName'),
  await queryInterface.removeColumn('jobs', 'matterNumber')
];

},

down: (queryInterface, Sequelize) => {
/*
Add reverting commands here.
Return a promise to correctly handle asynchronicity.

  Example:
  return queryInterface.dropTable('users');
*/

}
};

[rnukala]

In case any one still struggling to set string value for the null column, enclose the column in

(backslash') then value (backslash')

up: (queryInterface, Sequelize) => {
return queryInterface.sequelize.query('UPDATE "SeqUsers" SET place=\'default\' WHERE place IS NULL',

).then(function(users){
console.log("users", users);
});
},

[ajbraus]

This issue really needs a solution. ActiveRecord does this perfectly by calling the model inside the migration. Why can't sequelize do this as well?

[Arshiamidos]

one way is remove databases from server and db:migrate again .
it's work in my case

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.