How to Use Async SQLAlchemy in FastAPI

hey everyone in today's video I'm going to show you how to use async SQL Alchemy in your fast API projects so to do this I'm going to convert this example that I've already written here which is using regular SQL Alchemy and I'm going to convert it over to async SQL Alchemy so you can see where things need to change and before I get into that just know that if you need help with anything like this so using async SQL Alchemy earfast API project or anything else with your fast API projects I do have a coaching program where I work with people one-on-one so you can learn more about that at pretty printed.com coaching or you can go to the link in the description below so to start what I want to do is I want to tell you what I have in this example so this is the regular SQL Alchemy and then I'll convert it so after the Imports what I'm doing is I'm creating the engine for SQL Alchemy so this will allow me to eventually create sessions so here I have the session maker using the engine and I'm connecting to a sqlite database which hasn't been created yet that will be created in just a moment I need to check same thread as false because of the way that fast API Works internally this is only necessary for sqlite databases so if you use like postgres you don't need this particular argument so I have this session maker function which will create this session local class which I can use to instantiate a DB object that will then interact with a database and you'll see that Below in just a second I have this Base Class here which is from declarative base this will allow me to convert regular classes into SQL Alchemy models which I have here for the class user so I'm creating a table and a model called user the table name will be users here and then I have an ID and a username column then I'm creating everything so this will create the database will actually just starting this file without this will create the database but this will create all the models in the database so just this one user model then I have this helper function called gitdb so it uses the session local class that I created from the session maker when I instantiate this I get a DB object back that I can use to interact with the database what I'm going to do is I'm going to yield it here so I can use it and then once I finish using it it will return back to this function and then it will close the database so I don't have any problems with my connections I have this user base for pedantic just to create new users in the database and then I have two endpoints so one endpoint is for creating a user so slash user posts it's going to take in the user base which has a username and then it's going to use the pins to automatically load in the DB object by calling gitdb every time this endpoint runs so with the DB object here I can then instant data user and then add it to the session using db.at so that user I can call commit and then I can refresh to put the extra information on the user object here which will be the ID in this case because it's generated when I save to the database and I'll return this back to the caller so we'll see that in just a second and then this one is similar in the sense that it has the pins here and I'm calling I get DB which will return my DB object I'm going to use that to query the database for all the users and then I'm just going to return them as a list and it will automatically be serialized to Json even though this will be a list of SQL Alchemy objects so now let me get this started I will use uvcorn and then the name of the file is app and then the name of the object is app and I'll just put reload on and I'll go to localhost 8000 on my browser here I slash Docs okay so we see Swagger here and I have the two endpoints so one for creating a user and one for getting users so if I call the git for get all the users I see I get nothing I get users is blank here let me just increase the size there so we see I get uh nothing for users but if I go to um this one the other user and try it out I can put in a username so I'll use my name Anthony hit execute and we see it returns username Anthony and id1 so that ID one came from the database then I can go to get users again I can execute and now I see my user Anthony is one of the users that returns so this is using regular SQL Alchemy and it's also using just regular functions these aren't async functions for the endpoints that I'm defining so now that I have all that so that is the synchronous example let's get into the async example so the first thing I want to do is I want to install something to make this work so I need a driver that can interact with a sqlite database in an asynchronous manner so for that I'm going to use something called AIO SQL lights there are other ones but this is the one I'm going to use so pip install AIO SQL lights and I'll just install that and now that I have that I want to change the uh URI for my database so instead of just SQL Lite I'm going to do plus and then AO sqlite just like that so this will tell SQL alchemy that I want to use this particular driver to interact with my database so now that I have that let me bring in the SQL Alchemy things that I need so first I need the stuff from the async io section of SQL Alchemy so from SQL Alchemy dot EXT Dot async IO I want to import a function called creates async engine and I also want to import a function called async session maker so what I can do is I can take this function create async engine and I can just replace the create engine that I have here so now this is create async engine and this engine object will now be for an async engine and for the session maker I want to replace that with the async session maker so I'll just put that there so now the session local will be for async sessions not regular sessions for the declarative base this can remain the same I am going to change this a little bit later but for now this can remain the same and also the types of columns can remain the same I will change this later to give you an example at the end of the video uh but for now these two are fine so this is the first thing that needs to change so in the sync example I could just put this based on metadata.create all here because it's just executing the code from top to bottom so when it gets to this line it runs it but when I'm using async I can't simply await something here because I need to be inside of an event Loop so I can't just put an await something here so what I'm going to do is I'm going to get rid of this how about I just completely get rid of it and I'm going to put the create all inside of the git DB so what I can do here is first I can change this get DB to async gitdb and what I want to do is I want to say async with and then engine dot begin as connection and then in this block I can then await connection run sync and then base dot metadata dot creates all so this will call this create all using run sync so because I'm using async single Alchemy I then need to run this synchronously for this particular thing creating all the things in the database but I still have to use the AC with block for this just because I'm using the um async engine here so in the git DB the session local part is still the same and for closing the database I want to await closing it because I can't close it just by calling db.close anymore okay so now the user base is going to remain the same this is just pedantic and now let's go down to the two endpoints that I have so the first thing I want to do is I want to change both of them to async so async Dev just like that and for creating a user the part that I have to wait is committing so here after creating a user everything here happens outside of the database but as soon as I go to commit something uh I have to await so awaydb dot commit and I also have to wait uh DB dot refresh and down here this is going to be a little bit different so I'm not going to touch this one for now I'll just create a new user and show you how it works so let me go ahead and start this oh and by the way the session here is just for type checking so it's not actually use so what I can do is I can go up to the top here and I can import async uh session and I can use that async session for type checking so I can replace the session here in order in the session there okay so let's start this and everything starts up properly so now let's go to the endpoint for uh creating a user so let me just refresh so everything reloads I'll create another user and the username will be pretty printed here so I'll execute and we see I get id2 and username pretty printed so this is using the async version so everything here works fine and just in case you don't believe me what I'll do is I'll take away the awaits and then I'll try running this again so I'll create a third user called python user hit execute and now I get an error and it says that um the error here is that the user isn't persistent in the session but more importantly the warning that it's going to give me if I go above it should be something like I'm trying to call it without awaiting yeah so async session dot commit was never awaited so I have to wait to actually save the user to the database so let me go ahead and await that again and go back here and create the user and now we see ID3 and python user has been added to the database and there are no errors so for this one I need to change the way this query works because in SQL Alchemy 2.0 which supports async SQL Alchemy it also changes the way that queries are run and it also changes the way that the standard way of defining classes are done so what I'm going to do is I'm going to make those modifications now so I can run this query down at the bottom so the first thing is the base here so instead of calling a base just like this what I can do is I can import a class directly so declarative base declare relative base just like that and I'm going to inherit from this declarative base or actually not there let's just say here so I'll call this class base and I inherit it from declarative bass and I'll just pass right so this part works the same but I no longer have to call this function declarative base I can just remove it here the next thing I want to do is I want to change the way that I Define The Columns so before you would import like column and integer and so on to Define columns but now what you do is you use type checking so I want to define a column called ID and I need to give it a type so it's going to be mapped and I need to import this mapped from SQL Alchemy RM so map just like that so it's mapped and then the type inside of map is the type that I want the column to be so in this case I want it to be an ins and then this will be equal to any additional information that I want to pass so for this I need a function called Maps column and I'll just put this here and inside of map column then I can say that the primary key is true and that's it for this one so it just has the primary key and then I can get rid of the original ID so now let me add a username so username is similar so username and then once again I need mapped and then the type so this will be a stirves because it's a string and then I'll use map column again to say that unique is true if I didn't have anything other than just the string here then I wouldn't need the map column but because I want to say that this should be unique I can use the map column function here to say unique is true so I'll remove that and now at the bottom uh what I want to do is I want to change this query so instead of running the query like this to get the users the first thing I need to do is I need to tell the database I want to execute a query so this is going to be kind of similar to if you were using like the driver directly where you first execute on a cursor and then you get the results from the cursor so what I want to do is I'll say like results equals and then I need to await db.execute and inside of here I want to use a function called select and I'll pass in the user class so I need to import this select from SQL Alchemy so just up here select lower case and now that I have the results I want to convert them to a format that I can use so to get them in the same format as users before I can do results Dot scalars dot all so this converts the results to something that doesn't have the extra SQL Alchemy metadata and it gives me all the results so it's like a two-step process so this will just replace that so what I'll do is I'll recreate the database so we can see everything working so I'll reload the app and now I'll refresh this page we see I don't have a database yet because the only time this gets run is when I call get DB and the only time we get DB gets called is when I use one of the endpoints so what I'll do is I'll query for all the users so try it out execute it returns none and we see the database is here now and now let me go ahead and add a user so down here add a user username is going to be Anthony and I'll execute here we see it created username Anthony with id1 now if I go back here it executes I see Anthony is in the list of users and let me go ahead and create one more user uh pretty printed hit execute it was created properly I'll hit execute up here and now we see Anthony and pretty printed users ID one and two so that's all that you need to do to get async SQL Alchemy working in your fast API project so of course you need async functions and of course you need to await the things that need to be awaited for SQL Alchemy and you'll need to convert to using the new style of SQL Alchemy and I personally don't like this new approach to writing queries in SQL Alchemy even though I understand how it's closer to the model of interacting with databases directly in terms of writing queries but I just felt like the old approach was so much more convenient you know it actually made writing queries a little bit easier in a lot of cases and then you always had the fallback method of writing more verbose queries if you wanted to get something really complicated done but for simple cases I really like the old uh style of writing queries so let me know what you think of the the new style of SQL Alchemy and let me know if you have any questions about using async SQL Alchemy and fast API so that's it for this video If you like this video please give me a thumbs up and if you have subscribed to my channel already please subscribe so thank you for watching and I will talk to you next time

Channel: Pretty Printed

Views: 1,169

Keywords: fastapi, sqlalchemy, async

Id: cH0immwfykI

Length: 15min 20sec (920 seconds)

Published: Sat May 27 2023