Theo's Typesafe Cult•2y ago

Question about Prisma Joins and Performance

I'm starting to stack multiple "joins" with prisma but am getting concerned with the approach. I read that joins in general may not be great for performance so that this could be a plus, but maybe I misread or am making that up. What is the consensus on Prisma's way of handling joins or the idea that raw query is not type safe? Is anyone regularly stacking joins with Prisma and have any pro tips? Example:

  getSomething: publicProcedure
    .input(inputSchema)
    .query(async ({ input, ctx }): Promise<SomethingInterface[]> => {
      const something = await ctx.prisma.somethings.findMany({
        where: {
          id: input.id,
        },
        select: {
          value: true,
  
          TableB1: {
            select: {
              value: true,
  
              TableB2: {
                select: {
                  value: true,
                },
              },
            },
          },
          TableC: {
            select: {
              value: true,
            },
          },
          TableD1: {
            select: {
              value: true,
              TableD2: {
                select: {
                  value: true,
                },
              },
            },
          },
        },
        take: 1000,
      });

  getSomething: publicProcedure
    .input(inputSchema)
    .query(async ({ input, ctx }): Promise<SomethingInterface[]> => {
      const something = await ctx.prisma.somethings.findMany({
        where: {
          id: input.id,
        },
        select: {
          value: true,
  
          TableB1: {
            select: {
              value: true,
  
              TableB2: {
                select: {
                  value: true,
                },
              },
            },
          },
          TableC: {
            select: {
              value: true,
            },
          },
          TableD1: {
            select: {
              value: true,
              TableD2: {
                select: {
                  value: true,
                },
              },
            },
          },
        },
        take: 1000,
      });

8 Replies

andersgee•2y ago

the main thing is to make sure indexing is actually used if you use relationMode = "prisma" in your schema then you should put @@index([someId]) in some places, recent version of prisma extension for vscode warns for this other than that not sure what to say other than your code looks fine to me

sajOP•2y ago

Yup I’m already doing index on all relations. I just want to make sure I’m not shooting myself in the foot by using Prisma since I’m expecting even more complex joins down the road. In fact, I already ran into a wall with my example join and had to split up the select into another table inefficiently. This all stems from some Twitter convo I saw about how Prisma joins are not true joins, and how other ORMs handle this subject matter better. I thought maybe the community here could shed some light on this subject Also I was trying to do something like: TableA … TableB … … TableC … … … _count … … … … select TableD where TableC ColA But you couldn’t do a nested count with a select thinkies

andersgee•2y ago

kysely instead of prisma is an alternative that a few of us are using. its essentially a typesafe "raw sql" builder meaning any potential limitations would be in sql rather than the client aka prisma The main reason for using kysely though is just performance in general due to size of library, especially in serverless environments edit: saying kysely dont have limitations might not be the best choice of words, its not quite that nice of a syntax and sometimes you have to use escape hatches for special cases etc but anyway, its good stuff

Igal•2y ago

its not quite that nice of a syntax

got any examples/suggestions?

sometimes you have to use escape hatches for special cases

got any examples? if it doesn't break "WYSIWYG" and can be implemented in a type-safe way.. it has a good chance of getting into core.

andersgee•2y ago

I mean I basically just paraphrased their docs, not sure what youre asking for examples of? and the syntax I guess is a matter of taste https://kysely-org.github.io/kysely/

kysely

Documentation for kysely

Igal•2y ago

Seemed to me you've experienced some pain while using it, so was asking for feedback/suggestions.

andersgee•2y ago

oh, yeah no I actually love it xD was hedging myself a bit to not say its the greatest thing since sliced bread

JulieCezar•2y ago

Maybe a little late to the party... But I just watched this video from CodeDamn https://youtu.be/J2j1XwZRi30 Who said that Prisma doesn't actually do JOINs in the SQL waay, but rather gets all data from tables and then compares that... I don't know if that's true but if yes it could be a deal breaker...

codedamn

YouTube

We need to talk about Prisma

We migrated to SQL a month back and our biggest learning was to never use Prisma. Watch the full video to know what happened and how we found a solution. Read the full blog here: https://codedamn.com/news/product/dont-use-prisma Are you confused where to start coding/what to learn/what roadmap to take? Take this free 2 minute quiz: https://c...

Gaming

Programming

Question about Prisma Joins and Performance