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.
1import {
2 serial,
3 text,
4 integer,
5 pgTable,
6 AnyPgColumn,
7} from 'drizzle-orm/pg-core';
8import { relations } from 'drizzle-orm';
9
10export const categories = pgTable('categories', {
11 id: serial('id').primaryKey(),
12 name: text('name').notNull(),
13 parentCategoryId: integer('parent_category_id').references(
14 (): AnyPgColumn => categories.id,
15 ),
16});
17
18export const databaseSchema = {
19 categories,
20 // ...
21};We use the AnyPgColumn type to avoid the following error: categories implicitly has type any 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.
1import { IsString, IsNotEmpty, IsNumber, IsOptional } from 'class-validator';
2
3export class CategoryDto {
4 @IsString()
5 @IsNotEmpty()
6 name: string;
7
8 @IsNumber()
9 @IsOptional()
10 parentCategoryId?: number;
11}We also have to modify our service so that it can insert the parent category ID into our database.
1import { Injectable } from '@nestjs/common';
2import { CategoryDto } from './dto/category.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async create(data: CategoryDto) {
11 const createdCategories = await this.drizzleService.db
12 .insert(databaseSchema.categories)
13 .values({
14 name: data.name,
15 parentCategoryId: data.parentCategoryId,
16 })
17 .returning();
18
19 return createdCategories.pop();
20 }
21
22 // ...
23}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.
1import {
2 serial,
3 text,
4 integer,
5 pgTable,
6 AnyPgColumn,
7} from 'drizzle-orm/pg-core';
8import { relations } from 'drizzle-orm';
9
10export const categories = pgTable('categories', {
11 id: serial('id').primaryKey(),
12 name: text('name').notNull(),
13 parentCategoryId: integer('parent_category_id').references(
14 (): AnyPgColumn => categories.id,
15 ),
16});
17
18export const categoriesRelations = relations(categories, ({ one }) => ({
19 parentCategory: one(categories, {
20 fields: [categories.parentCategoryId],
21 references: [categories.id],
22 }),
23}));
24
25export const databaseSchema = {
26 categories,
27 categoriesRelations,
28};Thanks to defining the categoriesRelations, we can now use the relational query API built into the Drizzle ORM.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getById(categoryId: number) {
11 const category = await this.drizzleService.db.query.categories.findFirst({
12 with: {
13 parentCategory: true,
14 },
15 where: eq(databaseSchema.categories.id, categoryId),
16 });
17
18 if (!category) {
19 throw new NotFoundException();
20 }
21
22 return category;
23 }
24
25 // ...
26}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.
1import {
2 serial,
3 text,
4 integer,
5 pgTable,
6 AnyPgColumn,
7} from 'drizzle-orm/pg-core';
8import { relations } from 'drizzle-orm';
9
10export const categories = pgTable('categories', {
11 id: serial('id').primaryKey(),
12 name: text('name').notNull(),
13 parentCategoryId: integer('parent_category_id').references(
14 (): AnyPgColumn => categories.id,
15 ),
16});
17
18export const categoriesRelations = relations(categories, ({ one, many }) => ({
19 parentCategory: one(categories, {
20 fields: [categories.parentCategoryId],
21 references: [categories.id],
22 relationName: 'nested_categories',
23 }),
24 nestedCategories: many(categories, {
25 relationName: 'nested_categories',
26 }),
27}));
28
29export const databaseSchema = {
30 categories,
31 categoriesRelations,
32};Above, we add the relationName 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 nestedCategories in our service.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import { DrizzleService } from '../database/drizzle.service';
3import { databaseSchema } from '../database/database-schema';
4import { eq } from 'drizzle-orm';
5
6@Injectable()
7export class CategoriesService {
8 constructor(private readonly drizzleService: DrizzleService) {}
9
10 async getById(categoryId: number) {
11 const category = await this.drizzleService.db.query.categories.findFirst({
12 with: {
13 parentCategory: true,
14 nestedCategories: true,
15 },
16 where: eq(databaseSchema.categories.id, categoryId),
17 });
18
19 if (!category) {
20 throw new NotFoundException();
21 }
22
23 return category;
24 }
25
26 // ...
27}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.
1WITH RECURSIVE category_hierarchy AS (
2 SELECT id, name, 0 as level -- Starting with level 0 for the root category
3 FROM categories
4 WHERE id = 2 -- 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 -- We increment the level for each nested category
11 FROM categories category
12 JOIN category_hierarchy category_hierarchy
13 ON category.parent_category_id = category_hierarchy.id
14)
15SELECT * FROM category_hierarchy;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.