Showing the Latest Posts

Summary

In this video, I demonstrate how to structure database interactions in a web application. I show how to create a statements.go file to expose query structs, and explain that our article model doesn't need to match the database table exactly since it should reflect the application's definition of an article. I walk through creating the GetLatestArticles function and show how to map database rows to our model structure. I then update controllers to use a struct for sharing dependencies, particularly the database connection. Finally, I demonstrate adding test articles to verify everything works, and preview that we'll cover slugs and human-readable URLs in the next video.

Transcript

All right let's just jump straight into the code. First thing we need to do is we need to create a file in our db package that doesn't get deleted or regenerated every time we want sqlc generate and that is because we need to expose the query struct that we saw in the last video to the models so that they can actually interact with the database function that we get generated. So go into models internal db and create a new file called statements.go package db and we're going to create a variable called stmts just for statements but we call this new function that just creates a new struct of the of the query struct where all of the method lives. So this let's get that save yeah now we can go into our models folder again and we can create a new file called article.go package models let me just grab how our model for the article will look like. So the important thing to note here is that our article model would not necessarily look the same as our articles table and that's because the article model should reflect the application definition of an article. Let's say we were to add the text that we have in our metadata in our files right now the way to do this would probably be to add on another table called tags and then add a connection table where we connect some text we create with specific articles and then create a tag model and then have article model have a slice of tag model if that makes sense right so it would look something like let's say tags and then tag right now this is obviously not how the article table look or will look but the important thing is that this is how an article looks like from the point of view of the application. All right let's get rid of this we don't have this for now so let's focus on getting the get latest article function created it's pretty straightforward we just say get latest articles takes in a context and it also takes in this dbtx from the db package go and it returns us a slice of articles or an error and then we can call we can actually just now call the logic to actually interact directly with the database and get out some rows from all the articles we have so let's just say article rows on error db statements query articles pass the context and a dbtx give that a save and if we have an error we just return that error great now we need to do the mapping so as we said the the article model doesn't necessarily look the same as the the table does and i made a mistake here we also need to have a loop so we loop over all of the the article rows and of course it's range there we go so models will not be the same as the defined in the table right so we get the rows out and then we do all of the mapping if you go back to our example with the text we will also query for the specific articles text here and then do the do the mapping as needed and then finally simply just return the articles on no so this is how we interact with the database directly through the model layer and do all the mapping that we need to get our data from the database into the structure that we expect or the application defined structure now we need to update our controllers so we can actually start using these models in our application and here in our controllers file so far we have defined all of our controllers as just methods but it would be nice if we have a way to share dependencies between all of these these controllers right for example our our db connection pool so let's create a struct here called controller that's going to hold our our database or postgres struct that we created in our psql package so let's just call this db psql postgres give it a save and now we can turn all of these controllers into methods on this controller struct and then share the database dependency right so let me just quickly update let me just quickly update all of these controllers to be methods instead and now we have a bit of a name clash because we use c in both places so maybe let's say c like this instead no i don't like that let's just update this echo context to be ctx instead and then update all of the places that we use it so there we go now we are just going to update our home controller so we no longer pull out all of the articles from the the embedded files we're going to use the database so let's say c db and no sorry we're going to say models get latest and then we pass the context from the request and also the db pool give that a save and now we are pulling out all of the articles from the database convert them into our definition in the models and now our view here is complaining because we are returning a type of models article instead of what is defined in the view so let's go in here to a home page view and in here we no longer going to be using this definition we're going to be using models models like this and we need to update the fields down here so it's no longer meta title it's no longer meta description it's except there we go and it's meta so it's not longer meta.slug it's just slug and clean up some dependencies all right we are basically ready now we need to update our routers because we could before we could call our methods directly or functions directly now we live on a controller struct as methods right so let's go into routes here and add our controller as a dependency to our route struct so controllers controller take all of this and we pass it to the new route here and ctrl all right and now we just need to change it here a little so we use the controllers on the router struct instead of the controllers directly and there we go now we're almost there we just need a way to initialize this this controller struct right so func new let's just say new for now for now since we are in the package of controllers controller we return a controller struct with the db argument and if i refresh my lsp does it stop complaining it does not stop complaining yet we should have there we go finally in our main.go file we can now create our let's see what is we need to create our first our psql say psql new postgres just takes in the pool and then we need to create the controller controllers new pass in psql new pass in psql all right and then controller to the routers and now we basically have everything set up so if we jump into our browser and say localhost 8080 we have no post because we have nothing added to our database yet so let's add a few posts and then see if they actually are getting written on the home page all right so i'm just going to use the psql cli to create these articles but you can use whatever you want to a gui or whatever floats your boat right i have just prepared some insert statements here they are one-to-one with what we have in our front or front matter metadata in our article files the first one here is just the first article the second one here is going to be the second article but i'm going to set this to draft true so create that one as well now if we jump into our browser here and give it a refresh we should only see one article and we do and we can still go in and read it and everything is as expected so let's just jump back here all right so the basics of this is that we want everything to go through our models layer we make sure that we can only interact with the database through our models and so all of our data logic is is centralized in one place now we have been using some some manual labor here to to actually create these files right and we're using something like slugs that i maybe not have talked about yet so in the next video on video i want to go over like why what is a slug why do we use it what is human readable urls and how do we ensure that we have these in place once we actually create an article so we will add some logic in the next video where we actually start to implement the insert and creation logic of articles.