Creating the Database Layer

Summary

In this video, I demonstrate how to set up database interaction in our Go application. I start by configuring sqlc, a tool that generates type-safe Go code from SQL files. I create a configuration file to specify migration paths, query locations, and code generation settings. I then establish a database connection using a Postgres struct and the pgx library. I show how to create SQL query files and use sqlc to generate corresponding Go code, which allows for easy database querying. Finally, I integrate database migrations directly into the application using the Goose library, ensuring that migrations run automatically when the server starts. The goal is to create a clean, isolated approach to database interaction through our models layer.

Transcript

Okay so we have our first migration now and what we need next is a way to interact with the database. In our application we are going to be doing this through the models layer where all the the business logic will live. So let's say we have a title for an article and we have a rule that says it can only be a certain amount of characters. That check will happen in the models layer. But we also need a way to interact with the database directly, connect to it, run migrations. So in this video we will be adding that layer. We'll also be adding some configuration for our tool that is called sqlc. What sqlc does is it takes in a bunch of sql files and generates type safe go code so that in our actual application we are going to be working with go code throughout all of the layers. And then finally we will also be adding our migrations to the binary. We're going to embed them just as we did with our articles so that we can run up and down migrations or we can basically on deploy we can run it through our code base if we wanted to. So this is the focus and then in the next video we'll be setting up the actual models layer and start implementing the logic to pull out articles from our database. All right first things first let's just quickly add the configuration we need to use sqlc and to do that we just need to add a yaml file to our project. I'm gonna call it sqlc and then I'm gonna grab the configuration real quick and then we can work through it. So there are a bunch more options that you can you can do here but we only need these for now. If you want to look at what you can do further than this or in addition to this go check out sqlc's website. They have a bunch of different configurations you can do. But the gist of what we do here is we provide sqlc with the path to our schema which is going to be all of our migrations. So we basically just say our migration lives here based on our relative to the sqlc yaml file. All of our queries which is all the sql files will live in this directory which is our psql directory. We're going to be using PostgreSQL and the generated code we want to be in a package called db. And now this is probably the important part here is that we output this to models slash internal slash db. If you're not familiar with what internal does is basically hide a package away from other places of the code base. So we are internal here is on the same level as our models directory which means that everything in the models package can interact with the db package but anything outside of models cannot. So we cannot integrate or use our db functions in let's say handlers or the views. And doing it this way ensures that we only go through our models layer whenever we want to interact with data. And then finally we just specify some file names here and an important other important part here is that we emit the methods with a db argument and you will see why in the next episode. But basically this makes it so that we don't need to do a lot of dependency interjection when we work with our models. We can basically just create them as functions and then accept this db argument. We say here that we want to use pdx version 5 and we also say that whenever we work with the uuid data type we want to use this package. So really straightforward and now we can actually start adding sql files to the psql directory and and start generating a type safe go code to interact with the database. Okay let's add the logic to establish a connection to our database but also a struct to hold that connection. So jump into psql and create a file called psql.go. It's going to be in package psql and then we're going to create a function called createPoolsConnection. And this is going to accept a context and what is known as a uri which is basically just our connection string to the database and what is going to return us is a pointer to our pdx pool dot pool or an error and the meat of this function will literally just be opening this pool connection. So let's call the the connection pool db pool to pdx pool dot new pass in the context and the uri. If there's an error we just return it and if not then we return the db pool or nil. Let's give that a save and this also uses the pdx library that we specified in our sqlc file. Next let's create the Postgres struct that will hold this pool connection. So let's just say pool is a pointer to pdx pool dot pool and finally our function to create this Postgres struct that's going to accept an argument of type pdx pool dot pool and return us our Postgres struct. Postgres and dp db pool. And now we can use this in our main.go file to actually create a pool connection and start using our database. And with this in place we are we are basically ready to start using sqlc. So let's jump out of the psql file and create a new one called articles.sql and in here we need to add the the sql but we also need to add a comment that sqlc will use to to name our functions but also to know what the expected return is. So if we're only occurring for one record we can specify we only want one out. If you want many we're going to specify many and if you don't really care about the result we only want to know if there was an error or not we can use a third option. But for now let's just add one method that we are going to be naming query articles that's going to return many records and then we just write pure sql. So select all from articles and let's let's order it by released at in a descending order and then jump into the terminal and we're going to write sqlc compile sqlc generate. If you run this command in your terminal you will now see that in the root we have this models folder that we didn't create sqlc created for us and in here we have an internal and a db package that contains free files. Now this one here the db file is just some sqlc related stuff that it uses under the hood to make the queries and execute the queries so not really super relevant for us but what sqlc also generated for us is the entities package that has a representation of our articles table that we can see right here and this is where it gets really really nice. It also generated this query articles function for us that lives as a method on the curious struct and you can see here it just uses the sql that we provided and then it does all of the mapping for us so that we only need to worry about using the return data. We don't need to write all of this by hand which is what we used to do before sqlc came about and notice that it emits this argument here this db that's of type dbtx and if you jump in and look at this this is basically this interface and doing this was what i talked about in the beginning that now our our models can only can just accept this as an argument and then from our handlers or services or whatever we can pass this to it so so that you can't really interact with the database and we don't really need to do any sort of we don't need to have a model service that has a connection itself it just gets passed it from a higher level so this turns out into some really nice isolated code which would be very clear the separation of concerns so the last thing we're lacking here is the is that we need to apply all of these migrations or the single emigration that we have right now and i want to do this through through go we could we could use goose directly in the terminal but when we start to go into production and at the cicd pipelines having this as part of the code base makes it a lot easier to build out these these flows so that we we can migrate the database and release our our applications and handle all of the errors that might occur so we need to go back into our psql file and in here we are going to be adding a new variable called migrations and this is just as what we had in the articles so nothing really new here next we're going to go into our main.go and we're going to make it so that every time we run the server we will run the migrations ideally this is not really what you want but for our purposes as this states it will work fine so i'm just going to grab a rather large function here and i'm going to copy paste it in that will require some imports and you can just go ahead and add them as well you can run go mod tidy and it will add goose to the go mod file now goose is the same thing we used to generate the the migrations they also have a go package and this is the one that we're using down here and a lot of this is directly from what goose recommends you to do if you want to run migration through through your go application so we're not really going to touch it we're also going to improve on this later on so for now this is all we need to start running migrations finally we need to call this function so i'm going to grab a little bit of code here jump into main i'm going to add this and basically what we do we create a new context we pass this to the create pool connection as as long as as well as a env variable that we're going to touch upon in just a second then we run the migrations and if anything goes wrong we just panic and then we fix the error and now every time you run go run main.go we will run the migrations so we always are already are always at the latest version right now if you're not familiar with what env variables are it's basically a way to store secrets out of our code base so for example our database url here will contain secrets like a password and stuff like that that we don't want to expose and we will be adding some logic some config a config package later on but for now we only need to to have our database variable set so if i am to jump out of my terminal now clear this out and say export and say database underscore url that is going to be postgres sql it was called block admin and the password was password and it's on local host 5 4 3 2 and the database is called block dev and now we can say go run main.go I did something wrong what did we do wrong are we need two splashes here so re-export this and say go run main.go and now our migrations would have been applied so we actually have the articles table in our database and we can start making queues against it please note that this way of exposing env variables will differ depending on your system so for linux and for apple this is this is how it will how you can do it it will be different in in for windows but now we have all of our logic all the things that we need to start working with our database.