Efficient Database Updates with Prisma's Nested Queries
00:00 Let's combine these queries. So right here, we've got our note update. Let's do the delete many first. So we're going to add images. And here we'll have a delete many. And that's simply going to actually be very similar to this. So we just provide the where clause.
00:15 And it will be scoped to our particular note anyway. So we don't need to have the note ID here anymore. All we need is this part of the where clause. And look, that looks pretty darn familiar. Thanks, Copilot. And so we've taken care of the delete many. That's nice. So that's part of this one query now.
00:33 And then for our update, we're going to do an update many. And we can actually map across all of the image updates that we have. So let's say image updates. We'll map across all of those.
00:46 And we can add a where for selecting the image by its ID. And then we can also add our data. So we're updating the alt text. But we also need to think about the ID case for this. So actually, you know what?
01:06 That's going to be pretty similar to this thing as well. And in fact, it'll be exactly like this if I call this updates and updates. Ta-da. Same thing. Cool, cool. And then for our new images right here, we'll have a create, not a create many.
01:26 It works with the many things we want to create just fine. And for this, this is the new images. And actually, those are fine as they are. So it's just an array of the data that we want to have created because you don't need a where clause or anything like that. And we can delete this. Dun, dun, dun, dun.
01:42 And we can now allow Prisma to optimize this query for us. It can all be in a single transaction and everything. That's all handled. Oh, so actually, why don't we test this out? Let me show you how to do that. So here, we've got the app running. We've got our queries that are showing up in here. So let's take a look at the queries
02:00 that Prisma generates for this single update call. So I'm going to update this title. I'm going to change this to soccer. And I'm going to delete this one. And we're going to create another one. And that, cuddling. That should work.
02:20 We come over here and take a look at the query. Here's our POST request that we just made. And the queries actually will come before that log. So that POST request doesn't get logged until after the queries are all finished. So we've got our update right here. And then it looks like we've got a select and then a delete,
02:39 another select, an update, an insert, and another select, and then a commit. So if you wanted to fine tune your queries to be exactly what you wanted them to be, then yeah, you might just use like raw SQL in that case. But for me, I'm actually totally cool
02:56 just letting Prisma manage all the queries that we're making and writing my query in a actually pretty awesomely simple way like this. And additionally, if you're a little concerned about this, you're like, whoa, that's a lot more queries than I expected. That's fine if you're worried about that.
03:17 But keep in mind that we're running SQLite and that's running like literally on the same disk as the app itself. And so you can actually execute many, many, many, many queries in that sort of environment and not have an issue at all. But that's how you would check to see, okay, what queries is Prisma generating for this
03:37 is with the logs that we have established there. That is one way that you can do that. So in review, what we did here was we took the bunch of different queries that we made and we combined them into one with this note update. And this is all because these are relational updates.
03:55 If we were making like different updates, like user A transactions over to user B or whatever, those aren't really related. And so, yeah, you'd probably have those in separate transaction or queries and stick it inside of a transaction. But because all of these updates are happening with related records,
04:13 we can just do these nested queries and it's fabulous. It looks awesome to me. So well done, you did a good job. This was, yeah, a little bit heavier than some of the other exercises. So good job, let's move on.