Question about designing friend request / follow system with relational tables

I'm using Prisma on top of PostgreSQL. In my app, I have a Follow table, denoting follow relationships, and a FollowRequest table, denoting pending follow requests. I've enforced that in both the Follow and FollowRequest table, the permutation of two users is unique. So we can't have multiple duplicate FollowRequests. I want to write a function for my expres s server that can reject a follow request. I have the user context already. I see two ways of writing it: 1) Get the followRequestId from the request. Lookup the FollowRequest row in Prisma. If the user context matches with the FollowRequest, delete the row. This is two sequential database interactions. 2) Get the sourceUserId and the targetUserId in the request (basically the person who made the follow request, and the person who was requested). I can then check if the user context matches the targetUserId, but this time I don't need a database read to do so. I can then perform a Prisma delete with the unique combination (sourceUserId, targetUserId). This is just one database interaction. The second one feels a bit odd, because I'm not working with the unique followRequestId which is part of my FollowRequest table. I'm leveraging the fact that (sourceUserId, targetUserId) is unique. Are there recommendations for how to approach this? I'm asking here because I'm not sure if I'm missing considerations on efficiency / how Prisma works.
2 Replies
moosthuizen
moosthuizen2mo ago
For (1), instead of doing a read first and then a delete, could you not specify the same where: clause in the delete as you would for the find? You can then check the result to see if anything got deleted.
yeetcode.io
yeetcode.io2mo ago
hey, yes I think so! thanks, I am still learning SQL