API with NestJS #137. Recursive relationships with Prisma and PostgreSQL

NestJS SQL

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

When we work with SQL databases, we usually create tables that relate to each other in some way. Managing those relationships is one of the most fundamental aspects of working with SQL. Across various types of SQL relationships, there is one that sticks out.

Sometimes, one of our tables points back to itself, creating a recursive relationship. This often occurs with hierarchical structures. In this article, we learn more about recursive relationships and how to create them with Prisma.

Recursive relationships are sometimes called self-referencing relationships

Introducing recursive relationships

In the previous parts of this series, we’ve designed a database that uses various types of relationships.

When defining it in the Prisma schema, we use the attribute to define the relationships between our models.

schema.prisma

The only exception is when we define an implicit many-to-many relationship. If you want to learn more about defining regular relationships using Prisma, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma

Let’s create a hierarchical structure where a particular article category can have nested categories like this:

  • Node.js
    • Express
    • NestJS
      • Integrating with Prisma
  • React
    • React Hooks
    • Testing React

To achieve this, we need to connect the category table to itself. When we want to create a recursive relationship, we also need to use the  parameter but we need to name our relationship.

schema.prisma

The above schema creates a one-to-many recursive relationship where:

  • a particular category can have no more than one parent category,
  • multiple categories can share the same parent.

Now, let’s generate a migration.

migration.sql

As we can see, recursive relationships don’t differ too much from regular relationships at first glance regarding the underlying SQL.

Defining the nested categories

One of the ways that we can let the user define the nested categories is by providing them when creating the category.

create-category.dto

If you want to learn more about the decorator, check out API with NestJS #114. Modifying data using PUT and PATCH methods with Prisma

Similarly, we can do that when updating an existing category.

update-category.dto

When making the queries using Prisma, it is important to acknowledge that the user might provide an incorrect category ID in the . When this happens, it violates the foreign key constraint. Fortunately, we can handle that. To do that, we first need to adjust our enum.

prisma-enum.ts

We can now use the new property of our enum in the categories service.

categories.service.ts

Querying the nested categories

Connecting a category to its nested categories is relatively easy. However, fetching all of the related nested records might be tricky.

categories.service.ts

The above approach using causes our API to respond with the nested categories.

The problem is that it only returns the first level of nested entities. With Prisma, we must state how deep we want to query explicitly.

categories.service.ts

PostgreSQL has recursive queries that could solve this problem, but Prisma does not support them yet. As a workaround, we could create a recursive function where we specify the maximum number of levels.

categories.service.ts

Thanks to the method, we can fetch deeply nested categories up to a certain level.

Recursive query using raw SQL

If setting the maximum level of the query is not a good enough solution for you, you can use a raw recursive SQL.

With this approach, we fetch all deeply nested subcategories of a particular category.

Summary

In this article, we learned how to handle recursive relationships in PostgreSQL using Prisma. To do that, we created an example using categories and subcategories. An interesting part of the example was how to fetch categories along with all its deeply nested subcategories. We had to work around some of the limitations of Prisma to fetch deeply nested entities up to a certain level. If that’s not good enough, we were able to write a raw SQL query that fetches all entities without setting a maximum level. All of this gave us a good idea of how to handle various cases that include recursive relationships.

Series Navigation<< API with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range typesAPI with NestJS #138. Filtering records with Prisma >>
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Alex
2 months ago

I did everything carefully as it is written in this article, however, when creating a new category in the database, nothing is created and error:
Foreign key constraint failed on the field:
categories_parentId_fkey (index)