1 00:00:00,840 --> 00:00:06,480 So it's time to figure out how to connect our go code to a database, and we're going to do that not 2 00:00:06,480 --> 00:00:08,430 in our bookings project just yet. 3 00:00:08,460 --> 00:00:13,740 Instead, I want to do it in a very small standalone program that just shows us how everything works. 4 00:00:13,740 --> 00:00:19,050 And once we figure that out, then we'll implement those changes or implement that kind of code in our 5 00:00:19,050 --> 00:00:19,830 bookings project. 6 00:00:20,010 --> 00:00:21,070 So we're going to make that happen. 7 00:00:21,090 --> 00:00:25,470 I'm going to create a new database and I'm going to use post code just because I like it better than 8 00:00:25,480 --> 00:00:26,000 ever. 9 00:00:26,190 --> 00:00:28,410 But you can use the same kind of logic in. 10 00:00:28,920 --> 00:00:33,810 So I'm creating a new database, which I will call test, underscore, connect, and I'll open that 11 00:00:33,810 --> 00:00:36,720 database and I'll add one table in there. 12 00:00:36,720 --> 00:00:43,200 And the table is going to be called users and it will have an idea type serial, which postcode helpfully 13 00:00:43,200 --> 00:00:44,220 gives me by itself. 14 00:00:44,700 --> 00:00:50,420 But I'll add a column called First Name, First Underscore Name All lowercase. 15 00:00:50,820 --> 00:01:00,300 It will be of type Verkerk and I'll add another one called Last Name, and there will also be a marker 16 00:01:01,590 --> 00:01:02,910 and I will save those changes. 17 00:01:04,530 --> 00:01:09,150 So of course you're probably doing this using Beever, but I like this one better and I'm going to use 18 00:01:09,150 --> 00:01:10,590 it whenever I possibly can. 19 00:01:10,860 --> 00:01:15,990 So I've created this database and that's all I want to do in Postdoc or Endevour. 20 00:01:16,530 --> 00:01:20,790 Now I'm going to start up and I'm going to use Visual Studio Code because it's been a while since I've 21 00:01:20,790 --> 00:01:21,180 used it. 22 00:01:21,690 --> 00:01:27,030 And I will open this up and I will close this window and create a new project. 23 00:01:27,030 --> 00:01:28,330 So I'll create a new folder. 24 00:01:30,180 --> 00:01:34,800 Let me close this altogether and create a new window. 25 00:01:35,760 --> 00:01:43,620 And I have no open files or folders, so I have to open one of those and I want to open a folder and 26 00:01:43,620 --> 00:01:50,740 I'll create it in my visual studio code documents, which are here somewhere near there. 27 00:01:51,600 --> 00:01:54,790 And I will create a new folder called Test Connector. 28 00:01:56,070 --> 00:02:02,580 OK, so now I've created this file or created this this empty folder and I have no files open at all. 29 00:02:02,580 --> 00:02:03,780 So let's create a new file. 30 00:02:03,870 --> 00:02:09,720 And I will do that by looking at my explorer right here, going to test Kinect and creating a new file. 31 00:02:09,720 --> 00:02:12,840 And I will call that main go. 32 00:02:14,220 --> 00:02:20,520 Then I'll open my terminal window and I'm right here, my terminal, and I will create a go modifier 33 00:02:20,520 --> 00:02:22,590 by saying go mode in it. 34 00:02:23,340 --> 00:02:31,710 GitHub, dotcom, tesol test, underscore, connect just to create a go mod file. 35 00:02:31,830 --> 00:02:37,950 OK, so I have this document, I have my terminal window open which I'm going to close and let's create 36 00:02:38,130 --> 00:02:39,300 a simple go file. 37 00:02:40,170 --> 00:02:41,730 So package main 38 00:02:44,550 --> 00:02:50,220 and it has a main func func main and I'm going to do stuff in there and let's think about the things 39 00:02:50,220 --> 00:02:50,820 we want to do. 40 00:02:51,090 --> 00:02:57,630 The very first thing I want to do is to connect to a database and after I've connected to it, I want 41 00:02:57,630 --> 00:02:59,310 to test my connection. 42 00:02:59,760 --> 00:03:04,800 I'm going to put comments here and we'll write the code one one step at a time after I've connected 43 00:03:04,800 --> 00:03:12,330 to my database and after I've tested my connection, I want to see what's in that database. 44 00:03:12,330 --> 00:03:14,910 So get rows from table. 45 00:03:15,300 --> 00:03:16,050 We have a database. 46 00:03:16,050 --> 00:03:18,440 There's no rows in there, but we'll put some in in a moment. 47 00:03:18,480 --> 00:03:30,410 OK, then I want to insert a row, then I want to display do the get rows from the table again. 48 00:03:31,110 --> 00:03:34,650 So my goal here, first of all, is to connect to the database, which we haven't done yet. 49 00:03:34,650 --> 00:03:38,550 But we will then I want to test my connection to make sure it's actually active. 50 00:03:38,550 --> 00:03:45,270 I'm going to ping the database, then I want to display all the rows from a table which will add momentarily. 51 00:03:45,280 --> 00:03:46,800 So we'll get a couple of rows from the table. 52 00:03:47,310 --> 00:03:52,740 Then I want to insert a row, then get all the rows from the table again, which would show whatever 53 00:03:52,740 --> 00:03:55,140 rows we had here, plus the one that we added. 54 00:03:55,290 --> 00:04:04,440 OK, after we've done that, I want to update a row and then I want to once again get the rows from 55 00:04:04,830 --> 00:04:08,990 the table again, which should show our changed row. 56 00:04:09,870 --> 00:04:18,020 Then after that, I want to get one row by Edet, OK? 57 00:04:18,180 --> 00:04:25,120 And after that I want to delete a row and then I want to get the rows again. 58 00:04:25,830 --> 00:04:31,560 So what we're doing here is basic crud operations create, read, update and delete. 59 00:04:31,920 --> 00:04:38,850 And once I can do all of those things in this simple program, then I should be able to do that in our 60 00:04:38,850 --> 00:04:42,150 bookings program because the basic logic will all be right here. 61 00:04:42,480 --> 00:04:46,080 And you might want to save this file just as a reference to see how things are done. 62 00:04:46,560 --> 00:04:50,540 But we're going to be doing it frequently enough that you will become ever so used to it. 63 00:04:50,790 --> 00:04:53,720 So the first first step is to connect to a database. 64 00:04:53,940 --> 00:04:54,870 How am I going to do that? 65 00:04:54,870 --> 00:04:59,700 Well, in Go, there are a couple of packages that allow you to connect to postscripts. 66 00:05:00,150 --> 00:05:05,700 There's one called Libit PKU, and it's fine, I've used it in the past, it works quite well. 67 00:05:06,210 --> 00:05:07,930 And there's another one called Zhaxi. 68 00:05:09,660 --> 00:05:15,960 And interestingly enough, the author of Leyb PKU recently posted in a response to a question and one 69 00:05:15,960 --> 00:05:18,630 of the go forums, you know, which package should I use? 70 00:05:18,760 --> 00:05:23,400 And the author of Lempicka says, I will actually use Jacky's package because, you know, it works 71 00:05:23,400 --> 00:05:25,410 really well and it is a good package. 72 00:05:25,410 --> 00:05:27,240 And the URL for it is right here. 73 00:05:27,240 --> 00:05:30,510 And I'll put a link to this in the course resources page, if I remember too. 74 00:05:31,170 --> 00:05:32,550 But if I don't, it's right there. 75 00:05:32,550 --> 00:05:35,880 GitHub, dotcom slash jack slash p.g x. 76 00:05:36,480 --> 00:05:37,740 So we need to install this. 77 00:05:37,770 --> 00:05:42,960 So I'm going to copy the GitHub part of the euro everything but not including the https. 78 00:05:43,470 --> 00:05:45,060 And I'll go back to my terminal. 79 00:05:47,030 --> 00:05:54,110 Open it up and say, go get and I'm going to put V4 at the end of it, which will get the latest version, 80 00:05:54,120 --> 00:05:55,430 I think it's before is the latest. 81 00:05:56,000 --> 00:05:56,660 Yeah, it is. 82 00:05:56,720 --> 00:06:00,290 OK, so V4 and installing it will go get it. 83 00:06:02,120 --> 00:06:04,850 Update's might go mod file and it's there now. 84 00:06:04,850 --> 00:06:06,200 I need to connect to my database. 85 00:06:06,260 --> 00:06:14,150 So in order to do that I need to actually give a certain instruction to this, this program. 86 00:06:14,390 --> 00:06:19,280 So very first thing I'm going to do is say Köln, which will be my database connection pool which I'll 87 00:06:19,280 --> 00:06:24,080 talk about in a minute, possibly an hour equals sequel DOT Open. 88 00:06:24,650 --> 00:06:32,140 So it imports database SQL for me and I want to open of type PJI X, which is right out of the documentation 89 00:06:32,140 --> 00:06:36,820 and you can see that in his example file down here, PJI Connect, he's actually using a different one. 90 00:06:36,830 --> 00:06:38,190 Let's go to the Getting Started guide. 91 00:06:39,030 --> 00:06:45,080 OK, so how to connect world from postgrads p.g connect context background. 92 00:06:45,080 --> 00:06:49,100 I want to use the sequel Connect which is. 93 00:06:49,370 --> 00:06:50,180 Let's see. 94 00:06:52,020 --> 00:06:53,580 Databasing, this is the one I want. 95 00:06:53,610 --> 00:06:58,760 He has a database specific driver for postgrads. 96 00:06:58,770 --> 00:06:59,630 We're not going to use that. 97 00:06:59,640 --> 00:07:05,820 We're going to use the default sequel package that's part of go just to keep things as close to Pergo 98 00:07:05,820 --> 00:07:06,850 as we possibly can. 99 00:07:07,500 --> 00:07:11,160 So he gives us an example here of how to initiate a project and how to connect to it. 100 00:07:11,170 --> 00:07:12,510 And this is what we're doing right here. 101 00:07:12,540 --> 00:07:14,490 OK, so let's go back here. 102 00:07:14,490 --> 00:07:20,580 And our type is PGS and then we need to have a connection string and the connection string for postgrads 103 00:07:20,580 --> 00:07:21,300 is dead. 104 00:07:21,300 --> 00:07:22,860 Simple, specify host. 105 00:07:23,040 --> 00:07:27,200 Our host is equal to localhost because our database is right on this machine. 106 00:07:27,510 --> 00:07:30,960 If you were connecting to a remote machine, you'd have a different link there. 107 00:07:31,230 --> 00:07:36,810 But we're just using our localhost port is equal to five, four, three, two, which is the default 108 00:07:36,810 --> 00:07:39,090 port for progress for postgrads. 109 00:07:39,480 --> 00:07:43,980 The database name well we named ours Kest underscore connect. 110 00:07:44,760 --> 00:07:48,120 The user in my case is Tick's, which is my username. 111 00:07:48,120 --> 00:07:52,440 You'll specify your own username there and the password in my case is empty. 112 00:07:52,440 --> 00:07:54,240 And in your case it probably is too. 113 00:07:54,450 --> 00:07:56,960 But if you have a password you would specify it there. 114 00:07:57,510 --> 00:08:01,860 So that will give us a connection pool and potentially add an error. 115 00:08:02,040 --> 00:08:05,760 So we'd better check for an error if error is not equal to nil. 116 00:08:06,170 --> 00:08:07,020 What am I going to do here? 117 00:08:07,030 --> 00:08:08,970 I will just say log fatele. 118 00:08:08,970 --> 00:08:13,290 I can't go any further, so I may as well die and I will print a meaningful message. 119 00:08:13,290 --> 00:08:27,480 Their format s print F and I'll put it in the message unable to connect to and then I'll put my error 120 00:08:27,480 --> 00:08:28,220 message in there. 121 00:08:31,510 --> 00:08:37,360 Which I'll just and unable to connect and I'll put my error message, which will be a placeholder V 122 00:08:37,360 --> 00:08:42,270 followed by a new line, and then I'm replacing that placeholder V with the actual error. 123 00:08:42,560 --> 00:08:45,100 OK, so that gives us this. 124 00:08:45,400 --> 00:08:46,900 So let's just try that. 125 00:08:46,900 --> 00:08:48,340 Let's leave our connection empty. 126 00:08:48,760 --> 00:08:49,190 OK. 127 00:08:49,630 --> 00:08:54,400 And you think that might work and it won't and it won't work because when you go back to your getting 128 00:08:54,400 --> 00:08:57,690 started guide, you can't just import databased sequel. 129 00:08:57,700 --> 00:09:01,900 You actually need to tell this package which driver you're using. 130 00:09:01,990 --> 00:09:03,690 And our case we're using Peukan. 131 00:09:04,090 --> 00:09:07,730 So let's go down here and look at what he's doing, OK? 132 00:09:08,830 --> 00:09:12,120 He needs to import this standard library. 133 00:09:12,370 --> 00:09:16,580 So let's copy that line and paste it in here and see what happens. 134 00:09:17,260 --> 00:09:18,460 So I'll try running this. 135 00:09:18,460 --> 00:09:20,560 Go run mango. 136 00:09:22,600 --> 00:09:23,170 And it worked. 137 00:09:23,290 --> 00:09:23,950 No errors. 138 00:09:24,100 --> 00:09:24,890 That's great. 139 00:09:24,910 --> 00:09:30,640 OK, I actually want to use do more than that, more than just connect. 140 00:09:30,670 --> 00:09:32,740 I actually want to use this connection pool. 141 00:09:32,770 --> 00:09:35,740 So let's look at that icon that we have right now. 142 00:09:36,640 --> 00:09:43,840 When we connect to postgrads by opening a connection to PGS using this connection string, it returns 143 00:09:43,840 --> 00:09:48,880 a pool of database connections that we can use and we can actually specify how many connections should 144 00:09:48,880 --> 00:09:50,700 be in that pool and all sorts of things. 145 00:09:50,710 --> 00:09:52,330 But right now, I just want to connect. 146 00:09:52,360 --> 00:09:57,850 OK, once you've open that database pool, you have to close it when you're done with it. 147 00:09:57,850 --> 00:10:02,920 And that is absolutely critical because otherwise these pool, these connections to the variables will 148 00:10:02,920 --> 00:10:04,390 always stay open. 149 00:10:04,390 --> 00:10:06,810 Even if this program closes, they might stay open. 150 00:10:06,820 --> 00:10:09,940 So we need to close it when we're done and we know how to do that. 151 00:10:10,180 --> 00:10:15,740 We use the deferment defer con and it has a function on it called close. 152 00:10:16,540 --> 00:10:22,720 So after this after this function, this main function finishes executing, it will actually close that 153 00:10:22,720 --> 00:10:23,340 connection pool. 154 00:10:23,500 --> 00:10:23,920 All right. 155 00:10:24,340 --> 00:10:25,480 So let's log something. 156 00:10:25,480 --> 00:10:26,950 Log, print, line. 157 00:10:31,400 --> 00:10:36,260 Connected to database, perfect, what's the second thing I wanted to do? 158 00:10:36,290 --> 00:10:40,370 Well, I actually want to test my connection and we can do that by pinging the database. 159 00:10:40,520 --> 00:10:45,410 And we can do that simply by saying storing the variable error, which we've already initialized up 160 00:10:45,410 --> 00:10:45,620 here. 161 00:10:45,620 --> 00:10:47,570 So I don't have to use the assign operator. 162 00:10:47,570 --> 00:10:53,270 I can just equal I can say say for the connection to just ping the database. 163 00:10:54,050 --> 00:11:03,800 If error is not equal to nil, then I'll log Fadal and I'll say log fatele cannot ping database. 164 00:11:06,120 --> 00:11:07,560 And that will kill the program. 165 00:11:07,680 --> 00:11:11,970 OK, I could put the air in there if I wanted to, but that's sufficient for our purposes. 166 00:11:12,150 --> 00:11:18,780 Otherwise I'll say log print, line, pinged database. 167 00:11:19,680 --> 00:11:21,410 All right, let's run that and see what happens. 168 00:11:22,050 --> 00:11:25,730 So I'll clear the screen and I will say, go run main door. 169 00:11:25,770 --> 00:11:28,260 Go and see if we can ping the database we connected to. 170 00:11:30,110 --> 00:11:33,510 Connected the database ping database, so far so good. 171 00:11:33,530 --> 00:11:37,250 So we're actually connected to the database and we can talk to it by pinging it. 172 00:11:37,580 --> 00:11:42,580 We send it to ping it, send back, sends back a long response saying you can connect to me. 173 00:11:42,590 --> 00:11:43,190 So that's good. 174 00:11:43,400 --> 00:11:43,840 All right. 175 00:11:44,300 --> 00:11:46,130 Next, we want to get some rose from the table. 176 00:11:46,140 --> 00:11:48,770 Well, before I can do that, let's go put some rose in the table. 177 00:11:49,280 --> 00:11:52,610 So I'll look at my users table and I'll look at the content. 178 00:11:52,940 --> 00:11:55,850 And you'll use this do this using Dvir. 179 00:11:55,850 --> 00:11:56,960 Probably, but that's fine. 180 00:11:57,230 --> 00:12:06,050 I'll just insert John Smith and insert another row called Mary Jones. 181 00:12:06,410 --> 00:12:07,670 I think I left a space in there. 182 00:12:07,670 --> 00:12:08,480 Should probably get rid of it. 183 00:12:09,550 --> 00:12:09,830 I did. 184 00:12:11,030 --> 00:12:13,100 OK, and I'll save this now. 185 00:12:13,100 --> 00:12:15,500 I have two rows in my database and I have something to query. 186 00:12:15,740 --> 00:12:18,320 OK, so how do I get rows from the table? 187 00:12:18,770 --> 00:12:24,080 Well, you can see here I'm actually getting rows here and then I'm getting rows here and then I'm getting 188 00:12:24,080 --> 00:12:24,630 rows here. 189 00:12:24,650 --> 00:12:28,680 Seems to me that might be a great place for a function of some sort. 190 00:12:29,090 --> 00:12:33,460 So let's create a function and I'll call that function, get all rows. 191 00:12:33,680 --> 00:12:41,140 OK, so we'll just create it first funk, get all rows and it's going to have to have at least one parameter. 192 00:12:41,210 --> 00:12:45,380 It's going to have to take my connection, my connection pool and I'll call that con here just like 193 00:12:45,380 --> 00:12:46,010 I did above. 194 00:12:46,370 --> 00:12:50,980 And it's of type a pointer to school DB. 195 00:12:51,800 --> 00:12:53,420 That's what Con actually is. 196 00:12:53,450 --> 00:13:00,140 OK, so when I call Con up here, when I get this con school open, I can point at it and you see that 197 00:13:00,140 --> 00:13:02,480 it returns a pointer to school DB. 198 00:13:03,410 --> 00:13:08,960 So I've got my con, I'm going to pass that as a parameter to this function, get all rows and it's 199 00:13:08,960 --> 00:13:09,950 going to return something. 200 00:13:10,190 --> 00:13:11,480 What is it going to return. 201 00:13:11,990 --> 00:13:15,160 Well I think an error is sufficient for our purposes right now. 202 00:13:15,170 --> 00:13:20,300 If there's an error it will return that, which means if everything goes properly then I have to return 203 00:13:20,870 --> 00:13:21,190 now. 204 00:13:22,070 --> 00:13:24,920 OK, so I've got this function I'm going to call it. 205 00:13:25,100 --> 00:13:26,150 I can do it right here. 206 00:13:26,900 --> 00:13:28,550 I can say get rows from table. 207 00:13:28,550 --> 00:13:34,130 All I have to do is say error equals get all rows and then check for the error. 208 00:13:34,520 --> 00:13:40,040 If error is not equal to nil, log dot fatal and I'll just log the error. 209 00:13:40,330 --> 00:13:42,020 OK, I have to pass it. 210 00:13:42,020 --> 00:13:43,400 The connection con. 211 00:13:44,420 --> 00:13:48,240 So we're calling this function, but it's not currently doing anything, so let's make it do something. 212 00:13:48,830 --> 00:13:51,490 So what I'm going to do is relatively straightforward. 213 00:13:52,370 --> 00:13:58,370 I am going to say store in a variable called rows which will be all the rows that come back from the 214 00:13:58,370 --> 00:14:05,300 database query and potentially an error because it'll throw in if we get an error from our connection 215 00:14:05,300 --> 00:14:07,670 pool, I am going to write a query. 216 00:14:08,690 --> 00:14:16,910 So there's the syntax and the query is just going to be select ID, first name, last name from users. 217 00:14:18,140 --> 00:14:24,740 So there I'm going to call the database connection pool con, execute the function query and pass it 218 00:14:24,740 --> 00:14:29,120 this query and it's going to store the variable, the results in the variable rows. 219 00:14:29,120 --> 00:14:30,680 So I need to check for errors. 220 00:14:30,680 --> 00:14:34,610 If error is not equal to nil log Fadal. 221 00:14:36,950 --> 00:14:38,180 No, I don't want to die there. 222 00:14:38,210 --> 00:14:39,710 I'll just say log, print line. 223 00:14:48,060 --> 00:14:50,850 Er, and then I'll return the ER. 224 00:14:51,430 --> 00:15:00,210 OK, so I lorgat here I return it, otherwise I have something useful in those rows now just as you 225 00:15:00,210 --> 00:15:03,740 have to close database connections when you're done with them. 226 00:15:04,110 --> 00:15:06,300 So you must close rows. 227 00:15:06,300 --> 00:15:12,570 Whenever I'm querying something that will potentially return more than one row, I have to close that 228 00:15:12,570 --> 00:15:13,050 connection. 229 00:15:13,230 --> 00:15:17,070 And here I know because I have two rows in the database, I'm going to get two rows. 230 00:15:17,430 --> 00:15:23,430 But no matter what I do when I'm writing a query that can potentially return more than one row, I have 231 00:15:23,430 --> 00:15:28,170 to close those rows when I'm done with them, which I can just do by saying defer rows dot close. 232 00:15:29,100 --> 00:15:33,150 So when this function get all roses done, it will close that those rows. 233 00:15:33,150 --> 00:15:39,180 And again, if you don't do that, if you forget to do that, you might not not notice that error for 234 00:15:39,180 --> 00:15:42,300 quite some time, hours, possibly even days. 235 00:15:42,330 --> 00:15:46,910 But eventually your database will run out of resources and it will just die. 236 00:15:47,160 --> 00:15:50,590 So you need to make sure that you close the rows when you're done with them. 237 00:15:51,210 --> 00:15:53,220 So I've got these rows of information. 238 00:15:53,220 --> 00:15:59,670 And the question that we have now is how can I pull something out of that rows variable and store it 239 00:15:59,670 --> 00:16:04,710 into variables that I can do something with and go, well, you do that, first of all, by declaring 240 00:16:04,710 --> 00:16:05,580 the variables you want. 241 00:16:05,940 --> 00:16:12,240 So I know that I'm going to have two variables, first name and last name, both of which will be type 242 00:16:12,240 --> 00:16:12,690 string. 243 00:16:12,810 --> 00:16:15,720 And I know that I'm going to have an ID of type int. 244 00:16:16,280 --> 00:16:18,600 OK, so I've declared those variables. 245 00:16:18,870 --> 00:16:24,780 Now I want to populate them with data from the rows variable and I do that just by arranging over the 246 00:16:24,780 --> 00:16:25,140 rows. 247 00:16:25,410 --> 00:16:28,950 I can say four rows and it has a function called next. 248 00:16:29,940 --> 00:16:34,950 So what it's going to do is range over all of the rows one at a time. 249 00:16:35,610 --> 00:16:41,910 And what I'm going to do as I arrange over them is scan the values from the rows into the variables 250 00:16:41,910 --> 00:16:42,870 I've declared here. 251 00:16:42,870 --> 00:16:44,340 And I do that as follows. 252 00:16:44,820 --> 00:16:50,100 Error is assigned and because it's inside the rows, this is a different area of error variable than 253 00:16:50,100 --> 00:16:50,730 the one up here. 254 00:16:50,730 --> 00:16:53,580 So I have to use the assign errors equal. 255 00:16:53,580 --> 00:16:55,140 Two rows dot scan. 256 00:16:55,320 --> 00:17:00,210 I am going to scan everything I've queried in the order that I've queried them. 257 00:17:00,210 --> 00:17:08,040 So ID first name and last name and I want to scan the variable, the first entry in the first row into 258 00:17:08,310 --> 00:17:18,450 ID, so I give it a reference to ID, then first name and then the last name and that potentially throws 259 00:17:18,450 --> 00:17:21,900 an error and if it throws an error I need to do something with that. 260 00:17:21,910 --> 00:17:27,210 So if error is not equal to nil, then what do I want to do. 261 00:17:27,390 --> 00:17:28,620 Log, print line. 262 00:17:31,950 --> 00:17:38,730 Er, and I'll just return the ER and that will get us out of this function and take us back to our main 263 00:17:38,730 --> 00:17:41,490 function and we'll do whatever we need to do from there. 264 00:17:42,150 --> 00:17:46,540 So now I have these variables populated with data ID, first name and last name. 265 00:17:46,560 --> 00:17:47,560 So what am I going to do with them. 266 00:17:47,700 --> 00:17:49,100 Let's scan something into them. 267 00:17:49,380 --> 00:17:51,950 I want to give some feedback when I run this program. 268 00:17:51,960 --> 00:17:57,110 So what I'll just say is format, print, line and I'll print those values. 269 00:17:57,750 --> 00:18:09,090 So I'll say record is and then I'll print it first name and last name and it'll continue to the second 270 00:18:09,090 --> 00:18:10,240 row and do the same thing. 271 00:18:10,740 --> 00:18:16,680 OK, the last thing I need to do when I'm going through this, this rose after I do the four rows dot 272 00:18:16,680 --> 00:18:21,150 next is you always should check for an error again if error. 273 00:18:22,770 --> 00:18:26,190 And in this case I'm going to say if error equals rosewarne error. 274 00:18:29,040 --> 00:18:36,620 Or error and error is not equal to nil, just shorthand here, then do something log fatal and I will 275 00:18:36,620 --> 00:18:40,350 log fatal here, error scanning rows. 276 00:18:40,620 --> 00:18:45,390 And this is just good practice because you might get an error as you're scanning through this and it 277 00:18:45,390 --> 00:18:46,590 might not be caught by this. 278 00:18:46,590 --> 00:18:51,060 It's always good practice when you're done scanning through all of your rows just to check for an error 279 00:18:51,070 --> 00:18:51,750 one more time. 280 00:18:52,110 --> 00:18:57,840 And there's then then just to make things more readable, I'll say format, dot, print line and I'll 281 00:18:57,840 --> 00:18:58,320 just put. 282 00:19:00,100 --> 00:19:05,380 Some dashes, so I can see when I'm done with one query to get all rows and the next. 283 00:19:05,440 --> 00:19:08,080 All right, so this should run. 284 00:19:08,230 --> 00:19:12,520 Let's open our terminal, clear our screen and say, go run mango. 285 00:19:15,450 --> 00:19:20,850 And it did we connected to our database, then we pinged our database, then we got record one is John 286 00:19:20,850 --> 00:19:23,010 Smith and record two is Mary Jones. 287 00:19:23,010 --> 00:19:25,570 So that all worked just perfectly. 288 00:19:26,010 --> 00:19:27,220 What's the next thing we want to do? 289 00:19:27,600 --> 00:19:31,630 We want to insert a row while inserting a row is not that difficult. 290 00:19:31,650 --> 00:19:33,230 We're going to use our connection pool. 291 00:19:34,320 --> 00:19:36,930 So in this case, I'll define my query first. 292 00:19:36,960 --> 00:19:43,110 Just as a string, I'll say my query string is assigned the value of and whenever I'm writing queries, 293 00:19:43,110 --> 00:19:46,420 I tend to use tactics because then I can put the query on multiple lines. 294 00:19:46,710 --> 00:19:51,350 This one will fit on one line so I could use double quotes, but that ticks are your friends. 295 00:19:51,360 --> 00:19:55,380 That way you can write your query in a readable format because some of our queries are going to get 296 00:19:55,380 --> 00:19:57,270 quite long, so you may as well get in the habit of doing it. 297 00:19:57,270 --> 00:20:03,300 Now, I'm only going all I'm going to do is write straight sequel, which we already know insert into 298 00:20:03,330 --> 00:20:12,150 users, and I'm going to insert just into first name and last name and let the serial take care of the 299 00:20:12,150 --> 00:20:12,810 ID column. 300 00:20:13,800 --> 00:20:17,130 Certain values and the values I'm going to put in, I'll put in. 301 00:20:17,580 --> 00:20:22,050 Well, I can't I could take them in here, but it's not what we're going to do because what I'm going 302 00:20:22,050 --> 00:20:25,490 to do instead is use placeholders, dollar sign one and dollar sign two. 303 00:20:25,650 --> 00:20:26,610 That is my query. 304 00:20:26,910 --> 00:20:28,110 And one of those dollar signs. 305 00:20:28,110 --> 00:20:29,340 Well, I'll show you right now. 306 00:20:30,300 --> 00:20:35,040 Let's call and I'm going to discard the first return from this function. 307 00:20:35,040 --> 00:20:36,300 But trap for the error. 308 00:20:36,510 --> 00:20:41,760 Let's call Condori exec and I'm going to exact a query. 309 00:20:41,760 --> 00:20:46,620 I'm going to execute a query and the program and execute is query that variable, a defined rate of 310 00:20:46,620 --> 00:20:47,280 the line above. 311 00:20:47,820 --> 00:20:53,640 And I'm going to substitute for dollar sign, one in dollar sign to these values in this order, Jack 312 00:20:54,360 --> 00:20:55,370 and Brown. 313 00:20:57,030 --> 00:21:02,340 So when I when this query is called, it passes the query to the database. 314 00:21:02,790 --> 00:21:04,470 Here's a query that's going to be coming. 315 00:21:04,470 --> 00:21:06,420 And these are the placeholders for the values. 316 00:21:06,810 --> 00:21:10,410 And then it reads the values that are the dollar sudden. 317 00:21:10,410 --> 00:21:13,950 One is substituted with Jack Dollar sign to a substitute with Brown. 318 00:21:13,950 --> 00:21:20,220 And this is a much safer way of executing a query than trying to do it simply by building script strings. 319 00:21:20,760 --> 00:21:28,440 Because what this forces the database to do is to not allow arbitrary sequel to be executed if you just 320 00:21:28,440 --> 00:21:29,440 build a string by hand. 321 00:21:29,460 --> 00:21:35,430 So if I instead say I took some user input for first name and last name and someone said my first name 322 00:21:35,430 --> 00:21:44,760 is delete star or delete from users semicolon, the potential exists that when the exact command is 323 00:21:44,760 --> 00:21:49,560 run that it says, oh, look, you want to run this first part of the query, whatever it is, but then 324 00:21:49,560 --> 00:21:51,720 you want to delete everything from the user's database. 325 00:21:52,230 --> 00:21:56,520 And calling the query this way, using the substitutions dollar sign one for Jack. 326 00:21:56,700 --> 00:22:02,220 It prevents the database from inadvertently running malicious code that might have been inserted by 327 00:22:02,220 --> 00:22:02,670 a user. 328 00:22:02,820 --> 00:22:06,680 So it's best to use this format and that's what we'll be using for the remainder of this course. 329 00:22:07,710 --> 00:22:10,710 So I run that query and it potentially throws an error. 330 00:22:10,710 --> 00:22:13,940 So let's say if error is not equal to now, what are we going to do? 331 00:22:13,950 --> 00:22:17,160 I will just say log fatal error and done. 332 00:22:17,430 --> 00:22:17,850 All right. 333 00:22:18,420 --> 00:22:23,850 And then once this if this succeeds, then we just want to call our rose again. 334 00:22:23,850 --> 00:22:28,840 So I'll just copy this and paste it here and let's see what happens. 335 00:22:29,130 --> 00:22:33,720 So let's open our terminal, clear the screen and type. 336 00:22:33,720 --> 00:22:35,040 Go run Maeng. 337 00:22:35,040 --> 00:22:36,660 Go and see if we did everything right. 338 00:22:38,810 --> 00:22:44,630 And it looks like we did so we connected to the database, sweeping the database, we listed all the 339 00:22:44,630 --> 00:22:46,110 records that are currently in there. 340 00:22:46,460 --> 00:22:49,670 Then we did our insert and then we listed all the records again. 341 00:22:49,680 --> 00:22:51,120 And now our new record is there. 342 00:22:51,200 --> 00:22:51,720 Perfect. 343 00:22:51,740 --> 00:22:55,280 And if I run that again, of course, I'll get a fourth row with Jack Brown. 344 00:22:55,460 --> 00:22:59,070 And I just put one more thing in here just to make sure that it works. 345 00:22:59,070 --> 00:23:02,050 Says that it gives us more meaningful information. 346 00:23:02,060 --> 00:23:11,860 I'll say log, print line, insurgent a row just to give some more feedback on the screen. 347 00:23:11,870 --> 00:23:12,650 So let's run it again. 348 00:23:14,460 --> 00:23:19,760 I'll open the terminal, clear the screen, and this time I should get Feroze two with Jack Brown. 349 00:23:19,760 --> 00:23:20,810 But different IDs. 350 00:23:22,520 --> 00:23:24,600 And I do, so that works great. 351 00:23:25,040 --> 00:23:26,120 Next, what do we want to do? 352 00:23:26,150 --> 00:23:27,360 We want to update arow. 353 00:23:27,530 --> 00:23:34,970 Well, updating her, as you might expect, is pretty much the same syntax as far as inserting a row. 354 00:23:34,970 --> 00:23:38,920 But instead of writing a query that inserts a row, we write a query that updates her up. 355 00:23:39,050 --> 00:23:41,000 So I'm going to use a new variable this time. 356 00:23:41,000 --> 00:23:48,110 I'll call it statement TMT that's going to be assigned the value of impact text because I don't know 357 00:23:48,110 --> 00:23:49,370 how long my query is going to be. 358 00:23:49,380 --> 00:23:57,470 I'll say update users set first name equal to Jackie in single quotes. 359 00:24:01,140 --> 00:24:08,620 Where first name equals Jack now, is that a good syntax? 360 00:24:09,360 --> 00:24:10,470 No, it is not. 361 00:24:10,530 --> 00:24:15,360 I mean, I can do it here because I'm controlling the input, but I really should do this instead. 362 00:24:15,900 --> 00:24:19,470 Dollar sign one and dollar sign, too. 363 00:24:19,830 --> 00:24:22,530 OK, so there's our statement now. 364 00:24:22,530 --> 00:24:29,010 We just call our exact function on our connection pool, so I'll ignore the O ignore the results of 365 00:24:29,010 --> 00:24:29,670 that again. 366 00:24:30,120 --> 00:24:34,040 And I will just say trap for the error is equal to conduct exact. 367 00:24:34,500 --> 00:24:37,380 And I'm going to pass the statement this time, not my query. 368 00:24:37,710 --> 00:24:43,770 And then what I want to say is set first name equal to in double quotes because it's a string Jackie 369 00:24:44,550 --> 00:24:47,240 and my second substitution will be Jack. 370 00:24:48,090 --> 00:24:53,280 So and if it does print the error and stop the program. 371 00:24:54,120 --> 00:24:57,510 OK, and now we'll say log print line 372 00:25:00,300 --> 00:25:03,870 updated one or more Rose. 373 00:25:06,160 --> 00:25:11,020 And then I'll get the rose from the table again, so I'll copy this and paste it down here. 374 00:25:11,650 --> 00:25:16,360 Now ask yourself what's actually going to happen when I run this program? 375 00:25:16,390 --> 00:25:19,990 Well, if you look at our terminal window, how many rows do we have with the name? 376 00:25:19,990 --> 00:25:21,210 First name said to Jack? 377 00:25:21,250 --> 00:25:28,090 We have to say when I run this, it should insert a third row with the name of Jack and then rename 378 00:25:28,090 --> 00:25:30,850 all of those rows to Jackie Brown. 379 00:25:31,060 --> 00:25:32,050 That's what I think is going to happen. 380 00:25:32,080 --> 00:25:32,710 Let's find out. 381 00:25:32,980 --> 00:25:33,870 So I'll clear the screen. 382 00:25:35,530 --> 00:25:37,420 Go run, Mango. 383 00:25:40,180 --> 00:25:46,330 And it did exactly that, it inserted another row to give us three, and then it renamed all of those 384 00:25:46,330 --> 00:25:48,170 rows to Jackie Brown. 385 00:25:49,060 --> 00:25:50,910 What if I just wanted to update one room? 386 00:25:51,340 --> 00:25:52,570 Well, I could do that very easily. 387 00:25:52,630 --> 00:25:58,320 And I can do it by simply saying adding my work or modifying my work clothes. 388 00:25:58,330 --> 00:26:06,120 I can say update users set first name equal to Jackie where ID equals dollar sign too. 389 00:26:06,130 --> 00:26:08,750 And in this case, the idea is not a string. 390 00:26:08,950 --> 00:26:10,500 Let's get an idea that we can use. 391 00:26:10,750 --> 00:26:13,750 I'll use record five because that's currently set to Jacqui. 392 00:26:13,750 --> 00:26:18,390 And I know that it will be the next time I run this program so I can say where it is equal to five. 393 00:26:18,880 --> 00:26:24,400 So let's save that, clear the screen and run it. 394 00:26:24,590 --> 00:26:27,930 Go run mango and do make sure that you save it. 395 00:26:27,940 --> 00:26:34,150 I actually under my visual studio code, which I'm becoming somewhat more familiar with, I have autosave 396 00:26:34,150 --> 00:26:36,430 turned on just so I don't ever have to think about it. 397 00:26:36,430 --> 00:26:37,270 But it's up to you. 398 00:26:37,810 --> 00:26:41,680 Anyway, let's run it and see if our update this time works on just one record. 399 00:26:43,550 --> 00:26:49,430 And it does so it changed just this record five, which was I think it was record five I was doing. 400 00:26:50,710 --> 00:26:55,040 At 5:00, it changed 5:00 to Jackie Brown, so life is good so far. 401 00:26:55,060 --> 00:26:59,350 And you notice that these are not in any particular order because I've not specified an order clause. 402 00:26:59,590 --> 00:27:05,170 So six actually came before 5:00 because presumably this was the most recently updated one. 403 00:27:05,320 --> 00:27:07,480 But I never trust the order from a database. 404 00:27:07,480 --> 00:27:11,080 Any time I want to get things in a particular order, I'll add an order clause. 405 00:27:11,230 --> 00:27:13,630 But that's beyond the scope of what we're trying to do right now. 406 00:27:13,870 --> 00:27:14,200 All right. 407 00:27:14,200 --> 00:27:15,200 What do we want to do next? 408 00:27:15,460 --> 00:27:17,010 Get one rowby ID. 409 00:27:17,530 --> 00:27:21,760 We'll getting a rowby ID uses a slightly different syntax. 410 00:27:21,940 --> 00:27:24,850 You're called down here when I call, get all rows. 411 00:27:24,850 --> 00:27:28,760 I'm using query and I'm actually deferring the rows close. 412 00:27:28,780 --> 00:27:32,620 When you're getting one row, you're either going to get one row or none. 413 00:27:32,630 --> 00:27:36,380 You know that things are a little bit different, but it's not that difficult. 414 00:27:36,400 --> 00:27:38,410 So let's rewrite our query. 415 00:27:38,590 --> 00:27:40,780 I already have the variable, so I don't have to assign it. 416 00:27:40,780 --> 00:27:50,470 And this time I'm going to say for the query, select ID, first name, last name from users where ID 417 00:27:51,940 --> 00:27:53,890 equals and I'll put my placeholder in there. 418 00:27:54,800 --> 00:28:01,330 And now again, I'm going to call my a method or a function on my connection pool and I'm going to store 419 00:28:01,330 --> 00:28:04,420 it in one variable because this method only returns one round. 420 00:28:04,480 --> 00:28:09,640 So I'll call my variable RO and use my assigned assignment operator to create it and initialize it. 421 00:28:09,640 --> 00:28:15,670 At the same time, it's equal to from my connection pool query row. 422 00:28:15,970 --> 00:28:20,710 And this will return, as you can see here, it actually executes a query that is expected to return 423 00:28:20,710 --> 00:28:22,570 at most one row. 424 00:28:23,440 --> 00:28:31,090 And I know that when I specify where ID equals an ID and ID is my primary key, it can only ever return 425 00:28:31,090 --> 00:28:32,320 at most one row. 426 00:28:32,800 --> 00:28:37,150 So I call it and I pass it my query and I pass it. 427 00:28:37,150 --> 00:28:39,190 My substitution for dollar sign one. 428 00:28:39,220 --> 00:28:40,690 I know there's an ID one slot. 429 00:28:40,690 --> 00:28:41,420 Specify that. 430 00:28:42,640 --> 00:28:47,200 So now where is the error, where is the potential error in this case. 431 00:28:47,230 --> 00:28:51,310 Well, it's a little different when you use query row, it doesn't return row and error. 432 00:28:51,310 --> 00:28:52,600 It just returns one thing. 433 00:28:53,110 --> 00:28:57,040 And it's not until I try to read that row, but I potentially get an error. 434 00:28:57,070 --> 00:29:00,670 So I now write my reading of that row as follows. 435 00:29:01,420 --> 00:29:04,320 I will say, well, I need somewhere to store these things. 436 00:29:04,350 --> 00:29:05,950 Let's declare some variables first. 437 00:29:06,610 --> 00:29:14,620 I'll save our first name, last name string, same as I do in the function down here. 438 00:29:14,620 --> 00:29:20,560 But because this is in the scope of only the main function, I have to re declare those and var id is 439 00:29:20,560 --> 00:29:21,250 of type it. 440 00:29:21,420 --> 00:29:23,080 OK, so now I have somewhere to store this. 441 00:29:23,440 --> 00:29:33,940 Now I can say error equals row dot scan and I scan my values in ampersand id for the first one ampersand 442 00:29:33,940 --> 00:29:39,010 first name for the second one and ampersand last name for the third one. 443 00:29:39,670 --> 00:29:40,810 And then I check for my error. 444 00:29:41,830 --> 00:29:47,670 So I say if error is not equal to nil and I'll just log fadhel log fatal error. 445 00:29:48,310 --> 00:29:48,700 All right. 446 00:29:49,360 --> 00:29:51,760 So let's see if this works so I'll save it. 447 00:29:52,210 --> 00:29:54,160 Open my terminal, clear the screen. 448 00:29:54,430 --> 00:29:56,140 Go run Nango. 449 00:29:58,430 --> 00:30:03,570 And it seems to have worked just fine, except that I forgot to print this stuff out. 450 00:30:03,570 --> 00:30:07,190 So here I need to say log print, blind 451 00:30:10,040 --> 00:30:21,920 query, row returns and then I'll put it first name and last name and let's run that again. 452 00:30:22,100 --> 00:30:22,970 Clear the screen. 453 00:30:25,850 --> 00:30:29,360 And it runs query row returns one John Smith. 454 00:30:29,390 --> 00:30:33,410 OK, so now we know how to get multiple rows and we know how to get a single row. 455 00:30:34,460 --> 00:30:36,760 The last thing I want to do here is delete a row. 456 00:30:37,100 --> 00:30:39,780 And again, this is not that difficult. 457 00:30:39,800 --> 00:30:41,900 So let's delete one row again. 458 00:30:41,900 --> 00:30:43,010 We just have to have a query. 459 00:30:43,770 --> 00:30:44,840 I'll use my query. 460 00:30:44,840 --> 00:30:51,770 Variable that have already initialized is equal to delete from users where ID equals dollars on one 461 00:30:53,060 --> 00:30:55,790 and I run my query as follows. 462 00:30:56,780 --> 00:31:02,720 I'm ignoring the result that is passing me but looking for the error and that's equal to. 463 00:31:03,500 --> 00:31:04,760 There's nothing new on that side of it. 464 00:31:04,760 --> 00:31:11,530 So I use the equal sign Condori exec and I pass it my query and this time I'll delete it. 465 00:31:11,550 --> 00:31:12,100 Six. 466 00:31:12,140 --> 00:31:14,360 I know that exists right now, so that's a work just fine. 467 00:31:14,630 --> 00:31:25,490 If error is not equal to nil log fatal error and now will print out my results, I'll just print out 468 00:31:25,640 --> 00:31:35,810 log dot print line, deleted a row and then I will get all of my rows from the table again. 469 00:31:35,810 --> 00:31:38,840 So I'll copy and paste it down here. 470 00:31:41,530 --> 00:31:48,640 So what should happen now is after I've executed this last row, ID six shouldn't be in the last list 471 00:31:48,640 --> 00:31:49,090 listing. 472 00:31:49,090 --> 00:31:53,530 So let's save this, open the terminal, clear the screen, go run mango. 473 00:31:58,300 --> 00:32:02,510 And I.D. sex is conspicuous by its absence. 474 00:32:02,530 --> 00:32:04,190 So everything seems to work. 475 00:32:04,450 --> 00:32:08,950 So what we have now, and this is just the basic way of doing it, we're going to improve it by using 476 00:32:08,950 --> 00:32:10,390 contacts a little bit later on. 477 00:32:10,810 --> 00:32:14,210 But right now, we can connect to a database, close our connection pool. 478 00:32:14,210 --> 00:32:17,200 When we're done with it, we can ping it to make sure it's active. 479 00:32:17,500 --> 00:32:19,120 We can get all the rows from it. 480 00:32:19,630 --> 00:32:23,260 We can insert into it, we can update data in it. 481 00:32:24,250 --> 00:32:27,820 We can get one rowby ID and we can delete from it. 482 00:32:28,030 --> 00:32:31,390 So that's basically everything you're ever going to do with the database. 483 00:32:31,810 --> 00:32:34,570 Your queries might get more complex and they will. 484 00:32:35,260 --> 00:32:42,070 But this is the basic methodology for working with a database, and that's all we need to worry about 485 00:32:42,070 --> 00:32:42,640 at this point. 486 00:32:42,850 --> 00:32:43,240 All right. 487 00:32:43,480 --> 00:32:50,410 So let's move on and start thinking about how we're going to connect our bookings application to a database 488 00:32:50,740 --> 00:32:52,180 in a way that makes sense. 489 00:32:52,180 --> 00:32:56,770 And one of the things we're going to work on or keep in mind is we do this is right now we're using 490 00:32:56,770 --> 00:32:58,150 postcrisis our database. 491 00:32:58,570 --> 00:33:06,070 What if down the road you decide to switch from postgrads to my school or Maria DB or DB or one of the 492 00:33:06,070 --> 00:33:09,480 many, many databases that are available to you as a programmer? 493 00:33:09,820 --> 00:33:14,680 So we're going to make our database queries as portable as we possibly can.