Handling relational databases with NestJS, Prisma and GraphQL

Introduction

This article aims at presenting the way we implemented our GraphQL API in use at Escape to interact with a relational database. It includes among other things:

  • A quick overview of the different tools provided by Nest to do GraphQL APIs
  • Some guidelines and code examples to serve entities from the database, and especially to handle fields pointing to related entities.
  • Our take at solving N+1 queries in this context.

💽 Stack and context overview

At Escape, our SaaS solution is powered by a GraphQL backend. This backend is a monolith application built on NestJS, with its Apollo GraphQL server, interacting with a SQL database using the Prisma ORM (Object-relational-mapper).

After such a name dropping, and if you do not share our stack, you can discover some of these tools if you would like to:

  • GraphQL is a query language. It defines a set of rules to follow when designing an API, and a query format used to interact with it. GraphQL is not a standalone framework or tool, it is a specification, used by other providers to develop their own framework for doing GraphQL applications, like Apollo.
  • NestJS is a JavaScript framework used for building web applications.
  • Apollo is a TypeScript implementation of the GraphQL specification, allowing for building graph APIs in JavaScript. It is bundled within NestJS by the @nestjs/graphql package, providing useful decorators for nest-like query and object declaration.
  • Prisma is a TypeScript ORM used for database-related interactions. Using it with NestJS is well documented here.

💻 Coding a basic resolver

📊 Data models overview

Our model is slightly basic for this example: We have users with some classic fields (id, email, username, password) and users can create projects within the Escape SaaS. These projects are named Applications within our code.

Users have many applications, and here is the resulting SQL relational graph:‌

🔍 First resolver

In GraphQL, the queries are handled by resolver functions. In NestJS, we will implement such a function in a class-based style, enforced by the framework.

The following code defines two things:

  • A query getUser for retrieving a User from the database
  • A mutation createUser for creating a new User in the database

Note: The PrismaService used here is taken from the NestJS documentation

@Resolver(() => UserObjectType)
@Injectable()
export class UserResolver {
  constructor(private readonly prismaService: PrismaService) {}

  @Query(() => UserObjectType)
  async getUser(
    @Args('id', { nullable: false })
    email: string
  ) {
    return this.prisma.user.findUnique({
      where: { id }
    }); // This code is related to Prisma and may vary with your ORM
  }

  @Mutation(() => UserObjectType)
  async createUser(
    @Args('UserCreateInput', { nullable: false })
    createUserInput: CreateUserInput
  ) {
    const password: string = await bcrypt.hash(createUserInput.password, 10);
    return await this.prisma.user.create({
      data: {
        ...createUserInput,
        password,
      }
    }); // This code is related to Prisma and may vary with your ORM
  }
}

With this, we can perform GraphQL queries to the API like this one:

mutation {
  createUser(
    UserCreateInput: {
      email: "new-guy@escape.tech"
      password: "j;vs?xsblk"
      username: "maxence"
    }
  ) {
    id
    email
    applications {id scans {alerts {id}}}
  }
}

📩 Results Serialization

Wikipedia: In computing, serialization is the process of translating a data structure or object state into a format that can be stored or transmitted.

Serialization (especially specifying which field can be queried and which field can not) is automatically handled by the framework by specifying the returned type of a Query.

This is why we define separate object types for both users and applications, which will be the returned types of our resolvers.

@ObjectType()
export class UserObjectType {
  @Field()
  id: string;

  @Field()
  email: string;

  @Field()
  username: string;
  
  // this is new
  @Field(() => [ApplicationObjectType], { nullable: false })
  applications: ApplicationObjectType;
}

One can notice here that the password field is excluded from the UserObjectType class so that one can not query it from the getUser query.

query {
  getUser(id: 1) {
    id
    email
    password // This will result in an error
  }
}

↔️ Handling SQL relationships

➕ Adding a relational field returned by the API

The applications are queried in a separate SQL query. To achieve this, we declare an application field in our UserObjectType class.

@ObjectType()
export class UserObjectType {
  @Field()
  id: string;

  @Field()
  email: string;

  @Field()
  username: string;
  
  // this is new
  @Field(() => [ApplicationObjectType], { nullable: false })
  applications: ApplicationObjectType;
}
@ResolveField(() => [ApplicationObjectType])
async applications(@Parent() user: User) {
  return await this.prisma.application.findMany({
    where: { ownerId: user.id }
  });;
}

When a query asks for the applications field of a user returned by getUser, the resolver defined in UserObjectType.applications is executed. This is because we annotated it with the @ResolveField directive.

We can now query a user's applications:

query {
  getUser(id: 1) {
    id
    email
    applications { 
      id 
      name 
    }
  }
}

🎯 Querying dynamic fields

Since the @ResolveField decorator enables us to create a custom resolver function, we can use it to return computed data.

For instance, for a given user, we might want to count its applications or count its active applications. To do so, we need to:

  • Update the UserObjectType model to declare the dynamic fields that it can return
  • Update the UserResolver class to implement the two dynamic resolvers
@ObjectType()
export class UserObjectType {
  @Field()
  id: string;

  @Field()
  email: string;

  @Field()
  username: string;
  
  @Field(() => [ApplicationObjectType], { nullable: false })
  applications: ApplicationObjectType;
  
  @Field()
  applicationsCount: number;
  
  @Field()
  activeApplicationsCount: number;
}
@ResolveField(() => number)
async applicationsCount(@Parent() user: User) {
  return await this.prisma.application.aggregate({
    where: {
      ownerId: user.id
    },
    _count: true
  });
}

@ResolveField(() => number)
async activeApplicationsCount(@Parent() user: User) {
  return await this.prisma.application.aggregate({
    where: {
      ownerId: user.id,
      isActive: true
    },
      _count: true
  });
}

We can now query these fields like so:

query {
  getUser(id: 1) {
    id
    email
    applicationsCount
    activeApplicationsCount
  }
}

✍️ Writing field functions

With this directive, we define functions that can use the same directives and decorators as the classic queries resolvers. This means that we can use the @Arg directive and make a single, parametric resolver for these two fields.

@ResolveField(() => number)
async applicationsCount(
  @Parent() user: User, 
  @Args('isActive', { nullable: true }) isActive: boolean
) {
  const isActiveWhereStatement = isActive ? {} : { isActive: true }
  return await this.prisma.application.aggregate({
    where: {
      ownerId: user.id,
      ...isActiveWhereStatement
    },
    _count: true
  });
}

And then we can perform the similar query

query {
  getUser(id: 1) {
    id
    email
    allApplicationsCount: applicationsCount
    activeApplicationsCount: applicationsCount(isActive: true)
  }
}

☠️ The N+1 problem

Per the Prisma documentation:

The n+1 problem occurs when you loop through the results of a query and perform one additional query per result, resulting in n a number of queries plus the original (n+1). This is a common problem with ORMs, particularly in combination with GraphQL, because it is not always immediately obvious that your code is generating inefficient queries.

This part is already well documented by Prisma itself, so we will not do a lot more than applying their advice on our specific problem here.

In our case, when we query a user, with the getUser query, we can ask to retrieve its applications, using a dedicated applications field resolver. When we only query one user, everything is fine and we run the application resolver at most once.

If we make multiple queries for the users, for some admin panel, for instance, we will have a query getUsers able to retrieve multiple users.

query {
  getUsers() {
    id
    email
    applications {
    	id
        name
    }
  }
}

However, such a query will call the applications resolver once per retrieved user, leading to n additional queries.

To solve this issue, we will have to replace our findMany Prisma calls with the fluent API provided by the ORM. The difference here between findMany and <x>.applications() calls is that Prisma is able to batch even between multiple resolvers calls.

@ResolveField(() => [ApplicationObjectType])
async applications(@Parent() user: User) {
	return this.prisma.user.findUnique({
    	where: { id: user.id },
    })
    .applications()
}

Conclusion

This article was about giving an overview of how we arrange several technologies and frameworks (GraphQL, NestJs, Prisma) together to build up our backend infrastructure. Some of the principles explained here are guidelines for our development team.

We used a very simplistic example for illustrating this article. However, if you love contributing to complex applications, and desire to be involved in the entire process of developing such systems, from design to deployment, you might want to join us @Escape!

Maxence Lecanu

Maxence Lecanu

Fullstack developer @Escape, I help my team turning our ideas into production.
Paris