WEBVTT 00:00.270 --> 00:07.480 OK so in the last video we got our Noad file javascript communicating with our minuscule database of 00:07.660 --> 00:12.810 being very very simple communication we're not actually interacting with tables but this is secret code 00:12.810 --> 00:13.670 that we're running. 00:13.890 --> 00:17.640 So the next step of course is getting it to interact with tables. 00:17.640 --> 00:22.980 So we're going to in this video we're going to create our table we're going to define our schema create 00:22.980 --> 00:30.500 our table in my as well separate from javascript and then we're going to try and query it from here. 00:30.510 --> 00:35.850 So basically we're going to go into my ask you Al Qaeda table let's add two or three users and then 00:35.940 --> 00:38.410 we're going to see if we can fetch them from node. 00:38.640 --> 00:39.770 But before we get there. 00:39.930 --> 00:45.060 One thing I want to address very briefly is when we're looking at this code you know this connection 00:45.060 --> 00:51.210 like queery function error results fields or this code appear far connection equals blah blah blah blah 00:51.210 --> 00:51.870 blah. 00:52.080 --> 00:55.700 You might be wondering how did you know what code to type. 00:55.740 --> 00:56.900 Where did I find this. 00:56.910 --> 00:59.230 It's not in the Maia's QOL documentation. 00:59.250 --> 01:01.210 This is not an official Maya. 01:01.210 --> 01:10.080 Q Well sanctioned library basically the way that it works is that random developers who teams developers 01:10.140 --> 01:17.280 solo developers go and create these libraries that connect my as well to other languages. 01:17.430 --> 01:19.470 Some of them are more supportive than others. 01:19.470 --> 01:25.290 Like I said some of them are just one person but they usually have their own documentation so the documentation 01:25.290 --> 01:29.120 for this is on get home. 01:29.180 --> 01:31.060 It's good how about com slash my s. 01:31.080 --> 01:33.370 Q LJ s slash my ask. 01:33.420 --> 01:35.500 Well there goes my fridge again. 01:35.910 --> 01:37.210 Very active today. 01:37.650 --> 01:42.750 But if we scroll down you can see well there's a lot of stuff to take a look at but it basically talks 01:42.750 --> 01:48.550 about installing it talks about setting up your connection talks. 01:48.630 --> 01:52.900 Then if you keep going down about once you've established a connection what can you do. 01:52.920 --> 01:54.870 Well here's a very simple query. 01:55.080 --> 01:59.160 We saw something very similar although this is just selecting one. 01:59.310 --> 02:03.180 We did something a little more complicated but if we keep going. 02:03.180 --> 02:09.840 Eventually you'll see we get things about queery which is where we're going just a little bit. 02:09.930 --> 02:13.680 But basically I want to show you there is a source of documentation. 02:13.680 --> 02:15.780 So here we talked about some queries how it works. 02:15.810 --> 02:20.430 We'll come we'll come back to this in a bit but I'm not just pulling this stuff from nowhere and I certainly 02:20.700 --> 02:21.610 don't have it memorized. 02:21.600 --> 02:23.730 I'm not just you know I don't just know how it works. 02:23.730 --> 02:27.030 I'd say the approach is more OK I know what I need to build. 02:27.030 --> 02:28.980 I'm going to be building it with Ruby. 02:29.060 --> 02:30.680 Now let me find the ruby. 02:30.750 --> 02:35.260 My SUV driver or a gem which is what they're called instead of packages in Ruby. 02:35.280 --> 02:36.260 I may find in my ask. 02:36.280 --> 02:38.210 Well Jim Zera connector. 02:38.220 --> 02:42.450 And then you find and then you look through the documentation and you figure out how it works. 02:42.550 --> 02:43.930 OK how do I connect first. 02:43.980 --> 02:45.320 That's the first thing you need to do. 02:45.690 --> 02:47.640 And there's not one syntax. 02:47.640 --> 02:49.440 It varies from language to language. 02:49.440 --> 02:50.840 Somebody made a decision. 02:50.850 --> 02:57.210 So if we go back to our slides here you know somebody made the decision to call this method queery which 02:57.480 --> 03:03.870 I mean it makes sense to call it queery but it could have also been called Make query or perform query 03:04.890 --> 03:07.180 or when creating the connection. 03:07.200 --> 03:09.120 May I ask you all that create connection. 03:09.210 --> 03:14.210 I don't know if someone could have called this this could be database name instead of database but somebody 03:14.220 --> 03:15.830 is making those calls. 03:16.170 --> 03:21.060 And I'm not disagreeing with the way this is named in any way but I'm saying you have to play by the 03:21.060 --> 03:22.810 rules of whoever defined this. 03:22.860 --> 03:27.330 Just like when we're working with my ask you Will we have to use them I ask you all syntax. 03:27.330 --> 03:30.480 There is a specific syntax we have to learn. 03:30.480 --> 03:33.450 Same thing for using these libraries. 03:33.600 --> 03:36.610 So it's not something that comes standard with javascript or with code. 03:36.810 --> 03:38.590 It's something we had to plug in ourselves. 03:38.670 --> 03:41.970 And that means we need to go do the work to figure out how to use it. 03:42.330 --> 03:42.940 OK. 03:43.170 --> 03:46.370 So next up we're going to create our schema. 03:47.340 --> 03:48.900 And it's pretty simple. 03:48.900 --> 03:51.350 All that we need are two fields. 03:51.450 --> 03:57.780 So we'll run create table users will do email and a pretty standard length is 255. 03:57.780 --> 04:05.550 So not that an email is that long but for fields in general textfield people will do Vardar to 5:5 and 04:05.610 --> 04:07.240 we'll make it a primary key. 04:07.290 --> 04:08.860 Why do we want to do that. 04:08.880 --> 04:12.880 Basically we don't want someone to be able to sign up twice with the same email address. 04:12.900 --> 04:18.960 Also it gives us a couple more options for exercises so it's a good thing to do and it's good practice. 04:19.020 --> 04:24.280 This is probably the first time we've seen primary key without autoincrement then we've also got created 04:24.310 --> 04:26.960 out and that's going to be a time stamp. 04:27.000 --> 04:28.040 It could be a date time. 04:28.060 --> 04:33.410 But remember back to that video we did about the difference time stamp is smaller. 04:33.420 --> 04:40.220 It takes up less space and memory and it works just as well except for really far away data in the past. 04:40.260 --> 04:46.240 And in the future 1970 up until when he 38 I think it is is the current range. 04:46.410 --> 04:50.470 We're not going to worry about that because we're dealing with current dates. 04:50.490 --> 04:54.940 You know we're creating a mailing list app or a waitlist app people are setting up now so we don't you 04:54.970 --> 05:01.730 know nobody's entering a date from 650 or something then we're setting the default value to be now so 05:01.730 --> 05:05.800 that when someone signs up it will automatically be filled in as the current time. 05:06.020 --> 05:11.510 But don't forget when we're working with F.A. we are going to insert like 500 users into this table 05:11.810 --> 05:18.740 and we are going to provide a created at a time so that we don't get you know 500 users with the exact 05:18.740 --> 05:19.420 same time. 05:19.520 --> 05:23.750 So we want this default value to be there but we'll also be able to overwrite it manually if we want 05:23.750 --> 05:24.050 to. 05:24.230 --> 05:30.560 OK so let's hop over to cloud 9 and the first thing I'm going to do close this down is just make a new 05:30.560 --> 05:31.880 file. 05:32.060 --> 05:34.820 I'm just going to call it schema data as well. 05:34.820 --> 05:38.150 And the only reason I'm doing that is just to have a record of this. 05:38.150 --> 05:41.390 You could just open up my ask out and start typing. 05:41.420 --> 05:42.260 Create table. 05:42.400 --> 05:43.350 I'm going to do it in here. 05:43.490 --> 05:46.120 Create table users. 05:46.670 --> 05:47.090 OK. 05:47.330 --> 05:57.950 So we wanted email and then we wanted create it at an email is Vardar to 5:5 primary key and created 05:57.950 --> 06:05.810 at is time stamp would work just fine if you left a date time and we want default to be now or we could 06:05.810 --> 06:07.890 also say current time stamp. 06:08.420 --> 06:11.990 OK just like that and we'll save. 06:12.770 --> 06:15.100 We'll hop down to our terminal down here. 06:15.130 --> 06:18.040 I'm in the my ask you well CLIA. 06:18.320 --> 06:25.190 And I'm just going to select database right now and see what we're working with to in the JOIN US database. 06:25.190 --> 06:26.870 So we made a new empty database. 06:26.870 --> 06:33.090 If we look at you know show tables there's nothing here that's correct. 06:33.290 --> 06:34.720 So we're going to create this. 06:34.870 --> 06:40.580 You can either copy and pasted in or you can run the file which I'll do just because it's been a while 06:41.150 --> 06:43.730 source schema data as well. 06:44.540 --> 06:45.230 OK. 06:45.410 --> 06:47.360 Now let's see if it works. 06:47.420 --> 06:49.340 Show tables this time. 06:49.340 --> 06:54.560 Now we have a users table and we can describe it perfect.