API with NestJS #171. Recursive relationships with Drizzle ORM and PostgreSQL

NestJS

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

Managing relationships between the tables in our database is one of the fundamental aspects of using SQL. When a particular table points back to itself, it creates a recursive relationship. In this article, we learn what the recursive relationships are and how to work with them using the Drizzle ORM and PostgreSQL.

We sometimes refer to the recursive relationships as the self-referencing relationships.

Recursive relationships

In the previous parts of this series, we’ve created a database with articles, users, and categories.

Above, we have a many-to-one relationship between users and articles where a particular user can be an author of multiple articles. Still, an article can have just one author.

If you want to know more about many-to-one relationships with the Drizzle ORM, check out API with NestJS #151. Implementing many-to-one relationships with Drizzle ORM

Also, we have a many-to-many relationship where a single category can relate to various articles, and an article can belong to multiple categories.

To learn more, check out API with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL

Adding a recursive relationship

The recursive relationships often occur when we deal with hierarchical structures. A good example is working with nested categories, such as:

  • React
    • Testing React
    • Redux
  • Node.js
    • NestJS
      • Integrating with the Drizzle ORM
    • Express.js

To achieve the above in our database, we must create a recursive relationship where a particular category can point to its parent.

database-schema.ts

We use the type to avoid the following error:
implicitly has type because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

Defining the parent

Now, we can modify our DTO to allow the users to define the parent category.

database-schema.ts

We also have to modify our service so that it can insert the parent category ID into our database.

categories.service.ts

Fetching the related entities

At some point, we might want to fetch the details of a parent or children. Fortunately, the Drizzle ORM’s relational query API allows us to do that.

Fetching the parent

To easily fetch the parent of a given entity in the recursive relationship, we need to define the relationship in our database schema.

database-schema.ts

Thanks to defining the , we can now use the relational query API built into the Drizzle ORM.

categories.service.ts

Fetching the children

Besides fetching the parent of a given entity, we might want to fetch all of their children. To do that, we need to adjust the definition of our relationship in our database schema.

database-schema.ts

Above, we add the to avoid the following error: There are multiple relations between “categories” and “categories”. Please specify relation name

Thanks to the above, we can use the in our service.

categories.service.ts

Fetching the deeply nested entities

It’s crucial to notice that in the above example, we’re only fetching one level of entities, so we can’t see the nested categories of our nested categories.

PostgreSQL has support for fully recursive queries that would solve this problem, but the Drizzle ORM does not support that yet. If we need that, we can write a raw SQL query.

Summary

In this article, we explored the recursive relationships with PostgreSQL and the Drizzle ORM. While doing that, we used an example of categories and subcategories. We used the Drizzle ORM to fetch one level of the nested entities and leveraged raw SQL to fetch the deeply nested entities. Thanks to all of the above, we learned how the recursive relationships work, what the limitations of the Drizzle ORM are, and how to overcome them if necessary.

Series Navigation<< API with NestJS #170. Polymorphic associations with PostgreSQL and Drizzle ORMAPI with NestJS #172. Database normalization with Drizzle ORM and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments