FastAPI & SQLModel - Database Interaction in FastAPI apps with SQLModel

in this video we're going to take our fast API application and we're going to add a relational database behind the application to store and retrieve data from and in this video we're going to see how to use the SQL model package and we're going to use that for the models and for the data layer of the application so we're going to remove the dummy data from previous videos and we're going to add this relational database in a fast API application and in the next video we'll also add the almic package to manage our database migrations so that's coming up if you're enjoying this content give the video a thumbs up and subscribe to the channel if you've not already done so and let's dive in so we're going to start by creating a database in this project and we're going to use this package called SQL model in order to interact with the database from our fast API application now if you're wondering what SQL model is it's based on python type annotations and it's powered by pantic and SQL Alchemy and SQL model is designed to be intuitive easy to use highly compatible and robust now if we look at what we had in our application so far we were hardcoding these bands and there were some previous videos and a playlist on this topic you can check that out if you've not already done so or you can just follow along with this video if you want to learn how to integrate fast API with SQL model and a relational database now in the previous videos we had a schemas dopy fill as you can see here and the purpose of these classes was to model the structure of the data that we were working with in our fast API application what we're going to do in this video is convert these models from pantic Models to SQL model classes now we've done a previous video on SQL model if you want to check that out but basically a SQL model class is a subass of the pantic base model and so all of the functionality that we get with pantic models is available on the SQL model classes as well so let's start by renaming this file from schemas dopy to models.py and what we're going to do at the top of this file is we're going to import the SQL model class and we're going to create sub classes of that instead of the base model and by doing that we'll get that database functionality later in the video before we do any of that of course we need to install SQL model I have a python virtual environment activated and we're going to run the PIP install SQL model command and that's going to install that package into this environment once we've installed that we can go to the top of the file here and from SQL model we're going to import two things first of all the SQL model Base Class and we're also going to import the field class as well now once we've imported these we're going to go down to the actual model classes and because we're going to work with a database we're going to change things up a little bit within these classes so first of all I'm going to change the name of the album class to album base and instead of pantic base model it's going to inherit from the SQL model Base Class so for an album these are the base attributes and that's the title and the release date of the album if we go back to main.py here's the schema that we've been working with in this series The albums are represented here in a list and you can see they have a title and a release date now we're adding a database and that's going to add an ID primary key so that's why we're changing that to an album base and then we're going to create a class called album and that's going to inherit from from that album Base Class and we're going to add a new field for the ID here so that's going to be of type integer and that's going to be equal to a field and that's what we imported from SQL model and we're going to give it a default of none and we're going to say primary key equals true here and that's going to make that the primary key for the album table now because this is going to be a database table in SQL model we can add a keyword argument to this class and that's table equals true and basically any SQL model subass that has table equals true is going to be represented as a database table so just to re explain what's Happening Here we have a base class containing a title and a release dat and that inherits from the cql model class and then we Define a subass of that called album and that will inherit from our Base Class and it adds that table equals true which tells SQL model this should be a database table and it contains the ID field as well as the inherited fields from The Base Class above now let's go down and do the same with the band models so we have a base class here and that's going to inherit from SQL model and one thing we're going to do is move the albums from The Base Class down to the band create class so let's do that now we're going to paste this in and change it in a second the band create class should inherit from band base and this is the schema of a post request that's sent to fast API in order to create a new band with optionally some albums in the database so let's change the typeint for the albums that are going to be sent to the back end this is either going to be a list of the album Base Class so if we scroll up here we have album base and that contains a title and the release date for the album and note that it doesn't contain an ID because that's set by the database on creation so when we create a band we can optionally pass a list of these album base instances and because that's optional we will use the union type here and we can see that that will be optionally none so we don't need to send albums when we create a new band and we're going to set the default value to none for this particular field and the last thing is the band with ID class we're going to create a table in the database called band so I'm going to change the name of that to band and we're going to pass table equals true as a keyword argument so that's going to inherit all of the fields in the band base and it's going to add the ID to that and again we're going to set that to a SQL model field and we're going to set the default to none and primary key to true now one other thing we need to do is set up relationships between these models so I'm going to go to the top here and from SQL model we're going to import a relationship object now the reason for this is because we're now using database schemas and we have a relationship between a band and an album a band has albums and an album belongs to a particular band so an album has a foreign key to the band table and we need to represent that in the SQL model schema so in the Base Class here what we're going to do is add a band ID field and that's going to be of type integer so when we have an album we're going to link that to a band via the integer foreign key and because this is a foreign key we're going to use the field object in SQL model and we're going to and that's going to avoid those errors and what that means is that if we have an album we can access the do band property to go from that album to the related band instance and we can do this in Reverse as well so if we go down to the bottom here we have the band if we wanted to fetch all of the albums that are associated with that band what we can do here is set up a property called albums and that's going to be a list of these album instances and we set that to a relationship and again we're going to pass back populates into that and we're going to reference the band on the album so this band property here it references what we have on the album model the property with the relationship is called band and that's what we are back populating here and the same in Reverse we're back populating albums and that's the name of this property on the band model so we've restructured the schema. py file into this models.py file that contains database models what we're going to do now before we actually create the database is go back to main. Pi and at the top we need to fix some of these Imports so we changed schemas to models and we changed band with ID to just band because it's now the name of a database table and we're also going to import the album model as well now for now what we're going to do is comment all of these end points that we created before out and we're going to fix them up one by one later in the video Let's now go to the left hand side and we're going to create a database. pi file and this file is going to Define and store our database connection information now at the top I'm going to bring a couple of imports from SQL model we're going to to import the create engine function and we're also going to import the SQL model instance and the session object as well now let's define a database URL and we're going to set this to a SQL light or SQL light URL and I'm not sure exactly how you pronounce this someone said one thing someone said another you can leave a comment if you know how that's supposed to be pronounced but what we're going to do is Define a file here called database.sql light and sqlite is a file based database and that reduces the complexity a bit because we don't need to install any external systems or processes like post SQL we can just create a file on the file system and that's going to be our database for this project once we've got the URL we're going to create the engine using that create engine function and we're going to pass the database URL into that and I'm going to pass eal equals true and finally at the bottom of this file let's define a function called init DB and we're going to call a function on the SQL model. metadata object and that's the create all function and we pass the engine into that now what this SQL model. metadata is going to do is it's going to look at all of the models that have that table equals true parameter and these are the models that should represent database tables and it's going to create those tables in the database using this create all function now we can test this init DB function in main.py and to do that we're going to create what's called a lifespan event in fast API so what's a LIF span event let's go to the documentation if you need to Define logic that should be executed before the application starts up or Define logic that should be executed when the app's shutting down you could use these lifespan events for both of these use cases so let's go down and we're going to look at the code here and what we need to import is something called the async context manager from Python's standard libraries context lib module so let's import that at the top and if we go back to the documentation we're going to define a function called lifespan and that's going to take the fast API application as a parameter and it's going to be decorated with that async context manager so let's copy these two lines of code and go back to the application I'm going to paste them in here and what we're going to do is import this init DB function that we defined in the DB module and we're going to call that in this lifespan function here so let's do that now we're going to import from DB let's import the init DB function so let's call that within the lifespan event and then we can use the yield keyword in Python and after we do that the fast API application will start up and our database tables will be created by this function the last thing to do is pass the lifespan function into the fast API object here and we can do that using the lifespan keyboard argument so now when we start this fast API application we expect to see the database created and we're going to create the tables in that database so let's go to the terminal here and we're going to use uicorn to run the application and our app is in main.py so we're going to reference Main and the name of the app object is app so let's copy that and we're going to paste that in here and run this command and you can see on the terminal when we started that a create table command has been executed against the database and on the left hand side we now have a db. SQL light file and this file is going to be our database for now if you want to know how to set up fast API with mySQL or postres and that could be on Docker containers as well let me know in the comments what we're going to do now is go back to the db. piy file and just below the inet DB function we're going to define a new function and this one's going to be called get session now what we want to do here is return a session that can be used by our fast API Handler functions so we've commented out all of these Handler functions and each one of these before was operating with this hardcoded data that we had on line 17 we're now going to go to the database to fetch data and also to add new data to the database and so in order to do that every one of our functions is going to need a database session so this function that we're going to create is going to be a fast API dependency that we're going to inject into those Handler functions so at the top of this file we're going to import from SQL model the session object in fact we've already done that and what that means is that we can create the session here using a context manager so with the session and we pass the engine into that object and we can call the returned value session here what we're going to do within this dependency is just yield that session now we want to inject this dependency into our Handler function so let's go back to main. Pi and at the top of the file we've already imported inet DB from that module so we're going to also import the get session function and from fast API at the top we're going to import an object called depends so now that the setup is complete we're going to go down and look at these Handler functions that we've commented out and we're going to bring these back one at a time in this video now what I'm going to start by doing is looking at the post end point here at the bottom and this is an endpoint to create a new band in the database so let's uncomment this now and immediately we can see some of the changes that we need to make here so band with ID that was changed to the band model when we create a new band our post request body is going to expect a schema that conforms to the band create model and then after we create the new band in the database we want to return that band model that actually represents a record in that database table now we can get rid of this logic here we don't want to append this band to a list in memory and we can remove this band with ID line and the ID that's being defined on the line above that's now going to come from the database so we can actually remove all of this what we're going to do is create a band from the data that's been posted in the request body so let's create a variable called band and we're going to instantiate a band here and we're going to Define each of the fields on that object so the band takes a name and that's going to be equal to the band data that we have above that should contain a name field and if we go back to models.py and we look at the band create schema that inherits from this base that contains a name and also a genre so let's copy that and go back to main. Pi when we instantiate the band we're also going to pass in the genre here and that's going to be from the band data and again it's the genre field in that post data now what we want to do is add this new band to the session but we need to actually get access to the session within this fast API Handler function so let's inject that dependency now and I'm going to start by restructuring this a little bit so we can see this so after the band data we're going to create the session and that's going to be of type session which we need to import at the top so let's go to the top and from SQL model we can import session here let's go back down to the function and what we're going to inject here is using the depends function the get session function that's in the databased module so you can use these fast API dependencies to inject certain objects into your Handler functions we now have a session and what we can do is add the new band that we have above to that session using the session. add method and then at the bottom we can call session Doom commit in order to actually make that change in the database and before we return the band we can call session. refresh and pass that band in in order to refresh that data from the database so an example of why you do that is that when you create the band here it doesn't have a primary key that's the ID that will only be created after you commit that to the database table and then once you've done that you can refresh the band object to get back all of its data including that primary key now the reason that we've got these lines in between the session. ADD and session. commit functions is that when we create the band if we look at the schema for band creat dat and we can do that in models.py this might also contain a list of albums for that band so when we send the post request containing the band data we may also have a key there called albums that is a list of these album instances so we need to handle that now in main. pi so after we create the band what we're going to do is check whether the posted data and that's the band data contains a property called albums so let's do that now if band data. albums is defined what we're going to do is Loop over each one of them so for album and band data. albums we can create an album object for that and what we can do is instantiate an album and we can pass some properties in there such as the title that's going to be equal to album. tile and just to refresh our memory let's go back to models.py the album contains the title the release date and also the band ID so let's go back here let's pass the release date in here I'm going to set that equal to album. release date and finally we can pass the band and as well so we're going to do that using the band parameter and we're going to move this to a new line so that we can see this clearly we've set the properties for the album and we're using the relationship to set the band equal to the band object that we have on line 60 above and we can do that because of this relationship that we have in the album class once we've done that it's very simple we can go back to this file here and for each album object we want to add that to the session so that it's going to be saved to the database so we can use the session. add function and we're going to add the album object to that session now we can test this out by going to api. http and what this is using is an extension for vs code called rest client and that allows you to send requests to your application from vs code directly and here's an example request with the name and the genre of the band if we send that request by hitting this button just above the post request we get back the data on the right for that new band that's been added to the database and you can see it's been given the ID from the database itself and what I'm also going to do is send a request that includes another key called albums and I'm going to set this to a list of data that conforms to that band create schema so when we send a post request the album should contain the title and the release date let's try this out by again hitting the send request button and we're getting back this issue here that there's a field required and that's the band ID field now I'm going to fix this by going back to the models.py file and it's this band ID we can set this to an integer or none and the reason we might want this to be none is that when we send a post request with the album data we're not going to specify the band ID as part of that request so let's make this an optional field and go back to api. http so let's send the request again and this time we're getting back an internal server error and the problem here and well done if you've noticed this if we go back to main.py when I create the album instance we are passing in the release date but I've called that release date data so let's change that and fix that and we can now save this and let's try it one more time if we go back to api. http let's send this request and this time we get back the data now let's move on we're going to go back to the main.py file and we're going to go up to this particular endpoint here that Returns the band by its ID so I'm going to uncomment these lines of code and then we can start changing this function now first of all this is going to return a band instance and this line of code here is looking up a band in that list in memory we're no longer going to use that what we need to do as a parameter to this Handler function is inject the session as we did down here so I'm going to copy this line of code and we're going to go back up to this function and we're going to inject that session as a parameter once we've done that what we can do is we can try and fetch the band from the database using the session. getet function in SQL model that takes an entity as the first parameter and then it takes the primary key lookup as the second parameter now this function takes a ID as a parameter in the URL that's a path parameter in fast API so we're going to pass that ID into the session. getet function so basically we're looking up a band by the ID that's provided in the URL we can then check if that band is none so if we don't find a band using that ID we're going to keep this code the same here we're going to raise an HTTP exception with a status code of 44 otherwise if we do find the band we're just going to return that to the client so this function is very similar we've just changed some of the types here and we're now doing a database lookup to try and fetch that band from the database by its ID and let's move on to the final function here I'm going to uncomment this line of code here or these lines of code sorry and we'll test both of these in a second now I'm going to start by changing what's being returned from this list endpoint so this is the function that's going to fetch all of the bands from the database and return them as Json data to the client so that's going to return a list of bands and again we can get rid of the inmemory lookup and again we're going to go down to this other function and we're going to copy the dependency for the session and bring that into the list function once we're in that function we can fetch a band list and to do that we're going to use the session. EXA function and then we can use a selection from SQL model to select all of the bands from the database using that select function and then after we've executed that we can fetch all results using the dot all function now we need to import select at the top this comes from SQL model so as well as the session we'll import select and we can go back down to the function that gives us back a band list and I think we can actually keep the rest of this the same although we do need to uncomment the last line of code what we actually have here is we have some if statements and these are parameters that are coming into the function so for example the user might specify a query parameter for the genre or a search term Q here so what's going on below is if we have those values set we're doing some filtering of the band list that's coming back this time from the database so now that we've made these changes let's finally test this out by going back to api. http and if we scroll down here we have a get request I'm going to remove the query parameter and we're going to send a request and that's a get request to the SL bands endpoint to fetch that list of bands let's test this out and we can see we get back the list that contains a single band and that's because we only have one band in the database if I go up to the top here and we send that post request again we're going to get back a second band and this time when we send that get request we can see that there are two bands in the list now these bands have ID of one and two so if we wanted to fetch a band by its ID and that's going to use the other Handler function we can add slash2 to that and when we send that request we get back only the band with that ID and if we send a request with an ID that doesn't exist in the database let's send that we get back the detail of band not found and the response code is 404 not found so what we've done in this video is we've refactored our models or schemas to now interact with the database using SQL model and we've created that SQL model Connection in the db. piy file using the create engine function and we also created a dependency that we were injecting into all of our fast API Handler functions and that allows them to get a session object from which they can then interact with the database and finally we're using that session in all of the handlers and now we're fetching data directly from a database and we're also creating new records in the database so what we can do to end this video is just remove the hardcoded list of bands we're no longer using that in this project we are now working with a database and that has a lot of advantages over a list that's in memory if our application crashes we're not going to lose all of the data that's been stored in the database and it's just a better way to organize the data in our application now in the next video we're going to add a lmic to this project and we're going to see how we can manage the data base and manage any changes to our database models in the models.py file using almic and migrations so that's coming up soon if you've enjoyed this video give it a thumbs up and subscribe to the channel for much more fast API jangle and other content thanks again for watching and we'll see you in the next video

Channel: BugBytes

Views: 3,299

Keywords:

Id: pRYzMF04fLw

Length: 24min 21sec (1461 seconds)

Published: Thu Mar 28 2024