Creating a Database Package
The video introduces a dedicated database package in a Go application to manage PostgreSQL connectivity. A psql struct is defined to encapsulate a pgxpool.Pool, providing a centralized abstraction for database interactions.
Initializing the Connection Pool
A constructor function (NewPsql) is implemented, accepting a context and a database URI. It parses the configuration, initializes the pgx connection pool, handles errors, and returns the configured struct instance.
Benefits of Using a Connection Pool
A connection pool avoids repeatedly opening and closing database connections, improving performance and efficiency. It enables concurrent goroutines to share connections while limiting total open connections to protect the database from overload.
Handling Configuration and Secrets
The database URI is externalized through environment variables to avoid hardcoding sensitive credentials. The configuration struct is updated to include a DATABASE_URI field loaded from the environment.
Wiring into the Application Entry Point
The database initialization is integrated into main.go, ensuring a valid database connection when the application starts. Although not yet used for queries, this setup prepares the application for future database interactions.
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 and in this episode we will be creating the database package
for our projects 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 packets database and in here we're going to create a struct called psql
that has our con field which is a pdx pool dot pool and let me just add this to our go.mod file
if we restart the lsp we can see that we have
we have no errors anymore right with this added to our go.mod file we can now write a
new psql function that takes in our context dot context dot context and then database uri
it's a string returns us the struct or
an error now we need to set up this pdx pool here and we're going to do that by saying a cfd for
config our error pdx pool pass pass config there we go and then we're going to pass the
database uri we're going to configure that in just a second 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 pdx pool and then new width config 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 there we go and pass the db pool
all and then a nil 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 i can run a query or we can add log logging or read twice the new psql function takes
our context and the database ui 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 our 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 efficient inefficient
especially when multiple the requests are hitting the app at once
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
go routines share the same pool of connections and the pool automatically limits how many
connections are open at once protecting the database from being overloaded
pdx pool handles all of this for us connection reuse timeouts clean up so we just need to focus
on writing the queries and not managing a connection and and circuits right so we are
passing this database uri to the 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 ui from our
configuration because this is going to be containing secrets that we don't want to hard
code in our in our code base and 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 data base uri that's a string
env it's going to be database underscore uri of course all other case
this way we can just add this to our dot env file and then then pass it when we when we wire
everything up in the in the main.go file finally let's wire this into our our command app main.go
file we're not going to use it for now but we're just going to be doing some some preliminary
setup right so before we have our router set up we're going to do a blank space because we're not
going to be using the the struct that gets returned we're only going to check the error
then we're going to say database new psql we're going to say past context and the
config and why can we not pass the config it's because we don't need the config we need the
database ui i'm going to return the error if there is an error and then we're going to leave the
the actual struct for later when we start to actually interacting with the with the database
so now whenever the application runs it ensures that we have a connection to the to the database
then we can then later pass on and then use to actually interact and
