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

October 21, 2024

This entry is part 171 of 180 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

import {

serial,

text,

integer,

pgTable,

AnyPgColumn,

} from 'drizzle-orm/pg-core';

import { relations } from 'drizzle-orm';

export const categories = pgTable('categories', {

id: serial('id').primaryKey(),

parentCategoryId: integer('parent_category_id').references(

(): AnyPgColumn => categories.id,

});

export const databaseSchema = {

categories,

// ...

};

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.

database-schema.ts

import { IsString, IsNotEmpty, IsNumber, IsOptional } from 'class-validator';

export class CategoryDto {

@IsString()

@IsNotEmpty()

@IsNumber()

@IsOptional()

parentCategoryId?: number;

}

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

categories.service.ts

import { Injectable } from '@nestjs/common';

import { CategoryDto } from './dto/category.dto';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(data: CategoryDto) {

const createdCategories = await this.drizzleService.db

.insert(databaseSchema.categories)

.values({

parentCategoryId: data.parentCategoryId,

})

.returning();

return createdCategories.pop();

}

// ...

}

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

import {

serial,

text,

integer,

pgTable,

AnyPgColumn,

} from 'drizzle-orm/pg-core';

import { relations } from 'drizzle-orm';

export const categories = pgTable('categories', {

id: serial('id').primaryKey(),

parentCategoryId: integer('parent_category_id').references(

(): AnyPgColumn => categories.id,

});

export const categoriesRelations = relations(categories, ({ one }) => ({

parentCategory: one(categories, {

fields: [categories.parentCategoryId],

references: [categories.id],

}),

}));

export const databaseSchema = {

categories,

categoriesRelations,

};

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

categories.service.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

async getById(categoryId: number) {

const category = await this.drizzleService.db.query.categories.findFirst({

with: {

parentCategory: true,

where: eq(databaseSchema.categories.id, categoryId),

});

if (!category) {

throw new NotFoundException();

}

return category;

}

// ...

}

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

import {

serial,

text,

integer,

pgTable,

AnyPgColumn,

} from 'drizzle-orm/pg-core';

import { relations } from 'drizzle-orm';

export const categories = pgTable('categories', {

id: serial('id').primaryKey(),

parentCategoryId: integer('parent_category_id').references(

(): AnyPgColumn => categories.id,

});

export const categoriesRelations = relations(categories, ({ one, many }) => ({

parentCategory: one(categories, {

fields: [categories.parentCategoryId],

references: [categories.id],

relationName: 'nested_categories',

}),

nestedCategories: many(categories, {

relationName: 'nested_categories',

}),

}));

export const databaseSchema = {

categories,

categoriesRelations,

};

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.

categories.service.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

@Injectable()

export class CategoriesService {

constructor(private readonly drizzleService: DrizzleService) {}

async getById(categoryId: number) {

const category = await this.drizzleService.db.query.categories.findFirst({

with: {

parentCategory: true,

nestedCategories: true,

where: eq(databaseSchema.categories.id, categoryId),

});

if (!category) {

throw new NotFoundException();

}

return category;

}

// ...

}

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.

WITH RECURSIVE category_hierarchy AS (

SELECT id, name, 0 as level -- Starting with level 0 for the root category

FROM categories

WHERE id = 2 -- Replace 1 with the id of the category you want to query

UNION ALL

SELECT

category.id,

category.name,

category_hierarchy.level + 1 -- We increment the level for each nested category

FROM categories category

JOIN category_hierarchy category_hierarchy

ON category.parent_category_id = category_hierarchy.id

)

SELECT * 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.

Series Navigation<< API with NestJS #170. Polymorphic associations with PostgreSQL and Drizzle ORMAPI with NestJS #172. Database normalization with Drizzle ORM and PostgreSQL >>