Is it possible to update existing data in postgresql database in a migration using sequelize

Question

I'm trying to add a field to a model using a sequelize migration, and need to update data from within the 'up' migration. However, calls to the database to update data within the migration do not complete or throw an error, they just hang.

I'm trying to add a registrationType field to an existing model in my database. This field should not be null, so I need to add the 'allowNull: false' prop. Old registrations, who do not yet have a registrationType set, should be updated to the right type, using data that is already present in the model. To do that, I need to access an ID field in the model, get the linked object (a registration links to a location, which has a locationtype) and use that to determine the registrationType. I added code to the migration as if it was a normal database operation updating some data, but these calls do not return or throw an error.

I cannot (and do not want to) use a default value, as the value should be determined for every registration, based on existing data (and adding a default value will render the allowNull prop obsolete). My approach: - add the column without 'allowNull' constraint (using addColumn) - update all existing data - add 'allowNull' constraint (using changeColumn)

"use strict";

/*const db = require("../models");
const Registration = db.Registration;
const Site = db.Site;
const Location = db.Location;
*/
const REGISTATION_MODEL = "Registrations";
module.exports = {
    up: async (queryInterface, Sequelize) => {
        const transaction = await queryInterface.sequelize.transaction();

        try {
            const Registration = await queryInterface.sequelize.import(
                "../models/registration.js"
            );
            const Site = await queryInterface.sequelize.import(
                "../models/site.js"
            );
            const Location = await queryInterface.sequelize.import(
                "../models/location.js"
            );

            await queryInterface.addColumn(
                REGISTATION_MODEL,
                "registrationType",
                {
                    type: Sequelize.STRING
                },
                { transaction }
            );

            console.log(
                " * Column added, going to update existing registrations."
            );
            const registrations = await Registration.findAll();
            console.log(
                `\tFound ${registrations.length} registrations to be updated.`
            );
            for await (const registration of registrations) {
                const site = await Site.findByPk(registration.SiteId);
                const location = await Location.findByPk(site.LocationId);
                await registration.update(
                    {
                        registrationType: location.locationType
                    },
                    { transaction }
                );
            }

            console.log(`\tUpdated ${registrations.length} registrations.`);
            console.log(" * Adding 'allowNull:false' to field.");
            //allowNull: false
            await queryInterface.changeColumn(
                REGISTATION_MODEL,
                "registrationType",
                { type: Sequelize.STRING, allowNull: false },
                { transaction: t }
            );
            await transaction.commit();
        } catch (ex) {
            await transaction.rollback();
            console.error("Something went wrong: ", ex);
        }
    },

    down: (queryInterface, Sequelize) => {
        return queryInterface.removeColumn(
            REGISTATION_MODEL,
            "registrationType"
        );
    }
};

Output:

Loaded configuration file "config/config.json".
Using environment "development".
== 20191107134514-add-registration-types: migrating =======
 * Column added, going to update existing registrations.

And hangs after that.

The code shown here does not produce any errors, nor does it produce any output. I added console.log statements, and ran the migration again, which shows the code hangs at the first findAll() call. Can anyone tell me how I should do this?

Answer 1

Update: As discussed in the chat we need to separate the addColumn, changeColumn and update queries into separate files. Since we cannot run all these under the same transaction because they affect the same tables.

If you want to await inside the migration functions you need to make the up/down function async and run your transactions using await and async as well:

Below is the migration file for updating the table only. Create separate migrations for addColumn and changeColumn. Run them in the sequence:

1. Add Column
2. Update Table
3. Change Column

Migration for table update:

up: async (queryInterface, Sequelize) => {
    // declare transaction outside try catch so it is available in both
    const transaction = await queryInterface.sequelize.transaction()
    try {
        // No need to use transactions for read operations
        const registrations = await Registration.findAll()

        // using for...of loop which supports awaiting inside it
        for await (const registration of registrations) {
            const site = await Site.findByPk(registration.SiteId)
            const location = await Location.findByPk(site.LocationId)

            // Make sure to await on all sequelize methdos
            await registration.update({ registrationType: location.locationType })
        }

        // Commit transaction if no error occurs
        await transaction.commit()
    } catch (error) {
        // Rollback transaction if error occurs
        await transaction.rollback()
        console.error("Something went wrong: ", ex)
    }
}