A database package

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

insert and query data into the into the database