Bootcamp
Search…
4.1.5: Sequelize Many-to-Many with Non-FK Attributes in Through Table

Introduction

In Module 3.4.5: Non-FK Relationship Data in Join Table we added a measurement attribute to the join table between recipes and ingredients tables. In this module we will see how to do this with Sequelize. Sequelize refers to the join table as the "through table", due to the Sequelize through syntax to specify the name of the join table. The Sequelize docs on how to do this are here.

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, carts_development to distinguish the DB from other modules. Stop after creating the DB and follow the steps below to create a many-to-many SQL relationship with through table attributes with Sequelize.
Note that it's not strictly necessary to setup a completely new database for this, and you can reuse the database, migrations and seed from 4.1.4 if you want.

Migrations

npx sequelize migration:generate --name create-tables

Sample Migration File (<GENERATED_DATE>-create-tables.js)

module.exports = {
up: async (queryInterface, Sequelize) => {
// Create items and categories tables before the table that references them
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,
},
});
​
await queryInterface.createTable('carts', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
created_at: {
allowNull: false,
type: Sequelize.DATE,
},
updated_at: {
allowNull: false,
type: Sequelize.DATE,
},
});
​
await queryInterface.createTable('cart_items', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
// New: quantity is a non-foreign-key attribute in the through table.
// To access this attribute in our app, we will want a CartItem model.
quantity: {
type: Sequelize.INTEGER,
},
cart_id: {
type: Sequelize.INTEGER,
references: {
model: 'carts',
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) => {
// Drop tables with foreign key references first
await Promise.all([
queryInterface.dropTable('category_items'),
queryInterface.dropTable('cart_items'),
]);
await Promise.all([
queryInterface.dropTable('items'),
queryInterface.dropTable('categories'),
queryInterface.dropTable('carts'),
]);
},
};
Create the above tables in our DB by running migrations.
npx sequelize db:migrate

Models

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/cart.mjs

export default function initCartModel(sequelize, DataTypes) {
return sequelize.define(
'cart',
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
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/cartItem.mjs

Note that our model name must be cart_item so that Sequelize can automatically infer the table name cart_items. More on this behaviour here.
export default function initCartItemModel(sequelize, DataTypes) {
return sequelize.define(
'cart_item',
{
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataTypes.INTEGER,
},
// We define the CartItem model to access quantities of items in carts.
quantity: {
type: DataTypes.INTEGER,
},
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

To access attributes from through (aka join) tables we need to define one-to-many associations from the through table to each of its associated tables. In this module's example, we would need to define additional one-to-many relationships between the cart_items table and the carts and items tables respectively. These associations will be defined in the models/index.mjs file. See Sequelize docs for details.
Update models/index.mjs to the following. Note lines 38-41.
import sequelizePackage from 'sequelize';
import allConfig from '../config/config.js';
​
import initItemModel from './item.mjs';
import initCategoryModel from './category.mjs';
import initCartModel from './cart.mjs';
import initCartItemModel from './cartItem.mjs';
​
const { Sequelize } = sequelizePackage;
const env = process.env.NODE_ENV || 'development';
const config = allConfig[env];
const db = {};
​
const sequelize = new Sequelize(
config.database,
config.username,
config.password,
config
);
​
db.Item = initItemModel(sequelize, Sequelize.DataTypes);
db.Category = initCategoryModel(sequelize, Sequelize.DataTypes);
db.Cart = initCartModel(sequelize, Sequelize.DataTypes);
db.CartItem = initCartItemModel(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' });
​
// Connect Item and Cart models.
// Note: It's possible to use a Sequelize model class (i.e. CartItem)
// to connect the models Item and Cart instead of the table name (i.e. cart_items).
// Using variable is more robust than string because it's easier to detect typos.
db.Item.belongsToMany(db.Cart, { through: db.CartItem });
db.Cart.belongsToMany(db.Item, { through: db.CartItem });
​
// Define 1-M associations between CartItems table and associated tables
// to access CartItem attributes from Item and Cart instances
db.Item.hasMany(db.CartItem);
db.CartItem.belongsTo(db.Item);
db.Cart.hasMany(db.CartItem);
db.CartItem.belongsTo(db.Cart);
​
db.sequelize = sequelize;
db.Sequelize = Sequelize;
​
export default db;

Seeds

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)

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
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 items
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);
​
// Define cart data, 2 carts
const cartData = [
{
created_at: new Date(),
updated_at: new Date(),
},
{
created_at: new Date(),
updated_at: new Date(),
},
];
​
// Bulk insert carts
const [cart1, cart2] = await queryInterface.bulkInsert('carts', cartData, {
returning: true,
});
​
// Define cart item data, i.e. put items in cart
const cartItemData = [
{
quantity: 1,
item_id: peach.id,
cart_id: cart1.id,
created_at: new Date(),
updated_at: new Date(),
},
{
quantity: 2,
item_id: banana.id,
cart_id: cart1.id,
created_at: new Date(),
updated_at: new Date(),
},
{
quantity: 4,
item_id: peach.id,
cart_id: cart2.id,
created_at: new Date(),
updated_at: new Date(),
},
];
​
// Bulk insert cart items
await queryInterface.bulkInsert('cart_items', cartItemData);
},
​
down: async (queryInterface) => {
// Delete rows from tables with foreign key references first
await Promise.all([
queryInterface.bulkDelete('category_items', null, {}),
queryInterface.bulkDelete('cart_items', null, {}),
]);
await Promise.all([
queryInterface.bulkDelete('items', null, {}),
queryInterface.bulkDelete('categories', null, {}),
queryInterface.bulkDelete('carts', null, {}),
]);
},
};
Run seed migrations.
npx sequelize db:seed:all

Accessing Attributes From Through Table

get.mjs

This file is a script we're using to demonstrate above concepts and is *not* part of our Express app. The logic in this file would belong in a controller.
Now we can access cart items and their attributes from the cart instances.
import db from './models/index.mjs';
​
const getModels = async () => {
try {
// Retrieve the 1st cart
const carts = await db.Cart.findAll();
console.log('1st cart');
console.log(carts[0]);
const cart = carts[0];
// Get all items in the 1st cart
// We don't use items below, but this is to demonstrate that accessing
// associated data in a m2m relationship still works.
const items = await cart.getItems();
console.log('items');
console.log(items);
​
// Retrieve CartItem entries in the "through" or "join" table
// that are associated with the 1st cart. We need to access CartItem
// entries to obtain quantities for specific items in this cart.
const cartItems = await cart.getCart_items();
console.log('cart items');
console.log(cartItems);
​
// Retrieve the quantity of each item in the 1st cart
for (let i = 0; i < cartItems.length; i += 1) {
console.log('quantity: ', cartItems[i].quantity);
}
} catch (error) {
console.log(error);
}
};
​
getModels();

Sample Command

node get.mjs

Sample Output

# ...
​
_options: {
isNewRecord: false,
_schema: null,
_schemaDelimiter: '',
raw: true,
attributes: [Array]
},
isNewRecord: false
}
]
quantity: 1
quantity: 2

Exercise

Replicate the above code and verify results.
Copy link
On this page
Introduction
Setup Packages and Folders, Configure DB
Migrations
Sample Migration File (<GENERATED_DATE>-create-tables.js)
Models
models/category.mjs
models/item.mjs
models/cart.mjs
models/cartItem.mjs
models/index.mjs
Seeds
Sample Seed Data Migration File (<GENERATED_DATE>-seed-data.js)
Accessing Attributes From Through Table
get.mjs
Exercise