1 00:00:00,900 --> 00:00:04,700 Are not uncommon thing. You might have to do is provide some sort of 2 00:00:04,700 --> 00:00:08,800 web interface be it, an API or an actual HTML interface to a 3 00:00:08,800 --> 00:00:12,600 database. And I'm going to go through a simple example here where I 4 00:00:12,600 --> 00:00:16,700 provide access to sqlite database, using the database SQL, 5 00:00:16,700 --> 00:00:20,800 functionality to provide a web interface and then I'll change it 6 00:00:20,800 --> 00:00:24,600 from Just producing text to producing Jason to make something that looks a bit like an 7 00:00:24,600 --> 00:00:28,900 API. And then I'll actually add methods to it so we can update the database and 8 00:00:28,900 --> 00:00:29,800 see how it operates. 9 00:00:30,000 --> 00:00:34,600 It's so first of all, I've prepared a fairly simple program which allows us to access a 10 00:00:34,600 --> 00:00:38,800 database of scientist names and let me just bring up that database we 11 00:00:38,800 --> 00:00:40,100 see what it looks like. 12 00:00:42,100 --> 00:00:46,400 So there's a number of scientists names in here with different field values. And within the 13 00:00:46,400 --> 00:00:50,800 program, I have defined an API or web-based API, which I'm going to use 14 00:00:50,800 --> 00:00:54,800 to access it. So let's first of all, start with main, I just 15 00:00:54,800 --> 00:00:58,900 connect to the database in a very familiar way which is to use SQL to open from 16 00:00:58,900 --> 00:01:02,800 the database package and that particular database file and 17 00:01:02,800 --> 00:01:06,700 then I'm actually storing the database handle if you like in this 18 00:01:06,700 --> 00:01:10,500 database server struct, which is defined here. And 19 00:01:10,600 --> 00:01:14,800 And the moment that's all it has but it could be many other things in it and 20 00:01:14,800 --> 00:01:18,900 I'm going to use the net/http package to start 21 00:01:18,900 --> 00:01:22,700 serving HTTP on port. 8080 on my local machine, 22 00:01:22,700 --> 00:01:26,200 and there's one route here which is / API 23 00:01:26,200 --> 00:01:30,700 and it's going to call a method value. If you remember earlier in this, I talked about 24 00:01:30,700 --> 00:01:34,900 method values. Well, DS, which is a database server has a 25 00:01:34,900 --> 00:01:38,800 method, which is here API, which has the right signature 26 00:01:38,800 --> 00:01:40,400 to be handled by a 27 00:01:40,600 --> 00:01:44,500 Little funk in net/http so I can just call it in there. This is the method 28 00:01:44,500 --> 00:01:48,700 value here. And so when anyone calls makes a 29 00:01:48,700 --> 00:01:52,400 an HTTP request, the / API on here, will end up being 30 00:01:52,400 --> 00:01:56,700 sent into this function here. And this function has a couple of parameters. One is 31 00:01:56,700 --> 00:02:00,900 this response writer which is where you send your response back to the user. And there's 32 00:02:00,900 --> 00:02:04,400 a point to the request itself, which came in which is already been passed 33 00:02:04,400 --> 00:02:08,900 and the API wanted to find is pretty simple. It allows you to look up somebody by 34 00:02:08,900 --> 00:02:10,500 last name and get their first name. 35 00:02:11,000 --> 00:02:15,900 So first thing I do is I pass any form values so that I can pass. The 36 00:02:15,900 --> 00:02:19,800 last thing I'm going to expect there to be a last name, so last 37 00:02:19,800 --> 00:02:23,900 equals something and if there isn't, I'm going to throw a bad request error 38 00:02:23,900 --> 00:02:27,500 back saying it's missing and if there is I'm going to get the last 39 00:02:27,500 --> 00:02:31,900 name and then I'm going to go to the database and pull out the corresponding 40 00:02:31,900 --> 00:02:35,800 record. So let's go into here and I'm going to say I'm going to use the database 41 00:02:35,800 --> 00:02:39,800 handle which is in the date of this database server. And to do this 42 00:02:39,800 --> 00:02:40,400 query, get the 43 00:02:40,700 --> 00:02:44,500 Name where the last name equals that and I'm using here a 44 00:02:44,500 --> 00:02:48,100 placeholder and passing in last as a parameter to query. 45 00:02:48,900 --> 00:02:52,800 And if that goes wrong, then I'll say there's a problem and if not, I 46 00:02:52,800 --> 00:02:56,800 will get the first row. I'm assuming they'll only be one row. Of course, there 47 00:02:56,800 --> 00:03:00,800 might be more than one being this case, it's Unique scan out. Just the 48 00:03:00,900 --> 00:03:04,700 first name, that's all I've asked for here. So I can just do a scan like this, 49 00:03:05,000 --> 00:03:09,400 and I'm just going to print it out. I'm just going to say this person has this name or if it's not 50 00:03:09,400 --> 00:03:10,400 found. This is what 51 00:03:10,600 --> 00:03:14,700 Happened. So this will end up returning a 200 because there's no error and this 52 00:03:14,700 --> 00:03:18,600 whole thing right now is a working server which can talk to this database. So 53 00:03:18,700 --> 00:03:21,000 let's just make sure it builds 54 00:03:22,400 --> 00:03:26,700 That looks pretty good and run it. So if you look at this 55 00:03:26,700 --> 00:03:30,800 last thing here, listen serve, it's now sitting waiting for 56 00:03:30,800 --> 00:03:34,900 any requests on port 8080. And what I can do is go over here 57 00:03:34,900 --> 00:03:37,300 and just do a curl against it. 58 00:03:40,900 --> 00:03:44,700 So 404 not found because there's nothing on /. If I go to the 59 00:03:44,700 --> 00:03:48,700 API and going to get an error missing laughs, let's just get the 60 00:03:48,700 --> 00:03:52,900 headers out of that. You're going to see there. It says, 400 bad request because that was the 61 00:03:53,100 --> 00:03:57,900 thing. I told it to do up here, which is if there was no last then give me a 400, bad request. All right, 62 00:03:57,900 --> 00:04:01,600 let's see what happens. If we ask if somebody who isn't a database, let's last for Ada 63 00:04:01,600 --> 00:04:05,900 Lovelace. So we're going to go last equals 64 00:04:06,400 --> 00:04:10,700 the place and there you see Lovelace first name is Aida so it's gone. 65 00:04:11,000 --> 00:04:15,900 It's found the thing is done. This database query is got Rose, paused it and return it to the user. 66 00:04:16,200 --> 00:04:20,900 And it's really a very small amount of code to be able to do that. Let's just do one more. Let's try one where 67 00:04:20,900 --> 00:04:24,800 the person is missing. So I don't see John 68 00:04:24,800 --> 00:04:28,900 Logie, Baird in here and bed not found. So that was the other 69 00:04:28,900 --> 00:04:32,600 branch of this here. So that's a basic hook up of a web-based 70 00:04:32,600 --> 00:04:36,900 interface to it to a database. Now, it's just, I'm going to go ahead 71 00:04:36,900 --> 00:04:40,700 now and change this obviously, textual responses aren't that great. Let's actually 72 00:04:40,800 --> 00:04:44,700 She return a Json response instead, so I've been much 73 00:04:44,700 --> 00:04:48,700 nicer if this API returns Json rather than returning a bit of 74 00:04:48,700 --> 00:04:52,800 text. So, I want to be a bit more structured and that's actually pretty easy to do. Go 75 00:04:52,800 --> 00:04:56,900 has an encoding /, Jason package which will do most of the work for you. So what 76 00:04:56,900 --> 00:05:00,900 I've done is I've modified the program here so that it now reads all the fields from the database to 77 00:05:00,900 --> 00:05:04,700 make it a bit more interesting. Stores them in a structure, I'm going to use an anonymous 78 00:05:04,700 --> 00:05:08,900 structure since I don't need to name it and then Marshalls that into Jason and writes 79 00:05:08,900 --> 00:05:10,700 it out. So just take a look at how that's 80 00:05:10,700 --> 00:05:14,400 You just import encoding/json here 81 00:05:14,400 --> 00:05:18,800 and this is all the same. I'm not modified this query. 82 00:05:18,800 --> 00:05:22,600 So that rather than just getting the first name, I get all the fields. And 83 00:05:22,600 --> 00:05:26,600 I've now when I ask you find the result, I've made this result 84 00:05:26,600 --> 00:05:30,800 variable here, you notice, I'm using Anonymous structure because I don't need to 85 00:05:30,800 --> 00:05:34,900 type red and just contains all the fields that I'm retrieving the ID first name 86 00:05:34,900 --> 00:05:38,200 last name and whether they're alive, which I then scan in. 87 00:05:38,200 --> 00:05:40,300 Unfortunately, I have to scan them all in like this. 88 00:05:41,000 --> 00:05:45,800 Is that the scan interface won't let me easily scan it into the structure, but it still 89 00:05:45,800 --> 00:05:49,800 works. And then I'm going to use Jason Marshall, which is going to take 90 00:05:49,800 --> 00:05:53,800 that crate it into Json into this thing. Jay obviously if something goes wrong with 91 00:05:53,800 --> 00:05:57,800 that that's an internal server error. We should talk about that and then I'm going to write the Jason 92 00:05:57,800 --> 00:06:01,900 out to W which is member is the response writer and that's it. So 93 00:06:01,900 --> 00:06:05,300 that's the change to make it work with Jason. So let's just 94 00:06:05,600 --> 00:06:06,700 build that guy. 95 00:06:08,600 --> 00:06:12,000 And run it. Okay, so that's running 96 00:06:12,500 --> 00:06:16,600 and we'll ask it to look up, somebody called Lovelace 97 00:06:17,000 --> 00:06:21,800 using their last name and I'm piping it through Jason PP. Just get some nice, stretch it out bird 98 00:06:22,000 --> 00:06:26,800 and there you go. So that's now query the database created Json output in much more 99 00:06:26,800 --> 00:06:30,400 structured format. So we're getting close to now to something which is an 100 00:06:30,400 --> 00:06:34,700 API. The big thing we haven't got right now isn't any distinction between getting and putting. 101 00:06:34,700 --> 00:06:37,500 So what I'm going to do is I'm going to modify this so I can actually update the 102 00:06:37,800 --> 00:06:41,700 Race by adding new people to it using a post. So having turned it 103 00:06:41,700 --> 00:06:45,700 into something that provides Jason. The last step is to make it update the 104 00:06:45,700 --> 00:06:49,800 database to make it into an API that actually does something useful. So what I've gone ahead 105 00:06:49,800 --> 00:06:53,200 and done is modified the code, so it looks at the HTTP request 106 00:06:53,200 --> 00:06:57,900 methods, see whether it's a get or a post. If it's a get then it's going to do what it did before 107 00:06:57,900 --> 00:07:01,500 and look up someone in the database and return their details. If it's a 108 00:07:01,500 --> 00:07:05,700 post, then it is going to go ahead and add that person to database. 109 00:07:05,700 --> 00:07:07,600 And for a post, we're going to have a first 110 00:07:07,800 --> 00:07:11,500 first name and a last name to be inserted into the database. So I did a little bit of 111 00:07:11,500 --> 00:07:15,800 refactoring, let's just take a look. I took out everything that was doing 112 00:07:15,800 --> 00:07:19,500 the get from the database, the query here and gave it made it into a function called 113 00:07:19,500 --> 00:07:23,400 get and I created a new function called Marshall. 114 00:07:23,800 --> 00:07:27,300 And what Marshall does is it writes some arbitrary piece of 115 00:07:27,300 --> 00:07:31,900 Json to the response writer. So we're going to use that a few different places to write different bits 116 00:07:31,900 --> 00:07:35,600 of Jason. I also added a Boolean called success to the 117 00:07:35,600 --> 00:07:37,500 result. So I could say whether something was 118 00:07:37,800 --> 00:07:41,500 For now. So all of my Json responses will have a Boolean success or not. 119 00:07:42,200 --> 00:07:46,900 And so the simple get a pi now is handled by the 120 00:07:46,900 --> 00:07:50,800 API function. So everything has a last name. So I checked the last 121 00:07:50,800 --> 00:07:54,900 name first, and then I go in and look at the request method, never forget, I do this, 122 00:07:54,900 --> 00:07:57,900 get thing. And if it's a post, I do the new post item 123 00:07:58,600 --> 00:08:02,300 and the post function, it has to get the first note, we need the 124 00:08:02,300 --> 00:08:06,400 first-name and make sure that's there and then it's simply doing an insert into 125 00:08:06,500 --> 00:08:07,600 with these 126 00:08:07,800 --> 00:08:11,800 He's placeholder elements for the first name and the last name. Let's tidy that up 127 00:08:12,300 --> 00:08:16,800 error. Obviously, if something goes wrong and then just return a success. So, let's return a 128 00:08:16,800 --> 00:08:20,800 very small piece of Jason, that just says success, that's what this Anonymous struct here, will 129 00:08:20,800 --> 00:08:24,300 do. So let's make sure that builds. 130 00:08:28,500 --> 00:08:32,800 Looks good. Okay, now let's go ahead and look someone up in 131 00:08:32,800 --> 00:08:34,300 here. So let's look up. 132 00:08:35,700 --> 00:08:38,900 Ada Lovelace. Again, just look her up. 133 00:08:41,500 --> 00:08:45,800 Okay, there she is. It gets her details. Look up someone who's not there. I'm going to look at Mary 134 00:08:45,800 --> 00:08:46,500 Somerville. 135 00:08:48,500 --> 00:08:52,700 So that's not successful. She doesn't exist and obviously you could have a different message in here. 136 00:08:53,100 --> 00:08:57,500 So let's add Mary Somerville. So what I'm going to do is I'm going to turn this into a post. 137 00:08:59,000 --> 00:09:03,500 And I am going to say first name, Mary. 138 00:09:05,300 --> 00:09:09,900 And so that's that's been added to the database and let's just check the she's there. And 139 00:09:09,900 --> 00:09:13,900 there she is. She's been added Mary Somerville, and that's what particular ID, and obviously, we could go 140 00:09:13,900 --> 00:09:17,900 into the database. We have a look at the record as being created within their. So, as you can see, 141 00:09:17,900 --> 00:09:21,900 is not a lot of code to start building up an API like this. And 142 00:09:21,900 --> 00:09:25,500 obviously, there are many other things you can do to around templating 143 00:09:25,500 --> 00:09:28,600 because go have a nice template you Library. So you might want 144 00:09:28,700 --> 00:09:32,600 To have templated responses in some cases when it's HTML. But 145 00:09:32,600 --> 00:09:36,900 basically between encoding/json database, SQL, and net/http. Those 146 00:09:36,900 --> 00:09:40,800 three packages, you can build a fully featured API server, that 147 00:09:40,800 --> 00:09:42,900 talks to a database with really, no problem at all.