Learning Golang: Relational Databases using MySQL

hello my name is mario welcome to another learning go video in today's episode i'm sharing with you how to interact with relational databases specifically when using mysql as usual the link to the code will be in the description of this video feel free to check that out so this episode is based on the previous video that i recorded when i introduced database sql but i was using the pgx driver for postgres i want to show you a few key differences when using my sql first of all we're going to be using this package called go sql driver my sequel i think it's one of the most popular ones it works for maria dv as well if you want to use that instead of using my sql it's basically the same step so i have this example that like i mentioned before that is literally using a sort of like example that what we did with pgx but what i'm going to be doing is showing you the differences and i'm going to be refactoring this a little bit in the end to show you what what is um applies specifically to my sql there is a readme here that is important because i'm going to be using these for running these instructions for running my docker container locally of my sql and also for creating the table so first things first i'm going to create this container that is going to be running there uh this usually takes a little bit so you have to wait before you actually interact with my sequel this is obviously the first time but as soon as you see this message this is ready for connections uh you should be able or rather not that one this one ready for connections uh you should be able to actually interact with the database so now that i have that what i'm going to be doing is i'm going to be taking the same container name i'm going to do my sql root p local host and if you remember these details were actually the values that i have here in the when i run the container using docker so i'm just going to be connecting to the database the password is password and now i have my database uh available so the dv name is the database that i'm going to be using so use db name and use copy and paste what i have above here for creating the table now i don't have any users so select start from users there are no users so for this example i'm going to be creating a record so insert into users which will be name and birth year and values which will be mario and 1900 so with this i can insert i inserted a record if i run this again i have my record already there now if we go back to the example what we really need first of all is to download the module and for downloading the module it's literally just doing a go get mysql latest and this will be adding the information that we need to our module so in our go mode we actually have this that was just added we need to call the go ma mod tidy to remove the indirect that is right here so as soon as the that is completed uh actually before i do that because what just happened is that because i did the mod tidy it's still referring to the file to the package that i was using for postgres so if you do this again that's why you saw that it's downloaded in a few of different packages for postgres and whatnot so after i do the mod tidy i can just reload the file and the indirect command will be gone now if you're familiar with how the modules work behind the scenes i have a video as well so i will be leaving that one in the description so you can check it out whenever you have some free time now how does it work in in reality before i do that i want to show you just a quick walkthrough to the code it's just basically opening a connection you know their typical url configuration for connecting to the database using the dsn it's opening like i said a new connection to the not the connection but it's opening the the connection is is a pinging and inquiring value that specifically searches for let me break the break here so it's easier to follow so specifically is searching for a record that has the name mario and the the birth year 1900 which is what i i just added of one level if you remember right here now all of this is still using this should be impossible it should be like this just still using all the different configuration values that are coming from postgres specifically when using the pdx package so if we try to do this literally just changing a few of the configurations that we have so the user is not using it anymore it's root we don't need this thing but i want to i'm going to comment that out for now because it's going to be important in a few moments and i do my sequel and in practice all of this should be working right so if i do a go run main uh it says that the network is failing the reason is unknown and this is when things get a little complicated so i want to show you the actual content that the dsn is generated now i want to show you all of this and explain you why why is important because the way this package driver this driver was implemented is a little bit different uh for the dsn specifically so if you notice right here is that is you use it your your scheme and then user password and so on however the way the people that work on this package implementing dsn is a little bit different you notice that there is a username password and there is a protocol in here and the address with the port and so forth so what will be the protocol then well typically when you're using my sql and you're connecting to this database you're going to be using tcp obviously there are other ways to to connect to the database but let's say most of the times you're opening a tcp a connection to my sql so with this if we run this again and now the issue is saying hey there is an error because my sql is doesn't have access but if you remember we're now using my sql we're using root and here comes the big problem the issue is that we cannot use url.url for creating a dsn a valid the ascent and instead we have to use a type that is common in the mysql uh package and it's called config and this is something that is one of the biggest differences i will show you the other difference as well so i just save it so it stops the complaining so we have my sql config which is coming uh let me reload this because typically that's what happens with is this compiling you know that happens sometimes with go please and go theme so what i'm going to be doing which is typically the solution is to close and open the editor and that's pretty much it so there you go it's one of those things that if you're using golfing i'm still having a hard time but anyways so we have a user which will be root we have a password which will be password we have the net which will be tcp because that's how we're connecting the db name will be db name and another thing that i like about this uh that is that is added in the package itself is there are a few different arguments that you can pass in when you're connecting to the database and those will be uh give you different uh features depending on which one you use one that i'm going to be showing you is called parsed time i'm not going to use it just yet because i want to demonstrate how this works but it basically just tries to convert the details that are coming from the database that are saved in a data date time timestamp rather and then convert those into a time dot time type in go behind the scenes you don't have to use the conversion so for now i'm just going to comment i'm going to get rid of this yep we don't need this we don't need this anymore we're going to take the config and the config has a uh bars um this and their format dsn method and that one will be the one that we're going to be using for connecting to the database all right so to recapitulate we are not using the url.url that we used before now we are using config now we run it again now it's working and if you notice i mean it's failing but it does expect it now if you notice the actual dsn is following the structure that is coming from the documentation so you have a user password the tcp the database and so on and so forth okay so everything seems to be working as expected so how we go about fixing this unknown dollar sign error message so basically in by sql instead of using the dollar sign placeholder you need to use question mark and that's basically now you can use whatever you were using before and it will work now this is what i was showing you a while ago um about the parse time option that you can pass in in the in the connection extreme and for that this is actually a type or writer a field called pars time that is a boolean you put it through and you run this again and now it's working now the utc is just easy to see behind the scenes so it's converting those for you so you don't have to worry about any of that and that's basically it with my sequel literally everything compared to pgx specifically is that the things that that you are available that you have available are not the same as pdx has a way to also interact with my with postgres via their own api this driver does not have that option you have to use the standard library methods and conventions that are coming from databases sql the package in the standard library for interacting with the database it's not a big deal honestly the the only thing that we'll be covering in a future video will be how to do bulk inserts using my sequel is similar to bjx so i hope this makes sense to you and any questions any comments please let me know in the section below i will talk to you next time stay safe and take care bye

Channel: Mario Carrion

Views: 246

Keywords: golang, golang mysql, golang relational, golang databases, golang database/sql, golang relational database, golang database tutorial, golang databases tutorial, golang database mysql, golang go-mysql, go-sql-driver/mysql, golang go-sql-driver/mysql

Id: gT2ztW4j66A

Length: 10min 44sec (644 seconds)

Published: Thu Dec 09 2021