API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL

NestJS SQL

This entry is part 154 of 158 in the API with NestJS

Creating relationships across tables is a crucial aspect of working with SQL databases. Previously, this series focused on using the Drizzle ORM to create simple relationships, such as one-to-one and many-to-one relationships. In this article, we learn about many-to-many relationships, which are slightly more complex.

Introducing many-to-many relationships

A many-to-many relationship is necessary when multiple records in one table connect to multiple records in another table. A good example of this is the relationship between categories and articles. A single category can relate to various articles, and likewise, an article can belong to multiple categories. For instance, the article you are reading is listed under both SQL and JavaScript categories.

Until now, with the Drizzle ORM, we have set up one-to-one and many-to-one relationships. We accomplished this by using a basic column containing a foreign key corresponding to a row in the related table.

The design becomes more complex when linking a specific article to multiple categories. We can’t put multiple IDs in the column. To address this, we need to create a joining table.

Creating the table allows us to store the connections between individual articles and categories.

Many-to-many relationships with Drizzle ORM

The first step to implementing a many-to-many relationship with the Drizzle ORM is to modify the database schema.

database-schema.ts

While adding an column to our table is an option, it is unnecessary. Instead, we can use a composite primary key made up of and . This approach offers several benefits beyond saving disk space. Because PostgreSQL enforces the uniqueness of primary keys, this method ensures that an article cannot be assigned to the same category more than once.

Now, we can create a new migration based on the changes in the schema.

If you want to learn more about migrations with Drizzle, check out API with NestJS #149. Introduction to the Drizzle ORM with PostgreSQL

The last step is to run the migration to modify our database and add the new tables.

Connecting articles to categories

An article can belong to several different categories. Therefore, when creating a new article, we should be able to handle the following data format:

Let’s modify our DTO to support that.

create-article.dto.ts

The array with two elements means we need to insert two rows into the table.

We must create an article and insert the above rows in a single transaction to achieve that.

If you want to learn more about transactions with Drizzle ORM, check out API with NestJS #153. SQL transactions with the Drizzle ORM

articles.service.ts

Fetching the categories of a particular article

While we could perform join queries manually to fetch the categories of a particular article, the Drizzle ORM offers a more straightforward solution. However, we must provide the Drizzle ORM with details about our relationships.

database-schema.ts

Thanks to creating the , , and objects, Drizzle ORM now has all the information it needs to do the join queries for us. We can use this to fetch the data of a particular article with all of their categories.

articles.service.ts

Since the data of a particular category is nested inside each object in the array, we need to parse the data to get the array of categories.

Fetching all articles from a particular category

We can use a similar approach to fetch all articles of a particular category.

categories.service.ts

Again, we need to transform the data to take into account that the properties of each article are deeply nested.

Summary

In this article, we discussed the many-to-many relationship and demonstrated how to implement it in a project using the Drizzle ORM and NestJS. Using articles and categories as an example, we learned how to handle a joining table with the built-in features of Drizzle. This allows us to efficiently manage many-to-many relationships in our projects that use the Drizzle ORM.

Series Navigation<< API with NestJS #153. SQL transactions with the Drizzle ORMAPI with NestJS #155. Offset and keyset pagination with the Drizzle ORM >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments