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 @relation attribute to define the relationships between our models.
1model User {
2 id Int @id @default(autoincrement())
3 email String @unique
4 name String
5 password String
6 articles Article[]
7 address Address? @relation(fields: [addressId], references: [id])
8 addressId Int? @unique
9}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 @relation parameter but we need to name our relationship.
1model Category {
2 id Int @id @default(autoincrement())
3 name String
4 articles Article[]
5 parentCategory Category? @relation("CategoriesHierarchy", fields: [parentCategoryId], references: [id])
6 nestedCategories Category[] @relation("CategoriesHierarchy")
7 parentCategoryId Int?
8}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.
1npx prisma migrate dev --name add-nested-categories1-- AlterTable
2ALTER TABLE "Category" ADD COLUMN "parentCategoryId" INTEGER;
3
4-- AddForeignKey
5ALTER TABLE "Category" ADD CONSTRAINT "Category_parentCategoryId_fkey" FOREIGN KEY ("parentCategoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;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.
1import { IsString, IsNotEmpty, IsInt } from 'class-validator';
2import { CanBeUndefined } from '../../utilities/can-be-undefined';
3
4export class CreateCategoryDto {
5 @IsString()
6 @IsNotEmpty()
7 name: string;
8
9 @IsInt({ each: true })
10 @CanBeUndefined()
11 nestedCategoryIds?: number[];
12}If you want to learn more about the @CanBeUndefined 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.
1import { IsString, IsNotEmpty, IsInt } from 'class-validator';
2import { CanBeUndefined } from '../../utilities/can-be-undefined';
3
4export class UpdateCategoryDto {
5 @IsString()
6 @IsNotEmpty()
7 @CanBeUndefined()
8 name?: string;
9
10 @IsInt({ each: true })
11 @CanBeUndefined()
12 nestedCategoryIds?: number[];
13}When making the queries using Prisma, it is important to acknowledge that the user might provide an incorrect category ID in the nestedCategoryIds. When this happens, it violates the foreign key constraint. Fortunately, we can handle that. To do that, we first need to adjust our PrismaError enum.
1export enum PrismaError {
2 RecordDoesNotExist = 'P2025',
3 UniqueConstraintViolated = 'P2002',
4 ForeignKeyConstraintViolated = 'P2003',
5 ConnectedRecordsNotFound = 'P2018',
6}We can now use the new property of our enum in the categories service.
1import {
2 ConflictException,
3 Injectable,
4 NotFoundException,
5} from '@nestjs/common';
6import { PrismaService } from '../database/prisma.service';
7import { CreateCategoryDto } from './dto/create-category.dto';
8import { Prisma } from '@prisma/client';
9import { PrismaError } from '../database/prisma-error.enum';
10import { UpdateCategoryDto } from './dto/update-category.dto';
11
12@Injectable()
13export class CategoriesService {
14 constructor(private readonly prismaService: PrismaService) {}
15
16 async create(category: CreateCategoryDto) {
17 const nestedCategories =
18 category.nestedCategoryIds?.map((id) => ({
19 id,
20 })) || [];
21 try {
22 return await this.prismaService.category.create({
23 data: {
24 name: category.name,
25 nestedCategories: {
26 connect: nestedCategories,
27 },
28 },
29 include: {
30 nestedCategories: true,
31 },
32 });
33 } catch (error) {
34 if (
35 error instanceof Prisma.PrismaClientKnownRequestError &&
36 error.code === PrismaError.ConnectedRecordsNotFound
37 ) {
38 throw new ConflictException(
39 'Some of the provided category ids are not valid',
40 );
41 }
42 throw error;
43 }
44 }
45
46 async update(id: number, category: UpdateCategoryDto) {
47 try {
48 const nestedCategories =
49 category.nestedCategoryIds?.map((id) => ({
50 id,
51 })) || [];
52 return await this.prismaService.category.update({
53 data: {
54 name: category.name,
55 nestedCategories: {
56 connect: nestedCategories,
57 },
58 },
59 include: {
60 nestedCategories: true,
61 },
62 where: {
63 id,
64 },
65 });
66 } catch (error) {
67 if (!(error instanceof Prisma.PrismaClientKnownRequestError)) {
68 throw error;
69 }
70 if (error.code === PrismaError.RecordDoesNotExist) {
71 throw new NotFoundException();
72 }
73 if (error.code === PrismaError.ConnectedRecordsNotFound) {
74 throw new ConflictException(
75 'Some of the provided category ids are not valid',
76 );
77 }
78 throw error;
79 }
80 }
81
82 // ...
83}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class CategoriesService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getById(id: number) {
9 const category = await this.prismaService.category.findUnique({
10 where: {
11 id,
12 },
13 include: {
14 articles: true,
15 nestedCategories: true,
16 },
17 });
18 if (!category) {
19 throw new NotFoundException();
20 }
21 return category;
22 }
23
24 // ...
25}The above approach using nestedCategories: true 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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3
4@Injectable()
5export class CategoriesService {
6 constructor(private readonly prismaService: PrismaService) {}
7
8 async getById(id: number) {
9 const category = await this.prismaService.category.findUnique({
10 where: {
11 id,
12 },
13 include: {
14 articles: true,
15 nestedCategories: {
16 include: {
17 nestedCategories: true,
18 },
19 },
20 },
21 });
22 if (!category) {
23 throw new NotFoundException();
24 }
25 return category;
26 }
27
28 // ...
29}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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { PrismaService } from '../database/prisma.service';
3import { Prisma } from '@prisma/client';
4
5@Injectable()
6export class CategoriesService {
7 constructor(private readonly prismaService: PrismaService) {}
8
9 getAll() {
10 return this.prismaService.category.findMany();
11 }
12
13 private includeNestedCategories(
14 maximumLevel: number,
15 ): boolean | Prisma.Category$nestedCategoriesArgs {
16 if (maximumLevel === 1) {
17 return true;
18 }
19 return {
20 include: {
21 nestedCategories: this.includeNestedCategories(maximumLevel - 1),
22 },
23 };
24 }
25
26 async getById(id: number) {
27 const category = await this.prismaService.category.findUnique({
28 where: {
29 id,
30 },
31 include: {
32 articles: true,
33 nestedCategories: this.includeNestedCategories(10),
34 },
35 });
36 if (!category) {
37 throw new NotFoundException();
38 }
39 return category;
40 }
41
42 // ...
43}Thanks to the includeNestedCategories 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.
1WITH RECURSIVE category_hierarchy AS (
2 SELECT id, name, 0 as level -- Starting with level 0 for the root category
3 FROM "Category"
4 WHERE id = 1 -- Replace 1 with the id of the category you want to query
5 UNION ALL
6
7 SELECT
8 category.id,
9 category.name,
10 category_hierarchy.level + 1 -- Incrementing the level for each nested category
11 FROM "Category" category
12 JOIN category_hierarchy category_hierarchy
13 ON category."parentCategoryId" = category_hierarchy."id"
14)
15SELECT * FROM category_hierarchy;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.