Full Web Apps with FastAPI Transcripts
Chapter: async databases with SQLAlchemy
Lecture: The remaining database queries

Login or purchase this course to watch this video and the rest of the course contents.
0:00 Over here in the package service,
0:01 those were the final queries that we needed to convert to async.
0:05 And because it's exactly the same as what we've been doing,
0:08 I decided to go ahead and do the conversion,
0:10 and we'll just talk through it real quick.
0:12 So for the release_count, just like we saw before,
0:14 we're gonna do a select with a function count of Release.id and execute it, that's
0:19 pretty straightforward, at least given what we saw for users.
0:21 package_count, exactly the same. But Package instead of Release. This latest_packages,
0:26 remember, this was our most complicated query that we had in our system.
0:30 But from here to here, it was exactly the same as the old query.
0:35 We just have to, instead of doing session.query,
0:38 we just say "select" and then you can't do the all() anymore,
0:41 what you do is just scalars() like that.
0:43 Then we did this little set trick just like before to make sure we get only
0:47 unique ones in case a package has had a release within a couple of times of
0:52 some of the other ones, right?
0:53 We had a little duplication, and that gets rid of it.
0:55 get_package_by_id again super similar.
0:57 You get a select, here your filter just like before, await executing it.
1:01 And this first() is now scalar_one_or_none.
1:05 Also converting the latest release for a package.
1:07 Very, very straightforward. This was basically the same thing.
1:11 Execute it, these are scalar_one_or_none, good to go.
1:14 So that's it. Those are all the queries.
1:16 But what I wanted to do together with you,
1:18 it's just integrate these queries back into the rest of the application.
1:22 So let's go find where this is being used and update it.
1:25 It should be just that IndexViewModel right there and notice PyCharm is saying all
1:29 of these, all of these are a problem. None of them are returning integers or lists, what
1:35 in fact they're returning is coroutines.
1:38 We gotta execute the coroutine and get its value out by awaiting it.
1:43 And then this one, we now can say more concretely,
1:46 this is a list of Package. That will give us a tiny bit more autocomplete.
1:54 So release count, package count,
1:57 we already just took care of. latest_packages,
2:00 I believe this is also just used in that one location.
2:03 Yes, it is and we're doing things, I'll get there.
2:09 get_package_by_id, where is that being used?
2:11 You'll find it, there you go. It's over in this DetailsViewModel.
2:16 Now, this is one of those that's going to take some work.
2:19 We now have to await this,
2:21 and this one is also async,
2:24 so we're gonna have to await that.
2:26 That means we will need to do some sort of work.
2:29 Let's reorder this a little bit, like this.
2:33 We need to do some sort of work in order to allow us to await this.
2:37 Like I said, constructors,
2:40 the dunder init, cannot be asynchronous. That was already down there,
2:44 so I guess that was just duplicate.
2:46 So this part right here needs to run somewhere else.
2:49 However, these things need to be defined as optional things of whatever they are, so
2:57 this means Optional[Package], so we can write this code here,
3:03 define it correctly, and then load it up. Come over here,
3:06 same type of thing but for Release.
3:12 Package release, the latest version, that's already set up there.
3:15 Okay, good. So now all we gotta do is make an asynchronous function that
3:22 we can call wherever we were using this before.
3:26 Remember, it's called DetailsViewModel,
3:29 and it's in packages. So that would mean it's in the package view detail function.
3:33 There's only one, apparently,
3:35 so that kind of solves that right?
3:37 But the last thing to do is make this async and await vm.load() we
3:44 should be good to go with that.
3:47 We may have just done it.
3:48 That might have been the last function.
3:49 Let's see, where is this one being used?
3:54 Yeah, it's already being awaited.
3:56 That's cool. Oh, however,
3:59 I just now noticed, you may have have already noticed that, we need a "self" over here to
4:03 pass it along, perfect.
4:07 All right, let's go click around the site and see if it works.
4:10 Make sure there's no "you forgot to await something"
4:12 warning. So here we have number
4:15 of projects, releases, users. Those all look real. Notice
4:18 I added two more by adding those Sarah Jones3 and 4 in, which is pretty
4:22 cool. We have the awscli.
4:25 The part that we just worked on is, what happens if I click this? Mmm,
4:30 not something good. What has happened here?
4:32 Multiple rows were returned, right here.
4:35 Oh, yeah, we just want to say scalar
4:38 I believe. It's, scalar_one_or_none is if I'm trying to get some kind
4:42 of like user or something were it's supposed to be unique.
4:44 Let's see about that. There we go. Because,
4:46 of course, there's multiple releases.
4:49 We just want one, which is the latest.
4:51 Alright, perfect. This is working right here.
4:54 Looks like everything's good. We could check out the home page, that awscli
4:58 sure looks like it, so that, that's working.
5:02 Let's click on one other, gevent,
5:05 perfect. There's details about the gevent,
5:07 the library, and this is all working.
5:08 So homepage, package is good. Let's just one more time.
5:12 Log in, log out. We log in,
5:15 log out and finally do one more register.
5:17 I'm sure this will work because I've done it so many times,
5:21 but we'll do a test with the wrong one
5:23 there, already exists. That's right,
5:30 we wanted Sarah Jones5, and Sarah Jones5 is now registered.
5:36 That's it. Let's double check, really quick here,
5:39 make sure there's no "you forgot to await something".
5:41 No, no warnings either.
5:44 Looks like we got everything converted over to this new
5:48 SQLAlchemy style of programming. Now I want you to just think back to that diagram
5:52 where we saw the animations where we saw all the interactions, so over here. Here,
5:58 where we saw those interactions when our request would come in to,
6:00 like, slash project slash whatever.
6:03 What are we doing here? We're going.
6:05 And we're saying I want to start a little bit of work.
6:07 So this whole section here is gonna run synchronously,
6:11 which builds up the variables to work with.
6:15 And then we're gonna await this load jumping back over here.
6:19 This is where we're going to the database to do a query and say: you know,
6:22 anything that's happening right now, if another request comes in while we're waiting for the
6:27 database, cool, just go
6:28 let that request do its thing,
6:30 maybe start off another database call, when this database gets back to us,
6:34 just put the answer right there.
6:35 Oh, now we're gonna start another database call,
6:37 and if anything happens during that, it's cool,
6:40 just go process it. When this one's finished,
6:42 drop the answer right there. And then a little tiny bit of work,
6:46 like checking for None on two items and a string format.
6:49 Most everything happening in this whole series right here is about waiting on the database, in
6:54 those two database calls. Were doing that asynchronously, that means we're not blocking up the
6:59 server, were not consuming hardly any resources while that's happening, on the web server.
7:03 Obviously the database is working its heart out, trying to give us the answers,
7:07 right? using its indexes and doing its thing.
7:09 But as long as the database can handle the traffic,
7:11 this particular part of our application is not gonna be the bottleneck,
7:15 and that is super, super cool.
7:18 It's possible because async and await, we were able to use async and await because
7:24 we're using the latest SQLAlchemy,
7:26 the beta version that supports actually asynchronously talking to the database.
7:31 Super cool, hopefully you'll appreciate this a ton.
7:34 I think it's really, really neat,
7:35 and it unlocks some amazing potential for your web app.