In this lesson, we'll be covering how to build associations bewteen differen models/tables. We'll set up foreign keys and execute queries to include the related data. Associations are one of the most important topics we'll cover when it comes to relational databases as they are the principles that make relational databases as popular as they are.
- Fork and Clone
npm installsequelize db:create
Take five minutes and read the Sequelize docs on associations:
Associations are the relationships that we define between different data entries accross different tables. For example:
- A user can
have manypets - Pets
belong toone user
Relational databases rely on associations in order to join data that we need in a an organized manner. It also helps reduce database load. Databases can become overwhelmed when being queried repeatedly in a short amount of time. By joining data, we can load all of the data we need with one query.
Let's start with building a User model:
sequelize model:generate --name User --attributes firstName:string,lastName:string,password:string,email:stringThe above command will generate the following model:
'use strict'
const { Model } = require('sequelize')
module.exports = (sequelize, DataTypes) => {
class User 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 association here
}
}
User.init(
{
firstName: DataTypes.STRING,
lastName: DataTypes.STRING,
password: DataTypes.STRING,
email: DataTypes.STRING
},
{
sequelize,
modelName: 'User'
}
)
return User
}Let's update the User model to have a lowercased tableName
...
{
sequelize,
modelName: 'User',
tableName: 'users'
}
...Next we'll need to update our migration to reflect our desired tableName:
'use strict'
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstName: {
type: Sequelize.STRING
},
lastName: {
type: Sequelize.STRING
},
password: {
type: Sequelize.STRING
},
email: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('users')
}
}You can now execute the migration:
sequelize db:migrateLet's start by creating a task model:
sequelize model:generate --name Task --attributes title:string,userId:integerThe above command will generate the following model:
'use strict'
const { Model } = require('sequelize')
module.exports = (sequelize, DataTypes) => {
class Task 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 association here
}
}
Task.init(
{
title: DataTypes.STRING,
userId: DataTypes.INTEGER
},
{
sequelize,
modelName: 'Task'
}
)
return Task
}Let's update the table name for our Task:
...
{
sequelize,
modelName: 'Task',
tableName: 'tasks'
}
...We'll now associate the User and Task model. The relationship we want to define is a hasMany/belongsTo:
- User
hasManyTask - Task
belongsToUser
Now, you may be thinking that this is a complicated process... Believe it or not, it isn't! Sequelize models actually have built in methods that make it easy to associate our created models!
In your User model, we'll attach a method that will tell Sequelize in what direction our association flows. Find the static associate(models) method in the User model.
We'll now define our hasMany relationship:
class User 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 association here
User.hasMany(models.Task, { foreignKey: 'userId' })
}
}Here's a quick breakdown on how this works:
Now we'll associate our Task to our user.
Let's add a belongsTo method to the Task model:
class Task 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 association here
Task.belongsTo(models.User, { foreignKey: 'userId' })
}
}The belongsTo method works the same way as the hasMany method except that we are telling Sequelize that the Task belongs to a User.
Next we'll need to update the userId field to declare it as a foreign key:
Task.init(
{
title: DataTypes.STRING,
userId: {
type: DataTypes.INTEGER,
onDelete: 'CASCADE',
references: {
model: 'users',
key: 'id'
}
}
},
{
sequelize,
modelName: 'Task',
tableName: 'tasks'
}
)Here's a quick breakdown on what we did to the userId field:
Now we need to reflect this change in the Task migration:
'use strict'
module.exports = {
up: async (queryInterface, Sequelize) => {
await queryInterface.createTable('tasks', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
title: {
type: Sequelize.STRING
},
userId: {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
references: {
model: 'users',
key: 'id'
}
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
}
})
},
down: async (queryInterface, Sequelize) => {
await queryInterface.dropTable('tasks')
}
}Note: Make sure to update the table name for Task!
Now that we've defined our associations, we're ready to finish migrating our database. Run the following command to execute the Task migration:
sequelize db:migrateNext we'll create a seed file to execute some queries:
sequelize seed:generate --name taskReplace the contents of the seed file with the following:
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.bulkInsert(
'tasks',
[
{
title: 'Build an App.',
userId: 1,
createdAt: new Date(),
updatedAt: new Date()
}
],
{}
)
},
down: (queryInterface, Sequelize) => {
return queryInterface.bulkDelete('tasks', null, {})
}
}We can now run the seed files:
sequelize-cli db:seed:allLet's see if our associations actually work. We'll run a join query on our User table:
psql sequelize_associations_development
SELECT * FROM users JOIN tasks ON tasks."userId" = users.id;Utilizing the Sequelize Docs, write two queries that match their provided SQL statements. The functions have been provided for you in query.js
To run your file, execute:
node query.jsIn this lesson, we learned how to define and query associations. Associations are an important concept in the relational database world and are a core fundamental to what makes these databases so powerful.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent