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
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
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
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.
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
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).
i see, my setup is also a bit rare, i just wanted to use vite and express lol
Can you attach an example of the query you are executing?
I'm getting an type error called WhereUniqueInput all the time, I can make it work with any
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.i had this app working with mongoose, zod, trpc, etc and switched to prisma+postgres
sorry, here's the code it's a simple array replacing code
Is the
email column set up as a unique column in your Prisma schema file?yep
I'm assuming input.prods is part of the autogenerated zod schema that you mentioned before?
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
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.I have the exact auto generated zod type it is asking for
then i get this in react, it is asking for the relations, I think i did this yesterday
sorry that's just arrays of strings, not relations
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.
nope they are array of strings, not relations
why is it asking for the arrays?
I'm guessing because it looks like those properties are required, according to your Prisma schema.
but arrays can be empty in prisma
yes, but you'd still need to provide those when executing the query
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...
You may be able to pass those arguments along like so:
I'm not sure how to apply that
I get a type error, let me see
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.not possible
this works but i get the same error
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
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.
mongoose worked great
hope so, hope it is a mistake made by my side
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.
true, the zod generator feels kinda weak, it's a mix of 2 libraries, at least that what the author says
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.
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
So you wrote out a similar query by hand, without ANY zod stuff and still got the same error?
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
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.
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
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).
I'm using the supabase DB which is set to east US same as the server
mongo is in east US too
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.
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
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!
yeah i got more ideas!
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.
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 