1 00:00:00,920 --> 00:00:06,920 So let's create some migrations and to do that, the first thing I want to do is go back to the sort 2 00:00:06,980 --> 00:00:10,490 documentation and see how this works. 3 00:00:10,520 --> 00:00:14,680 So I want to create databases such as click on and see what happens. 4 00:00:15,830 --> 00:00:17,690 So I'm not going to create a database this way. 5 00:00:17,720 --> 00:00:21,710 What I'm going to do, first of all, is make sure that I have postgrads running. 6 00:00:21,710 --> 00:00:22,400 I do. 7 00:00:22,400 --> 00:00:26,630 And then I'm going to open Beevor and just clear out anything that's in there right now. 8 00:00:26,630 --> 00:00:30,680 So I know I'm starting with an empty database, a database that has no tables. 9 00:00:32,330 --> 00:00:34,100 So I am on here. 10 00:00:34,100 --> 00:00:35,380 I want to go to. 11 00:00:35,390 --> 00:00:35,810 Yeah, yeah. 12 00:00:35,810 --> 00:00:37,130 There's a new I'll upgrade later. 13 00:00:38,150 --> 00:00:41,900 I want to look and see what's inside my bookings database. 14 00:00:41,930 --> 00:00:50,150 So I click on that and I look at it and I have under my schemas, under my public, under tables, I 15 00:00:50,150 --> 00:00:52,700 have, I have three tables and I'm going to get rid of all of this. 16 00:00:52,700 --> 00:00:54,680 So I'll right click on this and choose. 17 00:00:54,680 --> 00:00:55,180 Delete. 18 00:00:57,020 --> 00:00:57,590 Yes. 19 00:00:58,730 --> 00:01:00,200 And the same thing with people. 20 00:01:00,200 --> 00:01:01,160 I will delete that. 21 00:01:02,000 --> 00:01:02,630 Yes. 22 00:01:03,140 --> 00:01:04,490 And the same thing with fonts. 23 00:01:04,490 --> 00:01:05,420 I will delete that. 24 00:01:06,240 --> 00:01:06,830 Yes. 25 00:01:07,070 --> 00:01:07,540 All right. 26 00:01:07,550 --> 00:01:09,660 So I now have an empty database. 27 00:01:09,680 --> 00:01:10,030 Great. 28 00:01:10,040 --> 00:01:13,760 So let's Heidi Dvor and see what we do here. 29 00:01:14,420 --> 00:01:16,910 How do we start working with us, getting started with pop. 30 00:01:16,910 --> 00:01:17,800 That's where I want to be. 31 00:01:18,560 --> 00:01:21,050 So gives me some documentation. 32 00:01:21,050 --> 00:01:22,220 Supports postgrads. 33 00:01:22,220 --> 00:01:22,610 Good. 34 00:01:22,610 --> 00:01:23,720 We have it installed. 35 00:01:23,720 --> 00:01:24,140 Good. 36 00:01:24,560 --> 00:01:25,040 All right. 37 00:01:25,040 --> 00:01:26,240 Let's go to configuration. 38 00:01:27,260 --> 00:01:28,580 And this is what we need to do. 39 00:01:28,580 --> 00:01:32,930 We need to actually create a configuration file for our database. 40 00:01:33,290 --> 00:01:34,610 So I'm going to copy this. 41 00:01:35,090 --> 00:01:43,460 And this needs to exist as a file copy under my code, right at the root level of my application. 42 00:01:43,460 --> 00:01:50,030 I'll create a new file and I will call that database dot y y Emelle. 43 00:01:50,960 --> 00:01:53,960 And I'll just I'm not going to add that to get and I'll tell you why later on. 44 00:01:54,440 --> 00:01:55,480 I'm going to pass that in there. 45 00:01:56,030 --> 00:02:02,180 So this describes how soda connects to our database and our dialect is postgrads. 46 00:02:02,180 --> 00:02:02,750 That's great. 47 00:02:03,110 --> 00:02:07,580 Our database name is actually called bookings I think. 48 00:02:09,770 --> 00:02:10,520 Let's make sure. 49 00:02:11,670 --> 00:02:12,540 Yep, bookings. 50 00:02:14,670 --> 00:02:16,750 And the user is my name. 51 00:02:17,160 --> 00:02:22,110 Your username will be your name, of course, and it has no password, so I'll leave that empty hostis 52 00:02:22,110 --> 00:02:22,740 localhost. 53 00:02:22,740 --> 00:02:25,040 That's one two seven zero zero one. 54 00:02:25,050 --> 00:02:25,980 That's an IP address. 55 00:02:25,980 --> 00:02:28,860 That is the Lookback address and always refers to the current machine. 56 00:02:29,310 --> 00:02:30,260 And the pool is five. 57 00:02:30,270 --> 00:02:31,620 And that's all I care about right now. 58 00:02:31,650 --> 00:02:33,500 I'm not going to worry about test or production. 59 00:02:33,660 --> 00:02:35,300 I just want this stuff set up. 60 00:02:35,700 --> 00:02:42,450 So once this is created, I now can open a terminal window and clear out the screen and I can write 61 00:02:42,450 --> 00:02:49,020 SOTA generate and I'm going to put the word filles because that's the language used to describe the 62 00:02:49,020 --> 00:02:56,880 migration's used by SOTA and I'll create our users table, so I'll name my migration create user with 63 00:02:56,880 --> 00:03:00,050 uppercase U table. 64 00:03:00,810 --> 00:03:05,580 And when I do that it says step change to your file file. 65 00:03:05,580 --> 00:03:12,390 What it's done for me and I'll just hide this screen is this created a new directory called Migration's 66 00:03:12,540 --> 00:03:12,960 right here. 67 00:03:13,950 --> 00:03:16,380 And inside of that it's created two new files. 68 00:03:16,620 --> 00:03:19,740 One is called Create User Table Down. 69 00:03:19,740 --> 00:03:24,000 That is the down migration and one is called create user table up. 70 00:03:24,000 --> 00:03:25,230 And that's the up migration. 71 00:03:25,230 --> 00:03:26,220 So let's open that one. 72 00:03:26,370 --> 00:03:27,720 And that is an empty file. 73 00:03:28,110 --> 00:03:29,820 And I don't want it to remain empty. 74 00:03:29,940 --> 00:03:31,530 I actually want to do something with it. 75 00:03:33,910 --> 00:03:42,400 And this file is where I can create a table and I think back to the documentation for soda, when you 76 00:03:42,400 --> 00:03:47,980 look at things like Migration's right here, I want to create a migration's writing on migration. 77 00:03:47,980 --> 00:03:50,770 So it says it tells me to do exactly what I just did. 78 00:03:51,010 --> 00:03:53,530 So to generate this the name of the migration. 79 00:03:53,800 --> 00:03:55,300 And then it creates two files. 80 00:03:55,330 --> 00:03:56,230 They're both empty. 81 00:03:56,230 --> 00:03:58,450 And I need to populate them with the things that I want. 82 00:03:58,600 --> 00:04:01,000 I can use straight sequel migrations. 83 00:04:01,000 --> 00:04:04,270 I can write a sequel by hand if I want to, but I'm not going to. 84 00:04:04,270 --> 00:04:05,850 And I'll tell you why a little later on. 85 00:04:06,760 --> 00:04:08,200 So let's look at example. 86 00:04:08,200 --> 00:04:12,340 Let's look at an example of creating a fiz migration. 87 00:04:12,340 --> 00:04:15,420 I want to create a table and here's an example. 88 00:04:15,430 --> 00:04:23,170 So let's copy this one, because it's a great example and I'll paste it in to my my empty up migration 89 00:04:23,170 --> 00:04:25,240 and make sure you have the up migration. 90 00:04:26,170 --> 00:04:27,820 And I'm going to create a table users. 91 00:04:27,820 --> 00:04:32,730 And I want this table to correspond to what I built when I described it. 92 00:04:33,010 --> 00:04:34,090 So I have an idea. 93 00:04:34,090 --> 00:04:35,800 First name and a last name in an email. 94 00:04:35,830 --> 00:04:38,980 Let's do those four to start with Idy. 95 00:04:39,790 --> 00:04:41,230 I'll manually put that in there. 96 00:04:41,230 --> 00:04:45,910 I don't have to, but I'm going to tag column, Heidi. 97 00:04:46,630 --> 00:04:47,920 It's going to be an integer. 98 00:04:50,200 --> 00:05:00,100 And it will be primary true and what that does is say to it says Tiffy's, that this column Idy will 99 00:05:00,100 --> 00:05:03,250 be of type integer and it's going to be my primary key. 100 00:05:03,400 --> 00:05:09,250 OK, and the primary key is that ID field we used, what we used, what we described, our database 101 00:05:09,250 --> 00:05:09,710 structure. 102 00:05:10,570 --> 00:05:11,650 So what was the second thing? 103 00:05:11,650 --> 00:05:14,610 I wanted to think it was first name, first name, last name and email. 104 00:05:14,740 --> 00:05:16,030 So we already have an email. 105 00:05:16,030 --> 00:05:23,580 Let's just duplicate that twice and changes to first name and change this to last name. 106 00:05:24,880 --> 00:05:26,350 And it's that all that I want in there. 107 00:05:26,380 --> 00:05:28,150 Well, no, in this case it's not. 108 00:05:28,150 --> 00:05:30,460 I'm actually going to put something in these parentheses. 109 00:05:30,460 --> 00:05:33,970 I want I'm going to say is the default value for this. 110 00:05:33,970 --> 00:05:36,010 If nothing is specified, make that empty. 111 00:05:36,370 --> 00:05:37,620 I'll do the same thing here. 112 00:05:38,140 --> 00:05:44,700 Default, which has to be, in quotes, default, empty for email. 113 00:05:45,070 --> 00:05:49,420 I don't want the email to be empty, so I'm going to leave that one as it is. 114 00:05:49,840 --> 00:05:51,400 And what else do we have after email? 115 00:05:52,000 --> 00:05:54,580 We have password created out and updated that. 116 00:05:55,560 --> 00:06:01,530 So password, I had Barkha 255 here, but that's actually not what I'm going to use. 117 00:06:01,830 --> 00:06:03,520 I'm going to make that a different length. 118 00:06:03,550 --> 00:06:07,230 So let me go back here and change this to password. 119 00:06:08,460 --> 00:06:15,210 It is of type string, but its size is going to be 60 and it's going to be 60 because the hashes we 120 00:06:15,210 --> 00:06:20,440 generate instead of the password are always going to be exactly 60 characters long. 121 00:06:20,460 --> 00:06:22,170 So make that one size of 60. 122 00:06:22,890 --> 00:06:23,770 So that's password. 123 00:06:23,790 --> 00:06:24,650 What else do we have? 124 00:06:25,650 --> 00:06:28,560 We have created and updated at an access level. 125 00:06:28,740 --> 00:06:39,900 OK, so we can leave, we can delete all of these and we can say access level and that will be an integer 126 00:06:39,900 --> 00:06:41,400 and the default I'll make one. 127 00:06:42,030 --> 00:06:47,460 And I'm not going to bother with created at an update about because one of the nice features of soda 128 00:06:47,760 --> 00:06:50,730 is that it actually creates those columns for us. 129 00:06:50,910 --> 00:06:51,290 All right. 130 00:06:51,690 --> 00:06:53,130 So I've got this file created. 131 00:06:53,460 --> 00:06:55,320 I seem to be connected to my database. 132 00:06:55,320 --> 00:06:57,930 Everything set up properly in my database YAML. 133 00:06:58,140 --> 00:06:59,340 Let's find out if it works. 134 00:06:59,520 --> 00:07:06,000 So all I have to do is make sure I'm in the root level of my project and say, soda migrate. 135 00:07:07,490 --> 00:07:12,980 And it says, all right, it said, created the user table, successfully applied one migration's the 136 00:07:12,980 --> 00:07:18,650 info is the length of time it took and then it says server version is thirteen point one PPG dump version 137 00:07:18,650 --> 00:07:19,570 is twelve point three. 138 00:07:19,730 --> 00:07:27,020 All it's telling me is that the client I have for PGP dump is a different version than the client that 139 00:07:27,590 --> 00:07:28,630 the server is using. 140 00:07:28,640 --> 00:07:33,950 And that's just because I have two versions of postgrads installed, one for version twelve point three 141 00:07:33,950 --> 00:07:35,600 and one for version thirteen point one. 142 00:07:35,720 --> 00:07:37,880 That is an error I can safely ignore. 143 00:07:38,990 --> 00:07:41,750 Now, what I have done this I've run this migration. 144 00:07:41,750 --> 00:07:47,810 Let's go over to divert and let's refresh our public schema by clicking on it and choosing refresh. 145 00:07:47,990 --> 00:07:52,400 And we should see two tables, one that we just created called user right here. 146 00:07:52,430 --> 00:07:57,020 And there is user and I can look at the properties for columns the users has. 147 00:07:57,020 --> 00:07:58,280 What let's refresh this. 148 00:07:58,280 --> 00:07:58,940 They're there. 149 00:07:59,510 --> 00:08:04,610 There's my first my ID, my first name, my last name, my email, my password access level. 150 00:08:04,610 --> 00:08:06,950 And it automatically created those columns for us. 151 00:08:07,100 --> 00:08:07,640 Perfect. 152 00:08:08,000 --> 00:08:10,410 So what's this table schema migration? 153 00:08:10,490 --> 00:08:11,150 Well, let's look at it. 154 00:08:12,140 --> 00:08:13,700 This is one that SOTA creates. 155 00:08:13,700 --> 00:08:17,090 If it doesn't exist the very first time it runs, it creates for you. 156 00:08:17,300 --> 00:08:23,180 And if we look under data, you'll see that it has one one field called version. 157 00:08:23,600 --> 00:08:25,220 And inside of that is a number. 158 00:08:25,550 --> 00:08:33,710 And that number 20, 2011, and all those numbers following it, it corresponds to what you see in this 159 00:08:33,740 --> 00:08:36,230 migration's folder, that number right there. 160 00:08:36,320 --> 00:08:43,040 And that allows allows the sort of client to know the sort of program to know that migration has run. 161 00:08:43,550 --> 00:08:44,920 Well, that's great. 162 00:08:45,410 --> 00:08:48,470 What is the purpose of that down migration? 163 00:08:48,740 --> 00:08:49,760 Let's have a look at that. 164 00:08:50,780 --> 00:08:52,940 Inside of the down migration right here. 165 00:08:52,940 --> 00:08:53,900 We currently have nothing. 166 00:08:53,900 --> 00:09:01,430 And here's where you write the code to reverse the migration so that migration creates a user table 167 00:09:01,910 --> 00:09:05,540 and the down migration is simply going to drop that table. 168 00:09:05,540 --> 00:09:12,470 So I can actually run arbitrary sequel by putting the key word sequel and then in quotations, whatever 169 00:09:12,470 --> 00:09:16,130 sequel I want to run, I want to drop the table users. 170 00:09:16,910 --> 00:09:18,410 And that's all that I have to do. 171 00:09:19,010 --> 00:09:23,450 So now if I come back here and I'll continue to get these warnings, I'll fix it. 172 00:09:23,450 --> 00:09:27,230 After this election, I'll upgrade my PJI dump version so we don't see that error in the future. 173 00:09:27,230 --> 00:09:28,250 But right now, just ignore it. 174 00:09:28,460 --> 00:09:36,890 I can say sota migrate down and it will run the down script, which says drop the table users. 175 00:09:36,890 --> 00:09:45,530 So if I go back to deliver and refresh my public schema, refresh that users table is now gone and if 176 00:09:45,530 --> 00:09:51,720 I run, so to migrate, so to migrate again, it will create the user's table. 177 00:09:51,750 --> 00:09:55,070 So now if I refresh this in here, refresh. 178 00:09:56,850 --> 00:10:03,000 There's my users table, so the great thing about this is I can write all of the migration's for all 179 00:10:03,000 --> 00:10:09,690 of my tables and rebuild my database, my development database right from scratch. 180 00:10:09,690 --> 00:10:14,610 I can say drop everything by running all the down migration's and run up run all the migration's just 181 00:10:14,610 --> 00:10:18,240 by saying migrate up and it will recreate my database structure. 182 00:10:18,600 --> 00:10:24,480 And as I modified my database, as time goes on, I can just write Neumont a new migration's to add 183 00:10:24,480 --> 00:10:27,870 a column, delete a column, change your columns, type, whatever it may be. 184 00:10:28,140 --> 00:10:33,150 So this will be a really useful way for us to to manage our database structure. 185 00:10:33,930 --> 00:10:35,340 Now a word about git. 186 00:10:35,760 --> 00:10:42,630 I am never, ever going to commit this file database Whitemoor to get instead. 187 00:10:42,750 --> 00:10:43,700 I will copy this. 188 00:10:43,900 --> 00:10:50,760 I'll say copy all of this, this contents of that file and I'll create a new file under bookings called 189 00:10:50,760 --> 00:10:57,960 New File and I'll call it database dot y database, the y amount dot example. 190 00:10:59,190 --> 00:11:05,790 And I will add that one together and I will paste this in and I'll take out the username because you 191 00:11:05,790 --> 00:11:09,600 never want to start a password and get and in this case this is an example. 192 00:11:09,610 --> 00:11:16,350 So if I was to clone this repository, all I would do is copy database dot y melda example to database 193 00:11:16,770 --> 00:11:20,490 AML and put in my username and password and database name and everything else. 194 00:11:21,240 --> 00:11:27,690 So don't ever store passwords into git, particularly on a public git repository, because you know, 195 00:11:27,720 --> 00:11:32,140 that might be a potential method for some attacker to get access to your database. 196 00:11:32,700 --> 00:11:41,820 So what I'm going to do now is just go to my getting your file Dugit, ignore and add database y AML. 197 00:11:44,520 --> 00:11:51,810 To my getting Nawfal, and that way it will never add add that file by mistake to get so what we'll 198 00:11:51,810 --> 00:11:55,920 do in the next lecture is right, migration's for all of the other tables that we're going to be using.