Okay.
So in the last video we got our node file JavaScript communicating with our MySQL database, albeit
very, very simple communication.
We're not actually interacting with tables, but this is SQL code that we're running.
So the next step, of course, is getting it to interact with tables.
So we're going to in this video, we're going to create our table, we're going to define our schema,
create our table in my SQL separate from JavaScript, and then we're going to try and query it from
here.
So basically we're going to go into my SQL, create our table, let's add two or three users, and then
we're going to see if we can fetch them from Node.
But before we get there, one thing I want to address very briefly is when we're looking at this code,
this connection, query function, error results fields or this code appear if our connection equals
blah, blah, blah, blah, blah, you might be wondering how did you know what code to type?
Where did I find this?
It's not in the my SQL documentation.
This is not an official MySQL sanctioned library.
Basically the way that it works is that random developers, teams, developers, solo developers go
and create these libraries that connect my SQL to other languages.
Some of them are more supportive than others.
Like I said, some of them are just one person, but they usually have their own documentation.
So the documentation for this is on GitHub.
It's GitHub, slash my SQL, JS, slash my SQL.
There goes my fridge again.
Oh, very active today.
But if we scroll down, you can see, well, there's a lot of stuff to take a look at, but it basically
talks about installing it talks about setting up your connection.
It talks.
Then if you keep going down about once you've established a connection, what can you do?
Well, here's a very simple query.
We saw something very similar, although this is just selecting one, we did something a little more
complicated.
But if we keep going, eventually, you'll see.
We get two things about querying, which is where we're going just in a little bit.
But basically, I want to show you there is a source of documentation.
So here we talk about some queries, how it works.
We'll we'll come back to this in a bit.
But I'm not just pulling this stuff from nowhere, and I certainly don't have it memorized.
I'm not just you know, I don't just know how it works.
I'd say the approach is more, okay, I know what I need to build.
I'm going to be building it with Ruby.
Now let me find the Ruby, my SQL driver or a gem, which is what they're called.
Instead of packages in Ruby, let me find my SQL gem.
Is there a connector?
And then you find it.
And then you look through the documentation and you figure out how it works.
Okay, how do I connect first?
That's the first thing you need to do.
And there's not one syntax.
It varies from language to language.
Somebody made a decision.
So if we go back to our slides here, somebody made the decision to call this method query, which I
mean, it makes sense to call it query, but it could have also been called make query or perform query
or when we're creating the connection, my SQL create connection.
I don't know if someone could have called this.
This could be database name instead of database, but somebody making those calls and I'm not disagreeing
with the way this is named in any way, but I'm saying you have to play by the rules of whoever defined
this.
Just like when we're working with my SQL, we have to use the my SQL syntax.
There is a specific syntax.
We have to learn the same thing for using these libraries.
So it's not something that comes standard with JavaScript or with Node.
It's something we had to plug in ourselves.
And that means we need to go do the work to figure out how to use it.
So next up, we're going to create our schema and it's pretty simple.
All that we need are two fields.
So we'll run create table users, we'll do email and a pretty standard length is 255.
So not that an email is that long, but for fields in general, text fields people will do var cha two,
five, five and we'll make it a primary key.
Why do we want to do that?
Basically, we don't want someone to be able to sign up twice with the same email address.
Also, it gives us a couple more options for exercises, so it's a good thing to do and it's good practice.
This is probably the first time we've seen primary key without auto increment.
Then we've also got created at and that's going to be a timestamp.
It could be a date time.
But remember back to that video I did about the difference timestamp is smaller, it takes up less space
in memory and it works just as well, except for really far away dates in the past and in the future.
1970, up until 2038, I think it is, is the current range.
We're not going to worry about that because we're dealing with current dates.
You know, we're creating a mailing list app or a waitlist app people are setting up now.
So we don't know.
Nobody's entering a date from 1650 or something.
Then we're setting the default value to B now so that when someone signs up, it will automatically
be filled in as the current time.
But don't forget, when we're working with Faker, we are going to insert like 500 users into this table
and we are going to provide a created at time so that we don't get 500 users with the exact same time.
So we want this default value to be there, but we'll also be able to override it manually if we want
to.
Okay.
So let's hop over to Cloud nine.
And the first thing I'm going to do close this down is just make a new file.
I'm just going to call it schema SQL.
And the only reason I'm doing that is just to have a record of this.
You could just open up my SQL and start typing Create Table, but I'm going to do it in here, create
table users.
So we wanted email and then we wanted to create a that an email is var char 255 primary key and created
that is timestamp would work just fine if you left it as a date time and we want default to be now.
Or we could also say current timestamp just like that and we'll save we'll hop down to our terminal
down here.
I'm in the MySQL CLI and I'm just going to select database right now and see what we're working with.
Good.
We're in the join us database.
So we made a new empty database.
If we look at show tables, there's nothing here that's good.
So we're going to create this.
You can either copy it and paste it in or you can run the file, which I'll do just because it's been
a while.
Source Schema SQL.
Now let's see if it works.
Show tables this time.
Now we have a user's table and we can describe it.
Perfect.