Theo's Typesafe Cult•5mo ago

Extremely slow queries

I'm having elapsed time of 3 to 7 seconds and I just switched from mongo to postgres. Is it the DB location? I'm hosting this app on render and using their postgres DB also takes really long. Supabase takes about the same time, any clue??

53 Replies

SlaviiK•5mo ago

I think there were some general time issues for newly created projects But I am not sure exactly A lot of people get this type of delay

K1|ller•5mo ago

where did u get this info? I experienced the same before the last update i'll try downgrading no luck, the performance of prisma is BAD and i'm just returning 1 product I also think this ORM isn't ready for end to end type safety, I get a lot of errors with types in trpc inputs where I'm clearly passing the right type provided by zod that i generated from prisma

Endgame1013•5mo ago

Prima is a pretty tried-and-true ORM. It's not perfect, but most the type errors can be easily resolved by just restarting the TS server in VSCode.

K1|ller•5mo ago

I'm at day 10 trying to make Prisma work I'm probably facing bugs, i'm using express, trpc, auto generated zod schemas and prisma to be honest i'm more worried about the performance, it is taking around 3-7 seconds to return 1 product I can make it work with some any types

Endgame1013•5mo ago

Yeah it may be the auto generated zod schemas tbh. I've not personally used that before and I use Prisma in multiple projects (with MySQL and PostgreSQL) and I don't have any issues (aside from some un-optimized SQL queries, but that's a separate topic).

K1|ller•5mo ago

i see, my setup is also a bit rare, i just wanted to use vite and express lol

Endgame1013•5mo ago

Can you attach an example of the query you are executing?

K1|ller•5mo ago

I'm getting an type error called WhereUniqueInput all the time, I can make it work with any

K1|ller•5mo ago

Endgame1013•5mo ago

This is just speculation since I don't know the query you are executing, but it sounds like you are trying to run a .findUnique() operation on fields that are not set up as @unique in your Prisma schema file.

K1|ller•5mo ago

i had this app working with mongoose, zod, trpc, etc and switched to prisma+postgres

K1|ller•5mo ago

sorry, here's the code it's a simple array replacing code

Endgame1013•5mo ago

Is the email column set up as a unique column in your Prisma schema file?

K1|ller•5mo ago

yep

Endgame1013•5mo ago

I'm assuming input.prods is part of the autogenerated zod schema that you mentioned before?

K1|ller•5mo ago

nope it's the array of products input.prods is Product[] so cart is just Product[] including the relations, for example to a coupon code I apreciate that you take the time, i felt like in the middle of a desert for many days haha

Endgame1013•5mo ago

It sounds like cart is expecting you to pass along some unique values perhaps? I first try and duplicate that query and set the set property to an array of objects that satisfies the type constraints. Then, figure out what is different between that duplicated query and the data in input.prods. For example, the set property may be expecting you to provide an id property of some kind and that id is not included in input.prods. At least, that's what I would try first if I were you.

K1|ller•5mo ago

I have the exact auto generated zod type it is asking for

K1|ller•5mo ago

then i get this in react, it is asking for the relations, I think i did this yesterday

K1|ller•5mo ago

sorry that's just arrays of strings, not relations

Endgame1013•5mo ago

Yeah, I think you're right about it asking for relations. Like I said, I've never used autogenerated zod schema like that, so I'm not sure how accurate those schemas are, unfortunately.

K1|ller•5mo ago

nope they are array of strings, not relations

K1|ller•5mo ago

why is it asking for the arrays?

Endgame1013•5mo ago

I'm guessing because it looks like those properties are required, according to your Prisma schema.

K1|ller•5mo ago

but arrays can be empty in prisma

Endgame1013•5mo ago

yes, but you'd still need to provide those when executing the query

Endgame1013•5mo ago

https://stackoverflow.com/questions/70682378/how-to-create-a-optional-list-field-in-prisma

Stack Overflow

how to create a optional list field in prisma

I am creating a basic crud api with nodejs and prisma. My Schema is as follows: generator client { provider = "prisma-client-js" } datasource db { provider = "postgresql...

Endgame1013•5mo ago

You may be able to pass those arguments along like so:

await prisma.user.update({
  where: { email: user.email },
  data: {
    cart: {
      set: {
        ...input.prods,
        included: [],
        required_products: [],
        product_images: [],
        tags: []
      }
    }
  }
});

await prisma.user.update({
  where: { email: user.email },
  data: {
    cart: {
      set: {
        ...input.prods,
        included: [],
        required_products: [],
        product_images: [],
        tags: []
      }
    }
  }
});

K1|ller•5mo ago

I'm not sure how to apply that I get a type error, let me see

Endgame1013•5mo ago

Again, let me stress the point that I would first try and recreate this query manually (so not using any of the zod schema stuff) and see what properties have to be set. Then, once you've made Prisma/typescript happy, compare the query you wrote vs the query that includes input.prods and see how the two differ. I'm willing to bet input.prods is missing some property that Prisma is expecting for this particular query. That's sort of the risk you take when using codegen tools like that, that is, in my 9+ years of experience.

K1|ller•5mo ago

not possible

K1|ller•5mo ago

this works but i get the same error

K1|ller•5mo ago

yeah but with the ProductWhereUniqueInputSchema type i don't get any complain, this is supposed to be the exact type it wants, it has always scream for it but i get this weird behavior i felt kinda alone commenting topics from 2021, this is why i think Prisma isn't ready for end to end type safety

Endgame1013•5mo ago

Prisma is definitely ready for end-to-end safety. I've been using it for years. I think your problem is the codegen tool you are using to produce the zod schemas.

K1|ller•5mo ago

mongoose worked great hope so, hope it is a mistake made by my side

Endgame1013•5mo ago

Most any experienced dev will tell you to be weary of codegen tools. Most of the time they can save you a lot of time, but unless you really understand what is happening, you're inevitably going to run into an instance like this when you can't figure out what is going on or why something doesn't work.

K1|ller•5mo ago

true, the zod generator feels kinda weak, it's a mix of 2 libraries, at least that what the author says

Endgame1013•5mo ago

Again, just try and write out a similar query by hand WITHOUT using the zod schema stuff. Just test it out, see what Prisma is expecting as input, then go back and compare what you wrote to that zod schema.

K1|ller•5mo ago

https://www.npmjs.com/package/zod-prisma-types ah yeah i did that, i created zod and typescript types, i got the same errors so i guess i wrote the same types

Endgame1013•5mo ago

So you wrote out a similar query by hand, without ANY zod stuff and still got the same error?

K1|ller•5mo ago

yep i ended up writing the same types 1 by 1 i built the same zod and prisma types probably a bug or, maybe because my stack is rare, i was thinking about that

Endgame1013•5mo ago

Possibly. I'd think either your Prisma schema is set up wrong, you don't have up-to-date @prisma/client types, or you're writing a bad query. If you're just using Postgres, express, and tRPC, then that's a pretty common stack, I would think.

K1|ller•5mo ago

here's the working app with mongoose, you can see the trpc queries in the console www.ez-tone.com/products it is fast, prisma is also very slow

Endgame1013•5mo ago

The prisma slowness is probably due to the server itself (either regional placement or resources itself). Although Prisma is known for not producing optimized SQL queries, I still don't think they'd have that much latency (3-7 seconds, like you mentioned).

K1|ller•5mo ago

I'm using the supabase DB which is set to east US same as the server mongo is in east US too

Endgame1013•5mo ago

I can't really think of much else to suggest, other than just trying to write that query out manually without any zod schemas (or any tRPC inputs for that matter). I'm talking about just plain Javascript objects/arrays.

K1|ller•5mo ago

i'll delete the node modules folder thanks for your help, i'll try for a coupe of days and let you know how i solved the issue, might be helpful probably some dumb mistake

Endgame1013•5mo ago

You can try that, too. Hopefully someone else may have some ideas, as well. Without seeing your whole setup, it's really hard to tell specifics, unfortunately. I hope I at least got you thinking of some ideas things to try!

K1|ller•5mo ago

yeah i got more ideas!

Endgame1013•5mo ago

Yes, please give us an update, especially once you solve this. I'm sure at least one other person in this server has or will have a similar issue.

K1|ller•5mo ago

the thing is that i need autoincrement and i'm dumb, couldn't do it with mongo lol this for the order id yep i'll let you know how i solved it (in the case i solve it) i think i have it, worked i didn't solve the speed of the queries but i solved the main big issue where i couldn't update the user's cart I had: model User { cart Product[] ... I was doing: cart:{ set:{ newArrayOfProds}} I wasn't connecting the new prods to the user, I was just setting the cart field to a new array so the correct code for adding a prod to the cart is: cart: { connect: input.prods.map(prod => ({ productId: prod.productId })) }, map all prods from input.prods and return all product id's i want to connect to the user and for deleting a prod from the cart: cart: { disconnect: { productId: isProductInCart.productId } }, in my store you can only buy 1 qty of each prod that's it so it took me 10 days to switch the db from mongo to postgres deranged

Gaming

Programming

Extremely slow queries