pgx Driver for PostgreSQL

>> Melkey: I think it's pretty cool that we have going on here, we have our back end, and we have connection to this database. Well, that's actually not true, we don't have true connection to this database, but we do have a running database, all right. So we're gonna go ahead and open up another tab, because we should have, depending on what your style is on my personal machine I have three tabs occupied. One with the editor, one with go dot main running, and then one with Docker. And then I open up this new one so I can keep importing stuff and exploring. When like I just said, we have Docker running, we have a database connected but this database is just, there's just chilling. It's just it has no affiliation to our project whatsoever, all because it's defined in the same project route, it doesn't matter. So here we're going to connect to that database, all right. And for us to connect to that database, we're gonna actually introduce a new package into our our project. And it's essentially a driver, it's a Postgres QL driver, the one that I recommend is by Jack C, and it is called pgx, okay? So it is a Postgres SQL driver and toolkit for go, it has about 11.4000 get up stars. It is relatively actively maintained, you can see a couple weeks ago, also written in Go. It's a pretty powerful driver that we are going to install and use in our project here. Quick side note, this particular driver, this package, if you are on an LSP go PLS. It sometimes bugs out and asks you to reinstall it, or install a particular version of it if you put in other packages. I think we'll see this in this course here, it's not a deal breaker, it's just very picky and a bit opinionated. But yeah, pgx, what we're going to use against just going to be our driver. So here we're gonna use version four, so we're gonna do, go get github.com/jackC/pgx/v4/STD lib, standard library.
>> Melkey: Going back to our editor within the store folder, add a new file, and this file is going to be called database. So database.go and we're actually gonna write stuff inside, so we can declare our package store. And the purpose of this particular file is going to allow us to connect to our running database right here, okay? The way we're gonna do this create a new function, so func open. And func open is going to not take any argument, but is going to return a type sql.db, and an error to bring in the SQL package. It's standard library import, you can do database slash SQL
>> Melkey: And then here we're going to basically instantiate a database struct that we're going to use throughout our application, so here we can type db err is going to be sql.open. This whole argument here is the connection string we're but the very first argument is going to be the driver that we're using, and we just installed pgx. So here we can do pgx, and now this is gonna be an interesting property in Go. Like I said, if I do FMT, the compiler yells at me, you don't, you're not using FMT, right? But I could just do this, because there are certain cases like the one we're going to see now with JAXI pgx where we don't implicitly use the library. But we are using, or we don't explicitly use the library, but we are implicitly using it via this pgx connection string here in the SQL open method. So here we're going to bring in github.com/jackC/pgx/v4/STD lib, it's gonna yell at us and in here we're going to just ignore that.
>> Melkey: Let me just go close all this.
>> Melkey: Yeah, there you go, so it just bugs out, so I have to do an LSP restart, I'm wondering if anyone else, does anyone else get that or you guys, it's kosher on your side? Good, yeah, so depending for me, on U of M with my configuration, it does bug out, but it Is working as expected, but thank you for being patient. Okay, next is gonna be the actual connection string, so here we'll do host equals, it's gonna be local host, okay? The user is going to be Postgres, the password is going to be Postgres, the db name is going to be Postgres, okay? And in here we'll do port 5432, the one we specified in our Docker compose file, and then we'll just add SSL mode equals disable.
>> Melkey: I'll zoom out just a little bit so you, you can see the entirety of the connection string and make sure I don't have any typos. Host localhost, user Postgres, password Postgres, dbname Postgres, port 5432 and SSL mode disabled. Next up, let's check that error, so if error does not equal to nil, what we're going to do is return nil. And here we're going to do format.error f, and here we'll say db, open method modulo W and pass in that error, right? So another format specify that we haven't seen before. Otherwise we can do format, print line, and we can say connected to database, instead of returning nil nil, we're going to return db.
>> Speaker 2: In a production app, where do you typically store your database credentials?
>> Melkey: Not in code, for a connection string, I would typically store them using AWS secrets. I actually am not a fan of doing.env files, I think they are highly dangerous. And so, I prefer to handle that burden with a proven service, like AWS client, secret manager, something like that, and then you pull it down. Dot env files are good for something you're building locally, I personally believe there's more condom pros with them. Okay, let's go back to the application and actually start using this db, so let's go ahead and create Postgres database error. I'm going to store.open, so I bring in that store mod package we just created, check that error. So if error does not equal to nil, let's go ahead and return nil and the error, and then in our application struck, let's go ahead and create a new db field. It's gonna be sql.db, like so you can see pgdb is yelling at us or the compilers killing in us. We'll just put db as pg, db like so, so now our application struct also has access to our database, okay? And this will work as is, but there is one criteria that we need to be aware of, and that is like loose connections and open connection with the db, right? So we want to make sure we close this, now, Go offers a pretty cool way of handling things like this. So if you go back to our main.go, right underneath the arrow does not equal to nil where we declare app, we can do app.db.close. Now, if you look at look at this, this will make no sense, because the db will close before of any ways of accessing it, right? But what we can do is use the built in defer function from go. And this built in defer function tells our application at the very end of execution, everything else has been done, then go ahead and call this function.
>> Melkey: So we defer it until the end, hence the name, very good, so now, with all of that, we can nuke our running go instance. If we do go run main.go, you should see now that we are connected to our database, and we are still running on port 8080. So now we have a clear connection between our back end and our database, even though it's not obvious, we only have this little print statement. We will see in the next sub module how we're actually going to load up our database with tables. How we're gonna explore them with psql, and actually explore data that we persist, read and delete.