3.3.2: Sequelize Many-To-Many (M-M) Relationships

Learning Objectives

  1. Sequelize provides relationship methods to query for data related through M-M relationships

  2. Understand how to set up models and migrations to support M-M relationships with Sequelize

  3. Understand how to query data related through M-M relationships with Sequelize

  4. Understand how to add and query for data in junction tables for M-M relationships

Introduction

Similar to 1-M relationships, Sequelize provides relationship methods for M-M relationships that make it easy to query related data. Using our Person and Personality example from the SQL M-M submodule where each person can have many personalities and vice versa, Sequelize allows us to call methods such as person.getPersonalities() and personality.getPeople() to retrieve related data.

The following is Sequelize's official introduction to M-M relationships with Sequelize.

  1. Note the concept of the junction table is central to many to many relationships

  2. In Sequelize's example their junction table is called actor_movies, but it could also have been called movie_actors if they wanted. The convention is to name the junction table with both model names concatenated, where the 1st model name is singular and the 2nd is plural. It does not matter which model is 1st and which is 2nd.

  3. Note that we declare an M-M relationship between 2 models with the belongsToMany association method, where we specify the junction table name in a 2nd parameter with a through attribute

  4. We will need to create the junction table actor_movies using a migration because we are not using Sequelize sync

  5. There is no need to define the actor_movies junction model ourselves unless we plan to include non-foreign-key data in that model. However, we will always need to create the actor_movies table with a migration because Sequelize will not create it for us.

Models and Migrations for M-M relationships with Sequelize

We will continue with the People and Personalities examples from the SQL M-M Relationships submodule.

When to define new model for junction table

If the 2 tables we are associating with an M-M relationship do not need to store non-foreign-key data in their junction table, we do not need to create a standalone model for the junction table in our app. However, we will always need to use a migration to create the junction table in our database.

In this section let us assume there is no non-foreign-key data in the junction table and focus on model association methods and the migration for the junction table.

Model association methods for M-M relationships

Given Person and Personality models, we apply the belongsToMany association method on each of them toward the other to set up an M-M relationship in Sequelize. Like in the Actor and Movie example in the Sequelize official docs above, we declare the through table name person_personalities in the belongsToMany method. This also means we need to create a person_personalities table in our database using migrations.

db/models/person.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Person extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      this.belongsToMany(models.personality, { through: "person_personalities" });
    }
  }
  Person.init(
    {
      name: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "person",
      underscored: true,
    }
  );
  return Person;
};
db/models/personality.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Personality extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      this.belongsToMany(models.person, { through: "person_personalities" });
    }
  }
  Personality.init(
    {
      trait: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "personality",
      underscored: true,
    }
  );
  return Personality;
};

Junction table migration for M-M relationships

Now that we've set up the M-M association between Person and Personality models, let's create a migration to create the junction table person_personalities in our database. The following migration assumes we have past migrations that have already created people and personalities tables.

20220531155826-create-personpersonality.js
"use strict";
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.createTable("person_personalities", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      person_id: {
        type: Sequelize.INTEGER,
        references: {
          model: "people",
          key: "id",
        },
      },
      personality_id: {
        type: Sequelize.INTEGER,
        references: {
          model: "personalities",
          key: "id",
        },
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable("person_personalities");
  },
};

Notice there are 2 foreign keys in the person_personalities junction table, 1 referencing the people table and the other referencing the personalities table. Even though the key in the references object is model, Sequelize expects the value to be a plural table name.

Just like that, we've set up an M-M association in our app and are ready to start using Sequelize relationships methods on this relationship!

Query M-M Associated Tables in Sequelize

In the Sequelize 1-M Relationships submodule we read about "special methods/mixins added to instances" and ignored the belongsToMany section. Let's go back and read the belongsToMany section to understand what relationship methods Sequelize provides to models associated with belongsToMany.

  1. Probably the most common relationship method we will use is fooInstance.getBars(), simply getting all the instances of the related table associated with the current instance. For example, person.getPersonalities().

  2. No need to worry about retrieving join table attributes in this section. We will explore that in the "Handling non-foreign-key data in junction table" section below.

  3. Note the large number of relationship methods we can call on model instances with a M-M relationship, including methods to count the number of related model instances, check existence of related model instances, set, add, remove, create related model instances. We will likely not need to use all of these, but good to keep in mind in case using them can simplify our code logic!

  4. We can generally assume that Sequelize will get the plural form of our model names right. For example, we can assume that the Sequelize relationship method to get personalities associated with a Person instance will be Person.getPersonalities() and not something like Person.getPersonalitys().

Handling non-foreign-key data in junction table

When we wish to store and retrieve non-foreign-key data in our junction table and access that data from our apps, we will need to explicitly create a new model in our apps for the junction model. Once we've created the junction model and junction table migration we can discuss how to query the junction model in our apps.

Models and migrations for junction tables with non-foreign-key data

Models

We must explicitly define the junction table model if we wish to query non-foreign-key data in the junction table. We name the model the singular of the table name, i.e. since the table name was person_personalities, the model is PersonPersonality.

In the model is the intensity attribute that accompanies each person's personality trait. We also include an id attribute, typically omitted in models that we will include here to enable querying our junction table (more on this below). We also include the foreign keys to People and Personalities tables respectively.

Notice there are 2 belongsTo association methods to Person and Personality models respectively. Recall belongsTo associations are typically used with 1-M relationships. We included these because we want to declare 1-M associations between both Person and PersonPersonality and between Personality and PersonPersonality. We will explore why we add these 1-M relationships in the next section.

db/models/personpersonality.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class PersonPersonality extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // Define separate 1-M relationships with both Person and Personality models
      // to enable them to query junction model
      this.belongsTo(models.person);
      this.belongsTo(models.personality);
    }
  }
  PersonPersonality.init(
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false
      },
      PersonId: {
        type: DataTypes.INTEGER,
        references: {
          model: "people",
          key: "id",
        },
      },
      PersonalityId: {
        type: DataTypes.INTEGER,
        references: {
          model: "personalities",
          key: "id",
        },
      },
      intensity: DataTypes.INTEGER,
    },
    {
      sequelize,
      modelName: "personpersonality",
      underscored:true
    }
  );
  return PersonPersonality;
};

To complete the 1-M relationships between Person and PersonPersonality and Personality and PersonPersonality, we will need to add a hasMany association in both Person and Personality models. Below is the same code as before except with the new association.

db/models/person.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Person extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // belongsToMany is for the M-M association to query related Personality instances
      this.belongsToMany(models.personality, { through: models.personpersonality });
      // hasMany is for the 1-M association to query junction model
      this.hasMany(models.personpersonality);
    }
  }
  Person.init(
    {
      name: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "person",
      underscored: true
    }
  );
  return Person;
};
db/models/personality.js
"use strict";
const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class Personality extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // belongsToMany is for the M-M association to query related Person instances
      this.belongsToMany(models.person, { through: models.personpersonality });
      // hasMany is for the 1-M association to query junction model
      this.hasMany(models.personpersonality);
    }
  }
  Personality.init(
    {
      trait: DataTypes.STRING,
    },
    {
      sequelize,
      modelName: "personality",
      underscored_true
    }
  );
  return Personality;
};

Migrations

Reference the person_personalities migration above on this page. Add an intensity column to the person_personalities table with integer data type.

Querying junction tables with non-foreign-key data

Sequelize has a separate, comprehensive guide to M-M relationships that includes how to manage non-foreign-key data in junction tables.

Defining junction model to access non-foreign-key junction table attributes

  1. The content on calling belongsToMany association methods is the same as what we read above

  2. Note: defining the junction model ourselves allows us to specify non-foreign-key junction model values when calling Sequelize M-M relationship methods such as fooInstance.addBar(), or in the example on this page, user.addProfile().

  3. No need to worry about composite unique key for now, assume id is still the primary key for the junction table

Through table versus normal tables and the "Super Many-to-Many association"

  1. Having an id primary key on PersonPersonalities model is necessary for the "Super Many-to-Many relationship" that allows both Person and Personality instances to query both the junction table and the table associated via the M-M relationship. I.e., to allow Person to query both related PersonPersonality and Personality instances, vice versa for Personality.

  2. Notice that the tables in the database look the same regardless of whether we declare 2 1-M relationships between person and person_personality and personality and person_personality, or 1 M-M relationship between person and personality.

  3. The "Super Many-to-Many relationship" requires us to declare 1-M relationships between the 2 M-M tables and the junction table, and an M-M relationship between the 2 M-M tables. In our person and personality example, this means declaring 1-M relationships between person and person_personality and between personality and person_personality, and an M-M relationship between person and personality.

  4. We can ignore everything below "Aliases and custom key names" in the Sequelize M:N Associations guide for now. Those concepts are more advanced and we can come back to them as reference if we need them.

Querying the junction model

With the "super M-M relationship" we can query our junction model in multiple ways. The cleanest way is to query using M-M relationship methods with the joinTableAttributes option.

person.getPersonalities({ joinTableAttributes: ['intensity'] });

Alternatively we can use 1-M relationship methods to access the junction table, but that may require more acrobatics with Person and Personality IDs.

Additional Resources

  1. React Select is a React component library that provides an elegant UI for collecting 1-M and M-M relationship data.

Previous3.3.1: Sequelize One-To-Many (1-M) RelationshipsNext3.3.3: Advanced Sequelize Concepts