Go & SQL databases: docker compose setup and first queries step by step tutorial

hello folks welcome to this session today I'm going to show you how to make queries to an SQL database with go and we will see how to set up your Dev environment with Docker compose so let's get started so I've created a new project in my IDE and then I'm going to open the terminal I go into the directory of that project and I'm going to initialize my go module so go mod init and then I type the module path github.com maximinia Honda slash demo Dash MySQL and then I press enter you will see that you will have a google.mod file created so now we are ready to start our project in order to test locally our development I'm going to use what is called Docker compose what is Docker you may ask so if we take a look at the Wikipedia page you can see that it's a set of platform as a service products that use OS level virtualization to deliver software package called containers we not go deep into that but I want just want you to know that Docker will help us in order to create containers and in those containers we will be able to run our application and we will be able also to create a database in order to test it would be on a local machine it will be a container running on a local machine and Docker compose is something that has been added to Docker that makes it very easy to do so you will first have to install a Docker desktop so I'm going to give you the link but you have the instructions here so you have the installation for Mac you can download the installer and you also have the installation instruction on windows so in order to run that you will first need to install docker so after you've installed Docker you come back to your project and then you're going to create a new file and you're going to name that file docker Dash compose dot yml so it's a yaml file and in this file we are going to describe what is our test environment the first thing that we are going to do we are going to define the version of the docker compose specification that we are going to use in that file so I write version then colon and then I open quotes and I'm going to put 3.3 then you are going to write services on the next line and then a column and then you press enter so generally your IDE will help you to write that and by the way here you can know that it has automatically added two spaces so that's how a yaml files are constructed you use two spaces in order to indent the file so here I'm going to Define services so then I have two spaces and then I'm going to Define all my services well I will have a database but I can also have another service which can be named application okay and then for each service I'm going to describe what is this service so it's just named database for the moment but I need to describe what do I put inside that just below database I'm going to start with image so this is a property and the value of this property should be the name of an image so for the moment let's put XXX here uh then we can also Define the container name and for the container name I'm going to name that database for instance and then I can set up some environment variables that will be set inside my container so each service in a data compose file will be a container that is built with what we call an image and we can also set some environment variables for the moment we will leave it empty um now we need to Define which image do we want so in order to find that you will need to go to a site that is called Docker hub Docker Hub is an image Library then you're going to click here on explore and then you can search for the software that you want to use I'm going to type MySQL to see what's in here so here you have a list of images available what I advise you is to filter only for Docker official images it's more safe so here you can see we have MySQL we have Maya DB we have perkona phpmyadmin so I want to install a mySQL database so I have the choice between those three so MySQL this is the MySQL original relational database management system and then you have some folks and we have the Maya DB Fork so it has taken the code from MySQL plus added some code and modified some code so we say it's a folk and you also have perkona so what I use is generally my ADB so I'm going to go for my ADB and then I need to find the image that I want to use and each image as what we call an image tag so you're gonna check here what you want so here you have the tags that are supported um so what I'm going to do is I'm going to select uh the latest one which is 10.10 so we need to say in our local compose file that we use my ADB add the version 10.10 so what I do usually is that I copy that so that part my rdb and then I go back to my Docker compose and then I paste that and then I have to give attack so then I purchase 10.10 and you can see that you have by the way autocomplete that is provided by the IDE so we are going to use this image Maya DB at the version 10.10 we're going to name the container database and then we can also provide some environments let's take a look at the documentation of the image here and you can see that you have examples here of environment viable so we need to set the my rdb root password and I'm going just to copy that and paste it here so you see we are going to set an environment variable Maya DB root password so this is the name of the environment variable and the value example here I'm going to remove application for the moment so here it's just for test so we are going to bootstrap a Maya DB database that will leave inside a container and we're gonna set the root password to example then we will need to set up networking in Docker compose so as you can see in the documentation by default Docker compose we've set up a single Network for your app each container for a service the database will be a container here we'll join the default Network and it will be reachable by other containers so if you have other containers and other services and discoverable by them and here you have an example here so you can see that you have two service the service web and you have those spots for the service web and the service DB and you have those spots for the service DB let me explain you briefly this concept of ports within Docker compose so here it's for machine on blue and then you have to occur here inside Docker you will have containers so let's add a container here it will be the container for web and then you can have another container here for instance we have a container for the database those two containers can talk to each other the web can talk to DB and DB can talk to web and they're going to do that via a port so those two containers can talk to each other but you can also talk to those containers from your machine you can talk to web you can talk to DB and you will talk to them via a specific part so which part is it you can see that you have two parts here a port is a number an integer number so here you have eight thousand and eight thousand here you have eight thousand one and here you have 54 32. so the first Port here is named the OST port what do I have here so the first one is the host port and the second one is the container port now let's say that I'm web and then I want to contact DB I'm going to send a request underneath twark and use the port 5432 the one that is written here so the container part of the DB and now if I'm DB and I want to contact web I'm going to use the part eight zero zero zero zero that's it and then if I am on my computer and I want to contact uh web I'm going to use whichbot 800 so this one so don't be confused it's the same here but if I want to contact the DB I'm going to use the port 8001 let's go back to our code so I should add that ports and then I should declare the ports that I'm going to use so ports then I can learn then enter and then a dash and the ports so let's do that so it's going to be pause and then I add a dash and it's going to be three zero zero six and three zero zero six I'm going to use the same boat for the sake of Simplicity but remember this is the hotspot and this is the container port now it's time for us to run that so to run that I open my terminal and then I type Docker Dash compose and then a hub so I have an error it says cannot connect to the docker demand so if you have that error it means that you have to start Docker so let me start that you should have this window if you work on Mac OS so the code desktop will start once stalker has started you can run Docker compose app in order to start your container so for the moment I will have just one computer and you can see that the image is downloading on my computer and once it's downloaded it will start this container and we will have a database so when you see that it means that your Docker compose infrastructure has been created on your local machine so everything is on your local on your computer nothing on the internet for the moment so if you want to exit that you can press Ctrl C and it's going to stop that you can use Docker compose app Dash D it's going to detach the process so you can still use your terminal after that so that's it we have installed a database that lives inside a container that we can use in order to test our go application so now we can develop the go application so what I'm going to do first is I'm going to create a directory internal each time I create an application I always create an internal directory and in that directory I'm going to create subdirectories and each subdirectory will be a go package the internal directory is a little bit specific if you put a package inside internal then you will not be able to have people importing your code in the code base that's a way to hide your internal details nobody will be able to import your code and run it if it lives inside internal otherwise if you put your card on GitHub anybody can inject that code into their project so it's a way to protect yourself against usage that you don't want inside this internal directory I'm going to create a new directory and I'm calling storage and inside that directory we're going to have the package storage so I'm going to create a new go file that I name storage so here I will create function methods types that will be inside the package storage then I will create at the root a new go file that I'm naming Main so main.go will be our entry point and you can see that my IDE as added package when function main so this is the entry point of your program your program will start here the first thing that I'm going to do in the package storage is to create a type interface so we name that interface storage and we will export it so it's going to be type storage and then interface and then your bank and close curly brackets a type interface is a contract you are going to Define inside a set of methods and in order to be a storage you should Implement all this methods so we can have a type MySQL storage where we will Implement all the methods so it can be a storage and we can also have another what we call implementation for instance mongodb storage where we'll have all those methods implemented so ready to type storage interface will be a contract and we'll Define our methods we will not Implement them inside the time.fs but we will just Define the name input and output so let's go what do we want to store we can store for instance books let's say that we want to have a software that will be used by a library uh so what I'm going to do is I'm going to create a new directory here that I name book and inside that directory I'm going to create a new go file that is a simple file that I name also book and inside that file so that will be the package book I will have a type book based on a struct and we are going to Define some Fields so we will have an ID that is of type string we will have the name that is of type string we will have the auto name that will be a string and we can also have a create time so the time at which we have we are going to create the book in the system so it's going to be a time.time and no I have time that is imported my my ID so I have my type book so I want to store books so then install my storage I'm going to Define an interface create book and it will take as input a book Dot book so you can see they have my import that is automatically added here and in output it's going uh to return an error because the creation of the book can fail and by the way why not just naming that create because we take as input a book so why not just create I have my interface no I want to create an implementation of my interface for my SQL so we want to develop the logic to create a book into MySQL to save a book into our database so to do that I'm going to create a new file a new go file in the storage directory that will be named MySQL and in this package I'm going to create a type and I name my SQL storage and it's based on a strict you can note that the type is exported because the first letter is capitalized so in all the parts on file program I will be able to import my SQL storage then I need to create the method create in order to implement my interface so in my interface I have just one method so I have to implement that method on this type to do that it's really easy you have to create a method on MySQL storage named create that tanks as input an element of type book DOT book right and that returns an error and then you can see that my IDE has automatically detected that I Implement an interface if I click here I will see this interface so the type MySQL storage implements the interface storage why does it implement the interface storage that's because I have this method defined that has the same name and signature um so for the moment let's return nil here and we will need to implement that to do an insert into our mySQL database database the next step is to create a new function a function that will initialize my SQL storage so I've named that function new MySQL storage and this function will return an element of type my sequence storage or an error and here we have to make the connection to the database so in order to make the connection to the database we first need to install a driver the standard library of go is not shipped with a MySQL driver you have to install it there is one driver that is really popular this is this one so the module passes github.com Square driver slash my secret to install it it's not really difficult we're gonna copy the path of the repository so github.com Square driver slash MySQL and then we're gonna open our IDE and inside the terminal we're going to type go get Dash U and then the module pass so it's going to add the driver to our code and then we will need to import it but with blank import so let me show you how to do that so you're gonna have to type here underscore and then you have to put here the path of the module so what does it do when you do that it's going to run the init functions that are defined here so here you can have many init functions that will be run here this is the standard way of adding a driver to your code so you do the blanking purpose you're not going to use internal functions from the driver but you just need those init functions to run it out register your driver then you're going to use the standard library to interact with your mySQL database once you've done that you will be able to open the connection to your mySQL database so let's do that so we're going to use SQL Dot open and then we have to give the driver name so the driver name is MySQL and then the data source name so the data source name is the where is the your database and what is the username and the password in short and which database you are going to use on that database server okay so let's construct this DSN for instance the moment let's put an empty string so I'm going to create a new variable here an mdsn and then I'm going to use fmt.printfinal Auto construct that DSN so we have to have the username password the address the port and also the database that we are going to work with so I'm going to create a new type that I named MySQL config and in this type that is based on a struct I'm going to list everything that I need so I have username that would be string password that is also a string I will have also a DB name for the database name I will need also uh the port which will be an integer and by the way we can mount button newint because there is no such thing as a negative board and we can also put the host here which is a size of type straight then it's time to set up the DSM as you can see in the readme you have an example of DSN we're going to Simply copy that and then paste it into our project null to replace it step by step so I copy that as first arguments of this printf and then we need to replace the username by the real username and the password etc etc so to do that my new MySQL storage we need to have an input parameter let's call that conf so it will be of type MySQL config and then I replace it step by step so press stage s for username so I have to use conf dot username for the password is going to be postage s it's a string so conf dot password then the protocols we will talk with the database with the TCP so it's TCP and then the address here we're going to put a process stage s for the host and then I pressed HD for the port so it's gonna going to be conf dot host for the first and then conf dot port and then we have to give also the power the DB name so percentage s again and then we replace that by conf Dot dbnet we can remove this parameter here not necessary then I can use this variable here so DSN and then SQL open uh will return a pointer to an element of type DB and an error so I create a new variable DB and Air and I have to check the r if Dr is different than nil then what I'm going to do is I'm going to return an empty my sequester Edge and here but I can do better I can return fmt error f and I'm going to add some context to my error so impossible to open SQL connection and then I add the error with percentage W and pressure w means wrapping there the color will be able to unwrap there I'm going to do a video in order to explain that but for the moment remember that when you want to return an error it's always good to add some text in order to describe the arrow and then use First Stage W in order to inject the error and allow the color to unwrap the function new my sequence storage need to return an element of type MySQL storage so let's do that so if I succeed into opening the connection then I can return my object and then nil here because everything went well and then on my my sequester Edge I need to have this so the pointer to the DB in order to make operations on the DB so I'm going to create a new field here that will be of type SQL dot DB and it will be an element of type pointer to sqldp and then I can set here the field DB which would be equal to DB all right those our new my secret storage is done uh so then when I have this create function I will be able here to do a request to my database I will be able to do a query uh and it will be an insert query first um I would like to test that new function against my database let's do that so I go to the main.co and then I'm going to create my MySQL storage so let's call that store and I'm going to call Storage dot new MySQL storage I should give a config so it's going to be a storage Dot mysqlconfig and then we're going to fill all the fields and by the way it can also return an error so we have also that so if the error is different than nil then we have to exit our program so we can use log dot fatal f in order to add some details impossible to create my SQL storage and then percentage s and we are going to add Dr here so the username will be root with Docker compose the password this is the password that is set up here example so let's modify the password Here the DB name um do we have database created I'm going to put here DB so the part the part I have to check the docker composer 3006. and then the host it's going to be named DB not DB sorry it's going to be named database because when you are inside Docker compose so the service name will be also the host so it's going to be database then on my store I can just log it for instance here and the idea here see after that I can use my storage here the first step is to make sure that you have your Docker compose stack Hub so I'm gonna type into my terminal Docker compose and then app it will create the container and start the container what we can do now is to run our program in order to do that I open a new terminal and I type go run main go it will compile and run the program so you can see that we have that that is printed on the screen and so it means that we are here why if we add an error we would have this fatal and fatal will exit the program with the status code equal to 1 and when you run that you will have that status got printed it's going to print process exited with status one so here it means that everything went well um let's try to do an experiment and I'm going to stop the database so I press Ctrl C in order to stop it and then let's try to run again and here you can see that we have a similar output but we don't have the program that is exiting uh with lock fatal should not work now let's take a look at our Now new mystery storage so it's called it's calling open and you can see that in the documentation that open opens a database connection okay you can see here that open map just validate its argument without creating a connection to the database to verify that the data source name is valid called ping so this is what we are going to call now in order to check that we have a good database connection otherwise it's Church checking that the DSN is correct so let's go back and then we are going to try a pink um so we're gonna call TB Dot ping and then if we have any raw we're going to have the same treatment here here except that the message will be impossible to pinch database let's try to run our programmer game so go run main.go in the terminal and you can see here that we have an error it says impossible to pin DB the rtcp lookup database no search host today error is on the host so here I'm for the host iPad database this is wrong I cannot use the service name as host when I'm trying to contact the container from my local machine here I'm in my local version I'm not inside a container so I should use localhost instead I could use database if my application were running inside a container let's try to run the program again and you can see here that we have a weird error it says unexpected end of file and we have an error in packets.com at the line 37 so here inside the driver so this is really weird um so if we take a look here at our main.go you can see that I use the both 3006 so it looks okay and here on my ports it's three zero zero six and three zero zero six I made a mistake here again so here the parts should be changed the default part for my rdb is 3306. so what I need to do I need to change the container port to 3306 and I can also put 3306 here to contact him from the port 3306 on my local machine uh so I did change that so there is one thing that I should do I should stop my Docker compose app and I will run Docker compose app dash dash false recreate in order to recreate uh the container so let's go right it seems to be down and now let's try to run the program again so we still have an error but it's different it says connection refers to the power three zero zero six so let's fix that here I should put 3306 so it should be the same as the host part let's try to run again then I have an access denied so it seems that the connection I can connect but I'm review my access is refused so it means that I don't have the right username password so in order to fix that we can set up environment variables the environment variables to set up in your Docker compose dot yaml are the following one so MySQL user password so username and password the database and the root password so you may know that on a database you have a root user that you can use in order to perform whatever you want but it's more advised to have another user that is not the root user and here I have this user that is a username user and password password uh so I'm going to use that in order to connect so I need to change my main.go here inside username I should put username inside a password it's password DB name it's DB it doesn't change the part is correct it should be this one because I'm on my local machine and the host is localhost it's correct because I'm on my local machine so let's try to run that again go around mango uh it says access denied why does it say that that's because I changed my environment variables and I didn't reboot all my containers didn't recreate my containers so I need to do that first so I'm pressing Ctrl C here and then I running I'm I have to run Docker compose app fast recreate in order to recreate the com the container that's walking let's try to run that again it says access denied for user username I made a mistake here it's should be user and not username here it's user so it should be user no let's try to run the program again so Goran main.com here you can see that I still have an error but I changed my Docker compose environment variable so it should not raise an error so this error made me lose like 30 minutes of building this video what I did if you remember was to press Ctrl C in order to stop Docker compose when you do that and then when you run that again if you change your environment variable that are defined on the docker compose file it's not actually updated so instead of doing control C so you can still do Ctrl C if you want to have access to your terminal you should do Docker compose down number two stop all the containers and remove everything and then you can run the car compose app fast recreate let's do that so the database server will start and then we can try to launch a request so I open a new terminal and then I type go run main.co and normally we should be okay and here we are okay so the Ping succeeded we can connect to database no I would like to have some tables in that database in order to test properly my application so I will create some SQL scripts in order to initialize that database so let's get started I'm going to create a new directory then I'm calling Docker and then DB init and in that directory I'm going to create SQL scripts in order to populate the database so I'm going to create a first script I'm calling that init.sql and here I can build my SQL queries in order to initialize the database I purpose to use this script in order to create the table book so we have a column ID create time update time that are of type timestamp we have a column name that is varsha255 auto name that is varsha255 and a primary key that is ID and ID is auto incrementing note that we have our init script we want the my ADB container to run that script when it creates to do that if you take a look at the my ADB documentation you have to put these files into this directory to do that I will need to modify my Docker compose file I will add a key that is named volumes and in that key I'm going to specify which directory I want to put into the container so it's a little bit like the part directive here so the idea here is to put first the path of the directory on your local machine so here it's in the directory Docker here and it's DB in it and after the colon you're going to put the pass inside the container so it's going to be slash Docker entry point Dash init DB dot d just a small world about volumes with Docker so when I had that it means that this directory on my host on my machine it's going to be replicated into this directory on the container we create a shared volume between my localhost and the docker container so everything that is inside DB init will be inside this directory Docker Dash and Report Dash in ADB dot d in order to for that to be applied I'm going to run a Docker compose down and then we are going to up again the container totoka compose app dash dash false recreate no I would like to test if this change has been done successfully so to do that I'm going to connect to the database as my user that is created here so I run Docker compose exec database I put the service name so if I want to connect to another service I change that and then I'm going to ask for the Maya DB command line and I will connect as the user user so Dash you then the username and then with the password which would be password so Dash p and the password let's try a document I'm connected uh so I can run show databases in order to list all the database that I have here so we have two database so this one is created automatically and this is our database so I'm going to use DB okay and then I'm going to run show tables in order to display all the tables so you can see that we have one table that is book that has been created so my init script is working now that we have our Dev setup ready I'm going to finish the implementation of this create method so the first thing to do is to create a variable query that will contain the query so I will need to do an insert in into the table book and I'm going to insert the following row so create time so the current create time will be set and we need to also set the name of the book and then the author name and what will be the values so I put values here and then the parenthesis so for the create type I'm going to put now so on the current time to be set and then I will put those question marks in order to Define what we are going to put into a name and auto name then the next step is to run on my database so s dot DB I'm going to run exec context and why do I use exact context and not just exec because the two methods exist it's better to use exact context because with exact context we are going to pass as first argument a context and the context will allow you to propagate in your calls timeouts and also cancellation signals um so let's say you have a server and you have an API call that will make an insertion into the database if the duration of the insertion it goes over a certain amount of time you will be able to cancel the column propagate this cancellation to your database so I use exact context but in order to use that I need to modify my input parameter for create I need to add a context here so I named that settings and the type will be context dot context but since I had another input parameters to My Method it doesn't follow the interface so I will need to just change my interface so I add the context here so CTX end of type context dot context and I will also give a name to the book and no it's good so I can pass the context so the first argument will be the context of CTX that we got in inputs and then the second argument will be the query and then we can give the values that are expected here in the order that they are expected here so the first one will be the name so we're gonna get B dot name and the next one will be B Dot author name and exact context will return the result so let's call that insert a result and an error so when we have an error we want to return an error so if there are different than nil then I'm going to return an hour and as usual I'm going to wrap here also error while insert and then I put a column and percentage the value in order to wrap there with the insert result by the way there is a small typo here we are able to get the ID of the book that has been created and if you remember in my database I have an ID column and it's set to Auto increment so the value will Auto increment is going to be 1 then 2 3 etc etc um so it might be interesting to retrieve that ID because I insert that and I want to have the ID here so by the way the ID here is a string I'm going to modify that and put an end here because if I take a look at my table structure is here ID is an INT so um we'll retrieve this ID so to do that I'm going to use insert result dot last inset ID and this method will either return the ID or an error so ID and an error so I will under also Dr error while getting last insert ID I wrap the message and then I retrieve that ID so now what do I do with this ID I would like to return it to the user so I will need to slightly change my interface here I will also return the book that I have created so books.book and then an error in order to return the book okay so the method is ready now it's time to test it um so I'm go back to my main.co and I'm going to use store in order to call create so I'm going to give as first argument to context so I'm going to create a new context with context.background and contacts that background doesn't have a timeout or limit it will just wait until we have the result and then we can give the book so book Dot book the idea I'm going to leave it like that so default zero the name I'm going to put practical lessons Auto name I'm going to put my name and then for the create time I can put time dot now and by the way uh with the create time we don't use that in our query so we use B dot name and B Dot autonom and the create time the value will be equal to no I want to change that behavior I would like to insert the query time that is passed by the caller so I'm going to just add B dot create time here that way when I call create with a book and I feel the great time this will be what will be in the database um all right so I have to check here if something goes wrong so I'm going to check the arrow and the message is going to be impossible to insert and I'm going to log our sodium and then I can use log dot println in order to print the book that has been inserted into the database all right so let's uh test this script so I open a new terminal and then I'm going to run my program so go run main dot go as usual right so you can see that we have this that is printed on the screen so it seems that it worked so if we go back to our console I'm going to select everything from book and see if we have our book and you can see that we have to create time here that is said we have the name and the autonom so it seems to work all right so we'll stop here for the moment I will probably do another session about storage because it's at the head of each project with Google generally speaking with microservices I will just add some to-do's to the project um so here I do not like the fact that we return a book rare returning a book we should return only an error right I create a book I expect just an error if I my book is not created but why returning the book we are forced to do that because we have this ID column that is in Auto increments and so we cannot give an ID to our database and then set the ID so maybe if we want to not return a book we will have to modify that column and maybe put a string info into the ID and when I insert it in my storage I'm going to insert the ID classically the ID field of book is in 64 and we have been forced to modify that type in order to fit with the database I would suggest here maybe to use a string like it was before and maybe populate with and unique ID so you can use a library that generates a new ID and then insert that into your database why do I say that that's because for some databases you don't have this integer ID you have a string and generally we put uuids in the ID so your code will be more adaptable to other databases not only in MySQL another thing that I want to say is that for those two Fields so name and author name a string can be very large and in our database structure we have only varka 255 so varsha255 so it's limited to 255 characters and we didn't check that before inserting we even checked that the name in Auto name is not greater than 255. it can be a risk in our current implementation so I'm going to add a to-do check the input checking the input parameters is really important in an application it's going to improve the security of your application this is almost the end of this session so what do you need to remember you should always test your database interaction code with a real database and Docker compose is a great tool to do that in order to start your Docker compose stack you're going to type in your terminal Docker compose up and then dash dash false recreate if you want to First recreate the containers then in order to stop you're going to use Docker compose down for the ports and the volumes it's always the same pattern you have the host then a colon and then the container so the hotspot and the container port for the volumes it's going to be the path of your host so your localhost and the bus on the container with the go blank import you will run init functions of the package that you import and this is what we are doing when we are going to import our SQL driver we use a blanking port then I want you to remember that go is not shipped with a DB driver you have to install one and you will need to install another one if you want to contact another type of database then one important thing you should pass the context to your storage functions it will allow you to control the timeouts and the cancellation propagation in your application and by the way it's not only for storage if you have an API call you want to have a context with that then you should validate the input of your functions and the input of your code in general if we take the example the varsha255 then if I give a string that is greater than 255 characters then it's going to be cropped and we don't want that so thanks for watching this video was a bit long 50 minutes and I want to have your opinion about that is it too long tell me what you think in the comments and again some links to follow me practicalgressions.com you can read my book about go there is the online version free but you can also buy the digital version of the paper version to support me I also have a Goku's on that website 32 hours to build a real life website you can also follow me on Twitter LinkedIn and YouTube so see you later

Channel: Maximilien Andile

Views: 2,266

Keywords:

Id: q9uj1CniRYk

Length: 53min 44sec (3224 seconds)

Published: Mon Nov 28 2022