So it's time to figure out how to connect our go code to a database, and we're going to do that not
in our bookings project just yet.
Instead, I want to do it in a very small standalone program that just shows us how everything works.
And once we figure that out, then we'll implement those changes or implement that kind of code in our
bookings project.
So we're going to make that happen.
I'm going to create a new database and I'm going to use post code just because I like it better than
ever.
But you can use the same kind of logic in.
So I'm creating a new database, which I will call test, underscore, connect, and I'll open that
database and I'll add one table in there.
And the table is going to be called users and it will have an idea type serial, which postcode helpfully
gives me by itself.
But I'll add a column called First Name, First Underscore Name All lowercase.
It will be of type Verkerk and I'll add another one called Last Name, and there will also be a marker
and I will save those changes.
So of course you're probably doing this using Beever, but I like this one better and I'm going to use
it whenever I possibly can.
So I've created this database and that's all I want to do in Postdoc or Endevour.
Now I'm going to start up and I'm going to use Visual Studio Code because it's been a while since I've
used it.
And I will open this up and I will close this window and create a new project.
So I'll create a new folder.
Let me close this altogether and create a new window.
And I have no open files or folders, so I have to open one of those and I want to open a folder and
I'll create it in my visual studio code documents, which are here somewhere near there.
And I will create a new folder called Test Connector.
OK, so now I've created this file or created this this empty folder and I have no files open at all.
So let's create a new file.
And I will do that by looking at my explorer right here, going to test Kinect and creating a new file.
And I will call that main go.
Then I'll open my terminal window and I'm right here, my terminal, and I will create a go modifier
by saying go mode in it.
GitHub, dotcom, tesol test, underscore, connect just to create a go mod file.
OK, so I have this document, I have my terminal window open which I'm going to close and let's create
a simple go file.
So package main
and it has a main func func main and I'm going to do stuff in there and let's think about the things
we want to do.
The very first thing I want to do is to connect to a database and after I've connected to it, I want
to test my connection.
I'm going to put comments here and we'll write the code one one step at a time after I've connected
to my database and after I've tested my connection, I want to see what's in that database.
So get rows from table.
We have a database.
There's no rows in there, but we'll put some in in a moment.
OK, then I want to insert a row, then I want to display do the get rows from the table again.
So my goal here, first of all, is to connect to the database, which we haven't done yet.
But we will then I want to test my connection to make sure it's actually active.
I'm going to ping the database, then I want to display all the rows from a table which will add momentarily.
So we'll get a couple of rows from the table.
Then I want to insert a row, then get all the rows from the table again, which would show whatever
rows we had here, plus the one that we added.
OK, after we've done that, I want to update a row and then I want to once again get the rows from
the table again, which should show our changed row.
Then after that, I want to get one row by Edet, OK?
And after that I want to delete a row and then I want to get the rows again.
So what we're doing here is basic crud operations create, read, update and delete.
And once I can do all of those things in this simple program, then I should be able to do that in our
bookings program because the basic logic will all be right here.
And you might want to save this file just as a reference to see how things are done.
But we're going to be doing it frequently enough that you will become ever so used to it.
So the first first step is to connect to a database.
How am I going to do that?
Well, in Go, there are a couple of packages that allow you to connect to postscripts.
There's one called Libit PKU, and it's fine, I've used it in the past, it works quite well.
And there's another one called Zhaxi.
And interestingly enough, the author of Leyb PKU recently posted in a response to a question and one
of the go forums, you know, which package should I use?
And the author of Lempicka says, I will actually use Jacky's package because, you know, it works
really well and it is a good package.
And the URL for it is right here.
And I'll put a link to this in the course resources page, if I remember too.
But if I don't, it's right there.
GitHub, dotcom slash jack slash p.g x.
So we need to install this.
So I'm going to copy the GitHub part of the euro everything but not including the https.
And I'll go back to my terminal.
Open it up and say, go get and I'm going to put V4 at the end of it, which will get the latest version,
I think it's before is the latest.
Yeah, it is.
OK, so V4 and installing it will go get it.
Update's might go mod file and it's there now.
I need to connect to my database.
So in order to do that I need to actually give a certain instruction to this, this program.
So very first thing I'm going to do is say Köln, which will be my database connection pool which I'll
talk about in a minute, possibly an hour equals sequel DOT Open.
So it imports database SQL for me and I want to open of type PJI X, which is right out of the documentation
and you can see that in his example file down here, PJI Connect, he's actually using a different one.
Let's go to the Getting Started guide.
OK, so how to connect world from postgrads p.g connect context background.
I want to use the sequel Connect which is.
Let's see.
Databasing, this is the one I want.
He has a database specific driver for postgrads.
We're not going to use that.
We're going to use the default sequel package that's part of go just to keep things as close to Pergo
as we possibly can.
So he gives us an example here of how to initiate a project and how to connect to it.
And this is what we're doing right here.
OK, so let's go back here.
And our type is PGS and then we need to have a connection string and the connection string for postgrads
is dead.
Simple, specify host.
Our host is equal to localhost because our database is right on this machine.
If you were connecting to a remote machine, you'd have a different link there.
But we're just using our localhost port is equal to five, four, three, two, which is the default
port for progress for postgrads.
The database name well we named ours Kest underscore connect.
The user in my case is Tick's, which is my username.
You'll specify your own username there and the password in my case is empty.
And in your case it probably is too.
But if you have a password you would specify it there.
So that will give us a connection pool and potentially add an error.
So we'd better check for an error if error is not equal to nil.
What am I going to do here?
I will just say log fatele.
I can't go any further, so I may as well die and I will print a meaningful message.
Their format s print F and I'll put it in the message unable to connect to and then I'll put my error
message in there.
Which I'll just and unable to connect and I'll put my error message, which will be a placeholder V
followed by a new line, and then I'm replacing that placeholder V with the actual error.
OK, so that gives us this.
So let's just try that.
Let's leave our connection empty.
OK.
And you think that might work and it won't and it won't work because when you go back to your getting
started guide, you can't just import databased sequel.
You actually need to tell this package which driver you're using.
And our case we're using Peukan.
So let's go down here and look at what he's doing, OK?
He needs to import this standard library.
So let's copy that line and paste it in here and see what happens.
So I'll try running this.
Go run mango.
And it worked.
No errors.
That's great.
OK, I actually want to use do more than that, more than just connect.
I actually want to use this connection pool.
So let's look at that icon that we have right now.
When we connect to postgrads by opening a connection to PGS using this connection string, it returns
a pool of database connections that we can use and we can actually specify how many connections should
be in that pool and all sorts of things.
But right now, I just want to connect.
OK, once you've open that database pool, you have to close it when you're done with it.
And that is absolutely critical because otherwise these pool, these connections to the variables will
always stay open.
Even if this program closes, they might stay open.
So we need to close it when we're done and we know how to do that.
We use the deferment defer con and it has a function on it called close.
So after this after this function, this main function finishes executing, it will actually close that
connection pool.
All right.
So let's log something.
Log, print, line.
Connected to database, perfect, what's the second thing I wanted to do?
Well, I actually want to test my connection and we can do that by pinging the database.
And we can do that simply by saying storing the variable error, which we've already initialized up
here.
So I don't have to use the assign operator.
I can just equal I can say say for the connection to just ping the database.
If error is not equal to nil, then I'll log Fadal and I'll say log fatele cannot ping database.
And that will kill the program.
OK, I could put the air in there if I wanted to, but that's sufficient for our purposes.
Otherwise I'll say log print, line, pinged database.
All right, let's run that and see what happens.
So I'll clear the screen and I will say, go run main door.
Go and see if we can ping the database we connected to.
Connected the database ping database, so far so good.
So we're actually connected to the database and we can talk to it by pinging it.
We send it to ping it, send back, sends back a long response saying you can connect to me.
So that's good.
All right.
Next, we want to get some rose from the table.
Well, before I can do that, let's go put some rose in the table.
So I'll look at my users table and I'll look at the content.
And you'll use this do this using Dvir.
Probably, but that's fine.
I'll just insert John Smith and insert another row called Mary Jones.
I think I left a space in there.
Should probably get rid of it.
I did.
OK, and I'll save this now.
I have two rows in my database and I have something to query.
OK, so how do I get rows from the table?
Well, you can see here I'm actually getting rows here and then I'm getting rows here and then I'm getting
rows here.
Seems to me that might be a great place for a function of some sort.
So let's create a function and I'll call that function, get all rows.
OK, so we'll just create it first funk, get all rows and it's going to have to have at least one parameter.
It's going to have to take my connection, my connection pool and I'll call that con here just like
I did above.
And it's of type a pointer to school DB.
That's what Con actually is.
OK, so when I call Con up here, when I get this con school open, I can point at it and you see that
it returns a pointer to school DB.
So I've got my con, I'm going to pass that as a parameter to this function, get all rows and it's
going to return something.
What is it going to return.
Well I think an error is sufficient for our purposes right now.
If there's an error it will return that, which means if everything goes properly then I have to return
now.
OK, so I've got this function I'm going to call it.
I can do it right here.
I can say get rows from table.
All I have to do is say error equals get all rows and then check for the error.
If error is not equal to nil, log dot fatal and I'll just log the error.
OK, I have to pass it.
The connection con.
So we're calling this function, but it's not currently doing anything, so let's make it do something.
So what I'm going to do is relatively straightforward.
I am going to say store in a variable called rows which will be all the rows that come back from the
database query and potentially an error because it'll throw in if we get an error from our connection
pool, I am going to write a query.
So there's the syntax and the query is just going to be select ID, first name, last name from users.
So there I'm going to call the database connection pool con, execute the function query and pass it
this query and it's going to store the variable, the results in the variable rows.
So I need to check for errors.
If error is not equal to nil log Fadal.
No, I don't want to die there.
I'll just say log, print line.
Er, and then I'll return the ER.
OK, so I lorgat here I return it, otherwise I have something useful in those rows now just as you
have to close database connections when you're done with them.
So you must close rows.
Whenever I'm querying something that will potentially return more than one row, I have to close that
connection.
And here I know because I have two rows in the database, I'm going to get two rows.
But no matter what I do when I'm writing a query that can potentially return more than one row, I have
to close those rows when I'm done with them, which I can just do by saying defer rows dot close.
So when this function get all roses done, it will close that those rows.
And again, if you don't do that, if you forget to do that, you might not not notice that error for
quite some time, hours, possibly even days.
But eventually your database will run out of resources and it will just die.
So you need to make sure that you close the rows when you're done with them.
So I've got these rows of information.
And the question that we have now is how can I pull something out of that rows variable and store it
into variables that I can do something with and go, well, you do that, first of all, by declaring
the variables you want.
So I know that I'm going to have two variables, first name and last name, both of which will be type
string.
And I know that I'm going to have an ID of type int.
OK, so I've declared those variables.
Now I want to populate them with data from the rows variable and I do that just by arranging over the
rows.
I can say four rows and it has a function called next.
So what it's going to do is range over all of the rows one at a time.
And what I'm going to do as I arrange over them is scan the values from the rows into the variables
I've declared here.
And I do that as follows.
Error is assigned and because it's inside the rows, this is a different area of error variable than
the one up here.
So I have to use the assign errors equal.
Two rows dot scan.
I am going to scan everything I've queried in the order that I've queried them.
So ID first name and last name and I want to scan the variable, the first entry in the first row into
ID, so I give it a reference to ID, then first name and then the last name and that potentially throws
an error and if it throws an error I need to do something with that.
So if error is not equal to nil, then what do I want to do.
Log, print line.
Er, and I'll just return the ER and that will get us out of this function and take us back to our main
function and we'll do whatever we need to do from there.
So now I have these variables populated with data ID, first name and last name.
So what am I going to do with them.
Let's scan something into them.
I want to give some feedback when I run this program.
So what I'll just say is format, print, line and I'll print those values.
So I'll say record is and then I'll print it first name and last name and it'll continue to the second
row and do the same thing.
OK, the last thing I need to do when I'm going through this, this rose after I do the four rows dot
next is you always should check for an error again if error.
And in this case I'm going to say if error equals rosewarne error.
Or error and error is not equal to nil, just shorthand here, then do something log fatal and I will
log fatal here, error scanning rows.
And this is just good practice because you might get an error as you're scanning through this and it
might not be caught by this.
It's always good practice when you're done scanning through all of your rows just to check for an error
one more time.
And there's then then just to make things more readable, I'll say format, dot, print line and I'll
just put.
Some dashes, so I can see when I'm done with one query to get all rows and the next.
All right, so this should run.
Let's open our terminal, clear our screen and say, go run mango.
And it did we connected to our database, then we pinged our database, then we got record one is John
Smith and record two is Mary Jones.
So that all worked just perfectly.
What's the next thing we want to do?
We want to insert a row while inserting a row is not that difficult.
We're going to use our connection pool.
So in this case, I'll define my query first.
Just as a string, I'll say my query string is assigned the value of and whenever I'm writing queries,
I tend to use tactics because then I can put the query on multiple lines.
This one will fit on one line so I could use double quotes, but that ticks are your friends.
That way you can write your query in a readable format because some of our queries are going to get
quite long, so you may as well get in the habit of doing it.
Now, I'm only going all I'm going to do is write straight sequel, which we already know insert into
users, and I'm going to insert just into first name and last name and let the serial take care of the
ID column.
Certain values and the values I'm going to put in, I'll put in.
Well, I can't I could take them in here, but it's not what we're going to do because what I'm going
to do instead is use placeholders, dollar sign one and dollar sign two.
That is my query.
And one of those dollar signs.
Well, I'll show you right now.
Let's call and I'm going to discard the first return from this function.
But trap for the error.
Let's call Condori exec and I'm going to exact a query.
I'm going to execute a query and the program and execute is query that variable, a defined rate of
the line above.
And I'm going to substitute for dollar sign, one in dollar sign to these values in this order, Jack
and Brown.
So when I when this query is called, it passes the query to the database.
Here's a query that's going to be coming.
And these are the placeholders for the values.
And then it reads the values that are the dollar sudden.
One is substituted with Jack Dollar sign to a substitute with Brown.
And this is a much safer way of executing a query than trying to do it simply by building script strings.
Because what this forces the database to do is to not allow arbitrary sequel to be executed if you just
build a string by hand.
So if I instead say I took some user input for first name and last name and someone said my first name
is delete star or delete from users semicolon, the potential exists that when the exact command is
run that it says, oh, look, you want to run this first part of the query, whatever it is, but then
you want to delete everything from the user's database.
And calling the query this way, using the substitutions dollar sign one for Jack.
It prevents the database from inadvertently running malicious code that might have been inserted by
a user.
So it's best to use this format and that's what we'll be using for the remainder of this course.
So I run that query and it potentially throws an error.
So let's say if error is not equal to now, what are we going to do?
I will just say log fatal error and done.
All right.
And then once this if this succeeds, then we just want to call our rose again.
So I'll just copy this and paste it here and let's see what happens.
So let's open our terminal, clear the screen and type.
Go run Maeng.
Go and see if we did everything right.
And it looks like we did so we connected to the database, sweeping the database, we listed all the
records that are currently in there.
Then we did our insert and then we listed all the records again.
And now our new record is there.
Perfect.
And if I run that again, of course, I'll get a fourth row with Jack Brown.
And I just put one more thing in here just to make sure that it works.
Says that it gives us more meaningful information.
I'll say log, print line, insurgent a row just to give some more feedback on the screen.
So let's run it again.
I'll open the terminal, clear the screen, and this time I should get Feroze two with Jack Brown.
But different IDs.
And I do, so that works great.
Next, what do we want to do?
We want to update arow.
Well, updating her, as you might expect, is pretty much the same syntax as far as inserting a row.
But instead of writing a query that inserts a row, we write a query that updates her up.
So I'm going to use a new variable this time.
I'll call it statement TMT that's going to be assigned the value of impact text because I don't know
how long my query is going to be.
I'll say update users set first name equal to Jackie in single quotes.
Where first name equals Jack now, is that a good syntax?
No, it is not.
I mean, I can do it here because I'm controlling the input, but I really should do this instead.
Dollar sign one and dollar sign, too.
OK, so there's our statement now.
We just call our exact function on our connection pool, so I'll ignore the O ignore the results of
that again.
And I will just say trap for the error is equal to conduct exact.
And I'm going to pass the statement this time, not my query.
And then what I want to say is set first name equal to in double quotes because it's a string Jackie
and my second substitution will be Jack.
So and if it does print the error and stop the program.
OK, and now we'll say log print line
updated one or more Rose.
And then I'll get the rose from the table again, so I'll copy this and paste it down here.
Now ask yourself what's actually going to happen when I run this program?
Well, if you look at our terminal window, how many rows do we have with the name?
First name said to Jack?
We have to say when I run this, it should insert a third row with the name of Jack and then rename
all of those rows to Jackie Brown.
That's what I think is going to happen.
Let's find out.
So I'll clear the screen.
Go run, Mango.
And it did exactly that, it inserted another row to give us three, and then it renamed all of those
rows to Jackie Brown.
What if I just wanted to update one room?
Well, I could do that very easily.
And I can do it by simply saying adding my work or modifying my work clothes.
I can say update users set first name equal to Jackie where ID equals dollar sign too.
And in this case, the idea is not a string.
Let's get an idea that we can use.
I'll use record five because that's currently set to Jacqui.
And I know that it will be the next time I run this program so I can say where it is equal to five.
So let's save that, clear the screen and run it.
Go run mango and do make sure that you save it.
I actually under my visual studio code, which I'm becoming somewhat more familiar with, I have autosave
turned on just so I don't ever have to think about it.
But it's up to you.
Anyway, let's run it and see if our update this time works on just one record.
And it does so it changed just this record five, which was I think it was record five I was doing.
At 5:00, it changed 5:00 to Jackie Brown, so life is good so far.
And you notice that these are not in any particular order because I've not specified an order clause.
So six actually came before 5:00 because presumably this was the most recently updated one.
But I never trust the order from a database.
Any time I want to get things in a particular order, I'll add an order clause.
But that's beyond the scope of what we're trying to do right now.
All right.
What do we want to do next?
Get one rowby ID.
We'll getting a rowby ID uses a slightly different syntax.
You're called down here when I call, get all rows.
I'm using query and I'm actually deferring the rows close.
When you're getting one row, you're either going to get one row or none.
You know that things are a little bit different, but it's not that difficult.
So let's rewrite our query.
I already have the variable, so I don't have to assign it.
And this time I'm going to say for the query, select ID, first name, last name from users where ID
equals and I'll put my placeholder in there.
And now again, I'm going to call my a method or a function on my connection pool and I'm going to store
it in one variable because this method only returns one round.
So I'll call my variable RO and use my assigned assignment operator to create it and initialize it.
At the same time, it's equal to from my connection pool query row.
And this will return, as you can see here, it actually executes a query that is expected to return
at most one row.
And I know that when I specify where ID equals an ID and ID is my primary key, it can only ever return
at most one row.
So I call it and I pass it my query and I pass it.
My substitution for dollar sign one.
I know there's an ID one slot.
Specify that.
So now where is the error, where is the potential error in this case.
Well, it's a little different when you use query row, it doesn't return row and error.
It just returns one thing.
And it's not until I try to read that row, but I potentially get an error.
So I now write my reading of that row as follows.
I will say, well, I need somewhere to store these things.
Let's declare some variables first.
I'll save our first name, last name string, same as I do in the function down here.
But because this is in the scope of only the main function, I have to re declare those and var id is
of type it.
OK, so now I have somewhere to store this.
Now I can say error equals row dot scan and I scan my values in ampersand id for the first one ampersand
first name for the second one and ampersand last name for the third one.
And then I check for my error.
So I say if error is not equal to nil and I'll just log fadhel log fatal error.
All right.
So let's see if this works so I'll save it.
Open my terminal, clear the screen.
Go run Nango.
And it seems to have worked just fine, except that I forgot to print this stuff out.
So here I need to say log print, blind
query, row returns and then I'll put it first name and last name and let's run that again.
Clear the screen.
And it runs query row returns one John Smith.
OK, so now we know how to get multiple rows and we know how to get a single row.
The last thing I want to do here is delete a row.
And again, this is not that difficult.
So let's delete one row again.
We just have to have a query.
I'll use my query.
Variable that have already initialized is equal to delete from users where ID equals dollars on one
and I run my query as follows.
I'm ignoring the result that is passing me but looking for the error and that's equal to.
There's nothing new on that side of it.
So I use the equal sign Condori exec and I pass it my query and this time I'll delete it.
Six.
I know that exists right now, so that's a work just fine.
If error is not equal to nil log fatal error and now will print out my results, I'll just print out
log dot print line, deleted a row and then I will get all of my rows from the table again.
So I'll copy and paste it down here.
So what should happen now is after I've executed this last row, ID six shouldn't be in the last list
listing.
So let's save this, open the terminal, clear the screen, go run mango.
And I.D. sex is conspicuous by its absence.
So everything seems to work.
So what we have now, and this is just the basic way of doing it, we're going to improve it by using
contacts a little bit later on.
But right now, we can connect to a database, close our connection pool.
When we're done with it, we can ping it to make sure it's active.
We can get all the rows from it.
We can insert into it, we can update data in it.
We can get one rowby ID and we can delete from it.
So that's basically everything you're ever going to do with the database.
Your queries might get more complex and they will.
But this is the basic methodology for working with a database, and that's all we need to worry about
at this point.
All right.
So let's move on and start thinking about how we're going to connect our bookings application to a database
in a way that makes sense.
And one of the things we're going to work on or keep in mind is we do this is right now we're using
postcrisis our database.
What if down the road you decide to switch from postgrads to my school or Maria DB or DB or one of the
many, many databases that are available to you as a programmer?
So we're going to make our database queries as portable as we possibly can.