Sequelize js , how do we change column type in migration

Question

how do we change column type in migration. In my migration 1 I have a migration that added the column. Now I want to change the column type from string to text , should I create a new migration file which is like changeColumn or I can create new migration file the same with migration 1 but I have just to change the type to text ? Thank you.

#Migration 1

await queryInterface.addColumn(SampleModel.tableName, 'name', {
  type: Sequelize.STRING,
  allowNull: true,
}, {
  transaction,
});

Migration 2 (does creating new migration would work like this ? still addColumn but i change the type to text)

  await queryInterface.addColumn(SampleModel.tableName, 'name', {
      type: Sequelize.TEXT,
      allowNull: true,
    }, {
      transaction,
    });

Solution 1

changeColumn bugs to be aware of

I was really confused by those on 6.5.1, so I thought it would be good to leave warning here.

Bug 1: SQLite triggers on delete cascades

SQLite does not have ALTER COLUMN as mentioned at:

• ALTER COLUMN in sqlite

• https://sequelize.org/master/class/lib/dialects/sqlite/query-interface.js~SQLiteQueryInterface.html

  A wrapper that fixes SQLite's inability to change columns from existing tables. It will create a backup of the table, drop the table afterwards and create a new table with the same name but with a modified version of the respective column.

The problem is that sequelize does not take ON DELETE CASCADE on doing this, so when it gets rid of the original table to recreate it, this triggers the cascades, and so you lose data.

What they would need to do is to temporarily drop any cascades before the migration and restore them afterwards. I'm not sure how to work around this except by surrounding a migration with a migration that drops the cascade and another one that restores them.

Thankfully in my use case it doesn't matter that much, as SQLite is just a quick way to develop locally, so it is fine if it nukes the DB every time as I can just reseed it. Using SQLite locally is a bad habit actually considering the inconsistencies across Sequelize support for different DBMSs.

Bug 2: you have to pass type: on PostgreSQL even if you are not changing it

For example, if you wanted to change just the default value of a column you might want to do:

    await queryInterface.changeColumn('User', 'ip',
      {
        defaultValue: undefined,
      }, { transaction }
    );

but on PostgreSQL this gives an error:

ERROR: Cannot read property 'toString' of undefined

For some reason, it does not give a backtrace, I love Node.js and its ecosystem, so after an hour of debugging I found that the type is required. Supposing that the column was of type DataTypes.STRING, we would do:

    await queryInterface.changeColumn('User', 'ip',
      {
        type: Sequelize.DataTypes.STRING,
        defaultValue: undefined,
      }, { transaction }
    );

otherwise it tries to do a type.toString() at: https://github.com/sequelize/sequelize/blob/v6.5.1/lib/dialects/postgres/query-generator.js#L466 but type is undefined.

Solution 2

you can use changeColumn instead of addColumn because addColumn will add new column in your table . you can define your migration like this :

Migration File

module.exports = {
    up: (queryInterface, Sequelize) => {
        return Promise.all([
            queryInterface.changeColumn('your table name ', 'name', {
                type: Sequelize.TEXT,
                allowNull: true,
            }, {
                transaction,
            })
        ])
    },

    down: (queryInterface, Sequelize) => {
        return Promise.all([
            queryInterface.changeColumn('your table name ', 'name', {
                type: Sequelize.STRING,
                allowNull: true,
            }, {
                transaction,
            })
        ])
    }
};

Comments

[Admin]

ahh so the down on the migration is type: Sequelize.STRING, ?

[Vyas Arpit]

if you want to undo migration than it'll set your type as older one which is STRING .

[Admin]

if the column already exist bay do i still need to invoke if (!Object.keys(tableDef).includes('name')) { ?