Python FastAPI Tutorial (Part 5): Adding a Database - SQLAlchemy Models and Relationships

Hey there. How's it going everybody? In this video, we're going to be learning how to add a database to our fast API application using SQL Alchemy. So, up until now, we've been storing our posts in a Python list in memory. And that's fine for learning the basics of creating endpoints, but it's a major flaw because every time we restart our server, all of our data disappears. It resets back to the hard-coded list of posts, and that's not practical for any real application. So, in this video, we're going to fix that by connecting our application to a real database. We'll use SQL Lite for this tutorial because it's built right into Python and doesn't require any separate server installation. But the library that we'll use to interact with the database is SQL Alchemy. And that's the most popular OM for Python. And the great thing about using an OM like SQL alchemy is that if we decide later to use Postgress or MySQL in production, then you can switch databases with just a quick configuration change and the rest of your code stays exactly the same. And we'll be making that switch to Postgress later in the series before we deploy our application. So we're also going to set up relationships between users and posts. We don't have authentication yet, but if we design our database correctly right now, then adding authentication later becomes a lot more clean. So let's go ahead and get started. So before we write any code, uh let me lay out the architecture that we're going to be building here. So we're going to have three layers in our application. First, we're going to have our database models. These are going to be the SQL alchemy classes that define what we store in the database. Second, we have our pyantic schemas. We already created these in the previous tutorial. I have those pulled up right here. These define what we accept and return from our API. And third, we have our API routes. Uh these are the fast API endpoints that can handle the actual request, and those are here. Now, you might be wondering, why do we need uh separate models and schemas? Can't we just use one thing for both? Well, there's actually a library called SQL Model and it's created by the same author as fast API and it tries to solve that exact problem. It combines SQL Alchemy and Pideantic into a single model definition, but there's a few reasons why we aren't using that here. First, SQL Alchemy is pretty much the industry standard for working with databases in Python. And I think this is what you're likely going to be seeing in the real world. Most production uh fast API applications use separate SQL alchemy models and pyantic schemas. So when you join a team or look at open source projects, I think that this is most likely what you're going to see. Second, understanding the separation helps you understand what's actually happening. So SQL model is built on top of both libraries. So knowing them separately makes SQL model easier to pick up later if you want to do that. And third, the separation approach gives you more control. Uh so you often need different fields for creating versus returning data. Uh like we'll see with the user ID in the postcreate but not in the post base like we have here. Now SQL model can handle this but it requires inheritance patterns that end up looking similar to what we have anyway. So I think SQL model is a great concept and I think that he did a great job with it. But for learning purposes, I think we should start with what we're going to do here in this video. And the reason that separation makes sense is that each layer has a different job. Database models have OM specific features like relationships and database column types. Pyantic schemas define our API contract uh what we accept and what we return. So keeping those separate means that we can change one without affecting the other. So the data flow is going to kind of work like this. So a request is going to come in. Pyantic is going to validate it. SQL Alchemy stores or retrieves the data and then pyantic formats the response and the response goes out. So there's clear boundaries between each layer. So let's get started with this approach. So first of all, we need to install SQL alchemy. If you are following along with pip, then that is just a pip install sql alchemy. But for this tutorial, I'm using UV. So I'm going to use UV add SQL Alchemy. Okay, we've got that added. And again, for now, for the database, we're going to be using SQL Lite. And since SQL Lite is built into Python, we don't need to install any separate drivers there. Now, just to be clear, SQL Lite is great for learning and development. But in production, you'll usually want something like Postgress. Uh the nice

thing is that the patterns that we're building here translate directly to Postgress later. Same SQL Alchemy code. We just change the connection URL. So now let's create our database configuration file. So let me open up our sidebar here. Oops. And I have my git open here. I instead want the file explorer here. Okay. So let me open back up that sidebar. Okay. So now let's create a database py file here. And this is going to hold all of our database setup code. And it'll be imported by our main. py and our models file. Now I have the full contents in my snippets file. So I'm going to paste that in. Uh but don't worry, I'm going to explain all of this. I know that um it's not great when tutorials copy and paste a lot of code. Uh, but there's so much here that it would take forever to type out. So, I'm going to uh just copy and paste that here and explain as we go. Okay. So, let me paste this in and get rid of that comment there and save that. So, let's break down what we're doing here. So, the database URL here tells SQL Alchemy where to connect for SQL light. Uh, that's what this is right here. The dot here is the current directory and blog. DB is the file name. This file gets created automatically. Uh when we switch to Postgress, changing this connection string should be one of the only changes that we need to make and the rest of your code will stay the same. Now the engine here is our connection to the database. Uh this check same thread equal to false. This is SQLite specific. SQL light normally only allows one thread but fast API handles multiple requests across threads. So we disable that restriction. Uh you wouldn't need to do this for Postgress or MySQL. Now session local here this is a factory that creates database sessions. A session is essentially a transaction with the database. Each request gets its own session. And we set auto commit equal to false, auto flush equal to false because we want to control when changes are committed. And this is a standard fast API pattern. Now for the base here, you might see older tutorials using this um declarative base declarative base. Spelled that incorrectly, but you get the point there. That's the old SQL alchemy version one way. uh inheriting from declarative base is the modern version two approach with better type-checking support. So that's what we're doing here. And get db down here at the bottom. This is a dependency function that provides sessions uh to our routes. It's a generator using this yield db here. Uh the width statement makes the se session work as a context manager. Uh kind of like opening a file. This ensures uh cleanup even if an error occurs. So, Fast API's dependency injection calls this function for each request and handles that cleanup automatically. Now, if you're not familiar with dependency injection, we'll see it here in just a bit. It's basically just a way of saying, "Hey, this route needs a database session to work. " So, go ahead and give it one. Instead of creating the session inside the route, uh, we declare that we need one and fast API just provides it. And we'll see exactly how this looks when we update our routes. Okay. Okay, so now let's create our database models. So this will define our database tables using SQL Alchemy's OM. So over here I'm going to create models. py and again I'm going to paste this full file from the snippet, but I will explain everything and walk through it bit by bit. And as usual uh all of the code will be available uh for download in the description section below. So, let me make sure that I don't go too far here. And now, let me paste this in here. And now, let's go over this. So, first we have our imports. I'm going to skip this for now. We'll talk about this in just a second. So, first we need uh date time and UTC for timestamps. UTC is the modern Python way to get UTC time zone. So from SQL alchemy here we're importing some uh column types and some relationships like foreign key from SQL alchemy OM we are importing map and mapped column and then from our database we are importing base and now we can see that the first thing that we're creating here is this user model. Now, why are we creating a user model when our current post just have an author as a string? Well, in a real application, uh posts

should be associated with actual user accounts. So, we're going to add authentication in future tutorials. So, it's better to set up proper relationships now and that's going to avoid, you know, complex database schema changes later. So, for our user model here, the table name here tells LQL Alchemy what the name of the table is. uh we have mapped int here that is a type intent for our IDE and mapped column here defines the actual column. So for our ID here we have primary key equal to true and that makes it auto increment. You can see for username and email here these are strings. We have this set uh to unique equal to true on both username and email. That means that no duplicates are allowed. And nullable equal to false means that this is a required field. Now for the image file here, we're saying that this can be either a string or none. And what this is going to do is store just the file name to our images. So like corey. jpeg. It's not going to store the entire path. So why are we doing it that way? So that decouples our database from our file structure. So if uh we were to uh reorganize our files later uh and rename some directories then we don't need to update the database. We could just change that in one place. So it separates the data from the presentation which is just a nice production pattern. Now I'm going to skip this post for a second and let's immediately talk about this image path property down here. So this is Python. This is not database column stuff. Basically, what we're saying here is if a user has an uploaded custom image, then we're going to return that image from this media/media/profile pictures. If they don't have an uploaded image, then we're going to return that from for/static/profile pictures and then just return that default. jpeg. So, we're separating these static files, which are files that get shipped with our app. uh we're separating that from media which is going to be user uploaded content and this is best practice from you'll see this in Django and some other frameworks it makes uh deployments and backups a lot easier and also whenever you commit your code uh you probably don't want to commit all of this usergenerated content but we would want to include things like our you know default pictures and things like that. Okay. So this post here we can see that this is a relationship and this creates a one to many relationship here. So one user has many posts. We can see that this is a list of posts here and that back populates uh we set that equal to author that links to the author field on a post. So this allows us to do something like you know once we get this working we can say user. post to grab all of that user's post. Now this is something that you might not notice right off the bat here but we are referencing this post here uh before it's actually defined down here. So this is something called a forward reference and in Python 3. 14 uh which is the latest version. This works without any extra endpoints. On older Python versions you would need to add uh up here at the top from future import annotations as the first import of the file. It has to go above everything else. Now, I thought about leaving this out and just having this tutorial uh, you know, be for the latest version of Python, but I'm going to leave this import in the code. On Python 3. 14, it doesn't do anything, but it makes the code work on older Python versions, too. Uh, so if you're following along on an earlier version of Python, then I want this to work for you. I think I would also get a lot of comments of like, hey, why doesn't this work if students are using the earlier versions of Python? So, this import makes sure that the forward references work correctly on those uh earlier versions. And there's no harm in leaving it there. So, I'm going to go ahead and leave that. Okay. So, moving on to the post model here. Uh this is basically the same pattern. We have our table name equal to post here. We have an ID. We have a title. Um our content here. Now when we get to the user ID field here, this is a foreign key and that links post to users. So foreign key uh users do ID that means that this must reference a valid user. You can see that we also have an index of true on the user ID. Now an index is kind of like uh the index in a textbook. So without it

the database scans every row to find matches. Primary keys get indexes automatically, but foreign keys don't. And we'll likely be querying posts by user ID pretty frequently. So indexing makes those queries much faster. The trade-off is that we have slightly slower writes, but that's usually worth it. Now for date posted here, we can see that this is a uh date time with time zone equal to true. That ensures that we have time zone aware storage. Now SQL light stores datetimes as text but this ensures that Postgress will use uh timestamp TZ when we migrate later and it's a good habit to be explicit about time zones and the lambda here that default just gets called at the creation of each post so that it sets that to the current date time with uh that UTC uh time zone. Now this author here this is a relationship. Now this is a many to one side of things and this back populates to post. So that links back to that user. post that we saw before. So this allows us to do something like post. author and get that user back. And SQL Alchemy is going to handle all of those joins automatically for us. So that's really nice. Okay. So now we're going to have to update our podantic schemas that we created in the last tutorial. We're going to have to update those to work with these database models. So, let me get our schemas here. So, first we need to add some imports here. Uh, so we need datetime for the date posted field. So, I'll say from datetime import datetime. And I believe the only other one that we need here is I think we need email string from paidantic uh for our emails. So, I'll save that. Now remember before in our dummy data that we were using with this in-memory list of posts here author is just a string but now in our models we actually created this uh user model that has a lot more information to it. Uh so we're going to have to add our user schemas as well. So I'll put these before our post schemas here. And just like with our post, I'm going to go ahead and create a user base, a user create, and a user response. So, let me go ahead and paste these in here. And this is going to be user base here. And these will inherit from user base. And this is going to be uh user create. And this is going to be user response. So I will just put in a pass for these for now. And now let's go ahead and update these. Okay. So for user base, this is going to be uh what's shared between both the user create and the user response. So let's say that we want a username. This is going to be a string. And we will have this be a field. And we will put in a min length here of 1. And let's put in a max length of 50 for our usernames. Now for email, this is going to be an email string that we imported up here. And let's set a field on this. And we'll set a max length here to uh 120 characters, I think, would be good for an email. So this email string from paidantic validates that it's a proper email format automatically. So we don't need to write our own validation which is awesome. Uh and we also don't need a minimum length for email because email string already validates that it's not empty. Okay. Okay, so now that we have our base, just like in the last tutorial, we'll write user create and user response for what we expect when we create a user and what we expect uh to get back as a response for a user. So for now, we're just going to keep this pass here with user create kind of like we did here with postcreate. Uh for now, it's going to be the same with just username and email. But when we add authentication later, we'll add a password field here since a password is something that we pass in when creating a user, but shouldn't be included in a response obviously. Okay. And for user response here, just like with post response, I'm going to grab this model config here and set that with from attributes equal to true. Uh so that pyantic can read from our SQL alchemy model. And we will also include the ID for a user and that's going to be an integer. We will include the image file

for a user. Now this can be string or none. Then we're also going to have an image path here and that is going to be a string. Now here's something pretty cool. Uh so this image path this is a property on our user model. If I go back to models here then we can see that image path is a property of that user model. So it's not a database column but from attributes equal to true lets pyantic read those properties also. So the logic for building that path is defined once here in the model and then we get it in our schema automatically. Otherwise, we would have to do something like a computed field decorator here within our schema as well to calculate that image path and determine whether it's a default image or a user uploaded image. But we've already done that in our user model. And since it reads those properties as well, uh we can just use it here for free. Now, one thing to note here is since our user response inherits from user base, this is also going to include the email field in the response. Now, that's fine for what we're doing now, but returning a user's email in a public API response is probably a privacy concern. We'll fix this in later tutorials by splitting this into separate public and private user schemas but for now just keep that in mind that we are going to address this later. Okay. So now let's update our post base here. So we need to remove this author field since that now comes from the relationship. And for postcreate here we are going to add in a user ID field for now and that's going to be an integer. Now this is temporary for testing. Actually I will go ahead and put temporary here because now when we create a post we're going to pass in the user ID manually for now. But when we add authentication we're going to get the current user from the session instead. and that'll just automatically be used there. Now, finally, for post response, we need several updates here. So, first I'm going to go ahead and add a user ID here and that's going to be an integer. So, we'll return that. Now for date posted, this is now going to be a datetime and also we're going to have an author for each post and this is going to be a user response. Now that user response is kind of the cool part here because when SQL Alchemy loads a post, it can also load the related user. So, Pyantic sees that author field, validates the user object against user response and includes the full user data in our API response. So, we get nested JSON with the author's username, email, image path, all of that automatically. And also this datetime field here, this is going to get serialized to the standard ISO 8601 format automatically. So there's no need for you know serialization or custom conversions or anything like that. Okay. So now let's update our main py file uh to use everything that we have created here. So first let's update our imports. So we need to add several new ones for database support here at the top. So first I'm just going to put all of these right here and it will um sort these automatically. So from typing we are going to import annotated. Now annotated is the fast API pattern for type dependencies. We'll see that in just a second. But now I also want to import depends to from fast API here. So we'll import that. Let me go ahead and save this and it'll sort what we've imported so far. So depends this is for dependency injection. It's how we'll inject the database session into our routes. And now we just need two more imports here. So down here I'll say from SQL Alchemy uh we are going to import select and then from SQL alchemy. org we are going to import session. So select this is the SQL alchemy version two style for querying. It replaces the older DB. query pattern that you might see in some older tutorials. And session is for type hints. So our IDE knows what

type our DB parameter is. And now lastly, let's uh also add the imports for our uh database and models. So I can import our database models and then we'll say from database we want to import that base our engine and we want to import git db. So models gives us access to our post and user models that we just created. uh base and engine are for creating tables and git db is our dependency function that provides database sessions. Okay. So now before we create our app, we need to create our database uh tables and this happens right after the imports here. So I'm going to say base metadata all and then we can just say bind is equal to the engine and this looks at all of our models that inherit from base and creates the tables if they don't already exist. Now this is item potent uh which means that it's safe to run multiple times and if that already exists then nothing will happen. And this happens on app startup here. Now we also need to mount a media directory for user uploaded content. Uh we'll do that after the static mount here. So I'll say app domount and we will mount forward sldia and this will be I need to be outside of the quotes here. This will be static files. And if I get that comma in place, it'll autocomplete for me. So, static files, we will have the directory be equal to media. Now, this doesn't exist yet, but we are going to create this in just a second. And we will set the name equal to media as well. That's the same pattern that we used here for the static files. Uh, this creates a for/dia URL prefix and serves files from that media directory. So let me go ahead and create that right now. So in our project here, I'm just going to create this at the root of our project. I'll call this media. Now if you remember in our models here, uh we were saving user uploads into profile pictures from within media. So let's go ahead and also add that profile pictures there. So new folder within that we'll do profile pics. Okay, so that media for user uploads that is going to be empty for now, but users will upload images here later on once we add that feature in. Okay, so now that we're using a database, we are no longer going to need this in-memory post list here. So I'm going to get rid of that. And now let's add some user endpoints. So let's add an endpoint here for creating a user. Now I will copy what we have for creating a post here. So this app. post route. Let me copy that. And then let's see. I'll just put this. This is going to be an API route. So I'll put it below our template routes here. And I'll just put it at the top of our API routes here. So now this is going to be fairly similar, but instead of API- post, it's going to be API- users. Instead of a post response, this is going to be a user response. And for the function here, I will call this uh create user. Um now I am getting let me just do a pass here really quick. This is being underlined. So I'm assuming I forgot to uh import those. Yes. So from our schemas here, we can see that we're importing postcreate and post response. Let's also import user create and user response. Okay. So now down here, so you can see that so far creating a new user, we're basically using the same pattern that we used for creating a new post. So we specify the route instead of API- post, it's going to be API- users. We also specify what we're returning when this is created. So here we're returning a post response. user response. And then the status code for a successful creation which is that 2011 created. And also just like we did with our function signature with our create post, we are also going to pass in uh what we are creating here. This is going to be a user and this is going to be a user create and that is going to be

for our request body validation. But now we are going to do something different here that we didn't do before and this is because we're now using a database. So now I'm going to say database and this is going to be annotated a session a database session. And then we're going to say that this depends on get db. And this is our dependency injection here. So let me explain what's happening here. So this tells fast API, hey before running this function, call get db and pass the result as the db parameter here. So remember get db from our database. py file. Uh, this creates a session and yields it. So, fast API handles all of that for us. It gets it calls get DB. It gives us the session and it cleans it up when the request finishes. So, each request gets its own database session. It's automatic. It's clean and it's a standard fast API pattern that we're going to be seeing a lot. Okay. So, now let's write the body of this function here. So first we need to check if a username already exists uh when we create a new user. So I'm going to say result is equal to we'll do a db. execute. And now we want to do a select query here. We want to select from models. user and we want to do a select where models do user dot username. We want to check if that is equal to this user that we're trying to create here if it's equal to that username. And I'm not sure why it's giving me maybe it wants a comma there. Okay. So now we're going to see if there is an existing user. So I'll say existing user is equal to this result. caler. Sorry that's going to be scalers and then we are going to dot first and what this does is it just gets the first user object or none if there is no match. So now we can test this just by saying if existing user then we want to raise an HTTP exception here because if we're creating a user where there's already one in the database uh then we want to say no you can't do that username already exists. So we will pass in a status code here of 400. And again I'm going to use this these constants here. So this is going to be 400 bad request. And for the detail here we can just say that for the detail we will say username uh already exists. Now if I put a comma here it should format this a little bit better here. Now the database already has a unique constraint on there. So, uh it's not like we could add a uh a non-unique user anyway, but checking it first just gives us a friendlier error message here and also allows us to not even try to put that in there. So, we're going to also do the same thing for uh emails as well. We also want emails to be unique. So I'm just going to copy that. Paste this in here. And instead of username, I'm going to do email here and email again. And then we are checking for uh existing email here. And instead of username already exist, we will just say email already exists. So what we have so far is we are checking if a username already exists. We're checking if an email already exists. We're raising errors if either of those do. If they don't, then we can go ahead and just create the new user. So, I'll say new user is equal to models user. And we will set the username equal to whatever username they passed in here. And then we will set the email equal to whatever username was passed in there. And now we can add this to the database. So I can say db add we will add that new user. Now we can do a db

commit to commit that to the database. And also I will do a dbrefresh here and pass in that new user as well. So really quick, let me go over these here. So this DB add that stages the insert. The DB commit executes it and saves to the database. And the DB refresh reloads the object from the database. In this case, DB refresh probably isn't strictly necessary since SQL Alchemy already tracks the autogenerated ID after commit. Uh, but it's a good habit for when you have server side defaults or triggers or stuff like that. Okay. And if that goes well, then we just want to return that new user. And when we return that new user, uh, paidantic will automatically convert that to a user response like we set up here with the response model. Okay. So now that we have this route here for creating a new user, let's compare that to the route for creating a new post here. Uh, now remember this was within with our inmemory list here. Um, so this is going to change as well now that we're using a database. But what we were doing before, we weren't doing many checks or anything like that. We had to generate our own new ID and then we were just appending that to a list. Those got wiped out whenever we restarted. So now all of this stuff that we added in here, the dependency injection, the database, querying the database, things like that, that's kind of the pattern that we're going to be seeing from now on. So now let's add a get user endpoint. Uh, and maybe this will kind of start to click here a little bit. Now just like with the post here, I'm going to copy this as a starting point here. And now instead this is going to be for users. So API users, a user ID for getting a specific user. This is going to be a user response here. We'll call this route get user and this is going to be a user ID here. Now again, we're going to need the database for this route. So I'm going to go back up here. I'm going to get that same dependency injection that we used before here and grab that. And I'll go ahead and paste this in here. And now we want to get a user from the database. Now we already saw how to do this whenever we checked for an existing user up here. So it's just the same. It's going to be result is equal to db. execute. We want to select from the user model where and this is where it's going to be a little bit different. Uh but let me paste that as a starting point here. Instead of the username, we are using the user ID and we're going to check if that user ID is equal to the user ID here from our path parameter. So I will paste that in. And just like up here, we can say that we want the user to be equal to this result. scalers. first. That's going to get the first user object from our database result if there is one or it's going to return none if there is no match. So now we can say if user if one existed then just return that user. If not, then we will go ahead and just raise an HTTP exception here. And just like before, we'll do a status code here. And the status code will be status http 400 for whoops, I'm sorry. This is going to be a 404 not found. And for the detail here, we will just say user not found. So I hope that you're starting to kind of see the patterns here. So we have a path parameter here for the user ID. We have this dependency injection here uh to run the database and get the database session. We have a database query here. We're executing that database query getting a result if one exists or none. If there is a result then we'll go ahead and return that user. If there's not then we're raising a 404 not found. Okay. So now we've seen this pattern a couple of times now. But now every since we are changing the database and the database touches basically everything within our application we have to update all of our

routes. So let me use my snippets to update the remaining routes one by one and I'll explain what's happening as we go. So I'm going to first I will do this get user post route here and this is going to be an endpoint to get all posts by a specific user. So let me paste this in here. So we have a similar pattern here. Our response model is a list of post responses here. Uh notice that we are uh verifying that the user exists first and then we are querying for their post because without checking uh if the user exists first then an empty list could mean either that the user has no post or that the user doesn't exist. Uh but by doing this check first and returning this 404 user not found then we can confirm that an empty list is only for users with no posts. Now I'm also going to update our home route here. So let me grab this and update our home route which should be up here at the top. So I'll update that. So this one is pretty similar to what it already was but we are adding our database dependency here we are querying the database for all of the uh posts from the database. So we don't have any wear clause there instead of first we're using dotall and then we are just returning all of those posts uh to our template response. The template rendering stays the same as it was uh but we're just getting the post from the database instead of our in-memory list like we were before. So now let me update this post page route here. So I will update this as well. So this is the HTML route for a single post. Uh kind of the same pattern here. We have our dependency injection with the database. We are querying by the ID of the post from the URL path. We are grabbing the first result. And then this is the same as it was before. We're just saying if that post exists, then return that template. Otherwise, return an HTTP exception uh with a 404 not found. So now I think that is all of the routes that we had to update here. Let me check. Oh, that is all the ones that we wanted to update. But there's one more that we actually want to add in here. So we want to add a template route uh for viewing a specific users post. Um so we have the API version. This is the HTML version. So this is going to be user post page here. Let me grab this and I will paste this in right before our API routes there. So we gave this a name of user post page and we can reference that using URL 4 in our templates. We are verifying that the user exists. uh we get their posts if they do and we render a template with those posts. Actually, I think we have a few more um API endpoints that we do want to update. Here we can see if we scroll down uh any of these that have red underlines here are ones that are still referencing our old inmemory database there. So, let's go ahead and update these as well. So let's update the API endpoint for all of our posts. So go back to snippets here. Get posts. It needs to use the database as well. So I will paste that in here. We are querying for all of the post and just returning those. And Pyantic will automatically serialize the author relationship as a user response. So, we also need to update our create post. I know that this is a lot of updating. I uh it's just that whenever you do database stuff like this, it touches on every single route. Um but once we get it set up once, then we're not going to have to do this again. Um so now I will update our creating a new post route within the API here. So the route basically stays the same. Uh for our function signature here again database uh dependency injection. Our result we are uh getting whether a user exists because we have to verify that a user exists first. Uh this gives a better

error message than the database constraint violation. Um and then if that user does exist then we can create the post with that user ID. uh and then go ahead and add that commit that and then refresh and then we are returning that new post which is going to be a post response. Okay. And then finally yes this is the last one to update here. So from our snippets here this is for getting a post from the API. So I will grab this and let's replace that get post route. So here we are just querying for a post that matches the post ID. We are getting first or none there. If that post exists, then return it. And the author relationship, all of that gets populated automatically. And then raising a 404 not found uh if that post doesn't exist. Okay, so that's all of the routes. Now we need to update some of our templates. Uh the templates for the new relationship data. So in home. html here, so in our template in home. html and in post. html, we need to change how we display the date and the author. Since date posted is now a datetime object, we should format it to look a little nice. And author is now an object. So let's go ahead and look at both of these. So first, let's do the home route here. Let me scroll up here. So for the image source here, uh we were just hard- coding this to always use that default image, but now we can use that dynamic image path here. So for source, I'm instead going to say that this is going to be the post. author dot image path. Now anywhere that we see post. author here, before that was just a string. Now this is an object. uh with a bunch of different things. So now where we have post. author, we probably want this to be post. author. username. So that is what is going to uh display there for uh where it used to be, you know, corems. That's what the username is going to be now. And when we click on that username, right now that is a dead link. We instead want this to be uh URL for and we want this to be the URL for uh that route that we created uh specifically for a users post. So I believe we called that user post page. Let me double check that really quick. So user post page. Yes. And then we want the user ID for that route to be equal to post. author dot id. And now for this date posted uh this used to be a string but now this is a datetime object. So let's go ahead and use some formatting here. So I'll say post. date date posted dot strf time so string format time and what we will pass in here Python's standard datetime formatting method uh works in ginger 2 templates just like in python so what we can use here I will use a percent sign capital B that is the full month name and then we will do a percent sign lowerase case D that is going to be the day. Then we will do a comma and then percent sign capital Y and that will be the fourdigit year. So I'll go ahead and save that. And you might be wondering why we are formatting the date like this in the templates and not on the API side. So the API returns the standardized data in that ISO8601 format and the front end handles human readable formatting. So the same data can be formatted differently in different places. So that's likely how we want to do it. So now I want to do this within post. html as well. So here where we have post. author, we want this to instead be post. author author dot username. For the image here, I'm just going to reuse what we have here, post. author. image path instead of that hard-coded URL 4 for the default image. And for the date posted there, let's go ahead and grab

what we have there. So, I'll go ahead and replace that as well. Okay. So lastly, now we need to create a user post. html template. And I promise that this is the last thing that we're doing before we get to test all of this out. So it's a bit difficult to test as you go when you're adding a database for the first time just because it touches on every aspect of the application. So uh this is the last part that we need. So I'm going to create a new template here. And we are going to call this user_post. html. And now just like the other templates, I have a finished styled version here ready to go that we can copy from. So within this user postfinish. html here, I'm going to go ahead and paste that in. Now this is very similar to home. htm. HTML, but with a heading that shows, you know, post by user username. So, it'll say post by Corey M. Schaefer or something like that. But then we're just looping through that user's post just like we do in the homepage for all of the post. Um, and then we also have an empty state here down at the bottom that says no post by this user yet. Okay, so that was a lot. So now let's test everything. Now since that was so much that we updated at once, it is very possible that we have some errors here if I made a typo somewhere along the way and I wouldn't doubt it. So let's go ahead and test this. Now, if you have been testing along the way and you have a blog db file over here in your project already, the good thing about that is how we've set this up is that for testing those DB files are fairly dispensable. So, we can just delete the blog db file if one exists and every time our app starts up, it'll create that from scratch. Now, that would mean that it would delete any data that was in there and that we'll be starting from scratch. But for testing, that's usually what we want. And I'll provide us with a populate script uh in the series here soon to populate our database with some dummy data. But if you do have a blog db file, then don't be too nervous about deleting that. So now let's go ahead and run our fast API server here. So I'll say uh uvun fast api dev main. p py and run this. And now this is cached from earlier. So let me reload our page here. Now first of all let me go back to our code here. If we look in our project directory we can see that blog. db file was just created. So that's good. That's our database being created from our models. Now if we go back to the page, this is actually a good sign. We can see that there's no post being displayed, but the page is loading fine. There's nothing to show yet. That's to be expected because our database is currently empty. Uh but no errors that I can see. So that's a good sign. Let's go to the docs here. Actually, let's run this API route as well. That's an empty list. That's good. and let's rerun the docs here. Okay, so we can see some of the new routes that we've created. So now let's create a test user and some posts. So we need to create users before we can create posts because post right now require a valid user ID. So I'm going to click on this create user route here and then let's go on try it out. And now let's go ahead and just try out creating a user. So I'll do uh Corey M. Schaefer. And then for the email here, I'll do Corey mshafer atgmail. com. Let's execute that. And that worked. We got a 2011 response there. And we can see that it automatically gives me an ID of one. Image file is currently null, which is none. And our image path is that default image. Okay, so that's good. Let me go ahead and create another user here. Let's do Jane Doe. No spaces. We'll just do Jane Doe. And we will do Jane atacample. com. Execute that. And that worked. And now you can see it auto incremented her to an ID of two. So now we have two users to test with. So now let me go to get

user. Now if you remember my username was an ID of one. So I'll execute that. We can see that it does return uh my user for with that ID of one. So that's good. So now let's create some posts. So we want this post route here. So now let's go to try it out. ahead and fill in a title here. I'll say first database post and I'll copy that for now. And then I'll just say this is a sample DB post. The user ID that was one if I want this to be from my account. So I'll execute that and we can see that worked. So if we look at the response, we see that we got a 2011 created uh which is good. We have an ID for the post. We have the title, the content, uh the user ID, and the date posted is with that ISO 8601 standardized format. So that's all good. Now, one major thing to notice is this author field here. So it includes the full user information. So we have the username, the email, the ID, the image path, and the uh image file. So that's that database relationship in action there. So when Pantic serialized the response, it accessed post. author and SQL Alchemy automatically loaded that related user data. So that's pretty awesome. So now let's create a couple more posts here. Uh so I'm just going to change this to second database post. This is another sample DB post. I will post that from me again. Now let's do one more here. Third, this is yet another and I will do this one from that Jane Doe user that we created. So, I'll post that. Okay, so all of those are working. Now, let's try just a little bit of error handling here. We won't test literally everything that could go wrong, but uh for example, let's say that we wanted a user of 999. Let's execute that. We can see that we got a 404 user not found. That's good because there is no user 999 yet. So now let's verify that all these are in the API. So if I go to the get post route and try that out, if we execute that, then we can see that we get all three posts that we just created successfully. So that's good. And all of those have that full author information. So that's really nice. Um, now let's try to get a single post. So let's go to try it out and let's get a post with an ID of one. Execute that. We get that first database post. And lastly, let's try a database or a post that doesn't exist. And we can see post not found there. So all of that looks like it's working well. So now let's check out our templates and the front end here. Um so if I reload the page. Okay, we've got our first error. Let's go back to our code here. Okay, so it's saying no route exists for user post page here. Uh let's let me look at what this is here. Oh, actually we gave this a custom name. So name here of user post that was in my snippets. I totally forgot about that. So now where we're doing user post page here, let me do user post instead. Um, and actually we never updated that link in the post. html. So, let me grab this and update that dead link there. Okay. So, now let's try to restart our server here. And hopefully that was the only issue. Okay, that looks good. It looks like we are getting our post there. Now, let's just click around a little bit. So we can go to a specific post. post by a specific user. Let's make sure that works for Jane Doe as well. Okay. So that's working nicely. We can also see that the uh dates are nice and formatted here with the current time and the current date. Uh so that looks good. That looks a lot better than the uh information from the database which is that ISO standardized ISO format. This one is a lot more readable here. So, it looks like all of this is working well. Um, now let's do the big test here, which well, I guess we've already done it because we ran into an error. But what I was going to say is we can reload the server here to make sure that our data uh persists after the server shuts

down, which before when we used that in memory list, those did not. Any new ones did not. But now we have all those correctly stored in that blog. DB database and they persist across restarts. Now if you want to explore uh the database file, this blog. DB file here, uh there are a lot of free tools online that let you view SQLite uh databases, but I'm not going to go into that here. You can um look those up on your own if you want to actually dig into the database file there. But let's compare to what we had before this video uh compared to what we have now. So before when we were using that in-memory list, our data was in a Python variable. We lost that data on restart if we added anything new. We had manual ID generation when creating new posts, which was kind of a pain. We had to do manual searches through loops to find data that we were looking for. and it was not scalable in any way. So now with the database even though it was a lot of work uh our data is stored in an actual database that data persists across restarts. IDs are automatically generated for us. SQL queries are handled by SQL alchemy so we don't have to write raw SQL and we have a structure that will scale up much better now. So you can see that the API surface mostly looks the same but the internals have changed a lot. So far we've focused on reading from the database and creating some new records. But in the next tutorial we'll cover put and patch for update operations and we'll also look at delete for delete operations and then we'll be able to create read update and delete information from our database. But I think that's going to do it for this video. Hopefully now you have a good idea how to get uh set up with a database with SQL Alchemy and a fast API application. In the next video, we'll add in the rest of those CRUD operations that I was just talking about. But if anyone has any questions about what we covered in this video, then feel free to ask in the comment section below and I'll do my best to answer those. And if you enjoy these tutorials and would like to support them, then there are several ways you can do that. The easiest way is to simply like the video and give it a thumbs up. Also, it's a huge help to share these videos with anyone who you think would find them useful. And if you have the means, you can contribute through Patreon or YouTube. And there are links to those pages in the description section below. Be sure to subscribe for future videos. And thank you all for watching.