Bootcamp
Search…
4.1.2: Sequelize One-to-Many Relationships

Introduction

Sequelize makes querying for associated data in SQL relatively straightforward, but to leverage Sequelize we need to set up our migrations and models in specific ways. This module explores how to set up Sequelize to support one-to-many SQL relationships.

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, grocerystore_development instead of grocery_development to distinguish the DBs from modules 4.1.1 and 4.1.2. Stop after creating the DB and follow the steps below to create a one-to-many SQL relationship with Sequelize.

Migrations: Create Categories and Items Tables

npx sequelize migration:generate --name create-categories-items-tables
Replace the contents of the generated file with the following table-creation code. Note the new categories table and how the items table references it via the category_id foreign key column. Sequelize knows category_id references the id column in the categories table because of the references key in the category_id specification object.

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

module.exports = {
up: async (queryInterface, Sequelize) => {
// "categories" table needs to be created first because "items" references "categories".
await queryInterface.createTable('categories', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER,
},
name: {
type: Sequelize.STRING,
},
// created_at and updated_at are required
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,
},
category_id: {
type: Sequelize.INTEGER,
// This links the category_id column to the id column in the categories table
references: {
model: 'categories',
key: 'id',
},
},
created_at: {
allowNull: false,
type: Sequelize.DATE,
},
updated_at: {
allowNull: false,
type: Sequelize.DATE,
},
});
},
​
down: async (queryInterface, Sequelize) => {
// Items table needs to be dropped first because Items references Categories
await queryInterface.dropTable('items');
await queryInterface.dropTable('categories');
},
};
Create the categories and items tables in our DB by running migrations.
npx sequelize db:migrate
Verify table creation in psql.
psql -d grocerystore_development

Models: Create Category and Item Models

Category and Item model files look similar to their table definitions in the migration above.

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,
},
categoryId: {
type: DataTypes.INTEGER,
// This links the categoryId column to the id column in the categories table
references: {
model: 'categories',
key: 'id',
},
},
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

When we define a belongsTo or hasMany relationship on a Sequelize model, Sequelize uses a default naming convention to look for the relevant foreign key on the table that belongs to the other. For example, if we define a belongsTo relationship from item to category, Sequelize by default expects a categoryId foreign key in the items table. This behaviour is customisable as per Sequelize docs, but as much as possible we recommend sticking to the defaults to avoid unexpected bugs.
Defining belongsTo and hasMany relationships enable the source model to reference the target model, but not vice versa. This is why we often want to specify both belongsTo and hasMany, instead of just 1 or the other. For example Item.belongsTo(Category) enables us to run methods like item.getCategory() to get the category associated with that item, but does not enable us to run methods like category.getItems() to get the items associated with that category, unless we declare Category.hasMany(Item) in our model setup.
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);
​
// The following 2 lines enable Sequelize to recognise the 1-M relationship
// between Item and Category models, providing the mixin association methods.
db.Item.belongsTo(db.Category);
db.Category.hasMany(db.Item);
​
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 3 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) => {
const categoriesList = [
{
name: 'fish',
created_at: new Date(),
updated_at: new Date(),
},
{
name: 'fruit',
created_at: new Date(),
updated_at: new Date(),
},
{
name: 'meat',
created_at: new Date(),
updated_at: new Date(),
},
];
​
// Insert categories before items because items reference categories
let categories = await queryInterface.bulkInsert(
'categories',
categoriesList,
{ returning: true }
);
​
const items = [];
for (let i = 0; i < categories.length; i++) {
const category = categories[i];
​
items.push({
name: 'some item',
category_id: category.id,
created_at: new Date(),
updated_at: new Date(),
});
​
items.push({
name: 'other item',
category_id: category.id,
created_at: new Date(),
updated_at: new Date(),
});
​
items.push({
name: 'iitemmm',
category_id: category.id,
created_at: new Date(),
updated_at: new Date(),
});
}
​
queryInterface.bulkInsert('items', items);
},
​
down: async (queryInterface) => {
// Delete item before category records because items reference categories
await queryInterface.bulkDelete('items', null, {});
await queryInterface.bulkDelete('categories', null, {});
},
};
Run seed migrations.
npx sequelize db:seed:all

Created Associated Records with Sequelize

Create a Node app to find an existing category and create a new item associated with that category.

create-item-category.mjs

import db from './models/index.mjs';
​
db.Category.findOne({
where: {
name: process.argv[2],
},
})
.then((returnedCategory) => {
// Docs on .create
// https://sequelize.org/master/class/lib/model.js~Model.html#static-method-create
return db.Item.create({
name: process.argv[3],
categoryId: returnedCategory.id,
});
})
.then((returnedItem) => {
console.log('success!!');
console.log(returnedItem.id, 'returned item ID');
console.log(returnedItem.categoryId, `returned item's category ID`);
})
.catch((error) => {
console.log(error);
});
Run our Node app to create a new item associated with an existing category.
node create-item-category.mjs fruit banana
Open our DB in Psql to check the DB.
psql -d grocerystore_development
Check the DB to verify the new item and its association.
SELECT * FROM items;

Query Associated Tables in Sequelize

Note that almost all Sequelize methods return Promises, including association methods such as get<MODEL_NAME> and set<MODEL_NAME>.
Each Category and Item instance will have association methods that Sequelize provides because of the hasMany and belongsTo associations we defined in models/index.mjs. The association methods will be named after the associated model. More info in docs here.

Get Items in Given Category

In the following example, on line 8 we call the getItems method to get all items associated with category.

get-category-item.mjs

import db from './models/index.mjs';
​
db.Category.findOne({
where: {
name: [process.argv[2]],
},
})
// When we omit curly braces in arrow functions, the return keyword is implied.
.then((category) => category.getItems())
.then((categoryItems) =>
console.log(categoryItems.map((categoryItem) => categoryItem.name))
)
.catch((error) => console.log(error));

Sample Command

node get-category-item.mjs fruit

Sample Output

[ 'some item', 'other item', 'iitemmm', 'banana' ]

Get Category of Given Item

get-item-category.mjs

In the following example, on line 8 we call the getCategory method to get the category associated with item.
import db from './models/index.mjs';
​
db.Item.findOne({
where: {
name: [process.argv[2]],
},
})
.then((item) => item.getCategory())
.then((itemCategory) => console.log('found: ', itemCategory.name))
.catch((error) => console.log(error));

Sample Command

node get-item-category.mjs banana

Sample Output

fruit

Exercise

Replicate the above code and verify results.
Copy link
On this page
Introduction
Setup Packages and Folders, Configure DB
Migrations: Create Categories and Items Tables
Sample Migration File (<GENERATED_DATE>-create-categories-items-tables.js)
Models: Create Category and Item Models
models/category.mjs
models/item.mjs
models/index.mjs
Seed: Create Seed Data for DB
Sample Seed Data Migration File (<GENERATED_DATE>-seed-data.js)
Created Associated Records with Sequelize
create-item-category.mjs
Query Associated Tables in Sequelize
Get Items in Given Category
Get Category of Given Item
Exercise