All right.
So we're back here now.
And our goal for this video, I'll just lay it out there, is to have communication established between
my SQL database and this file of JavaScript.
If node, it's not going to be crazy.
We're just going to be basically running a very simple query like this.
Select We could do a third date or we could do something even sillier, like select one plus four.
But those are SQL queries that we could run in a MySQL database and get an answer.
So we're going to run them from this file.
And to do that, we need to first establish a connection.
And even before that, we have a bigger thing to take care of, which is that we don't have my SQL set
up in this cloud nine instance.
So if I'm back in the cloud, nine docs can see, remember when we ran this very, very early on my
SQL Ctrl space start and what it does is it starts my SQL in the current workspace that you're in.
And if you read this note, it says, the nice thing is that every workspace will run a separate database,
so your projects will never interfere with each other.
And that's really nice and useful.
But what that means is none of our data from our previous workspace is available here.
So all we need to do is just run this command.
So we'll do that.
Now, here I am in cloud nine.
I'm just going to run it.
And all that we should see is that it installs my SQL.
Everything's good, but something to take note of right now.
The root user.
So we need to remember this and you'll see why in just a moment.
So I'll just put a note down there.
Don't make fun of my username, please.
I regret it deeply, but it's here to stay.
Learn with Colt.
Yours will be something different.
But we need that credential.
Okay, so then the next thing, we now have a MySQL database.
I'm going to open up a new terminal over here and just keep it open and we're going to connect to the
CLI.
Remember how we do that?
It's my SQL dash CTL space space cli.
Okay.
And if we look at databases.
We have nothing really.
We have these kind of basic ones that we get from cloud nine.
Let's create a new database and we'll just call it join us.
Already forgetting the semicolons and then we'll use join us just like that.
So we have a database.
And just to show you, if we run a command like this select date, we get an answer.
Or if we do select one plus four and add a semicolon, we get an answer.
So this is in my SQL.
Hopefully, that's no surprise that that works.
Now, the question is, how do we get this file, which is not SQL at all, it's JavaScript.
How do I get it to talk to the SQL Server that's running and run some code in it?
And there's a couple of components to that answer.
The first thing we have to do is install the package.
So just like before it's NPM install, but instead of faker, we type my SQL.
It's the name of the package.
We hit enter.
And once it finishes, if you open up node modules, there's a new MySQL folder.
So this is the code that connects.
So on its own, it's not doing any connecting right now.
It's a library.
It's a toolset that we can utilize, but we have it on our machine now.
We have it in cloud nine.
So I'm going to come up here and require it just like I did for Faker.
I'll type my SQL equals require my SQL if I can type.
That on its own doesn't do anything.
Well, that's a lie, but it doesn't actually talk to our database in any way.
There's a bit more we need to do.
And it looks like this.
So what we do is establish a connection with the database.
So we require this first, we require MySQL, we save it to a variable, then we run MySQL, create
connection, which is a method that it comes with and we have to tell it a host, which we'll go over
later.
We're leaving it as local host, then user, which is going to be whatever your username was, mine
was learn with Colt and then a database name.
So we just made a new database, I think it was called Join US.
That's the one we want to work with.
So if we copy this over, I'm going to get rid of our generate address.
For now, I need to fill this in.
So some username for me.
Is this right here?
Learn with Colt.
That is what?
Oops.
That is what my SQL is expecting.
Says, Please make note of these credentials.
We want to keep keep track of that.
So we put that in there.
And then the database that we want to use is the one we just created, which was called Join Underscore
US.
Just like that.
So if we try this right now, we try running this file.
Node app JS.
Nothing happens, and that's actually a good sign.
That means that there wasn't a problem, but at the same time, we're not running any SQL code.
The point was, by the end of this video, we want to do something like this.
So then the next thing to do that looks like this and there's a lot to take in here.
Totally understand that we're going to start by just copying this over or you can type it as well.
But this is how we run queries.
Okay.
So there's a lot.
First thing I'll show you is we have a query.
I'll just do select, let's do create that's more accessible.
So we write our SQL code in here.
So we could do whatever SQL we want.
We don't have any tables yet in there, so we're not going to work with tables.
But we could do something like this, like create.
Then we just saved that to a variable called queue.
Now this is a trickier part.
So we've got connection that we.
This is our connection we created.
So we're referencing it connection, query.
We're telling it to run the query queue.
So I could have just taken this and put it directly here.
It just makes it a little longer if a line to look at.
But I'll do that to consolidate it and to show you that it works.
Then we have this whole function error results fields kind of a mess here.
It's a lot to look at.
What this is saying is when this query is finished, so we're going to reach out to our database over
here.
We're going to try running select date.
And if we just run it here, you can see it works.
So if we try running it from this connection, whatever happens when it's done.
Run all of this code here.
It's called a callback.
And again, I wish I could go into more detail, but basically this will run.
And if there is an error, there will be something stored in error here.
If we get results, it will be stored in results.
And we'll talk about fields later.
We're not going to use that now.
So I have this if statement that just says, if something happened, if there's an error, then just
stop throw error right there otherwise.
And we can even get rid of all of this and just print out even more.
Let's just get rid of everything and just print results so you can see what we get back just like that.
So there's still a lot going on, I realize.
Unfortunately, it doesn't get simpler.
But hopefully you can see the core idea.
We have our query and everything else here is just kind of the JavaScript wrapper around it.
To run this query, connect to a database, run the query.
So let's see what happens.
Okay.
So two things to notice.
One, we get this road data packet thing back.
We have cur date colon and then the answer for what cur date was, which is great.
But then we also have this other thing I don't know if you picked up on my cursor has changed.
I'm not in the regular terminal.
I have to hit control C to get out of there.
And that's because a connection was opened, but it actually was never closed and never ended.
So it's not a problem.
But basically that connection is persisting.
So at the end we can do a connection dot to either close or end.
Let's try it again.
There we go.
And it ends now.
And you can see we're done.
So now let's talk about our results.
First of all, we should pat ourselves on the back for getting this far.
We now are running simple queries.
Yes, but we're running queries here in this node file.
This sets the groundwork for everything else we need to do.
So imagine that instead of a single select date, what if this was insert 500 users and we're passing
that in and we're running it from Node talking to our database, which hopefully you think is cool.
Let's try changing this to something like select one plus five, saving it.
And you can see we're now getting row data packet.
One plus five colon is six.
So this what we're getting back, this syntax is kind of wonky.
I totally get that.
The way we can get around it, if we just want the answer, we can start by adding in zero, which is
basically if you're familiar with an array, it's going to give us the first item.
So if we try this again, now we're working with this row data packet in particular.
And to make this easier to access, I can just go in here and add an as.
So if I do as answer, for example, select one plus five as answer, I mean, if we run it over here.
This is what we get, right?
Well, this is not JavaScript.
So the whole job of this MySQL library is to or the package is to figure out how to take this and turn
it into something we can use.
So if I do as answer now, I try it again.
You'll see that it's stored now under the keyword answer so I can access it just like that.
Perfect.
So last thing that I'll show you, let's do select.
And I'm going to do this one in a separate line or as a separate variable and I'll call it our queue.
And instead of just selecting one plus six or one plus five, let's do select cur time as time comma
cur date as date then finally now as now.
And I do need that right there to OC.
So if we do that and I put Q here, I'm basically just saving myself from having to make a gigantic
line.
So by referencing Q it's referencing this query which is very useful.
It's going to run it.
And rather than doing all of this, let's just look at results like that.
Let's see what we get.
Okay.
Now, if you look at our results, we can do results zero, which will just get us basically the same
thing, but we don't have this array around it anymore.
More importantly, we now have three items coming back which should make sense, right?
If we went and took this over to my SQL, don't forget the semicolon.
This time we get three results time date and now.
So then the question is okay, let's print them out all separately.
So we'll have three prints, but we need to access them individually.
So we'll do time first and then date dot now.
So by putting in this alias the as when it comes back from my SQL, so imagine that diagram we're sending
it over, we're saying, hey, please send me, let's say cur time as time and it does that.
Here's cur time as time I'm sending it back to you.
JavaScript.
This JavaScript library takes it, sticks it in the results under the name time.
Only because we gave it that alias.
So whatever I change this to, I would need to access it under that name anyways.
Now if we do it, there we go.
I'm getting the three different things that we asked for.
Okay, so that's the extent that we're going to work with it here.
Next up, we're going to create our table and we're going to see how we start inserting and selecting
data from this file.
So rather than just selecting cur time, we're going to actually do something with the database, something
that we couldn't do without my SQL.