Table Relationships in Sequelize
Setting foreign keys and making ORM join tables
Getting started with Sequelize can be a little daunting at first. But for those who are more comfortable with JavaScript and object-oriented programming, it can also make working with a database much easier. In this post, I will focus on the relational mapping aspect of this API: setting foreign keys and creating join tables.
Introduction
Sequelize is a Node package that carries out Object Relational Mapping (ORM) between your server or your back-end code and a SQL database (MySQL, SQLite, PostgreSQL, or MSSQL). What is ORM? It basically converts data between incompatible datatype systems (i.e. SQL and JavaScript) using object-oriented code. With Sequelize, we can use JavaScript to create the database and the tables, as well as the queries to the database.
Models
Rather than creating tables, Sequelize uses models to create the tables. A model will be used in your JavaScript to both store the properties (i.e. the fields or columns in a table) as well as its associations with other models/tables and it’s methods for querying the database. Similar to creating tables in SQL, when you define a model, you will also define the datatype of each key/column.
For this post, let’s imagine a database with users, projects, and a join table to track all the users by project. Before we can assign the relationships between them, we need to make the models:
Associations
In SQL, we set foreign keys to make join tables and generally establish the relationships between tables — the database does not know what type of relationship your tables and their keys have. Whereas in Sequelize, we can actually declare the type of association between two tables.
There are two types of relationships in the example database:
- Each project has a project manager (user): one-to-one relationship
- Each project can have many members (users) and each user can have many projects: many-to-many relationship
After the models are established in the database, the associations can be declared. There are three Sequelize association types here: belongsTo, hasMany, and belongsToMany. Here is how these are used:
The belongsTo & hasMany associations set a many-to-one relationship. Where every project has one project manager, that project belongs to that user, whereas that user can have many projects. Sequelize will know that the foreign key for the user will be set on the project table.
Note that the column title for the foreign key can be set as the second argument in these methods. This step is not necessary — Sequelize will automatically name the foreign key user_id or userId, depending on if you have camelCase or snake_case enabled (see Naming Strategies in the docs). In our case, we wanted to have the id first and note that this user is the project manager.
For setting many-to-many associations and creating join tables, belongsToMany needs to be used on both models. Every user can belong to many projects, and every project can belong to many users. Each of these method invocations will set the foreign key in the resulting join table. Note that the through keyword sets the join table name here. You can create a many-to-many join table without creating a model, however the model can be useful if you’d like to store data in more than the default id columns (see previous example in the model section).
There are other associations that can be made in Sequelize, like one-to-one, that I haven’t covered here. Read more in the docs on all the Associations that can be set in Sequelize.
Methods
The power of Sequelize’s models and associations are the methods they make available for querying the database. Below I have two examples, one for adding a project and another for linking the project and users in the join table.
(Note that here I used async/await — Sequelize methods are asynchronous and return promises, and you can either use ES6 promises or async/await to handle them.)
In the first example, addProject, we use some basic model methods to find and create records in our tables. Once we have found the user and created the new project, we need to actually set the foreign key. The associations established previously generate getters and setters for our model. Here, we use the custom setter, setUser, to assign the user’s id as the foreign key for the new project. Note that the entire record returned from the database needs to be passed into this method to assign the foreign key.
In the second example, joinUserProject, we are creating the join table. We need to retrieve both records, and the use another custom generated method, addUser, to join them.
Custom methods are generated for all of these associations. I have shown just two, set and add. For example, the hasMany association between projects and users generates the following special methods:
project.getUsers()project.countUsers()project.hasUser()project.hasUsers()project.setUsers()project.addUser()project.addUsers()project.removeUser()project.removeUsers()project.createUser()
Read more about Sequelize Special Methods in the docs.
Which association to use
It can be tricky to figure out which association to make, but there are a few strategies you can use to determine this. The first is to think in terms of target and source model. The source is where the foreign key comes from, and the target is the model or table where the foreign key will live:
sourceModel.hasOne(targetModel)
sourceModel.hasMany(targetModel)
targetModel.belongsTo(sourceTable)This helpful code snippet above comes from Loren Stewart’s blog on the same topic, and is definitely worth checking out.
The other strategy is to just try both ways, and then check the result in your database. If you don’t see the foreign key in the right place, you probably have it backwards! Here is more on why associations are defined in pairs and how to determine which to use: Why associations are defined in pairs?
Conclusion
There is a lot more to explore in setting and working with Sequelize associations. In this post, I covered the basics of Object-Relational Mapping with Sequelize — using this ORM to set foreign keys, create join tables in your SQL database, and use some of the special methods created by these associations. Sequelize offers an approach to relational database queries that can make setting up and working with data a lot easier and faster for those familiar with object-oriented programming.
