Foreign Key Column Migrations Tutorial

Welcome to this tutorial on creating foreign key column migrations in Sequelize. Imagine you are building a complex railway system where different train lines (tables) need to be connected by specific tracks (foreign keys) to ensure that passengers (data) can transfer safely between routes. In this lesson, you will learn how to create these connections between tables by adding foreign key columns in your migrations.

In a relational database, defining a relationship means that one table includes a column whose values reference a column in another table. This reference acts as a link, ensuring that the data remains consistent and related. In Sequelize migrations, this is achieved by adding a foreign key column along with a "references" attribute that tells the database which table and column to refer to, and an "onDelete" attribute to specify how deletions in the referenced table affect the referencing table.

Defining Relationships in SQL and Sequelize

In standard SQL, if you want to relate two tables, you create a foreign key in one table that references the primary key in another table. For example, consider this SQL code:

CREATE TABLE TableOneName (
  id INTEGER PRIMARY KEY,
  -- other columns --
  tableTwoId INTEGER,
  FOREIGN KEY (tableTwoId) REFERENCES TableTwoName(id) ON DELETE CASCADE
);

In Sequelize, you do the same thing in your migration file using the queryInterface.createTable method. You define the foreign key column by including a references attribute. The references attribute is an object that contains:

model: The name of the table that you want to reference (note that this is the table name, not the model name).
key: The column in the referenced table that serves as the primary key (typically id).

You can also add an onDelete attribute to specify what should happen when a record in the referenced table is deleted. For example, setting onDelete: 'cascade' will automatically remove all records in the referencing table that depend on that record.

Example: Twitter-like Clone

Imagine you are designing a Twitter-like application with two tables: Users and Tweets. Each tweet is created by a user, so there is a one-to-many relationship between Users and Tweets.

First, create the Users table. Here is an example migration for the Users table:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstName: {
        type: Sequelize.STRING,
        allowNull: false
      },
      lastName: {
        type: Sequelize.STRING,
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });
  },

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

Next, create the Tweets table. Notice how we add a foreign key column userId that references the Users table:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Tweets', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      content: {
        type: Sequelize.STRING(280),
        allowNull: false
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      userId: {
        type: Sequelize.INTEGER,
        references: {
          model: 'Users',   // Note: this is the table name, not the model name
          key: 'id'
        },
        onDelete: 'cascade'
      }
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Tweets');
  }
};

In the Tweets migration, the userId column is defined with a references object that points to the Users table's primary key, id. The onDelete: 'cascade' setting ensures that if a user is deleted, all tweets associated with that user are automatically removed, preserving the integrity of your data.

Step-by-Step Follow-Along Exercise

Let's break down the process:

Imagine you're setting up your Twitter-like clone:

First, open your terminal and navigate to your project directory.

Next, generate a migration for the Users table (if you haven't already) and create the migration file with the appropriate columns as shown above.

Then, generate a migration file for the Tweets table with the foreign key:

npx sequelize-cli migration:generate --name create_tweets

Open the generated file and define the columns, making sure to add the userId foreign key column:

// In your create_tweets migration file
await queryInterface.createTable('Tweets', {
  id: { /* primary key definition */ },
  content: { type: Sequelize.STRING(280), allowNull: false },
  createdAt: { /* timestamp definition */ },
  userId: {
    type: Sequelize.INTEGER,
    references: { model: 'Users', key: 'id' },
    onDelete: 'cascade'
  }
});

Save the file and run your migrations using:

npx dotenv sequelize db:migrate

Check your database to verify that the Tweets table now includes the userId column and that it properly references the Users table.

When to Use Foreign Key Migrations

Foreign key migrations are essential when you need to define relationships between tables. They ensure that data remains consistent across your database, such as when a tweet must always be associated with a valid user. Using foreign keys also helps enforce business rules (like cascade deletion), making your application more robust.

In production environments, this is especially important to maintain data integrity and prevent orphaned records that no longer have a valid reference.

Additional Topics to Explore

Once you're comfortable with basic foreign key migrations, consider exploring:

Advanced relationship options like adding foreign key constraints on updates and cascading actions.
How to alter existing tables to add or change foreign key columns.
Using Sequelize associations to further simplify managing relationships in your application code.
Strategies for handling complex, multi-table relationships in a large-scale application.

Conclusion

In this tutorial, you learned how to perform a migration to add a foreign key column in Sequelize. You discovered that by defining a foreign key in your migration file using the references attribute, you can establish relationships between tables. Additionally, setting options like onDelete: 'cascade' helps maintain data integrity by ensuring that related records are automatically removed when their parent record is deleted.

With this knowledge, you are well-equipped to build relational databases that are both consistent and resilient. Keep practicing these techniques to master the art of database relationships in your applications.