Understanding Sequelize Associations: Part 1: One to One (1:1) mapping
This is 3 part blog series in which I will explain sequelize associations by creating a CRUD app. The description and links of each part is as follows:
- Part 1: One-to-One (1:1) Mapping
- Part 2: One-to-Many (1:n) Mapping
- Part 3: Many-to-Many (n:m) Mapping
This blog assumes that you have basic understanding of NodeJs, Databases and Sequelize.
Sequelize is node based ORM. It makes working with databases easy. But at the same time, I find it quite difficult to comprehend its documentation or find ‘how to’ articles on web. Recently, I was working with foreign keys with Sequelize. I had to spend hours going through documentation and other blogs across the internet to understand it. So here I am writing this blog, so that nobody else has to struggle like I did
Source repo for this blog
1. What are we going to do in this blog
In this blog, I will explain 1:1 mapping in sequelize by creating a CRUD app. The tech stack of the app is as follows:
- Backend: HapiJS
- Database: PostgreSQL
- ORM: Sequelize (Obviously!)
We will consider a hypothetical scenario where we have two tables- users and usersDetails. These two tables have 1:1 mapping, with userId as foreign key defined on userDetails. The ER diagram is as follows:
2. Generate Models
The first step to start off is to generate our database models. We will first create our models using sequelize-cli, then modify the generated models according to our associations
2.1 Create models using sequelize-cli
First, we will create users model. To achieve this, run the following command:
node_modules/.bin/sequelize model:generate --name users --attributes username:string,name:stringNext, we will create userDetails model. To achieve this, run the following command:
node_modules/.bin/sequelize model:generate --name userDetails --attributes userId:integer,mobileNum:string,address:textNote that these commands, along with models, will generate corresponding migrations file too
2.2 Modify models for association
Now that we have our migrations and models generated by sequelize, we will modify them according to our associations. Find the migration titled like: create-user-details and modify the userId attribute to match as follows:
userId: {
type: Sequelize.INTEGER,
references: { model: 'users', key: 'id' },
onDelete: 'CASCADE',
}Next, find the model users and add associations as follows:
users.associate = function (models) {
users.hasOne(models.userDetails, {
foreignKey: 'userId',
as: 'userDetails',
onDelete: 'CASCADE'
});
};If you want to understand the intent of this code, read it like: model users has one key defined on the model userDetails with name key as userId.
2.3 Side note: what are hasOne, belongsTo, hasMany, belongsToMany ?
All the four keywords are used to define associations in our sequelize model. One way to differentiate them is to consider the following points:
- Whenever the foreign key is defined on the source model (the model for which we are writing this association), use belongsTo or belongsToMany
- If the foreign key is defined on the target model (the model with which we will be associating), use hasOne or hasMany
- If we are considering multiple mappings (1:n, or n:m), then, we have to use hasMany or belongsToMany
For eg, in the above scenario, userDetails has the foreign key userId. Hence, I have used hasOne constraint. Read like: model users has one key defined on the model userDetails with name key as userId.
Similarly, if we had defined a belongsTo relation on userDetails, this would have been as follows:
userDetails.associate = function (models) {
userDetails.belongsTo(models.users, {
foreignKey: 'userId',
as: 'users'
});
};This can be read like: model userDetails has a foreign key which belongs to the model users. The foreign key is userId.
Having said that, in this particular scenario we don’t need belongsTo relation on userDetails, because we won’t be querying this model.
2.4 Run migrations
Now that we have all our models and migrations ready. We will run the migrations, so that our corresponding tables are replicated in our database. Run the following command:
node_modules/.bin/sequelize db:migrateIf you don’t have database created, you can create it as follows:
node_modules/.bin/sequelize db:createNow, you can see foreign key constraints defined on your tables:
3. Creating CRUD app
At this point we have our tables ready with required foreign key constraints. But to keep this blog complete, we will create a CRUD app, which will show how we can write our queries so as to utilise Sequelize to its fullest. The complete file can be found here
3.1 Create API
Our create API will be a POST API. This API will expect all details of users. And will save the details in users and userDetails table according to the keys provided.
The part which concerns us is from line 9 to line 25. Here, we can give name and userName directly. However, mobileNum, and address, needs to be provided in another object with key as userDetails. This is our alias that we have defined in our association. Similarly we add include query in line 17, to tell sequelize which other models should it add while creating the content. The generated query as seen on console is as follows:
Notice that sequelize executes two queries back to back for creating the required data. Please note that these queries are not a transaction.
3.2 Read API
Our read API will be a GET API. This API will expect no arguments. This will get details of all users. It will also include data in our userDetails model.
The part which concerns us is on line 7. Here we add an include statement. In this statement, we basically tell sequelize about what other models it should include when generating its query. If we hit this API using Postman. We will see the following on our logs:
Notice that the generated query has ‘left outer join’ statement. Hence, fetching all the details that we expect
3.3 Update API
Our update API will be a PUT API. This API will expect a URL parameter, which is the id of the record to be updated. However, there is no method for updating with association which sequelize provides. So, we will update the records one by one in each table. I know it sucks, but thats what we have to work with unfortunately.
Here, we create two objects on line 9 and line 13, which will be used to update the respective database tables. If the values provided are undefined, sequelize will detect that, and will not update the models at all. So essentially, we don’t have to worry about any undefined fields. We now use these objects to update tables one by one. After that, on line 31, we are waiting for sequelize to complete its operation. Once the operations are done, we return the request with a success message. The logs looks as follows:
3.4 Delete API
Our delete API will be a (HTTP VERB) DELETE API. This API will expect the id of record to be deleted as URL parameter. We will again use include keyword to delete records.
If by any chance, you get a foreign key constrain error (Validation error), stating that the record is still referenced by another table, make sure ‘CASCADE’ constraint is created on the database. Look at section 2 of this article to understand how you can achieve that. And also, make sure that this constraint is defined on models. The generated query as seen on console is as follows:
Wrapping Up
In this blog, I showed how you can create 1:1 Mapping and utilise sequelize functions to do basic tasks. In the next blog, I will write about One-to-Many (1:n) mapping - Link.
If you have any questions, please comment them. I will try to answer them as soon as possible
Source repo for this blog link
Complete repo:
