1 00:00:00,240 --> 00:00:00,660 Okay. 2 00:00:00,660 --> 00:00:08,010 So in the last video we got our node file JavaScript communicating with our MySQL database, albeit 3 00:00:08,010 --> 00:00:09,510 very, very simple communication. 4 00:00:09,510 --> 00:00:13,680 We're not actually interacting with tables, but this is SQL code that we're running. 5 00:00:13,680 --> 00:00:17,430 So the next step, of course, is getting it to interact with tables. 6 00:00:17,430 --> 00:00:22,770 So we're going to in this video, we're going to create our table, we're going to define our schema, 7 00:00:22,770 --> 00:00:29,910 create our table in my SQL separate from JavaScript, and then we're going to try and query it from 8 00:00:29,910 --> 00:00:30,360 here. 9 00:00:30,360 --> 00:00:35,910 So basically we're going to go into my SQL, create our table, let's add two or three users, and then 10 00:00:35,910 --> 00:00:38,490 we're going to see if we can fetch them from Node. 11 00:00:38,490 --> 00:00:44,220 But before we get there, one thing I want to address very briefly is when we're looking at this code, 12 00:00:44,490 --> 00:00:50,640 this connection, query function, error results fields or this code appear if our connection equals 13 00:00:50,640 --> 00:00:55,290 blah, blah, blah, blah, blah, you might be wondering how did you know what code to type? 14 00:00:55,620 --> 00:00:56,820 Where did I find this? 15 00:00:56,820 --> 00:00:59,160 It's not in the my SQL documentation. 16 00:00:59,160 --> 00:01:04,620 This is not an official MySQL sanctioned library. 17 00:01:04,620 --> 00:01:11,580 Basically the way that it works is that random developers, teams, developers, solo developers go 18 00:01:11,580 --> 00:01:16,530 and create these libraries that connect my SQL to other languages. 19 00:01:17,370 --> 00:01:19,470 Some of them are more supportive than others. 20 00:01:19,470 --> 00:01:24,270 Like I said, some of them are just one person, but they usually have their own documentation. 21 00:01:24,270 --> 00:01:28,050 So the documentation for this is on GitHub. 22 00:01:28,890 --> 00:01:33,630 It's GitHub, slash my SQL, JS, slash my SQL. 23 00:01:33,660 --> 00:01:34,890 There goes my fridge again. 24 00:01:35,640 --> 00:01:37,170 Oh, very active today. 25 00:01:37,560 --> 00:01:42,510 But if we scroll down, you can see, well, there's a lot of stuff to take a look at, but it basically 26 00:01:42,510 --> 00:01:47,160 talks about installing it talks about setting up your connection. 27 00:01:48,250 --> 00:01:48,670 It talks. 28 00:01:48,670 --> 00:01:52,930 Then if you keep going down about once you've established a connection, what can you do? 29 00:01:52,960 --> 00:01:54,730 Well, here's a very simple query. 30 00:01:54,910 --> 00:02:00,400 We saw something very similar, although this is just selecting one, we did something a little more 31 00:02:00,400 --> 00:02:01,180 complicated. 32 00:02:01,180 --> 00:02:04,780 But if we keep going, eventually, you'll see. 33 00:02:04,780 --> 00:02:09,669 We get two things about querying, which is where we're going just in a little bit. 34 00:02:09,880 --> 00:02:13,420 But basically, I want to show you there is a source of documentation. 35 00:02:13,540 --> 00:02:15,790 So here we talk about some queries, how it works. 36 00:02:15,790 --> 00:02:17,410 We'll we'll come back to this in a bit. 37 00:02:17,410 --> 00:02:21,640 But I'm not just pulling this stuff from nowhere, and I certainly don't have it memorized. 38 00:02:21,640 --> 00:02:23,710 I'm not just you know, I don't just know how it works. 39 00:02:23,710 --> 00:02:26,980 I'd say the approach is more, okay, I know what I need to build. 40 00:02:26,980 --> 00:02:28,660 I'm going to be building it with Ruby. 41 00:02:28,870 --> 00:02:33,730 Now let me find the Ruby, my SQL driver or a gem, which is what they're called. 42 00:02:33,730 --> 00:02:37,030 Instead of packages in Ruby, let me find my SQL gem. 43 00:02:37,030 --> 00:02:38,080 Is there a connector? 44 00:02:38,080 --> 00:02:38,920 And then you find it. 45 00:02:38,920 --> 00:02:42,490 And then you look through the documentation and you figure out how it works. 46 00:02:42,520 --> 00:02:43,930 Okay, how do I connect first? 47 00:02:43,930 --> 00:02:45,310 That's the first thing you need to do. 48 00:02:45,520 --> 00:02:47,620 And there's not one syntax. 49 00:02:47,650 --> 00:02:49,330 It varies from language to language. 50 00:02:49,330 --> 00:02:50,710 Somebody made a decision. 51 00:02:50,710 --> 00:02:57,580 So if we go back to our slides here, somebody made the decision to call this method query, which I 52 00:02:57,580 --> 00:03:04,090 mean, it makes sense to call it query, but it could have also been called make query or perform query 53 00:03:04,810 --> 00:03:09,120 or when we're creating the connection, my SQL create connection. 54 00:03:09,130 --> 00:03:10,930 I don't know if someone could have called this. 55 00:03:10,930 --> 00:03:17,440 This could be database name instead of database, but somebody making those calls and I'm not disagreeing 56 00:03:17,440 --> 00:03:22,390 with the way this is named in any way, but I'm saying you have to play by the rules of whoever defined 57 00:03:22,390 --> 00:03:22,750 this. 58 00:03:22,750 --> 00:03:27,220 Just like when we're working with my SQL, we have to use the my SQL syntax. 59 00:03:27,220 --> 00:03:29,200 There is a specific syntax. 60 00:03:29,200 --> 00:03:33,520 We have to learn the same thing for using these libraries. 61 00:03:33,520 --> 00:03:36,640 So it's not something that comes standard with JavaScript or with Node. 62 00:03:36,670 --> 00:03:38,620 It's something we had to plug in ourselves. 63 00:03:38,620 --> 00:03:41,890 And that means we need to go do the work to figure out how to use it. 64 00:03:43,030 --> 00:03:48,880 So next up, we're going to create our schema and it's pretty simple. 65 00:03:48,880 --> 00:03:51,250 All that we need are two fields. 66 00:03:51,250 --> 00:03:57,790 So we'll run create table users, we'll do email and a pretty standard length is 255. 67 00:03:57,790 --> 00:04:03,850 So not that an email is that long, but for fields in general, text fields people will do var cha two, 68 00:04:03,850 --> 00:04:06,970 five, five and we'll make it a primary key. 69 00:04:07,120 --> 00:04:08,470 Why do we want to do that? 70 00:04:08,770 --> 00:04:12,820 Basically, we don't want someone to be able to sign up twice with the same email address. 71 00:04:12,820 --> 00:04:18,490 Also, it gives us a couple more options for exercises, so it's a good thing to do and it's good practice. 72 00:04:18,940 --> 00:04:22,240 This is probably the first time we've seen primary key without auto increment. 73 00:04:22,600 --> 00:04:27,010 Then we've also got created at and that's going to be a timestamp. 74 00:04:27,010 --> 00:04:28,060 It could be a date time. 75 00:04:28,060 --> 00:04:34,420 But remember back to that video I did about the difference timestamp is smaller, it takes up less space 76 00:04:34,420 --> 00:04:41,200 in memory and it works just as well, except for really far away dates in the past and in the future. 77 00:04:41,590 --> 00:04:46,180 1970, up until 2038, I think it is, is the current range. 78 00:04:46,210 --> 00:04:50,440 We're not going to worry about that because we're dealing with current dates. 79 00:04:50,440 --> 00:04:54,010 You know, we're creating a mailing list app or a waitlist app people are setting up now. 80 00:04:54,010 --> 00:04:55,090 So we don't know. 81 00:04:55,090 --> 00:04:58,510 Nobody's entering a date from 1650 or something. 82 00:04:58,510 --> 00:05:04,060 Then we're setting the default value to B now so that when someone signs up, it will automatically 83 00:05:04,060 --> 00:05:05,890 be filled in as the current time. 84 00:05:05,890 --> 00:05:11,650 But don't forget, when we're working with Faker, we are going to insert like 500 users into this table 85 00:05:11,650 --> 00:05:19,450 and we are going to provide a created at time so that we don't get 500 users with the exact same time. 86 00:05:19,450 --> 00:05:23,770 So we want this default value to be there, but we'll also be able to override it manually if we want 87 00:05:23,770 --> 00:05:24,130 to. 88 00:05:24,160 --> 00:05:24,440 Okay. 89 00:05:24,490 --> 00:05:26,140 So let's hop over to Cloud nine. 90 00:05:26,140 --> 00:05:31,300 And the first thing I'm going to do close this down is just make a new file. 91 00:05:31,990 --> 00:05:34,420 I'm just going to call it schema SQL. 92 00:05:34,600 --> 00:05:38,200 And the only reason I'm doing that is just to have a record of this. 93 00:05:38,200 --> 00:05:43,840 You could just open up my SQL and start typing Create Table, but I'm going to do it in here, create 94 00:05:43,840 --> 00:05:45,850 table users. 95 00:05:47,200 --> 00:05:57,940 So we wanted email and then we wanted to create a that an email is var char 255 primary key and created 96 00:05:57,940 --> 00:06:05,080 that is timestamp would work just fine if you left it as a date time and we want default to be now. 97 00:06:05,080 --> 00:06:14,380 Or we could also say current timestamp just like that and we'll save we'll hop down to our terminal 98 00:06:14,380 --> 00:06:15,040 down here. 99 00:06:15,040 --> 00:06:22,930 I'm in the MySQL CLI and I'm just going to select database right now and see what we're working with. 100 00:06:22,930 --> 00:06:23,230 Good. 101 00:06:23,230 --> 00:06:24,880 We're in the join us database. 102 00:06:25,000 --> 00:06:26,860 So we made a new empty database. 103 00:06:26,860 --> 00:06:32,740 If we look at show tables, there's nothing here that's good. 104 00:06:33,220 --> 00:06:34,750 So we're going to create this. 105 00:06:34,750 --> 00:06:40,180 You can either copy it and paste it in or you can run the file, which I'll do just because it's been 106 00:06:40,180 --> 00:06:40,780 a while. 107 00:06:41,020 --> 00:06:43,180 Source Schema SQL. 108 00:06:45,220 --> 00:06:47,110 Now let's see if it works. 109 00:06:47,500 --> 00:06:48,970 Show tables this time. 110 00:06:49,150 --> 00:06:52,390 Now we have a user's table and we can describe it. 111 00:06:54,070 --> 00:06:54,700 Perfect.