Golang SQL Beginner Tutorial + SQLX

hey guys in today's video i want to show you how to connect to a sql database and go so we're going to be creating a simple crowd api with gin as well so we're going to be using the gen web framework and we're going to be connecting to a mysql database um this works with any sql database you just have to change the sql drivers so let's get started and let's first just go to our ide and i'm assuming you already have a database ready so i just have a database running on my localhost with a testdb and inside of that i just have a post table i'll leave the schema to this post table in the description so you can just create that first table okay so let's start off by first initializing our go modules let's do a go mod init i'm going to call this go tutorial and let's grab the sql driver so i'm using mysql so therefore i'm going to go for the go mysql driver and let's just grab this and install it so now that we have that let's also grab the gin web framework and of course if you guys are using something like postgres you want to look for the go skill driver for postgres and everything else will be very similar okay now that we have those two let's create a main dot go file with our main package and our function main um so what i actually want to do is create a database connection that can be used throughout the entire project so i'm going to create a new file a new folder sorry calling it db client now within db client i'm just going to create a go file db client dot go so this is going to be our db client package and in here i'm going to create a function that is actually going to connect us to our sql database so your um your sql database should already already be running at this stage so i'm just going to name this function initialize db connection now to create a database connection we have to use the sql package and we're going to be using the sql.open function now this expects two parameters the first is the driver name and the second is the data source name so i'll explain what those are so the driver name is quite simple it's actually just what your driver is so we're gonna be using mysql and we actually do need to use this so the the sql driver that we just imported we actually need to use this as a unused import so let's just grab that as well so we have the mysql [Music] driver here and here you can see it says unused import so instead we just want to put a underscore here because we're going to use its side effects so now we can actually use mysql here and of course if this was like postgres you want to put postgres here then you have the data source name you want to look at your driver and it will if you go to the documentation for this so this is just the github page i'll put a link to this in the description um you'll find there to be here so the sn data source name and this is the structure that has to be in so let's just copy this and comment out just so we have a reference so we first have our username of our database so of the user who's connecting to the database the password for that user and the protocol we're going to be using the address and the database so let's actually do this so my username is root i don't actually have a password so i'm just going to leave that empty and then we put the uh at symbol then we want to specify the protocol so we're going to be using a tcp protocol and then in brackets the address so i'm just going to be using localhost and then on port 3306 and then we specify the database name so i called it testdb it's just here test underscore db and then we can add in any other parameters so one parameter that i always like to add is the pass time and then just set that equal to true so what this will do is it will actually scan times like timestamps into the time dot time type so this type that go actually provides us that's destruct this will actually be able to scan times uh like things like timestamps into this um later on i'm just going to show you guys how to do this with sqlx um you guys will understand why as we go through this you understand why it might be a little bit easier later on to actually something like sqlx but anyway for now we um so this returns us two things a database connection and an error in case the connection in case something goes wrong it can't connect so here we have the actual database and then an error so if the error is not equal to nil then i just want to panic with that error otherwise i want to just do a database.ping just to make sure that it's actually connected once again this also throws an error so let's do the same thing and if that throws an error we also want to panic out this is just to check that we actually are connected to our database and finally if everything's good here then i actually want to expose this database connection so once again this is just a um sql.db so it's of this type and it's a pointer so i just want to expose this to the entire project so to do that i'm just going to create a variable hit variable out here calling it a database client which is going to be appointed to sql.db and then we're just going to say db client is equal to db so we're just saying this global variable and this is global because the uh because it's capitalized here so it's actually being exported from our db client package i'm just going to set that to the database connection which if as long as these two errors don't as long as there are no errors here this should go successfully so then inside main i'm just going to do db client dot initialize database connection okay so let's just run go run main.go so this should actually just cancel out straight away that's because we don't we're not um we're not waiting for anything but as you can see there wasn't any panics here so we can assume that these were fine next let's just set up a basic gen server so r dot let's say r which is going to be a router is equal to gen dot default and let's say gen dot run and let's run this on port 5000 and this does return an error so we can say if error is equal to this and the error is not equal to no i'm just going to panic that error so if the gen server for some reason can't start up then we just want to panic okay so now i'm going to create another folder calling it controllers and in here let's say post controllers post controller so i'm just going to create some functions so create a post let's actually just start off with this one for now so let's do create post and if you guys aren't familiar with gin i do have a video about it that's why i'm not going into in depth as to what i'm doing when it on the gen side of things but i do have a video to that so there'll be a link um in the description to that okay so this create post is just going to be a route that's going to be a post route and it's just connected to the controllers dot create post handler and i'm going to create a type here so post is going to be it's going to look like this can have an id of int it's going to have a let's just check our database that's title content and create that so title string content is also going to be a string and created at is going to be a time then if i just add the jsons for these okay so we have our post struct so this is our one row in our database as this following structure so to create a post i'm just going to first bind the json from the request um from the request body into a post so let's say request body is of type post and then i'm just going to bind it to the request body and this does throw an error so let's catch that if error is to this and the error is not equal to nil we want to return and we want to see.json let's just say http dot status unprocessable entry entity and then there's also throw in a nice little message error true [Music] invalid response request body okay so i'm not going to deal with the validation because that could be a video in itself and something i'm considering doing so if you guys want to learn about how to validate request bodies um then just let me know in the comments below and i'll make a video on that but here we're not actually going to deal with that because that would take a little while so instead we're just going to always make sure that while we're making the requests that we have all the data okay so we want to create a post and we have the data here so what we want to do is connect to is use this database client and then execute a query where we insert into the database so if we get the db client from our db client package on this we have a couple of a couple of methods that we can run what i want to run is the exec to execute and then the first parameter this expects is the query that we're going to execute so i want to do is insert into my posts table i want to insert the following so the first is the title and the content so title content and then the values are just going to be question marks so the reason we put question marks here is so that these values are actually escaped so this way it protects us against sql injections so even if let's say the title let's say title is equal to something like drop posts table or something um then it wouldn't run instead it would instead it would save that title as the literal string drop whatever table so this is just to protect us against sql injections so this is the first parameter this expect that this expects the query the next is actually the args so the arguments that we're going to pass and this is a variatic parameter so we can pass in as many as we want and it has to be equal to the number of question marks that we have here and a quick note um this is for mysql i think in postgres there's a different symbol and then other sql databases there are slightly different symbols so you want to check your driver as to which as to what symbol is that you need to be using so first thing the first argument that we want to pass to is the title so i'm just going to say request dot request data body dot title and if we just multi-line this and the second one is the request body dot content so here what will happen is this title here will be put into here and it will be escaped and the content here or we put into this question mark and it will also be escaped so this throws um this can through an error and it also gives us a result so the error if it throws an error so if error is not okay to know then i actually just want to return an error here and just say status internal server error and i just leave it like that otherwise we get res so res has a couple of things that we can access on it so we have the last insert that id so here in my database the id is a auto incrementing integer so if we want to for example return what id was created for this post then we have to use this last inserted id and this returns the id as a int 64 and also returns an error all right now i'm actually going to ignore the error and i think just for the sake of saving space i'm also going to ignore the error here if you're wondering what could throw an error um if this is a poorly if this is uh has a syntax error then that could throw an error if say we had another argument here that we're expecting that could also throw an error um so normally you would actually normally 100 should be looking for these errors but just for the sake of this video i'm not going to be capturing those errors but as you saw before that is roughly how we would handle it we would just do an if statement for that error and then catch it like that so here we can get the last inserted id and we can also do rows affected so if this um if this if we're executing a query such as deleting something um we have to we want to actually check how many things we deleted then we would do rows affected that's uh what we would use this for yeah what i'm going to do is just a c.json http status created and then just do gen dot h see error is false and the id is this id okay so now that we have this let's actually test this out so let's open up our terminal go run main.go so now we're connected and i'm going to open up postman go to local host 5000 and if you remember the we just said this is a post request to just the base path so if we do a post request and we set the json data where we just want title some title [Music] and content to be some some content so if we save this we see error is false and we get an id of one so if i could check my database now and i refresh you can see we have this row here that is how we create a new entry so if i keep calling this you can see that the id is incrementing i refresh this we have the new rows okay so that's nice now let's actually be able to get all of our posts so now i'm going to create a new function and get posts the gen get the gen context and then what i want to do is do let's create a variable to hold our posts that we're going to fetch from the database so i'm just going to say posts is a slice of post then i'm going to get the database client again and then this time i'm going to query you might notice there are a couple of queries so there's query and there's query row and there's also query context and query row context so query is used for fetching multiple rows so this is just anything more than one and query row is if you want to just fetch one row um context is if you want to use something like a database transaction so query context is the same as query except except it expects a context so a date a sql transaction and query row context is the same as query row except obviously it also expects a sql transaction context so we're going to be doing query here because we're expecting uh what this is going to get all of the posts so this has a similar um similar function uh signature to exec where it expects the query and then the args so the arguments so the query is going to be select id title content and create it at from posts and we're not going to put anywhere so there's just going to get all of them and then we have no arguments here so we can actually just leave out arguments and this is going to return to us two things rose and error so if error is not equal to nil then i just want to return that so let's just grab uh grab this arrow here and we can i'm just going to leave the message out of course you should probably have a standard way of handling your error messages um the purpose of this video isn't to show you how to structure error messages or anything like that it's just to show you how to interact with the sql database and go so now handling the error what are these rows so rows what we can do is we can loop through it so what we would do is we'd create a for loop and we'd say rows.next so rows.next returns a billion and it will keep being true until until we're done with scanning all the rows and then it'll be false and it will break out of this for loop so here we're scanning through all the rows that it's going to each row we have is going to loop through it and then i'm going to create a variable here called single post which is just going to be of type post and the reason we're creating this is because we're going to populate this with one of the rows and then append it to our existing post slice so i'm going to say rows.scan so scan is what you use when you want to map or just scan through all of the data you've got so like it's the id title content and creator that we want to scan those into this struct here so the way that we do that is we pass pointers to where we want to scan it to and the order of this is actually important so the first one so in our select you can see the first value we get is the id so here we have to say the first is the post single post.id and this can throw an error if for example let's say id was a string in our case here id is actually an int so it won't it will throw an error and you'll see that here this actually does throw an error so let's catch that and if that's true that's just copy and paste that um but here this won't work so let's say we ordered all of these so single post dot title because titles next and then the content and then the creator that so this actually won't work what we have to do is we have to pass pointers to these so for each one we want to pass in a pointer so now it's the rows.scan is going to go to the pointer of this so wherever that is and it's going to put the current row that we're on into post singlepost.id and once again this can throw an error if this is something like a um if the types don't match so if you try scan a string into an end then that's going to throw an error similarly if you try to scan null into an end that will also throw an error um just a quick note the way you would get around null is through pointers so as an example if content was a nullable field i would put a pointer here because pointers can be no so which is equivalent to null so once again if this was possibly null then i'd put a pointer here um but in our case in the database the content um content column is actually not now that's fine so now we have this post here and if this all goes throughout then post should be populated with all of the data from that current row so what i'm going to do is just say posts which is the variable we initialized up here and i'm going to append to this a single post then once all of this is done we're just going to return c dot json http status okay which is equivalent to 200 and just the posts okay so now let's test that out let's create a get request and this is going to be get posts now if we restart the server and we open up postman again this time this changes to get request and send that you can see we have all of the posts here all the way up until five and i know these all have the same uh same text so i'm just going to change it up a bit and then just fetch the data again just so you can see that these are actually unique so you can see some title 2 sum 2 11 11 again so we are actually getting the data from the database okay so i showed you guys how to get all the posts but now what if you wanted to only get an individual post so let's handle that so funk um get post get the gen context and then we this is going to be a parameter uh let's say id string is equal to this and we actually want this as a array as an integer sorry so we're going to use string convert and then this expects the string and it returns to us the id as a integer or an error i'm just going to ignore the error for now okay and in case you guys are wondering what this would look like it would just be like this so this is why i'm saying we have parameter because we are going to be getting this parameter from the url this is going to be the id of the element that we're trying to of the row that we're trying to access and i am just ignoring the errors for now um so now instead of using query we're going to use query row so let's say db client dot db client query row so i'm going to select id title content and create it at from posts where the id is equal to something so a id is a parameter which i'm just going to pass as this parameter so it's going to take the id escape into here to protect us from sql injections and this returns to us a row and then what we can do is do a row.scan so now it's actually very similar to this section here where row.scan can return an error so we're going to say row.scan and if the error is not equal to no then i'm just going to return an error now let's actually create a variable so post is of type post and the same as before we have to make sure to get this in the correct order so we have to do the id first title content and then created that the post dot id post dot title post dot uh content and then post star created at then if everything's fine we just return the we just want to return the post okay so let's test this out i think first let's actually just create a couple so create that one and that one so we created one with id of six and one with id of seven so now if i do a get request for six we get this one and now if i do a get request with seven in the url we get the second one so that is working nicely and if we do something like an id that doesn't exist then we get error so this will return a error here and the type of this error is actually this so it's sql.error no rows and i just actually if i put this in the response so if i do message is error.error and i restart the server okay so if we restart this and we check postman now the arrow is just this sql no rows and results set which is identical to this is identical to this error here sql no rows and result set so if you ever want to check if there's no rows you would just do something like if the error is equal to sql error in a rows then maybe return some nicer message like status not found and then we if we format it like this and we start the server again then this time oh i forgot to return but as you can see here the status is actually four or four and if we were to do something that exists like seven [Music] we get 200 because i've shown you guys how to create and get um using query and get using query row uh now one thing i actually want to show you guys is a package called sqlx so you'll understand now once we go through sqlx why i think this is a lot easier to use and why i actually recommend it so if we just get this package so if we paste that in so if we actually just look at the documentation what sqlx allows us to do is actually map to a scan to a struct and it makes things a lot easier compared to using query and query row so to use it the first thing we have to do is actually come here to our connection and instead use sql x dot new d i think we can just do open yeah so it's actually going to be the exact same as this so i'm going to copy it paste in here and this is very similar where it returns db and error except the db that returns here isn't of type um isn't the sqldb it's the sql xdb now if we look at our posts you can see that it actually won't throw an error so if i was to start up the server again and let's say test something out like this it's still working so sqlx just wraps um the sql package and so a lot of the the same a lot of the same methods are still available like query row is still available you can still use that which makes it really easy to swap to if you if you already have a project that already uses the sql driver um so why do i suggest using sqlx well let's take this example here so we are scanning into this uh posts you can see we're having to do quite a bit of manual work now if we were to do this with sqlx then it would be db client dot db client um and we have these two methods so we have gets and we have a select so get is to get a single row and select this to get a slice of rows so similar to this would be query row and this would be query so if we look at the the signature of this it expects a destination which is going to be a pointer a query string and the arguments and then this returns an error so let's actually copy this let's paste in here this is a string oh and actually first it expects a destination so destination for this is posts and then we have the query string and we don't actually have any arguments so just to clean things up a little bit i'm just going to remove all of these so here we have posts and we are actually oh this is the get all burst so make sure to move this to the select one once again get only gets one row but here we're trying to get all the rows so we're going to use select and now what this will do is it will automatically scan the fields here according to their name into the struct here now let's say you had a different name to what the struct is so here we call this id and then the databases id then what we would do is as an additional tag we would just say db and then specify the name here so in our case it's still id um and if i actually do this for all of them it's just something that i often like to do in case the name here changes this would need to still reflect whatever the column is called in the database and this would be created at so now we specified what the database names are and of course this isn't required unless your name here is different to what the name of the column is in the database now if we come down here this returns an error which actually is just for now ignore the error um but yeah that's it this is all we have to do to scan into a slice so it's a lot less code than what we had before and let's actually do the same for this so instead of having to do all this let's keep this variable here instead of having to actually scan it ourselves let's delete that let's copy the string and then we're going to say db client not db client and this time we're trying to get a single row so we're going to use get we're going to say we want to map this we're going to we want to scan this to this post variable we have here which is of type post just provide oops just provide the string and then the id is the same as before where the arguments are just a very addictive parameter at the end so the only thing different between these two is here we get rows back number here and we get rows back and it only expects the query and the id but with get we expect the destination where it wants to map this to where else to scan this to and still expects the query and the id or whatever other arguments that you have so now if i remove that this um this just returns an error as well which i'm just going to ignore for now you can see these are a lot smaller than us using the standard sql package so let's test this and actually make sure it's working so oh we have the unused import let's remove that so now we've started up let's get seven again and you can see it's still working still the same so now one thing i want to point out is this so let's say i remove so created app if i'm going to remove the database name here will actually throw an error so i think if i scan this you can see it's actually not working and if i log the error for that so if there is like to know i just want to print this standard out and you can see missing destination name crate created at in the post type um so now if i add this back in created at you'll see that now it should work and if i run this you can see we're not getting any error here and it's working but at the same time if i was to remove it for the database for the id one here and we were to restart the server and if asked to run this again you can see when i'm not getting error this time the reason for this is id is the same as the column in the database the birth id but created that is not the same because in the database that column expects a underscore so this is what it looks like in the database it has the underscore and here we don't have an underscore so that's the reason why um only for this one we can actually remove it for all of them apart from create the dat only for created out where it's slightly different to the column name in the database do we have to use this and another point is this uses the values here so the name of the values here so if you're doing something like posts so if you had multiple tables being left joined or joined on um and then you're accessing it like this you might want to do as id because it's going to use this name here or this name here to actually scan it into the into the struct so just keep that in mind but yeah that's that's it for this video i know it's quite a simple one and we didn't go over too much um i know i didn't go over the so we did the crate get and get so get single and get multiple the reason i didn't go over update and delete because they're very similar to the create one where you just do the update query um and the delete it would just be the delete query the main thing i want to show you guys is this execute method and the main thing you want to learn on this is that it could throw an error so just keep that in mind remember to handle the errors i'm just not doing it in this video and the two key methods that we get back from this result is the last inserted id and the affected rows so that's all there is for the execute and then when it comes to actually scanning data into variables i recommend using sqlx it's very simple and it's very very similar to the existing sql package but it gives us these two very nice very nice methods get and select where select will get you a slice will get you multiple rows um from whatever you selected here and it will just automatically scan it into the structure and if the name here the name of the struct whatever field is differs from the name of the the name of the column in your database then you want to specify here as an additional key db and then the name of the column in your database you don't need to do this for fields that already have the same name as the column name in the database and so just keep that in mind and um you want to also make sure that these are pointers so this will whenever you're scanning into something you have to provide a pointer to it instead of a copy of it and this also does return an error so you would want to take care of that error and this returns the same errors as the sql package so this error could be uh for example sql to error no rows so if there's no rows then uh even the sqls package will still throw the same errors so i hope you guys enjoyed this video hope made sense if you guys are interested in me making more go videos thinking about making one to show you guys how to use a json web token um and maybe one to show you guys how to handle validation since i think that's a very important thing that you have nice validation messages so if you guys enjoyed this video i'd appreciate a like if you want to see more go videos and just let me know in the comments below what you want to see um and i'd also appreciate a subscribe it really helps me out so yeah thanks for watching bye [Music]

Channel: LetsCode

Views: 2,758

Rating: 4.8688526 out of 5

Keywords: golang tutorial, go programming language, golang database, web application programming in go (golang), golang, go, go tutorial, sql, mysql, golang sql, golang mysql, gin, gin gonic

Id: vfL2x8XU9Vs

Length: 36min 51sec (2211 seconds)

Published: Sat Oct 24 2020