4.1.4: Sequelize Many-to-Many

Search…
Introduction
Similar to one-to-many associations, Sequelize enables many-to-many associations through under-the-hood foreign key dereferencing. See Query Associated Tables section below for how our many-to-many business logic might look with Sequelize.
Setup Packages and Folders, Configure DB
Set up Sequelize with a new Node application and configure the DB in the same way we did in Module 4.1.1: Intro to Sequelize. Update config.js to use a new DB name, manygrocery_development instead of grocery_development to distinguish the DB from module 4.2.1's. Stop after creating the DB and follow the steps below to create a many-to-many SQL relationship with Sequelize.
Use the cheat sheet to repeat the commands.​
Migrations: Create Categories and Items Tables
npx sequelize migration:generate --name create-categories-items-tables
Sample Migration File (<GENERATED_DATE>-create-categories-items-tables.js)
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('categories', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      name: {
        type: Sequelize.STRING,
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
​
    await queryInterface.createTable('items', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      name: {
        type: Sequelize.STRING,
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
    await queryInterface.createTable('category_items', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      category_id: {
        type: Sequelize.INTEGER,
        references: {
          model: 'categories',
          key: 'id',
        },
      },
      item_id: {
        type: Sequelize.INTEGER,
        references: {
          model: 'items',
          key: 'id',
        },
      },
      created_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updated_at: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },
​
  down: async (queryInterface, Sequelize) => {
    // Drop category_items first because it references items and categories.
    await queryInterface.dropTable('category_items');
    await queryInterface.dropTable('items');
    await queryInterface.dropTable('categories');
  },
};
Create the categories, items, and category_items tables in our DB by running migrations.
npx sequelize db:migrate
Verify table creation in psql.
psql -d manygrocery_development
Models: Create Category and Item Models
ItemsCategories model not necessary.
Note how we do not need to create a model for the join table! If the table only contains foreign keys, Sequelize does not require us to create a model file for the table. (If the table has non-foreign keys, you would need to create a model).
models/category.mjs
export default function initCategoryModel(sequelize, DataTypes) {
  return sequelize.define(
    'category',
    {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      name: {
        type: DataTypes.STRING,
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
    },
    {
      // The underscored option makes Sequelize reference snake_case names in the DB.
      underscored: true,
    }
  );
}
models/item.mjs
export default function initItemModel(sequelize, DataTypes) {
  return sequelize.define(
    'item',
    {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: DataTypes.INTEGER,
      },
      name: {
        type: DataTypes.STRING,
      },
      createdAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: DataTypes.DATE,
      },
    },
    {
      // The underscored option makes Sequelize reference snake_case names in the DB.
      underscored: true,
    }
  );
}
models/index.mjs
import sequelizePackage from 'sequelize';
import allConfig from '../config/config.js';
​
import initItemModel from './item.mjs';
import initCategoryModel from './category.mjs';
​
const { Sequelize } = sequelizePackage;
const env = process.env.NODE_ENV || 'development';
const config = allConfig[env];
const db = {};
​
let sequelize = new Sequelize(
  config.database,
  config.username,
  config.password,
  config
);
​
db.Item = initItemModel(sequelize, Sequelize.DataTypes);
db.Category = initCategoryModel(sequelize, Sequelize.DataTypes);
​
// in order for the many-to-many to work we must mention the join table here.
db.Item.belongsToMany(db.Category, { through: 'category_items' });
db.Category.belongsToMany(db.Item, { through: 'category_items' });
​
db.sequelize = sequelize;
db.Sequelize = Sequelize;
​
export default db;
Seed: Create Seed Data for DB
Seed migration files are a specific category of migration file used to generate seed data. This is helpful when creating DB copies for testing purposes and we need reproduce-able data in the DB.
Use sequelize-cli to generate a seed data migration file.
npx sequelize seed:generate --name seed-data
Sample Seed Data Migration File (<GENERATED_DATE>-seed-data.js)
The following seed data migration file creates 4 categories, and 3 items that reference each of those categories, i.e. 9 items total. All our added to the DB when the seed migration runs.
module.exports = {
  up: async (queryInterface) => {
    // Define category data
    const categoryData = [
      {
        name: 'fish',
        created_at: new Date(),
        updated_at: new Date(),
      },
      {
        name: 'fruit',
        created_at: new Date(),
        updated_at: new Date(),
      },
      {
        name: 'healthy',
        created_at: new Date(),
        updated_at: new Date(),
      },
      {
        name: 'canned',
        created_at: new Date(),
        updated_at: new Date(),
      },
    ];
​
    // Bulk insert categories, returning=true, 
    // and destructure the returned results array, for use in categoryItemData
    const [fishCategory, fruitCategory, healthyCategory, cannedCategory] =
      await queryInterface.bulkInsert('categories', categoryData, {
        returning: true,
      });
​
    // Define item data
    const itemData = [
      {
        name: 'banana',
        created_at: new Date(),
        updated_at: new Date(),
      },
      {
        name: 'tuna',
        created_at: new Date(),
        updated_at: new Date(),
      },
      {
        name: 'peach',
        created_at: new Date(),
        updated_at: new Date(),
      },
    ];
​
    // Bulk insert categories, returning=true, 
    // and destructure the returned results array, for use in categoryItemData
    const [banana, tuna, peach] = await queryInterface.bulkInsert(
      'items',
      itemData,
      { returning: true }
    );
​
    // Define category item data based on generated categories and items
    const categoryItemData = [
      // banana is a fruit
      {
        item_id: banana.id,
        category_id: fruitCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
      // banana is healthy
      {
        item_id: banana.id,
        category_id: healthyCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
      // tuna is fish
      {
        item_id: tuna.id,
        category_id: fishCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
      // tuna is canned
      {
        item_id: tuna.id,
        category_id: cannedCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
      // peach is fruit
      {
        item_id: peach.id,
        category_id: fruitCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
      // peach is canned
      {
        item_id: peach.id,
        category_id: cannedCategory.id,
        created_at: new Date(),
        updated_at: new Date(),
      },
    ];
​
    // Bulk insert category items
    await queryInterface.bulkInsert('category_items', categoryItemData);
  },
​
  down: async (queryInterface) => {
    await queryInterface.bulkDelete('category_items', null, {});
    await queryInterface.bulkDelete('items', null, {});
    await queryInterface.bulkDelete('categories', null, {});
  },
};
Run seed migrations.
npx sequelize db:seed:all
Query Associated Tables in Sequelize
Each Category and Item instance will have association methods that Sequelize provides because of the belongsToMany associations we defined in models/index.mjs. The association methods will be named after the associated model. More info in docs here.
Create an Item and Associate it with a Category
In a single Sequelize command we can create two rows- one in the items table and one in the category_items table.
createItemInCategory.mjs
import db from './models/index.mjs';
​
const createItemInCategory = async () => {
  try {
    const category = await db.Category.findOne({
      where: {
        name: process.argv[2],
      },
    });
​
    // create an item record AND a join table record
    const item = await category.createItem({
      name: process.argv[3],
    });
​
    console.log(item);
  } catch (error) {
    console.log(error);
  }
};
​
createItemInCategory();
Create a blueberry item and put it in the fruit category.
node createItemInCategory.mjs fruit blueberry
Associate an Item with a Category
We can write the command to create a row in the table that doesn't have a model- the join table.
associateItem.mjs
import db from './models/index.mjs';
​
const createAssoc = async () => {
  try {
    const category = await db.Category.findOne({
      where: {
        name: process.argv[2],
      },
    });
​
    const item = await db.Item.findOne({
      where: {
        name: process.argv[3],
      },
    });
​
    // create the row in the join table
    const result = await category.addItem(item);
    console.log(result);
  } catch (error) {
    console.log(error);
  }
};
​
createAssoc();
Add the peach item to the fish category. (In addition to the fruit category it's already in).
node associateItem.mjs fish peach
Note that for the above examples of creating association records we are passing in entire *item* objects into the methods. If we are creating association records we also always have the option of passing the item foreign key ids instead.
Get Items in Given Category
In the following example we call the getItems method to get all items associated with category.
get-categorys-items.mjs
import db from './models/index.mjs';
​
const getCategorysItems = async () => {
  try {
    const category = await db.Category.findOne({
      where: {
        name: [process.argv[2]],
      },
    });
    const categoryItems = await category.getItems();
    console.log(categoryItems.map((categoryItem) => categoryItem.name));
  } catch (error) {
    console.log(error);
  }
};
​
getCategorysItems();
Sample Command
node get-categorys-items.mjs fruit
Sample Output
[ 'banana', 'peach' ]
Sequelize Magic
Note how this method produces a JOIN query under the hood! When using the getItems method of the category Sequelize knows how to create the query between category_items and items and is able to get the Item records.
Get Categories of Given Item
get-items-categories.mjs
In the following example we call the getCategories method to get the categories associated with item.
import db from './models/index.mjs';
​
const getItemsCategories = async () => {
  try {
    const item = await db.Item.findOne({
      where: {
        name: [process.argv[2]],
      },
    });
    const itemCategories = await item.getCategories();
    console.log(itemCategories.map((itemCategory) => itemCategory.name));
  } catch (error) {
    console.log(error);
  }
};
​
getItemsCategories();
Sample Command
node get-items-categories.mjs banana
Sample Output
[ 'fruit', 'healthy' ]
Note that almost all Sequelize methods return Promises, including association methods such as get<MODEL_NAME> and set<MODEL_NAME>.
Exercise
Replicate the above code and verify results.