FastUI & SQLModel - Database integration in a FastUI Application

in this video we're going to extend the fast UI application that we built in the previous video and we're going to add a relational database behind the scenes that's going to store the data in the application and we're going to use the SQL model package in order to retrieve data from the database and also to insert new data into our database and we're going to give users the ability to delete data from our application and also to delete data from the underlying database so let's get started so I have a GitHub repository here where I pushed the code from the previous video and that video was the introduction to fast UI that should be appearing on the screen now so you can clone this repository and the code from the last video is in the starter directory and after I finish this video I'm going to push the completed code to this repository as well now I'm going to open vs code where I have already cloned that repository and what we're going to do is or what you should do is clone the repository and create a python virtual environment and then you can install the requirements with the pep install - R requirements. text command that's going to install all of these requirements into a python virtual environment and I've updated these requirements so for example we're now using fast UI version 0.3 and pantic version 2.5.2 now once you've installed these requirements what we're going to do is we're going to add a database to this project we're going to go to main.py and I'm going to have a quick look at what we did in the last video so we had a user model and that was just a pantic model that's coded into the application we now want to persist that data into a database so what we're going to do is create a database and create a table that can store these details for each user and users have an ID they have a name and they have a date of birth now what we're going to use is a package called SQL model and this is a package that builds on top of both pantic and SQL Alchemy and it allows you to Define model classes that have all the power of pantic models and can also interface with databases and because these model classes in SQL model if we go to GitHub they actually inherit from the pantic base model so these are very easy drop in Replacements in main. pi if we look at the API routes that we had that defined this fast UI application many of these are taking advantage and using these pantic models so by using SQL model we can quite seamlessly integrate the database functionality into this existing application so let's get started just now I'm going to expand the terminal here and in the environment I'm going to run pip install and we're going to install SQL model and while that's installing what I'm going to do on the left hand side in the starter directory is create a new file and I'm going to call this file db. pi and all of the setup code and the database models are going to be stored in this particular file for this small application so at the top from SQL model I'm going to import a method or a function sorry called create engine and this as the name implies it allows you to create an engine which will handle communication with the under under lying database now in this video I'm going to keep things simple and we're going to use an SQL light database which is a file based database it doesn't require any external installation but these Concepts should also work with databases like post GES so let's create a variable here and we're going to call this SQL light URL and we're using the SQL light protocol and we're going to call this file that we're going to create for the database db. SQL light 3 so like I said SQL light is a file based database once we have the name of the file what we can do is create the engine and I'm going to create a variable here called engine and we can call that function create engine that we imported and you can see in vs code what we need to provide to that is the URL which is a connection URL to the database so let's do that with the SQL light URL and I'm going to pass a keyword argument here of echo equals true and this will Define the engine that we're going to use in order to communicate with the database and we're going to read things from the database and write things to the database later in the video now what we need to do as well is create a model class in SQL model that's going to encapsulate the data that we had for this user so a user has an ID a name and a date of birth now what I'm going to do is actually comment this model out just now we don't need the pantic model because we're going to create a SQL model which is a subass of the base model that's going to represent this data anyway so let's go back to db. Pi and at the top from SQL model I'm going to import that SQL model class and let's also import field from SQL model now you could split your models out into a models. pi file or something similar but we only have a single model and this is a small demo so let's just keep everything in this file we're going to create a class called User it's going to inherit from SQL model and we're going to pass table equals true into that and inside the class we can Define the fields that we need so first of all we're going to define the ID and we're using the type hinting in Python here that's going to be an integer and we're going to set that equal to a field because we need to specify the primary key equals true constraint here so the ID is obviously going to be the primary key for this user table that we're going to create via this model we also had a name field that's very simple it's just a string and the date of birth is going to be an instance of a python date and of course we need to import the data object from the datetime module in Python so we do that at the top here and this is a very simple SQL model class and that's going to under the hood create a user table so now that we've specified the engine for for communicating with the database and also the model class how do we actually go ahead and create this database and create the tables in the database now of course in a real application you want to use something like almic for migrations but what we're going to do in this video is reference the documentation for SQL model and there's a function here and this code is very similar by the way to what we've just written so I'll leave a link to this below the video but there's a statement here called SQL model. metadata. create all so let's copy this statement here and what we're going to do is paste that right at the bottom of the file and we're passing the engine into that create all function and when this statement is executed it's going to actually create the database in other words it's going to create this file here and it's also going to create any tables that are registered with the SQL Alchemy metadata under the hood in other words any SQL model class with table set to true so let's now go over to the main. pi file and we're going to import the model class user and we're also going to import the engine so in main. pi at the top here I'm going to bring these Imports in and we can do that underneath the pantic imports from the DB module we're importing user and engine and I'm now going to just remove the code that we had here for the user pantic model class we don't need that anymore now if we run this application everything should still work as expected and that's because we're still using this hardcoded list of data this defines a list of user objects the only difference being that user is now a SQL model class it's not just a pantic base model but because we're using the hard-coded list and we're not actually saving any data to the database or reading things from a database there is still no persistence in this application what we're going to do now is we're going to take this hardcoded list of users and we're going to use that to populate a database when the server first loads when the application is started and we can use a fast API lifespan event in order to do that so let's go to the documentation for these events a lifespan event allows you to Define logic that should be executed before the application starts up and that means that the code will be executed only once and that's before the application your fast API app starts receiving requests and you can also Define code that's executed when the application is shutting down now if you scroll down on this page we're going to see a little bit about how we Define these events first of all we import from Context lib the async context manager so I'm going to copy that and bring it into our main.py file so let's do that right at the top and then if we go back back to the documentation what we do is we Define a function that's decorated with that async context manager and you can Define logic within that function that happens when the application starts and then in this case we have a yield statement and anything after the yield statement is executed when the application is shutting down so if you need to perform initialization actions you can do it within one of these lifespan events before the yield statement and any cleanup work can be done after the yield statement so what I'm going to do is go back to VSS code and we're going to Define one of these functions just above the app object so we need to decorate it with the async context manager decorator and I'm going to define the function called Lipan and it takes the fast API application as a parameter now in the body of this function I'm going to pass the list of users so let's cut that out of the main part of the application and we're going to Define that now inside of this LIF span event what we're going to do with each one of these users is we're going to add them to the session and save them to the user table in the database so how do we do that at the top from SQL model I'm going to import the session object so from SQL model let's import the session and we can then create a session create a connection to the underlying database with a context manager here so with session and we pass engine into that and we can give it a name and I'm just going to call it session we can now use this session to interact with the database so what I'm going to do is Loop over each user in the list of users and for each one of them we're going to first of all check to see whether that user already exists in the database we can do that with a method on the session object and that's the dot get method and this is SQL model code here basically when you call Dot get on the session it's going to return an instance based on the primary key identifier so we need to pass two parameters here the first one is the SQL model class and that's the user class so we're going to be looking up a user by its primary key and the primary key is what's passed in as the second argument so we have the user here and we can reference its ID with user. ID so this is going to perform that lookup in the user table and if the user exists it's going to return that and store it in this variable and we can check if that user does Exist by looking at that variable so we're going to check if the database user is not none and if the user is not none we can just continue because that user is already in the database but if the user does not exist with this ID the session. get method will return none and we can then progress to below this if statement and add them to the database we can do that by adding the user to the session so let's add them to the session and then after the for Loop has completed we can call session. commit and that's what's actually going to affect the underlying database and add the new users to that user table so that's the logic for when the fast API application starts up we take this list of users and then we open a connection to the database we Loop over each user in that list and if the user exists already then we just continue otherwise we are going to add the user to the session and then commit that at the end of the for Loop and that's going to add all users that are not already in the table the only other thing we're going to do here is just add a yield statement and we don't need to perform any cleanup actions so that's the end of the function so everything before the yield statement is what's executed on Startup now we need to perform One Last Action in order to get this working we're going to copy the name of the function which is lifespan and we need to pass that into to the fast API object when we're instantiating it and creating the application so the fast API object takes a keyword argument of lifespan so we can pass that function in there and that's going to perform those actions on Startup so with all that setup out of the way let's actually test this out when we start the application using uicorn the Imports are going to be brought in from the DB module and this lifespan event function is going to be filed and because we're looking at the db. piy fill this function here is actually going to create the database and it's going to create the user table and then we're going to populate that table within this context manager here and that's going to add the users from our hardcoded list into the database so let's test this out now I'm going to expand the terminal and we're going to use uicorn which is in the requirements file so you should have that installed and we're going to look at main.py and the app object within that and I'm going to pass the-- reload flag so that the uicorn server will restart whenever we make any changes now I have an error here and that's because I'm not in the correct directory so I'm going to stop this and we're going to go to the starter directory and rerun the uicorn command and that is now running and you can see some SQL statements here being executed so we have an insert into statement and that's going to add users to the table and we also have select statements that are looking up a user by its ID so the select statement is what's being executed on line 27 when we use session. getet it's going to look up users by the ID passed in and the insert statements are obviously coming from when we add the user to the session if they're not already in the database now just to verify that the users are in the table I'm going to right click the database file and open that database now I'm using a SQL light extension for VSS code in order to do this once you've done that you can look at the SQL light Explorer and we can look at the user table and we see that our four users are now part of this user database table so the data has been entered successfully on this startup event what we're now going to do is we're going to go on and we're going to amend the fast UI application to actually use the database data and to save new objects to the database what we're now going to do is we're going to go to the page in the fast UI app that listed out all of the users so let me scroll down here to the correct API route and we'll fix these errors later on these errors are coming from the fact that we've moved the user list it's no longer part of the global scope it's now part of the lifespan event function so we'll fix that later on this is the endpoint that lists out the users in a table in the fast UI application now the problem here is that again that users uh reference to the list of users is no longer applicable so what we're going to do is fetch the users from the database now so let's just remove this comment to keep things concise and we're going to create another session here so with the session and we pass the engine into that session and we're going to call the variable session so we're going to interact with the database here and get all of the users out of the user table so let's create a variable called users and we're going to call the session. exact method and we're going to pass a SQL model statement in here we can use a select call and we pass the model that we want to get back into that select function and then once we've executed that statement we can call do all and that's going to give us back all of the rows from that table now we need to import select at the top so let's scroll to the top here and from SQL model we're going to import select let's now go back to our function once we' fetched the users from the database we can actually use them directly here in the data that we're passing to the table component so no other changes are required here we've grabbed all of the users from the database these are SQL model instances and because these are subclasses of pedantics base model this is just going to work in place so that's very convenient and it shows the interoperability of SQL model and pantic so let's test this out if we go to the browser when I go to this page you can see we're getting the same data that we had before for these users but at this time that data is being pulled out of the database it's not hardcoded into the application itself now we can see the select statement that's being executed in the terminal here so I'm going to make this slightly bigger and you can see in the logs here that we're selecting all of those columns from the user table so the select statement is what's fetching this data for the users that's then being rendered in the table component now we can do exactly the same thing with the detail page so I'm going to scroll down and the detail page contains the information about a single user and that uses a fast API path parameter this has the user ID in the URL path and that user ID is then used to look up that user now beforehand we were looking up the user from the list of users that was hardcoded into the application this time we want to go to the database and we want to fetch the user from the table so we need to change the logic for this and we need to get a database session here so I'm going to use session object from SQL model and we'll call the object session here and this time what we're going to do is we're going to try and fetch the user Again by calling that session. getet method that we saw in the lifespan event so the session. getet method takes the SQL model class as the first argument and the second argument is the value for the primary key for that particular table so the value is now going to come from the URL here and it's passed in here as a parameter to the function so we can just pass that into session doget and it's going to try and find a user by that ID now again we can check just below the context manager is the user none so if user is none what we're going to do in that case is because we've not found a user with this ID we're going to copy this HTTP exception here and we're going to paste that into the if block so if we don't find a user we're going to raise an HTTP exception with the 404 status code and the message user not found and again that's actually all we need to do so we can get rid of the try accept block that we had before and actually let's Nest this over into the context manager so what's going on here is we create a database session we look up the user by the ID passed into the URL and then if that user does not exist we are raising that HTTP exception now if the user does exist we progress to the return statement here and the user is referenced here in the header text we render out the user's name and we also have the user being passed into a details component so again seamless integration here with SQL model and with the underlying database what we're going to do now is actually test this out so I'm going to save this file and we're going to go back to the browser here this is the list page containing the table of all of the users if we go to a single user let's say John we land on the detail page and you can see all of the details for this particular user and again if we go back to VSS code and look at the terminal you can see a select statement here for fetching that particular user so unlike before we have a where clause in this select statement where it's looking up the user by an ID that's passed in now there's one final page to fix in this application we have these references to the users and we move that into the lifespan event this is on the add user page now if we go back to the application and go back to the table here we have a button that takes us to a page containing a form that allows us to add a user to the system when we fill that form out it's going to send the data to this endpoint that we Define in the last video now the logic for this was very simple before we took that list of users and we appended the details for the new user what we need to do now is of course actually save this user to our database and this is better in many ways because beforehand we were just adding it to a list in memory and as soon as the uicorn server was restarted the data would be lost and we would be back to the initial hardcoded data what we're going to do now is save it to a database so that it's persisted and the next time application is started it's still going to be there so what I'm going to do again is we're going to create a session here so I'm going to copy this context manager and it's worth noting that because we're using this many times we can actually create what's called a fast API dependency and if we do that we can then inject the session into the Handler function as a parameter rather than writing this statement every single time but just to keep things simple I'm just going to copy paste it for now now in order to actually save our new user to the database we need to create an instance of this user SQL model and then we need to add that to the session and commit that to the database now we have some similar code here below this was creating a user when that user was represented just by a normal pantic model and with pantic we had created a variable called ID and we no longer need this so we're going to get rid of that and as well as the ID we took the data that was submitted by the user and that was coming in as this form parameter here which was an instance of the user form pantic model and we were calling model dump on that to pass those values into the user model so we're going to keep this form. model dump statement around and we're going to remove the ID now we don't need the ID anymore because if we go to db. Pi the ID is a field on our database model and it's got this primary key equals true keyword argument so the database is going to automatically generate an ID we no longer need to handle any of that log logic ourselves if we go back to main.py and we go back to this logic here once we've created the user model and that's an SQL model instance we can add it to the session with the session. add call and then once we've added the user model to the session we can commit that change to the database and it's going to insert the new user into the table and the final thing to do here we can get rid of the append call and we can just return the form response that's going to redirect the user in the fast UI application back to this page here so let's now save this and go back to the application and try submitting this form so we have a new user called John do if we submit this we can see that we actually get back that user in the table here so the question is is John do in the database let's go back to VSS code and I'm going to use that tool here to inspect SQL light again so let's open the database and we're going to show that user table and you can see we now have the user John do in this table so by submitting that form fast UI is send ing the data to this endpoint we then create a SQL model user instance by dumping the data from the form and then adding that user to the session and committing that to the database so this is all working fine we've added a relational database to this fast UI application let's now show one final piece of functionality we're going to add some new Fast UI functionality and we're going to actually add a delete user button to the detail page so let's say we have the page for John we want to have a delete user button appearing below this that allows us to actually remove John from the database so let's go back to main.py here and to start with we're going to define a very simple pantic model and I'm going to call this delete user form and it's going to inherit from pantic base model and it's going to contain a single field and that's the confirm field which is going to be of type bullan once we've done that we're going to go back to the user profile function which defines the detail page for a single user and we're going to add some code to the components that are returned from this page so below the user details here we're going to add a button and that button is going to be part of a form that when it's confirmed is going to try and delete the user from the database so let's write this out now we're going to create a div component and that can take a list of sub components let's create a header here so I'm going to copy the C do heading component and the text we want to pass into that let's pass a question here of delete user and we can make this an H4 tag by changing the level to four once we've defined the heading I'm going to use a model form component and we can parameterize those forms with a pantic model and that's automatically going to infer the fields that are to be displayed on that form so let's use that delete user form that had a single Boolean field confirm and then we can provide a submit URL to that model form now I'm going to use an F string here and we're going to refer dynamically to the user ID here so the submit URL is going to be/ API user and then the user's ID and then SL delete now this user ID is coming from a path parameter in this endpoint so we can reference that easily here in the components submit URL now as well as that we can also pass some extra keyword arguments into this model form we can pass class name in order to try and customize the style of the resulting form so class name we can use bootstrap classes under the hood because fast UI has bootstrap installed or at least the front end components of fast UI can use bootstrap so we're going to use some bootstrap classes here I'm going to say text left and we're going to specify that any buttons in this form should have the primary color so button and button primary and the last thing I want to do in these components is I want to pass some styles to the dev itself so after this list of components we can pass the class name to the dev itself and what we're going to do here is we're going to define a bootstrap card and I'm going to give that some padding and we're going to say column 4 so that it doesn't take up the whole page now this isn't going to look amazing but it's going to show that you can customize the Styles in a fast UI application so let's save this file and go back to the page and you can see that this is not looking particularly good at the moment the button seems to have extended across the whole card almost so I'm going to go back here and actually just remove these button classes from the model form let's try that again and go back here and well it looks a little bit better now so my apologies for that if we click confirm here and submit this form what we want this to do is actually delete the user from the database at the moment this is going to cause an error and that's because we've specified an API submit URL here but that doesn't actually exist in this fast UI application so the very last thing to do in this video is Define that API route so what we're going to do is copy this route and we're going to paste it down below in an app. poost decorator so we're using using fast API app. poost and we're pasting that same API URL and then we can Define the function here that's going to be called when we want to delete a user from the database it's going to take the user ID from the path parameter so that's going to be of type integer and then we also need to pass an annotation in here so I'm going to scroll up to the previous form that we had and it was this here I'm going to copy the signature for this so let's copy that and go back down to our function and paste that in here and I'm going to move this into new line so that we can see it more clearly so we take the user ID and we're also taking the form and we need to change the form instance here it's not going to be the user form it's going to be our new delete user form and that's the pantic model class that we created here with that confirm field once we've got that we can go back down to our function and we can handle the logic for actually deleting that user from the database now we're going to need access to the session so let's copy this session context manager and we're going to paste that into our function again we're using this in multiple places so it would make sense to create a dependency here once we have the session we're going to try and fetch the user from the database with the session. getet function that takes the user model and the user ID from the path parameter and that's going to fetch that user from the database or it will return none if it doesn't find a user with that ID now we only need to delete the user if it actually exists so we're going to check if the user is not none and if that's the case we can call session. delete and we can pass the user into that function and then again call session. commit so on this session object in SQL model as well as do get we also have a delete function that we can call if we need to delete a row from the database now the last thing to do in this function is return a response so what I'm going to do is just go to the end of this function and we're going to return a form response and that's going to be a go-to event back to the homepage of this application so let's now test this out and at the browser and we're going to see if this works we have the table of users here if we want to delete one let's say Jack we can go to the detail page and if we select confirm here and submit that we are redirected back to the table page but you can see that Jack is no longer in the table and we can do this for the new user John do as well so let's confirm that and delete John do now the question is are these users being deleted from the underlying database if we go back to vs code again I'm going to use this SQL light browser tool and we can show the table and we're down to only three users so the two users that we did delete they are actually removed from the database and the logic within this delete user function is working now one caveat before we finish the video remember we're using the uicorn server here with the reload flag if we were to change anything in this file and save the file you can see that the server restarted and that's actually going to rerun this lifespan event here and any users that you might have deleted from the application are going to be re addded so for example if we go back to the user table now after restarting that server we don't have three users we now have four now of course in a real application you wouldn't have a startup event like this but that's just something to know it doesn't mean that that the logic is not working it's just being refreshed on the startup event so that's all for this video in this video we have taken our fast UI application and we've added SQL model and a relational database to that and that allows us to have an application that can actually fetch data from the database and it allows us to add new users to the database via this button here and via the form and finally we've added functionality now to actually delete a user from the database so when this form is submitted it's going to remove the user from that database and redirect back to the table page and the main takeaway from this video is how seamlessly the SQL model can work with a fast UI makes it very easy to build these kind of applications that are backed by a relational database if you found this video useful give it a thumbs up and subscribe to the channel if you've not already done so and if you're enjoying this content consider buying a coffee for the channel there's a link in the description and if you have any other suggestions for similar content please let us know in the comments thanks again for watching and we'll see you in the next video

Channel: BugBytes

Views: 8,685

Keywords:

Id: XTn6esHGwe0

Length: 30min 19sec (1819 seconds)

Published: Sat Dec 30 2023