This section describes the various association types in Sequelize. When calling a method such as User.hasOne(Project), we say that the User model (the model that the function is being invoked on) is the source and the Project model (the model being passed as an argument) is the target.
Database design is the organization of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. Database design involves classifying data and identifying interrelationships.
With PostgreSQL as our object-relational database management system (ORDBMS) and Sequelize as our library of choice to implement object-relational mapping (ORM), we will be covering the bases when it comes to the associations, or relationships, that exist among the various tables in our database. Some associations include but are not limited to: one-to-one and one-to-many. In an effort to develop more familiarity, we will explore these associations in addition to others.
When it comes to one-to-one (O:O or 1:1) associations and their complements, a few real-world examples come to mind.
- When it comes to the United States, an individual citizen has one social security number. A single entity of a social security number belongs to an individual citizen.
- When it comes to sports, a baseball team has one mascot. A mascot belongs to a baseball team.
- When it comes to fitness, a gymgoer has one gym membership. A gym membership belongs to a gymgoer.
When it comes to one-to-many (O:M or 1:M) associations and their complements, a few real-world examples also come to mind.
- When it comes to hospitality, one single hotel has many restaurants. A restaurant belongs to a hotel.
- When it comes to a police department, one police department has many detectives. A detective belongs to a police department.
- When it comes to music, a rock band has many artists. An artist belongs to a band.
The purpose of this section is to primarily and ultimately get us to start thinking about how different sets of data can be associated. Specifically, this section aims to get us to ask and answer the root questions of: how does Entity X relate to Entity Y? How does Entity Y relate to Entity X? These general examples set us up nicely for the following section where we'll dig deeper into more specific examples of associations.
Link: https://github.com/ajLapid718/Affiliated
Here's the scenario. In our database, we have a table of players, a table of coaches, and a table of teams. The tables appear like so:
As it stands, we can most certainly perform many queries such as: getting a list of all of the players, getting a list of all of the coaches, getting a list of all of the teams, getting a single player, getting a single coach, getting a single team, removing a single player, removing a single coach, removing a single player, updating a single player, updating a single coach, updating a single team, and other possibilities. Although all of these are important capabilities, we can take this one step further.
What if we wanted to get a list of all of the players that are a part of the same team? What if we wanted to get the coach of a particular team? This is a situation where associations become both relevant and necessary.
Before going on any further, let's see how the tables look when they are populated in order to have more concrete information and details.
Now that we have some tangible data to refer to, we can take a more thorough look at what can be done via associations.
Let's say that our objective, at the moment, is to be able to get a list of all of the players who play for the Golden State Warriors. One way to think of this is that we need to add a column titled "teamId" to the players table. By doing so, we can determine which player is a member of which team. To drive the point home, if Stephen Curry had a teamId of 3 in his row (the same id number as the Golden State Warriors in the table of teams), then that would be our way to relate Stephen Curry to the Golden State Warriors.
Our associations, so far, appear like so (this can be found in database/models/index.js):
// Signature: Source.association(Target);
Team.hasMany(Player); // A one-to-many relationship that adds the column titled "teamId" to the table of players;
Player.belongsTo(Team); // A one-to-one relationship that adds the column titled "teamId" to the table of players;After adding those two lines of code, our tables now appear like so:
Almost there. So what just happened?
We declared:
- A team has many players
- A player belongs to a team
What did this provide for us?
- We added a teamId column to the table of players
How did this happen?
- As far as the Sequelize-given instance methods hasMany() and belongsTo() are concerned, we are able to construct and arrange foreign keys (teamId in the example above) and position them in the appropriate locations (within the table of players in the example above).
- In the example of Team.hasMany(Player):
- The source is the Team model
- The target is the Player model
- With an invocation of hasMany(), Sequelize constructs a foreign key from the source model and appends that foreign key on the target model for future reference
- In the example of Player.belongsTo(Team)
- The source is the Player model
- The target is the Team model
- With an invocation of belongsTo(), Sequelize constructs a foreign key from the target model and appends that foreign key on the source model for future reference
All of that to say: through the associations, we carefully and purposefully put teamId in the proper place.
Is there anything else to keep in mind?
- Associations are responsible for much more than generating foreign keys and altering tables
- Associations are also responsible for adding certain methods on instances of the models
- The hasMany() method provides the following methods for the source model, instances of our Team model in this case:
- Team.getPlayers()
- Team.setPlayers()
- Team.addPlayer()
- Team.createPlayer()
- Team.removePlayer()
- Team.hasPlayer()
- Team.hasPlayers()
- The belongsTo() method provides the following methods for the source model, instances of our Player model in this case:
- Player.getTeam()
- Player.setTeam()
- Player.createTeam()
For this reason, in addition to the convenience of appending columns denoting foreign keys and the nature of eager loading, associations like hasMany() and belongsTo() are significant pieces of database relationships.
To get back on track, let's run the seed script again, except this time, we'll assign teamIds to players. As a heads-up, for this example, the first 17 players will be assigned a teamId of 1 and the remaining 16 players will be assigned a teamId of 3.
At this point, the other two tables did not change yet. We're more focused on this new data that we can work with, the populated teamId column. Because of this, we can now query the database in order to get a list of all players who play for the same team.
The route and query look like so (this can be found in routes/teams.js):
// GET http://localhost:1234/api/teams/3/players;
// We want to get all of the players who play on a team where the team id is 3;
router.get('/:id/players', async (req, res, next) => {
let foundTeam;
try {
foundTeam = await Team.findOne({ where: { id: req.params.id } });
}
catch (err) {
next(err);
}
let playersOfTeam;
try {
playerOfTeam = await foundTeam.getPlayers(); // remember those methods Sequelize provides?;
}
catch (err) {
next(err);
}
res.status(200).json(playersOfTeam);
});The raw output looks like so:
There you have it! For all intents and purposes, we have retrieved a list of all of the players who play for the Golden State Warriors!
Based off of that, let's try to set another objective. Let's say that our new objective is to be able to get information about the coach of the Boston Celtics. One way to think of this is that we need to add a column titled "teamId" to the coaches table. By doing so, we can determine which coach belongs to which team. To drive the point home, if Brad Stevens had a teamId of 1 (the same id as the Boston Celtics in the table of teams) in his row, then that would be our way to relate Brad Stevens to the Boston Celtics.
After we add some associations, now all of our associations look like this so far (this can be found in database/models/index.js):
// Signature: Source.association(Target);
Team.hasMany(Player); // A one-to-many relationship that adds the column titled "teamId" to the table of players;
Player.belongsTo(Team); // A one-to-one relationship that adds the column titled "teamId" to the table of players;
Team.hasOne(Coach); // A one-to-one relationship that adds the column titled "teamId" to the table of coaches;
Coach.belongsTo(Team); // A one-to-one relationship that adds the column titled "teamId" to the table of coaches;After adding those two lines of code, and running the seed script, our table of coaches now appears like so:
Two things. One, our other tables are unchanged at this point. Two, in the recap section toward the bottom of this, we'll discuss hasOne() a bit more then and there, but we will not at the moment.
Almost there! We can now query the database in order to get the coach of a particular team. Let's aim for retrieving information regarding the coach of the Boston Celtics.
The route and query look like so (this can be found in routes/teams.js):
// GET http://localhost:1234/api/teams/1/coach;
// We want to get the coach of a team where the team's id is 1;
router.get('/:id/coach', async (req, res, next) => {
let foundTeam;
try {
foundTeam = await Team.findOne({ where: { id: req.params.id } });
}
catch (err) {
next(err);
}
let coachOfTeam;
try {
coachOfTeam = await foundTeam.getCoach(); // remember those methods Sequelize provides?;
}
catch (err) {
next(err);
}
res.status(200).json(coachOfTeam);
});The prettified output looks like so:
There you have it! For all intents and purposes, we retrieved information about the coach of the Boston Celtics!
That's that.
- Thanks to associations, we were able to grab certain relationship-dependent information
- The hasOne() method provides the following methods for the source model, instances of our Team model in this case:
- Team.getCoach()
- Team.setCoach()
- Team.addCoach()
- Team.createCoach()
- Team.removeCoach()
- Team.hasCoach()
- We went over belongsTo(), hasOne(), and hasMany()
- We mentioned the concept of foreign keys
- We mentioned the concepts of database management systems and object-relational mapping
- We used Postman
- We constructed queries
- https://medium.com/@jasmine.esplago.munoz/feeling-the-magic-with-sequelize-magic-methods-e9cc89ecdcc5
- https://stackoverflow.com/questions/49467654/what-methods-mixins-sequelize-adds-to-the-models-when-an-association-is-made
- https://gist.github.com/elliette/20ddc4e827efd9d62bc98752e7a62610
- https://gist.github.com/anonymous/79c2eed2a634777b16ff
- https://www.youtube.com/watch?v=icZqjfODmz0
- https://www.youtube.com/watch?v=5yOWs4Qarg4
- https://www.youtube.com/watch?v=EupsLAxCEAg
- https://www.youtube.com/watch?v=isk0JR0t_VQ
- https://sqlbolt.com/
- https://stackoverflow.com/questions/53293082/sequelize-query-returns-promise-how-to-wait-for-query-to-return-result/53304639#53304639
- https://stackoverflow.com/questions/26362965/prevent-junction-table-data-from-being-added-to-json-with-sequelize
- https://stackoverflow.com/questions/36208460/hasmany-vs-belongstomany-in-laravel-5-x
- https://laravel.com/docs/5.2/eloquent-relationships
- https://stackoverflow.com/questions/42708811/has-many-through-association-in-sequelize
- sequelize/sequelize#3036
- sequelize/sequelize#1775