Go tutorial part 5 (web app part 3)

Key Takeaways

hi guys this is tensor welcome to the goaline tutorial part 5 today we are going to actually put a database inside of our web app up until now we've been using text files to save all of the data that we've been using inside of our web app and now we are going to add a database to allow us to actually query and grab these things and then put them back in so for this we're going to use a database called SQLite for those of you who don't know what it is it's basically an embedded version of SQL so there are many flavors of SQL which is a relational database SQLite is I'd say the smallest one and probably the simplest one and if you are an independent developer and you're developing say an offline tour or even a small online application most likely you'll use something like SQL right because it's just so easy if you are developing something bigger maybe you'll use something like Postgres or you'll use my sequel if enough of you or they want to see say DB just comment on this video and let me know so here's our program for now as you can see it's this is the Edit part of it we can go to the test part of it we can edit all this so say we wanted to add some other stuff into it if we had just save as you can see it's been added and we can just go back and forth this way we've added this little navigation ok guys so the first thing we need to do is actually import to imports the first one that's going to be called database back slash SQL and this is actually a part of the golang language the other one is going to be our actual SQL driver which is just github.com madsen go SQL Lite 3 and for this one because we're not going to actually use it for anything we're just going to use an underscore so now we need to actually open our database to do this we need to create a database variable so we're gonna create a variable called database camera passing an underscore as well because this function gives us two things back one of them is an error since we don't want to do any error checking on it we're just going to pass the underscore to it so we're going to open our database and inside of this we need to pass in our driver which is here it's called SQLite 3 and we need to pass in the database name so we want to actually put it inside of this cache folder that I've created here so we're going to say cache backslash and we're gonna call it web DV so that's our database name and it's going to be inside this cache folder here actually also we want to be able to read our files from this cache folder as well so we're going to quickly change things to do that as well there we go so now both of these should read from the cache folder which will make things a little bit a little bit better because we'll be able to actually look just in the cache folder and it will have all of our backup files so we also need to define one more variable and this variable is going to be our string or a string of SQL to create the actual database so it's going to be called create DB and inside of it we're going to say create table if not exists pages so we're creating a table called pages if it doesn't already exist inside of our SQL database and this is going to have a title of type text it's going to have a body of type blob and it's going to have a timestamp of type text so we're using the blob type because that corresponds with the slice of bytes type from go inside of SQL and we're adding this timestamp part well you'll actually see why later when we actually start showing it off so now we need to actually change our save function here and actually I'm going to call this function save cash now so let's change everything else so we've changed us to save cast just to make it a little simpler to understand the first thing we need to do is actually define our time stamp in here so basically what this is doing is it's bringing the actual time into our variable here then we're formatting that into a string which were then assigning to our time stamp so now after we actually create our file here we're going to type DB exec and then we're going to cast that create DB variable into it and so what this will do is it will actually open up the database run this piece of SQL and then finish so actually commit it to the SQL database so now we're going to actually do our logic to actually save our variables inside the database so we're going to assign a variable T X to DB begin and T X basically just stands for transaction and then we're going to pass it or we're gonna assign a variable s TM T to TX dot preparer and inside of here we're going to say insert into pages title body timestamp values question mark question mark question mark basically this is just getting the the actual table ready to receive the values that we're about to send so these values we're going to send through s TM T exact we're gonna send the P title P dot body and timestamp oh and by the way when we created our timestamp variable here it actually imported string dot convert or string covers and time to actually allow it to work so if you guys aren't using IntelliJ like I am maybe you want to actually bring these in manually as always of course our code will be on github so if you want to take a look at it later you can definitely do that now we need to do TX not commits so we need to actually commit these changes to our database and that's what we're doing here now we're gonna actually take this off at the return value and we're going to return our error and the reason why we're doing this is well you'll see you later okay so this function now actually creates both a file in the cache and it creates a or it puts our values inside of our database so we can actually run this now and we could actually see what kind of database it's creating so so here if I've loaded it now it's actually reading from the cache folder here if we hit save it will actually save it into the database so let's do the same for our edit file and now we're going to bring up what's called the SQLite 3 browser let's open our database inside of here ok so now I've opened up the database here and as you can see we have a table inside of it and our table has a title body and timestamp and inside of the data we have one edit and it has all that gobbledygook text inside of it and a timestamp aside to it and our test also has all that gobbledygook assigned to it and a timestamp so this is nice but you'll see right now this is one of the flaws of using SQL if I hit save again and I reload the data in here now we have two edits and this is actually why we're using the timestamp so when we query the database we want to sort everything by the timestamp so that we get the latest iteration of the page that way if we actually load it we'll get the latest version but we'll also have a record of all the versions before it so that if something does go wrong we can go into our database and pull them out so let's actually see what the SQL will look like for this so for example say we wanted to select the edit page and we want to sort it by our timestamp let's do that real quick so we're gonna say select title and body from pages where title equals and we'll say edit order by Times and if we run this you'll see that it actually brings both the the rose out for us so we need to add one more little thing and that's dsee limits one and basically what this is saying is that we only want one row and so now we have the latest iteration of edit so we need to implement this into our load functions when we're gonna actually load this from our SQL so let's actually build those load functions now we're going to leave this load function alone for now and we're gonna build another function it's going to be called load source this function is going to get a title string and it's going to pass back a page pointer and an error in a tuple I know it's similar to our load function and that's on purpose so we're going to create a variable called name and this is going to be of string type and then we're going to create a variable called body and this is going to be a byte tight or a slice of bytes rather then we're going to say Q underscore equals DB query and we're going to input the SQL that we just used which is select title and body from pages where title equals and we're going to rather than pass in edit as hard code we're going to pass in title as a concatenated string here so this seems like it might work but it won't actually work and the reasoning is because title needs to be surrounded the actual variable itself inside of the SQL needs to be surrounded by quotes so we need to put single quotes on the outside of these double quotes so when it gets passed in there say it's test it will be passed in as a string of tests so these quotes will be around the test variable so now we're gonna say for Q dot next Q dot scan and we're going to scan for the name and the body variable what this is basically doing if any of you have ever used the console scan FM t dot scan function and go it's very similar are we passing the variables that we want to scan from the SQL query so in this case we're going to assign title to name and body to body we're going to return page with title equalling name and body equalling body now we want to do one more thing just to actually deal with the error handling here and that's replace this underscore with er R and then we want to check for an error so if AR r does not equal nil return nil a comma r r so now our function will actually return an error if an error happens when we query the database this is very important we're going to actually use it in our view and in our edit function so let's actually implement this into our view and edit functions so that we can actually load from the database so in our view handler we're going to actually say okay P comma error equals load source and we're going to pass in title like we do with our load function here and then we're going to comment out load here now I'm deliberately making an error here and we will actually see what that error is we're gonna do the same thing here for the edit function so load source title and let's comment this out so in theory we should get our page type here and it should pass into our execute template and we can actually remove these errors here for now just make them underscores because we don't really need them and let's see if this works so let's actually run it this is actually going to work because and I'll tell you why and the reason why it's working is because we already have edit and test inside of the database but if we actually delete the database so let's go in here and delete the database so now we've deleted the database if we actually run it now and we try to query the database we're actually going to get an error because there is no data inside of the database so as you can see here it shows us just a blank page even though our text files should pop up with you know actual text in it so this is a problem then this is you know why we actually want to throw the error so we're gonna actually handle the error here and we're gonna say if error is not equal to now and we're actually going to throw in our load function in here so if we get an error from our load source function we're going to load from the text file we're gonna do the same thing for our edit Handler and remember we're not reassigning P or we're not assigning P rather we're reassigning P so that's why I am removing the colon here so this should work but we will again run into a slight error I'm doing this on purpose just so that you guys can understand how the database is actually working so let's actually you run it again so here's our test page and this is being loaded from our text file all right none of this is in the database yet in fact the database should be empty but if we edit it and we hit save it should save it into our database but here's the problem now if we go to our edit page it will actually be empty there won't even be a title and the reason why this is happening is because when our view function fires even with edit in it it's trying to query the body or that's trying to query our page table and this is not actually throwing back an error it's just saying okay well it's empty there's no data in there and that's why we're getting nothing so it's not actually loading from the page either it's just loading from the source and there's nothing inside that source and therefore it's not working so to fix this we throw him one more little if statement so we say if P dot title equals an empty string we're gonna just say P if title equals load and the reason why we're doing this is we want to just say okay if this page is empty we want to use our load function instead so we want to use our text file so now everything should work as we you know had it before it except now we have a database to back everything up so as you can see our edit function loads even though we're not actually loading it from the database and if we go to test it all is in the database so if we bring up our SQL Lite reader okay so now I've opened web and as you can see we have two tests and an edit in here and they all have different time stamps and this is why it's actually working so let's say we save test yet again this will actually add a third test and if we go into edit we can do the same thing and we'll save it again and we could actually just f.y.i you could actually obviously edit these pages I'm not editing them just for the sake of simplicity but you could also edit these pages and it would add to the database and everything would still work fine okay guys so I hope you enjoyed this if you are going to work with SQLite I would say definitely download the DB browser you can get that from their webpage it's very handy it's very nice you don't have to guess what's inside your database so you can actually look our code will of course be on github and it will be linked in the bottom of this video if you liked this video please feel free to like it subscribe if you want to see more like this if you have any questions feel free to comment and if you hated this video you know you can dislike it if you want even scream obscenities at me in the comments section I don't really care anyway guys I hope you have a good day