We have to write a third migration, adding the userId column to our Post table, but also including it in our database Post model.

Generating a boilerplate migration file is very easy with the Sequelize CLI:

sequelize migration:create --migrations-path src/server/migrations --name add-userId-to-post

You can directly replace the content, as follows:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.addColumn('Posts',
        'userId',
        {
          type: Sequelize.INTEGER, 
        }),
      queryInterface.addConstraint('Posts', ['userId'], {
        type: 'foreign key',
        name: 'fk_user_id',
        references: {
          table: 'Users',
          field: 'id',
        },
        onDelete: 'cascade',
        onUpdate: 'cascade',
      }),
    ]);
  },

  down: (queryInterface, Sequelize) => {
    return Promise.all([
      queryInterface.removeColumn('Posts', 'userId'),
    ]);
  }
};

This migration is a bit more complex, and I will explain it on a step-by-step basis.

In the up migration, we are using the queryInterface to first add the userId column to the Posts table.

Secondly, we add a foreign key constraint, with the addConstraint function. The constraint represents the relationship between both the user and the post entities. The relationship is saved in the userId column of the Post table.

I experienced some issues when running the migrations without using Promise.all, which ensures that all promises in the array are resolved. Returning only the array did not run both the addColumn and addConstraint methods.

The preceding addConstraint function receives the foreign key string as a type which says that the data type is the same as the corresponding column in the Users table. We want to give our constraint the custom name fk_user_id, in order to identify it later.

Then, we specify the references field for the userId column. Sequelize requires a table, which is the Users table, and the field that our foreign key relates to, which is the id column of the User table. This is everything that is required to get a working database relationship.

Furthermore, we change the onUpdate and onDelete constraints to cascade. What this means is that, when a user either gets deleted or has their user ID updated, the change is reflected in the user's posts. Deleting a user results in deleting all posts of a user, and updating a user's ID updates the ID on all of the user's posts. We do not need to handle all of this in our application code, which would be inefficient.

Rerun the migration, in order to see what changes occurred:

sequelize db:migrate --migrations-path src/server/migrations --config src/server/config/index.js

The benefit of running migrations through Sequelize is that it goes through all of the possible migrations from the migrations folder. It excludes those that are already saved inside of the SequelizeMeta table, and then chronologically runs the migrations that are left. Sequelize can do this because the timestamp is included in every migration's filename.

After running the migration, there should be a Users table, and the userId column should be added to the Posts table.

Take a look at the relation view of the Posts table in phpMyAdmin. You can find it under the Structure view, by clicking on Relation view:

As you can see in the preceding screenshot, we have our foreign key constraint. The correct name was taken, and the cascade option, too.

If you receive an error when running migrations, you can easily undo them, as follows:

sequelize db:migrate:undo --migrations-path src/server/migrations --config src/server/config/index.js

This command undoes the most recent migrations. Always be conscious of what you do here. Keep a backup if you are unsure whether everything works correctly.

You can also revert all migrations at once, or only revert to one specific migration, so that you can go back to a specific timestamp:

sequelize db:migrate:undo:all --to XXXXXXXXXXXXXX-create-posts.js --migrations-path src/server/migrations --config src/server/config/index.js

Leave out the parameter --to to undo all migrations.

We have now established the database relationship, but Sequelize must know about the relationship, too. You will learn how this is done in the next section.