How To: Multiple Queries in a Single Sequelize Migration

How To: Multiple Queries in a Single Sequelize Migration

In this blog post, I will discuss some details about the most popular NodeJS ORM called Sequelize . I will particularly take a look at database (schema) migrations and how to run multiple actions in a single Sequelize migration.

This post is based on the features of Sequelize v6.

In fact, you can find most of the information that I provide here in the official Sequelize Documentation. However, while the documentation tells you "what you can do," I'll tell you "what you have to do" to solve your problems.

Intro

In case you are not completely familiar, here is quick information about Sequelize migrations.

You should create a migration with the sequelize-cli tool. Thus, install it first:

npm install --save-dev sequelize-cli

Then you create a migration using the following command:

npx sequelize-cli migration:generate --name create_user_table

Which will create a file named 20210326170558-create_user_table.js in your migrations folder:

'use strict';

module.exports = {
    async up(queryInterface, Sequelize) {
        // await queryInterface.dosomething
    },

    async down(queryInterface, Sequelize) {
        // await queryInterface.dosomething
    }
};

The migrations folder location depends on your config, so don't try to be too smart that use the CLI tool. Also, the file name starts with the date. Its format is important, so, again, don't be too smart, and use the tool to generate the file.

The exact contents of the generated file may vary, but in any case, you will be exporting two functions: up and down. I suggest to to use my template with async and await because it looks nice, and it will be easier to expand it in the future. The up function should do the desired migration, while the down function should roll it back.

Here are some examples:

// create the Users table

module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.createTable(
            'Users', {
                _id: {
                    type: Sequelize.INTEGER,
                    allowNull: false,
                    primaryKey: true,
                    autoIncrement: true
                },
                name: {
                    type: Sequelize.STRING,
                    allowNull: false
                }
            }
        );
    },

    async down(queryInterface, Sequelize) {
        await queryInterface.dropTable('Users');
    }
};

// add age column to the Users table

module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.addColumn(
            'Users',
            'age',
            Sequelize.INTEGER
        );
    },

    async down(queryInterface, Sequelize) {
        await queryInterface.removeColumn('Users', 'age');
    }
};

To apply all the migrations that were not applied previously run:

npx sequelize-cli db:migrate

To rollback the last applied migration run:

npx sequelize-cli db:migrate:undo

To rollback all the applied migrations run:

npx sequelize-cli db:migrate:undo:all

As you noticed in the previous examples, we were only doing a single action (query) per migration. What if you want to add two columns? Or add a table and a column to another table?

First Rule of Multiple Queries Per Migration: Don't

As stupid as it may sound: don't make migrations with multiple queries. If you want to add two columns: add two migrations. That's it. You can rarely come up with a pair of queries that have to run in a single migration.

Having said that, this is a post about how to run multiple queries in a single migration after all. Maybe you are fixing an existing migration that worked on an old DB but doesn't work on a new one. Or maybe you have some other needs. Let's take a look a the good ways to run multiple queries in a single migration.

Trivial Await Sequence

Each queryInterface query returns a promise. As a result, you will find many resources that will tell you to wrap everything in a Promise.all([]):

up(queryInterface, Sequelize) {
    Promise.all([
        queryInterface.changeColumn('Users', 'age', ...),
        queryInterface.changeColumn('Users', 'taxId', ...)
    ]);       
}

This doesn't work all the time. For example, there is no such thing as "changing a column" in SQLite. But as Sequelize is the most used NodeJS ORM, it is smart enough to create a new table instead, with the needed "changed" column, and copy all the data over. Promise.all runs all the internal promises asynchronously, and you don't want it, especially when for each change, a new slightly modified table is created, and all the data is copied over.

That's why you should use the async/await template and list all the queries in order:

async up(queryInterface, Sequelize) {
    await queryInterface.changeColumn('Users', 'age', ...);
    await queryInterface.changeColumn('Users', 'taxId', ...);
}

Each await line will cause the rest of the code to wait until its promise resolves. This code also looks cleaner, doesn't it 😎? Don't forget that you also need to do the opposite change in the down method, but you just stack the await queries in the same way.

In fact, the async/await code expands to simple promises, so here is also an ugly way to do the same thing without awaits:

up(queryInterface, Sequelize) {
    return queryInterface.changeColumn('Users', 'age', ...).then(() =>
        queryInterface.changeColumn('Users', 'taxId', ...)
    );
}

And, of course, it gets uglier with each new query.

‼️ Beware: this approach may cause very annoying situations when one of the queries fails. If that happens, you will end up with a partially applied migration that is not considered "applied" because it failed, so you won't be able to roll it back.

Wrapping Queries in a Transaction

"Who on Earth needs to run migration queries outside of a transaction?" you would ask? "Obviously, the most used NodeJS ORM runs migrations inside of a transaction!" you would think. But no, Sequelize doesn't do this automatically, so you need to do it yourself.

It's not a big deal; you just wrap all your await queries in a transaction block and pass the transaction in an object at the last parameter:

async up(queryInterface, Sequelize) {
    await queryInterface.sequelize.transaction(async(transaction) => {
        await queryInterface.changeColumn('Users', 'age', ..., { transaction });
        await queryInterface.changeColumn('Users', 'taxId', ..., { transaction });
    }
}

Pay attention: here we use a magic c̶o̶c̶a̶i̶n̶e̶ sugar called destructuring. If you named your transaction variable t, you would have to pass an object {t: transaction}.

By running all the queries in a transaction, we ensure that the previously applied queries will be automatically rolled back if any other query fails.

Bonus: Benefiting from Sequentiality

By using a sequence of await statements in a migration, we can be sure that each statement will run only after the previous one finishes. Thus we can pack some reasonable steps in a single migration.

Imagine that you want to add a creationDate column to a table. It should not be empty, and the default value should be the current time. This is not some special case, and the Sequelize query looks like this:

queryInterface.addColumn(
    'Alerts',
    'creationDate',
    {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW,
        allowNull: false
    }
);

This code also generates a valid query for MySQL. And in SQLite, you can totally create a table with such a column. But you can't add a column like this to an existing table 😒. And you may think that the most used NodeJS ORM sorts this out behind the scenes. But no, you have to deal with it yourself. So what you can do here is to add a new column without constraints, then change it, and add the constraints. And if you recall, changing a column in SQLite is achieved by creating a new table, and a new table can have such constraints 🤯. So here goes the migration:

'use strict';

module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.sequelize.transaction(async(transaction) => {

            await queryInterface.addColumn(
                'Users',
                'creationDate',
                Sequelize.DATE,
                { transaction }
            );
            await queryInterface.changeColumn(
                'Users',
                'creationDate',
                {
                    type: Sequelize.DATE,
                    defaultValue: Sequelize.NOW,
                    allowNull: false
                },
                { transaction }
            );

        });
    },

    async down(queryInterface) {
        // no transaction because we do only a single query
        await queryInterface.removeColumn('Users', 'creationDate');
    }
};

On the other hand, nothing stops you from making two single–query migrations out of this one. Still, maybe it's easier to comprehend if you have one "logical" action per file, such as "adding a certain column to a table."