API with NestJS #63. Relationships with PostgreSQL and MikroORM

NestJS SQL

This entry is part 63 of 168 in the API with NestJS

A significant advantage of SQL databases is handling relationships between various tables. Since, in web applications, entities often relate to each other, designing relationships is a big part of working with SQL databases. In this article, we continue learning MikroORM and use it to form relationships.

You can find the code from this article in this repository.

One-To-One

With a one-to-one relationship, a row from the first table has just one matching row from the second table and the other way around. An elementary example of that is creating an address entity.

address.entity.ts

Once we do that, we can use the decorator to declare the one-to-one relationship.

user.entity.ts

In our application, we assign just one user to a particular address. Because of that, it is a fitting example of a one-to-one relationship.

A thing worth noting is that above, we use the serialization built into MikroORM to hide the property using the option. Instead, we would use the serialization built into NestJS, but unfortunately, that would not work correctly with MikroORM.

When creating the user, we don’t need to create the address explicitly. Instead, MikroORM does that for us by default.

users.service.ts

The application we build in this article uses authentication. If you wanto to know more about it, check out API with NestJS #3. Authenticating users with bcrypt, Passport, JWT, and cookies

The migration

Doing all of the above and running gives us the following migration:

Migration20220529210655.ts

If you want to know more about generating migrations with MikroORM, check out API with NestJS #62. Introduction to MikroORM with PostgreSQL

The crucial thing to notice is that MikroORM adds the column to the table and makes sure it references the table.

MikroORM also adds a unique constraint to the column. Doing that makes sure the relationship is one-to-one because only one user can refer to a particular address. Trying to relate a second user to the same address would result in an error.

Fetching the related entities

We need to explicitly tell MikroORM to fetch the address of a given user if we want to do that.

users.service.ts

Thanks to doing the above, whenever we call the function, we get both the user and the address.

One-To-Many and Many-To-One

We need the one-to-many relationship when we want a row from the first table to link to multiple rows in the second table. With this approach, the rows from the second table can be linked to just one row from the first table, though.

A great example is a post entity and an author. The user can be an author of multiple posts, but a particular post can only have one author. We need to use the decorator to create a relationship like that.

post.entity.ts

To define an author of a post, we need to make sure that we pass it to our .

posts.controller.ts

We also need to create the data for the post entity properly and add the information about the author.

posts.service.ts

Thanks to the above, whenever an authenticated user creates a post, we store the information about the user.

The migration

Thanks to creating the above entities, MikroORM generates the following migration:

Migration20220529225632.ts

Like before, MikroORM added the column to the table that references the .

It is crucial to notice that the above migration does not make the unique in contrast to the one-to-one relationship. Thanks to that, a particular user can be the author of multiple posts.

Fetching the related entities

MikroORM, by default, does not query the details of the related entities.

We can alter this behavior by telling MikroORM explicitly to populate the additional information.

posts.service.ts

We can go even further and populate the nested properties too.

posts.service.ts

Fortunately, the property is built in a type-safe way, and TypeScript will ensure we don’t make a mistake here.

Many-To-Many

When we define a many-to-many relationship, a row from the first table can relate to multiple rows of the second table and the other way around.

A straightforward example is a post that can belong to multiple categories. On the other hand, a category can be associated with various posts.

category.entity.ts

post.entity.ts

MikroORM wraps the categories property in the wrapper, which is not a regular array.

The easiest way to let the user assign categories to a post is to expect the POST request to contain an array of category ids.

createPost.dto.ts

Providing the array when calling the method assigns categories to the created post.

Fetching the related entities

To ensure that the above works properly, we can fetch information about the related categories.

posts.service.ts

With the above approach, we send quite a lot of redundant data. Instead, we might want avoid populating the relationship’s data and fetch it separately.

The migration

By creating entities like that and generating a migration, we end up with the following file:

Migration20220530004103.ts

When we defined a many-to-many relationship between posts and categories, MikroORM created a table. To store the information about a many-to-many relation, MikroORM uses the and columns.

Bidirectional relationships

So far, we’ve been creating unidirectional relationships. It means that we’ve defined them only on one side of the relationship.

For example, we’ve used the decorator to assign an author to a post.

post.entity.ts

Doing the above and creating a migration caused our table to have the column.

Because the post entity contains the , we call it the owning side of the relationship.

We could make the above relationship bidirectional by defining it on the user’s side, using the decorator.

user.entity.ts

The crucial thing about defining a bidirectional relationship is that it does not modify the structure of the database. For example, creating a bidirectional relationship does not cause the user table’s rows to contain the posts’ ids.

Thanks to having the property in the user’s entity, we can easily populate it.

users.service.ts

We can also create bidirectional relationships using One-To-One and Many-To-Many.

The potential issues with bidirectional relationships

While the above solution gives us an effortless way to fetch additional data, it comes with a cost. Even though it looks straightforward, it contains quite a bit of implicit complexity.

When we fetch the user with a list of posts, MikroORM has to go through all posts in the table to find matching entities. So it might hurt our performance at some point if we use this feature carelessly.

Also, in our case, to achieve the bidirectional relationship, the file imports the file and vice versa. The above creates a circular dependency, which is usually frowned upon. If you want to know more about circular dependencies and what issues they might cause, check out API with NestJS #61. Dealing with circular dependencies.

Summary

We’ve gone through all the relationship types in this article and implemented them with MikroORM and PostgreSQL. Besides regular relationships, we’ve also defined a bidirectional relationship. As a result, we’ve learned quite a bit about how MikroORM works and how it affects our database structure. Thanks to that, we can better grasp how our application communicates with the database and how it can affect its performance.

Series Navigation<< API with NestJS #62. Introduction to MikroORM with PostgreSQLAPI with NestJS #64. Transactions with PostgreSQL and MikroORM >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Dominus
Dominus
2 years ago

This is awesome. Thanks for this. I would love to see a tutorial on how we could do the above in combination with ts-japi https://mathematic-inc.github.io/ts-japi/ so that we can apply jsonapi.org schema to REST API responses. Right now I’m working on a nestjs+mikro+postgres app and we’re trying different ways to do this, but it seems unclear. There is this library https://github.com/tzellman/nest-jsonapi however that uses transformalizer which seems to now be private and unmaintained. I’ve reached out to the developer of nest-japi and I’m awaiting any response on this matter. In the meantime, it looks like ts-japi is the best option.