Learning Golang: Relational Databases - Introduction to database/sql

hello my name is mario welcome to another learning go video in today's episode i'm going to be starting a series covering specifically how to access relational databases in go so let's go as usual the link to this example will be in the description this video will show the actual final implementation what i have right here is your typical uh use an empty empty go module that doesn't have anything outside of the minimum requirements in the standard library what we're going to be doing is we're going to be using the database sql package and specifically one of the drivers for accessing postgresql you can use postgresql with docker or maybe you can install it you know your typical a package manager if you're using linux or maybe you can download the actual binary the executable if you're using windows whatever you you like doing it now what i want to do in a future episode is i'm going to be covering also my sequel for this specific one is only posgres now go in the standard library does not provide a package for any database so what it provides though is a way to access multiple databases that happen to be implemented the api that the go standard library defined now first thing first we're going to be using this package like i said pgx or maybe the inside before but pgx is most recent it's one of the most supported packages for postgresql typically what you see online will be using lib pq but that one is not maintained that much anymore so highly encourage you if you're using postgres to use pgx instead pgx supports the database sql api as well as its own that i will be covering in future episodes in order to use this package you will have to download the module and in order to download the module you will have to initialize a module if you are not familiar with modules i highly encourage to look at the video that i recorded previously i will be leaving the link in the description so what we're going to be doing first we're going to be downloading the module and if you notice now is my go mod now i would include all those files or rather all those packages one important thing about bgx is that by default you will we will not you will be using the pgx implementation of uh writer you will not be able to database this database sql unless you import the stdlib package so what we're going to be doing here in main dot go is we're going to be doing an import uh the same you know the path that we defined before for the module and std lib and we're going to be doing an empty import and what this does is it initializes the package and that we need for accessing database the database in postgres now obviously we need to run our docker container because i said i want to be running docker so i have this fancy just copy and paste instruction that i will show in you that i will be oops i will be showing you right here so i have my docker container that is going to be using postgres 12.5 the database name is db name the password is password that user is user all of this is important because when we're connecting to the database we need to indicate all these fields so i have my docker container running right there if i do a docker ps it will show as usual so i'm going to be opening another uh another terminal and i'm going to be 20 21 11 17 and we're going to be doing here i'm going to be using this one for running the binary for the demonstration and this one what is going to be happening i'm going to be doing an it an interactive exec the name of the container that i'm running p sql u user and the db name and this is for showing you the different uh what the heck happened oh exact i move the parameters and so exact id and so on so forth so with this what i'm going to be showing you is a way to access the the values that i have in the database you can use a table plus if you are using mac os maybe you can use pg admin of your on windows you can use a client for accessing postgres this is a way a simple way to to get the data directly from the actual database without using a gui you can use whatever you want basically what i'm trying to say now how can we connect to postgresql and this is one of the important things to to have is that when you are dealing with specifically databases typically what you have is use a string that indicates the dsn which will be the data source name that describes the details about the connection that we're going to be using for connecting to database the way that i like doing it is defining the dsn but typically and this depends on the driver typically what you're going to be doing is uh using a string but in this case again in this case of this driver we're going to be using is the schema url let me let me open this or rather save it so we can get auto completion so the dsn will allow us to define a sort of like a schema using postgres so we have scheme postgres and we're going to be defining the host which will be localhost because that's where we're running 3054.32 if you remember those that was the port that i used for exporting my docker container which is actually right here and next will be the actual user so user will be user your user password you will be user if you remember again i'm going to scroll up a little bit so you can see it so you have user is user and password is password so we have user password and next will be a path that indicates the database name which will be db.name so now that i have here this i need to indicate um specifically to postgres i need to indicate if we're using a ssl mode in that case we'll be we can use the query param to get the data and then add this is ssl mode disable and with that what we're going to be doing is the sds dsn raw query q and code and now if you're familiar with the net well not that the net http route the net url package you will notice that we're using this type to create a url that we're going to be passing in to the sql open function and you will see that in a moment so with this we can actually take the data that we need and actually all right the configuration that we need and start connecting to our database so the first thing we're going to be doing is calling um database sql open and what open receives first if you notice is that it actually if you look at the documentation that i have here on the screen it will be receiving the driver name and the data source name the driver name will be pgx which is the is the package that we're importing right here and the actual dsn will be dsn string and with this if you remember what open is doing is returns a database which is not an actual database connection but rather a pool of connections and that is one of the confusing things in the beginning when dealing with databases in go so we're going to do generating a db and an error and we close this one and as usual if error is different than nil we're going to be doing fmp println sql open and error and return and we save it and everything's a seems to be working now the the thing with this is that when you open a database uh or a pool of databases or a pool of connections rather you need to close those so we are going to be closing them we now defer so defer function and we're going to be calling there a method for closing the functions in db so i'm going to printing out a message so you know that is closed for context so i'm using println instead of using for example log of fatal f or fatal ln because when you're calling fatal l it will call os exit and defer will not be called and when you're calling os dot exit that's something that you need to consider now with this in place what we are going to be doing is that uh we can take and actually um do something about the database so we can create a table that we're going to be using if we go and and again i have my b sql right here and i'm going to be taking another instruction for creating a database we can create a table called users and then these users will have a name varchar not known a birth year followed by some configuration it will be a small and and null default zero i mean you can literally do whatever you want but for this example we are creating a table so with this in place what we can do is actually use one of the instructions for inserting a value let's insert value let's call insert into users and we have name birth year see values the name will be you know me and then let's say 900 1900 and now we have a user and with a birth year okay so that's that's uh more than enough what are we going to be doing next is we're going to be accessing the value using a method called query now one of the things that happened in i don't recall exactly i think is what when context was introduced some of the functions uh rather the methods and some of the functions as well in the standard library started adding the context as one of the arguments in in new functions and if you haven't seen the video that where this where i was discussing context i will be leaving the link in the description as well so you can check that out but basically we're going to be using all the context like methods in db for interacting with our database context is used for cancellation error propagation and also for adding key value values that happen to be you know passed down into multiple layers in our program specifically for this one we're going to be using query row that only returns a row that we want to so we're going to be doing query row select from users where name equals well mario so we have a row error and typically we need to close this quote and you know as usual println db query context if there was an error and i return and finally what i want to be doing with the way it works we need to scan the values which is okay we get a result from the database we need to get the data into some sort of variable that go can understand and then we can do something for this value in this case rather instead of using select the star we're going to be using birth year and for birth year we're going to be using a type of n64 the way it works in go is you need to scan the values so you get a row and then if you notice is um so let me save this one so we have very query context and then row will be printing out a scan and we can do a birth year and that one will also oops will also return an error if something wrong happen so we have error we assign it and again if error is different than nil your your typical um error handler in go and finally we print ln the birth year in go so we print it out and everything should be compiling as usual so mismatch variables oh of course so row actually returned only query row context only returns a row so what's happening is that row has an error function that or method that it turns an error if something went wrong so we're going to be doing this and after that we can actually call a scan so with that if we run our binary you will see the actual oh of course remember i was using context so i need to pass in the context background so with that i can run it and and it should be okay because i got birth year and then closed all right so but there is a gotcha with this one let me show you something so the assumption right here is that we have a server running and actually we do right we have it we have our server running right here but most of the times we need to check if that actual connection is available so what i want to be doing is i'm going to be stopping this for now and i'm going to show you what happens when you try to run the same program without actually doing something which is one of the gotchas when trying to access relational databases and that will be calling ping before you actually access or execute any command what happens here is that sql open doesn't actually connect to any database it remembers a pool of connections what typically happens and depends on the driver is what it does is that it checks the dsn and it makes sure that the actual value is correct and it fails if it isn't so what we need to do here is we need to do a db ping context which we again using the default context it will return an error and if error is different than nil we need to just copy paste this thing put it up here return and move on so ping context if i run the same command you will notice that now it's going to be telling me hey there was an error with the pin context or rather pink context fail you cannot connect to the database that's it so if we start our previous server we start the container it runs i can actually run it again and it will work as expected so that's really cool so something to keep in mind when you are dealing with connections in go and again it depends there is a way to actually actually access the connection after you open i will cover that in a future episode so we have this we can now move on to the next uh command that is applicable to when accessing databases that will be actually inserting records into the database and that one will be a method called accept content and accept content and accept content exact context will be receiving a background or rather a context that it could be it doesn't have to be background again remember context the way it works is that you can pass down depending on the different layers that you have for passing down context propagation for error handling or rather for passing down cancellation and those kind of things so we have an an exec context and we're going to be doing we're going to be insert into user name birth year values we can call them wario and 1901 we close this and we quote it and what is happening here in exit context is going to be returning our result and result indicates if or how many rows were inserted the ideas the ids if any of those insertions they're returning an id and that depends on the driver actually and an error so we have a result error and we can do it again you're typical we can oopsie if err is different than nil what would i use close um exit db exit context er return and then result includes the what i was mentioning the the last inserted id and how many rows were affected in this case we don't really care too much of this so we're going to be changing this a little bit to be like that so we are executing it if we run this again let me open my docker extract so again you have select from users and we still have the one that we added manually before if i run this command you will notice that now i have two users right so everything again nothing overly complicated if we go and take these and one thing that you need to that you need to consider when dealing with insertions and typically these values are going to be coming from your in from your users and specifically when you're doing something that indeed that is a write operation like updates or insert you need to you you will be receiving the values from your customers like i said but instead of using some sort of string manipulation you have to use placeholders and i covered this in a video uh regarding sql injection and i highly encourage you that you watch that one as well the way placeholders work in postgres specifically is by using a sequential dollar sign placeholder that indicates the value circuit you want to be passing in your argument and let me change this a little bit so it's much more cleaner clearer so what is going to happen here is that remember all of these arguments that i have all of the methods that i'm showing you in exec exact context query row context and query context not only receive a query string but also arguments and those arguments are what we are going to be trying to replace as in as the placeholders okay so in this case there are two arguments we have one and two which will be equivalent to birth year and name respectively so name let's call it again let's try a different one will be warrior two and birth year will be let's say now a three all right so what is going to happen we can pass in those values now as the arguments that we indicated before in the documentation that is right here so we have arcs will be a variati variatic collection of arguments that like i have right here uh that's how it's indicated now if i run this again and what is going to happen is that why is this complaining oh i know okay so we have an error we close this and we can run it again what is going to happen now is that now i inserted another record if i run this again i now have another record now this is again called placeholders that is really important and it doesn't only apply to when you're making or doing write operations it also applies to when you're doing selects and and whatnot again it depends of your relational database and not all all the commands are available or support placeholders so it's just something that you need to check out in your database when you're working with specific engines and whatnot so next one we're going to be getting rid of this because we don't want to duplicate values we are going to be doing the last one which will be query context and query context similarly to previous arguments receives as a context and also allows you to indicate what fields or write the query that you want to do which will be birth year from the table from users and it returns rows and error and this is a little bit different to query row context that we have above here yeah because this one only returns one or is expected to return at most one and this one is expected to return more than one whatever is found so we're going to be selecting name and birth year forms from users and if you run this in the terminal it will be sort of like similar to what you see on the screen but we're going to be using another api that it comes in gold now where obviously if there is an error and i know this is going to repetitive and you're thinking hey is there any better way to do this and yes it is and i will cover that in future episodes so stay with me now rows has an error function or method in this specific case so if error is different than nil well there was another error and you need to just fail and move on because rose is sort of like um iterator is using sort of like an iterator pattern one of the things that we have to do is we actually have to close the rows after we are done using them so there's a function called close that you have to call typically in a defer defer after everything is working so like i said there is an iterator in rows and you have to take and use a for loop there are a few methods that are in this type one of them will be next and next as you may imagine it just returns true when there are more values that you can read from and next is using conjunction with another function called scan which is similar to the scan that we use in the query row context method let me show you so we have row next if next is true it will keep going and we're going to be doing is we're going to be doing something similar that we did before so we're going to define a bar birthday bar first year that actually we have to redefine this birth year but you know for the sake of defining it inside the block of the four and next we have to use a row scan that will take the values from the result that is coming from the database and assign those to the um values that we have in go and as you may imagine there is also another check for um checking you know error handling with the rows scan error and if there is an error you scan and finally we're going to just print out the values name name birth year and last one birth year and with that if we compile it you will see everything is success and if you run it you will see all the fields that we use access previously with our psql which is our command line so um how cool is this i know i get it that this all of these accessing the basic things are where there is so much boilerplate but i promise you in future episodes we're going to be doing is taking what we understood and what we learned before and and making sure that everything that we are using right here will be made easier when you have a better way and a better tools for accessing the database in a much more easier way either with auto generated code and maybe some sort of uh behind the scenes mapping between the fields the types and the columns into the database and also your typical orms so again thank you for watching and if you're still here you know write in the comments pgx or let me know what database you want to see in the next episode i will talk to you next time take care see

Channel: Mario Carrion

Views: 721

Keywords: golang, golang databases, golang database/sql, golang relational database, golang database tutorial, golang databases tutorial

Id: 2XCaKYH0Ydo

Length: 25min 32sec (1532 seconds)

Published: Fri Nov 19 2021