CODEWITHSUNDEEP
sequelize.jssequelize-cli
Artsem Stalavitski
Artsem Stalavitski

Reputation: 31

sequelize.js migration execution sequence

I have a several queries I want to do in the same migration. They are related so they have to be executed in the particular order. This is my migration's up method:

up: (queryInterface, Sequelize) =>
queryInterface.sequelize.transaction(transaction =>
  Promise.all([
    queryInterface.renameColumn(table, 'status', 'status_temp', { transaction }),
    queryInterface.sequelize.query(
      'ALTER TYPE enum_users_status RENAME TO enum_users_status_temp;',
      {
        transaction
      }
    ),
    queryInterface.addColumn(
      table,
      'isActive',
      {
        allowNull: false,
        defaultValue: false,
        type: Sequelize.BOOLEAN
      },
      { transaction }
    ),
    queryInterface.bulkUpdate(
      table,
      {
        isActive: true
      },
      {
        status: 'active'
      },
      { transaction }
    ),
    queryInterface.addColumn(
      table,
      'status',
      {
        allowNull: false,
        defaultValue: STATUS_FREE,
        type: Sequelize.ENUM(STATUS_FREE, STATUS_BUSY, STATUS_DELETED)
      },
      { transaction }
    ),
  ])
),

When I'm executing it order is mixing up in a weird way:

Executing (591dc838-4659-4de4-b605-54b7c03c527f): START TRANSACTION;
...
Executing (591dc838-4659-4de4-b605-54b7c03c527f): ALTER TYPE     enum_users_status RENAME TO enum_users_status_temp;
Executing (591dc838-4659-4de4-b605-54b7c03c527f): ALTER TABLE "public"."users" ADD COLUMN "isActive" BOOLEAN NOT NULL DEFAULT false;
Executing (591dc838-4659-4de4-b605-54b7c03c527f): UPDATE "users" SET "isActive"=true WHERE "status" = 'active'
Executing (591dc838-4659-4de4-b605-54b7c03c527f): CREATE TYPE "public"."enum_users_status" AS ENUM('free', 'busy', 'deleted');ALTER TABLE "public"."users" ADD COLUMN "status" "public"."enum_users_status" NOT NULL DEFAULT 'free';
Executing (591dc838-4659-4de4-b605-54b7c03c527f): ALTER TABLE "users" RENAME COLUMN "status" TO "status_temp";
Executing (591dc838-4659-4de4-b605-54b7c03c527f): ROLLBACK;

And I'm getting error because of this:

ERROR: column "status" of relation "users" already exists

Is there a way to make these queries run in particular order?

UPD: My expected result is smth like this

      START TRANSACTION;
      ALTER TABLE "users" RENAME COLUMN "status" TO "status_temp";
      ALTER TYPE enum_users_status RENAME TO enum_users_status_temp;
      ALTER TABLE "public"."users" ADD COLUMN "isNew" BOOLEAN NOT NULL DEFAULT true;
      UPDATE "users" SET "isNew"=false WHERE "status_temp" = 'active';
      CREATE TYPE "public"."enum_users_status" AS ENUM('free', 'busy', 'deleted');
      ALTER TABLE "public"."users" ADD COLUMN "status" "public"."enum_users_status" NOT NULL DEFAULT 'free';
      UPDATE "users" SET "status"='busy' WHERE "isFree" = false;
      UPDATE "users" SET "status"='deleted' WHERE "status_temp" = 'deleted';
      ALTER TABLE "public"."users" DROP COLUMN "isFree";
      ALTER TABLE "public"."users" DROP COLUMN "status_temp";

Upvotes: 2

Views: 4341

Answers (3)

Jumshud
Jumshud

Reputation: 1425

It is possible by using bluebirdjs Promise.map function with {concurrency: 1} option as below:

const Promise = require('bluebird');
...

up: function (queryInterface, Sequelize) {
    return Promise.map([
            queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 0'),
            queryInterface.addColumn(
                'table1',
                'column1',
                {
                    type: Sequelize.INTEGER,
                    after: 'id',
                    defaultValue: 0
                }),
            queryInterface.sequelize.query('SET FOREIGN_KEY_CHECKS = 1'),
            queryInterface.addColumn(
                'table2',
                'column2',
                {
                    type: Sequelize.BOOLEAN,
                    after: 'column3',
                    defaultValue: 0
                })
        ],
        (promise) => promise,
        {concurrency: 1}
    );
}

Note: You can set concurrency option to n in order to execute n promises concurrently

Upvotes: 0

Ellebkey
Ellebkey

Reputation: 2301

As Eugene pointed out, your problem is that you are calling all your migrations with Promise.all. I will highly recommend to do individual migrations files. This way you have a better structure of your project and easier to understand to new developers.

command to create a migration:

sequelize migration:generate --name [name-of-your-migration]

to execute all the migrations on creation order and those that where not run already:

sequelize db:migrate

Upvotes: 0

Eugene Shilin
Eugene Shilin

Reputation: 461

This is happening because you're using Promise.all which execute promises in parallel. There is no guarantee that your promises will be executed in order they defined. So you need to use .then method to execute your workflow consequentially.

up: (queryInterface, Sequelize) =>
queryInterface.sequelize.transaction(transaction =>
  queryInterface.renameColumn(table, 'status', 'status_temp', { transaction })
    .then(() => queryInterface.sequelize.query(
      'ALTER TYPE enum_users_status RENAME TO enum_users_status_temp;',
      {
        transaction
      }
    ))
    .then(...);
),

Upvotes: 3

Related Questions