# 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](https://sequelize.org) . 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](https://sequelize.org/master/manual/migrations.html). 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](https://www.npmjs.com/package/sequelize-cli)  tool. Thus, install it first:
```bash
npm install --save-dev sequelize-cli
```

Then you create a migration using the following command:
```bash
npx sequelize-cli migration:generate --name create_user_table
```

Which will create a file named `20210326170558-create_user_table.js` in your migrations folder:
```javascript
'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:

```javascript
// 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');
    }
};
```

```javascript
// 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:
```bash
npx sequelize-cli db:migrate
```

To rollback the last applied migration run:
```bash
npx sequelize-cli db:migrate:undo
```

To rollback all the applied migrations run:
```bash
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([])`:
```javascript
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:
```javascript
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 `await`s:
```javascript
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:

```javascript
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:
```javascript
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:
```javascript
'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."