Bootcamp
Search…
4.1.4: Sequelize Many-to-Many

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.

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.