API with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL

NestJS SQL

This entry is part 122 of 173 in the API with NestJS

Implementing relationships across tables is a crucial aspect of working with SQL databases. So far, this series covers using Kysely to design simple relationships such as one-to-one and many-to-one. This article looks into many-to-many, which is a slightly more advanced relationship.

Check out this repository if you want to see the full code from this article.

The idea behind the many-to-many relationship

We need to implement a many-to-many relationship if multiple records from one table relate to multiple records in another table. A very good example is a connection between categories and articles. A particular category can be related to various articles. On the other hand, a single article can be published under multiple categories. For example, the article you are reading falls both under the SQL and JavaScript categories.

So far, when working with Kysely, we implemented the one-to-one and many-to-one relationships. We used a simple column with a foreign key matching a row from the related table to do that.

The design becomes more complex when we want to connect a particular article to many categories. We shouldn’t put multiple IDs into the column. To deal with this challenge, we need to create a joining table.

By creating the , we can store the relationships between particular articles and categories.

Implementing the many-to-many relationship

The first step to implementing the many-to-many relationship with Kysely is to create a new migration.

20230827204025_add_categories_table.ts

While we could add the column to our table, it is unnecessary. Instead, we specify a composite primary key consisting of the  and . This approach has more advantages than just saving disk space. Since PostgreSQL ensures a particular primary key is unique, we cannot assign an article to the category multiple times.

Besides adding a migration, we also need to create additional interfaces.

categoriesTable.ts

categoriesArticlesTable.ts

Once we have them, we can alter our interface.

database.ts

Connecting articles to categories

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

The above array indicates that we want to add two rows to the table.

One way of inserting multiple rows into a particular table is with a query.

Above, we use the function built into PostgreSQL to expand an array to a set of rows. We can now combine it with the query to save the results of the into the database.

Let’s use the above knowledge to create an article and connect it to multiple categories in the same query. First, let’s create a model for an article with the category ids.

articleWithCategoryIds.model.ts

Now, we can add a new method to our repository that creates the article and connects it to categories with a single query.

articles.repository.ts

Fetching the category IDs of an article

Whenever we fetch the details of a particular article, we can attach the IDs of the related categories. The first step would be to prepare an appropriate model.

articleWithDetails.model.ts

You can go a step further and include the details of each category.

Now, we can make a separate query to fetch the categories related to a specific article.

articles.repository.ts

The above queries would benefit from wrapping them in a transaction. This is a broad topic that deserves a separate article.

Fetching all articles from a certain category

Another feature that might be needed is getting a list of all articles from a particular category. To achieve it, we need to join the data from the table with . First, we need to retrieve all article IDs from a specific category.

Since we now know the IDs of all articles, we can use the statement to match them with the rows from the table.

Let’s create a new model suitable for the above data.

categoryWithArticles.model.ts

Now, we can use all of the above knowledge to:

  • retrieve the data of a specific category,
  • match it with the articles,
  • fit the data into the new model.
categories.response.ts

Summary

In this article, we’ve explained the many-to-many relationship and implemented it in a project with Kysely and NestJS. When doing that, we used an example of articles and categories and learned how to manage a joining table and insert multiple records into the database with one query.

Some of the queries from this article could have been wrapped in a transaction, which deserves a separate article. Stay tuned!

Series Navigation<< API with NestJS #121. Many-to-one relationships with PostgreSQL and KyselyAPI with NestJS #123. SQL transactions with Kysely >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments