How to query your database using Prisma with NestJS

[utterances-bot]

How to query your database using Prisma with NestJS

Learn how to setup a database with Prisma 2.0 and query data using NestJS.

https://notiz.dev/blog/how-to-connect-nestjs-with-prisma

[Guiradi]

Hi! I am a Nest entusiastic and I've been trying to learn with your nestjs-prisma-starter template. Unfortunately I'm stuck in an error that is getting on me, and I can't find any others repositories with this stack, so I think I need your help!

Could you please take a look at my code? https://github.com/Guiradi/Sempapp-API My problems are 2:

First, I don't know why but prisma client and my nest models enums are not assignable (???) Type 'import("api/node_modules/.prisma/client/index").User[]' is not assignable to type 'import("api/src/models/user.model").User[]'. Type 'import("api/node_modules/.prisma/client/index").User' is not assignable to type 'import("api/src/models/user.model").User'. Types of property 'role' are incompatible. Type '"USER" | "ADMIN"' is not assignable to type 'Role'. Type '"USER"' is not assignable to type 'Role'.

And, my nested relationships are in loop! One model needs that the other one loads his relationship and it turned into a cycle! I can't find this in Prisma documentation...

Hope you could help me, this is my first project with Nest and Prisma and is for study only.

Great article by the way, it helped me a lot!

Thank you

Guilherme

[marcjulian]

@Guiradi You are trying to assign the Prisma User to the GraphQL User which is not working due to the following reasons.

1. Prisma `type` != GraphQL `class`

**Prisma** `User` ```ts /** * Model User */ export type User = { id: string createdAt: Date updatedAt: Date email: string password: string firstname: string | null lastname: string | null role: Role } ``` **Graphql** `User` ```ts @ObjectType() export class User extends Model { email: string; firstname?: string; lastname?: string; role: Role; posts: Post[]; @HideField() password: string; } ```

2. GraphQL Model contains relationships

**Graphql** `User` ```ts @ObjectType() export class User extends Model { email: string; firstname?: string; lastname?: string; role: Role; posts: Post[]; <- not available in the Prisma User type @HideField() password: string; } ```

It is similar for the enum. Inspect the generated prisma client to see the differents between the enum types.

I am not sure about the loop in nested relationships. Your code example is not available anymore for me to test.

Thanks for reaching out!

[johsthlm]

Hi,

Thanks for the nestjs prisma 2 starter template. I am prototyping with it for graphql.

What puzzles me; how do I code an explicit many-to-many relation in the resolvers? I have followed the guidance from prisma 2 for the explicit model:

https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations#many-to-many-relations

Cheers, Johan

[marcjulian]

Hi @johsthlm,

take the following schema.prisma

model Post {
  id         Int        @id @default(autoincrement())
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

For both models you create an entity class including the relation for the relation

Post entity:

import { Field, ObjectType } from '@nestjs/graphql';
import { Category } from './category.entity';

@ObjectType()
export class Post {
  @Field(() => Int)
  id: number;
  @Field(() => [Category])
  categories: Category[];
}

Category entity:

import { Field, ObjectType } from '@nestjs/graphql';
import { Post } from './post.entity';

@ObjectType()
export class Category {
  @Field(() => Int)
  id: number;
  @Field(() => [Post])
  posts: Post[];
}

Now you have two options to fetch the relationship in the resolver

1. Use Prisma Client include which will return the data for Posts and Categorys

import { Resolver, Query, Mutation, Args } from '@nestjs/graphql';
import { Post } from './entities/post.entity';

@Resolver(() => Post)
export class ProductsResolver {
  constructor(private readonly prisma: PrismaService) {}

  @Query(() => Post, { name: 'post' })
  findOne(@Args('id', { type: () => Int }) id: number) {
    return this.prisma.post.findOne({
      where: { id },
      include: {
        categories: true,
      },
    });
  }

  @Query(() => Post, { name: 'posts' })
  findMany() {
    return this.prisma.post.findMany({
      include: {
        categories: true,
      },
    });
  }
}

2. Use ResolveField to resolve the relationship

import { Resolver, Query, Mutation, Args } from '@nestjs/graphql';
import { Post } from './entities/post.entity';

@Resolver(() => Post)
export class ProductsResolver {
  constructor(private readonly prisma: PrismaService) {}

  @Query(() => Post, { name: 'post' })
  findOne(@Args('id', { type: () => Int }) id: number) {
    return this.prisma.post.findOne({
      where: { id },
    });
  }

  @Query(() => Post, { name: 'posts' })
  findMany() {
    return this.prisma.post.findMany();
  }

  @ResolveField()
  categories(@Parent() post: Post) {
    return this.prisma.post.findOne({ where: { id: post.id } }).categories();
  }
}

Hopefully this answer helps you, let me know otherwise create a demo repo with the problem.

Have a great day. Marc

[johsthlm]

Thanks Marc,

I tried your commented solution first. Then I wanted more control on the "connecting" attributed and followed this explicit schema of a relation table where I am stuck in creating a nice @ResolveField and also the mutation of creating the records building up a relation:

model Post {
  id         Int                 @id @default(autoincrement())
  title      String
  categories CategoriesOnPosts[]
}
model Category {
  id    Int                 @id @default(autoincrement())
  name  String
  posts CategoriesOnPosts[]
}
model CategoriesOnPosts {
  post        Post     @relation(fields: [postId], references: [id])
  postId      Int       // relation scalar field (used in the `@relation` attribute above)
  category    Category @relation(fields: [categoryId], references: [id])
  categoryId  Int      // relation scalar field (used in the `@relation` attribute above)
  createdAt   DateTime @default(now())
  @@id([postId, categoryId])
}

[some-user123]

I'm currently using Nestjs with Typeorm and in the model class I use

• transformer attribute of typeorm to map database values e.g. to DecimalJs classes and
• @Transform of class-transformer to transform the class to the actual output in the API and
• @ApiProperty from nestjs/swagger to document the response object.

Example:

@Column('decimal', {
    precision: 5,
    scale: 2,
    transformer: new DecimalJsTransformer(),
  })
@Transform((value: Decimal) => Number(value.toString()), { toPlainOnly: true })
@ApiProperty({ type: Number, example: 1.23 })
value: Decimal

How would I do this with Prisma where there is an autogenerated model class?

[marcjulian]

@some-user123 when you work with Prisma you are defining the database model in the schema.prisma and also create for each database model a typescript model for exposing in your REST/GraphQL API. You are not directly exposing the autogeneratede class from Prisma.

1. Create Prisma database model and have a look at the preview feature native types

model Product {
  value  Decimal    @db.Decimal(2,2)
}

2. Create Typescript entity for your API

export class ProductEntity {
  @ApiProperty({ type: Number, example: 1.23 })
  value: number;
}

Optional you can implement the generated type from Prisma on your entity like so

import { Product } from '@prisma/client';

export class ProductEntity implements Product {
  @ApiProperty({ type: Number, example: 1.23 })
  value: number;
}

I wrote about the duplication between Prisma Model and GraphQL API which also applies for REST.

[some-user123]

Thanks for your reply! this.prisma.product.findMany() would still return the autogenerated class, right? Then I manually need to trigger the transform to ProductEntity? Or is there a magical NestJS machenism to do so? (Not necessary for @ApiProperty of course, but for @Transform to be applied.)

[marcjulian]

Prisma returns your data as plain json objects. You have to return the correct values matching your Entity/Model of your response. Try it out with a simple response and you will see how it works.

[garyhe0780]

Nice article, one question thought: Is there an way to create two prisma client with nestjs-prisma