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