Summary
In this episode, we create a database package for our Go application that handles the connection to Postgres. We build a psql struct with a connection pool using pgxpool, which efficiently manages database connections by keeping them open and ready to use instead of constantly opening and closing them. We set up a NewPSQL function that takes a context and database URI from our config, establishes the connection pool, and returns the struct. Finally, we wire this into main.go to ensure a database connection is established when the application starts, preparing it for future queries and data operations.
Transcript
We have now explored how databases work and why we are using Postgres. It's time to connect our Go application to a real database. In this episode we will be creating the database package for our project. This package will handle the connection to Postgres and return a struct that we can then later use to actually start writing queries that will interact with the database. So I created a folder here called database and inside of that I'm going to create a file called psql.go in package database. In here we're going to create a struct called psql that has our con field which is a pgxpool.Pool. Let me just add this to our go.mod file. If we restart the LSP we can see that we have no errors anymore. With this added to our go.mod file we can now write a new psql function that takes in our context.Context and then database URI as a string, and returns us the struct or an error. Now we need to set up this pgx pool here and we're going to do that by saying cfg for config, then error := pgxpool.ParseConfig, and then we're going to pass the database URI. We're going to configure that in just a second. If we have an error we are just going to return an empty struct and the error. If there's no error we can actually create the database pool by calling pgxpool.NewWithConfig. We're going to pass the context and the config. Again we just return the error if there is an error. Finally we're just going to say psql := &psql and pass the db pool and then a nil error. So by having our own structure we create a single place where we can later add convenience methods. For example, we might add a helper function that can start a transaction, run a query, or we can add logging or retries. The new psql function takes our context and the database URI which then sets up the connection pool and then returns the instance as we have just seen. Before we move on let's talk about why we are using a connection pool. Every time our application talks to the database it needs a network connection. Opening and closing those connections over and over is slow and inefficient, especially when multiple requests are hitting the app at once. Connection pools keep a set of open connections ready to use. When the app needs to run a query it borrows a connection from the pool, uses it, and then puts it back when it's done. This means that we avoid the overhead of constantly reconnecting. We can have multiple goroutines share the same pool of connections and the pool automatically limits how many connections are open at once, protecting the database from being overloaded. pgxpool handles all of this for us - connection reuse, timeouts, cleanup - so we just need to focus on writing the queries and not managing connection circuits. So we are passing this database URI to the new function here but this is going to contain secrets that we don't want to hard code in the app. We'll need to pass the database URI from our configuration because it's going to change depending on the environment that we are in. So let's quickly update our config struct here to have a database URI that's a string with the env tag database_uri in all uppercase. This way we can just add this to our .env file and then pass it when we wire everything up in the main.go file. Finally let's wire this into our command app main.go file. We're not going to use it for now but we're just going to be doing some preliminary setup. So before we have our router set up we're going to do a blank identifier because we're not going to be using the struct that gets returned - we're only going to check the error. Then we're going to say database.NewPSQL and pass context and the config.DatabaseURI. We're going to return the error if there is an error and then we're going to leave the actual struct for later when we start actually interacting with the database. So now whenever the application runs it ensures that we have a connection to the database that we can then later pass on and use to actually interact, insert, and query data into the database.