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

/**
 *  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

@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

@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

[Flashantik]

Hello there,I'm just starting to learn prisma and nest.js.. And i have question, as i see in second options y are using ResolveField with this.prisma.post.findOne..

And suppose we have a query

Query {
  user(id: 1) {
    chats {
       ...
     }
  }
}

Looking at your code we will have the same query to the database twice
First is @query

this.prisma.user.findOne({
      where: { id },
    })
    SQL
    SELECT `main`.`User`.`id`, `main`.`User`.`email` FROM `main`.`User` WHERE (`main`.`User`.`id` = ? AND 1=1) LIMIT ? OFFSET ? [1,1,0]

The second is @ResolveField

this.prisma.user.findOne({ where: { id: post.id } }).chats();

SQL
SELECT `main`.`User`.`id` FROM `main`.`User` WHERE (`main`.`User`.`id` = ? AND 1=1) LIMIT ? OFFSET ? [1,1,0]
... Chats queries 

Which is not optimal at all.

It seems that the first option can be considered correct with include.
But what about the situation if we did not ask for chats ?

I would be happy to hear from you.

[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

[killjoy2013]

Very useful, thanks. Is there any sample project which has different data shape & api shape using Nestjs, prisma, GraphQL in SDL first approach?