sequelize-cli db:migrate "hangs" adding a check constraint to a newly-added column

Question

I have the following migration file:

'use strict';

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction(t => {
            return queryInterface.addColumn('Vendors', 'status', Sequelize.STRING(30), { transaction: t }).then(() => {
                console.log("Created column..."); // this prints
                return queryInterface.addConstraint('Vendors', ['status'], {
                    type: 'check',
                    name: 'Vendors_status_check',
                    where: {
                        status: ['UNAPPROVED', 'SUBMITTED_FOR_APPROVAL', 'APPROVED', 'SUSPENDED']
                    },
                }, { transaction: t });
            }).catch(err => {
                console.log("Error::", err);
            });
        });
    },

    down: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction(t => {
            // return queryInterface.removeColumn('Vendors', 'status', { transaction: t });
            return queryInterface.removeConstraint('Vendors', 'Vendors_status_check', { transaction: t }).then(() => {
                return queryInterface.removeColumn('Vendors', 'status', { transaction: t });
            });
        })
    }
};

When I run sequelize-cli db:migrate, I get the following output & then the the command just "hangs": never completes, never errors out:

Sequelize CLI [Node: 12.14.1, CLI: 5.5.1, ORM: 5.21.3]

Loaded configuration file "config\sequelize_config.js".
Using environment "development".
== 20200224080020-vendor-status: migrating =======
Created column...

If I comment out queryInterface.addConstraint completely, the command finishes & adds the status column to the Vendors table. Running this migration against PostgresQL, if that makes any difference.

As per sequelize db:migrate hanging I tried adding dialectOptions: {ssl: true} to sequelize config, but there was no effect other than a deprecation warning.

Update 1

FWIW, running the following code (i.e., without a transaction) works as expected:

return queryInterface.addColumn('Vendors', 'status', {type: Sequelize.STRING(30)}/* , { transaction: t } */).then(() => {
    console.log("Created column..."); // this prints
    return queryInterface.addConstraint('Vendors', ['status'], {
        type: 'check',
        name: 'Vendors_status_check',
        where: {
            status: ['UNAPPROVED', 'SUBMITTED_FOR_APPROVAL', 'APPROVED', 'SUSPENDED']
        },
    }/* , { transaction: t } */);
}).catch(err => {
    console.log("Error::", err);
});

It's probably waiting for a lock. Check `pg_stat_activity()` from a different connection to validate that. — , Feb 24 '20 at 11:33
Hmm... this is a development DB & there's no other activity against it other than the code I am running. However, it does work without a transaction. OTOH, I've trying this with minor variations for hours before posting this question... — markvgti, Feb 24 '20 at 11:44
@a_horse_with_no_name I figured out what `pg_stat_activity()` is (new to PostgresQL) and AFA I can make out, there are no active connections. — markvgti, Feb 24 '20 at 11:53

score 0 · Answer 1 · answered Aug 29 '22 at 13:31

This issue is old so the interfaces may have changed since then but to anyone who faces a similar 'hanging' issue with addConstraint in a transaction make sure you check the proper function definition: https://sequelize.org/api/v6/class/src/dialects/abstract/query-interface.js~queryinterface#instance-method-addConstraint

At the time of writing: async addConstraint(tableName: string, options: object): Promise

So in the context of this question the proper format should be:

return queryInterface.addConstraint('Vendors', {
                    fields: ['status'],
                    type: 'check',
                    name: 'Vendors_status_check',
                    where: {
                        status: ['UNAPPROVED', 'SUBMITTED_FOR_APPROVAL', 'APPROVED', 'SUSPENDED']
                    },
                    transaction: t
                });

Note the transaction is inside the options object since the options object should be of type QueryInterfaceOptions which extends the Transactionable interface.

score -1 · Answer 2 · answered Jul 27 '21 at 02:04

-1

addConstraint cannot take transaction as an option. So the solution is to not wrap a call to it in a transaction.

answered Jul 27 '21 at 02:04

frandroid

1,343
16
26

sequelize-cli db:migrate "hangs" adding a check constraint to a newly-added column

Update 1

2 Answers2