Learning Golang: Relational Databases - Transactions with PostgreSQL

hello my name is mario welcome to another learning go video in today's episode we're going to be talking about relational databases specifically about transactions as usual the link to the code will be in the description of this video so feel free to check that out this specific episode is going to be based on what we learned before with the introduction of database sql i'm going to be showing you two different ways to use transactions using both database sql and a concrete implementation of the pgx package which happens to be the driver for postgres to start with let me show you the schema that we are using just a reminder we have a schema called that is going to be using only a table called users there is a name and a birth year as the columns in that table important bits to remember will be that name is the bar chart no no and birth year is a small in null default zero this is important because we're going to be using a few things to make it fail and therefore i'm going to be explaining you why transactions are really important so let us start by showing you that we don't have anything in the database we don't have any values in the table so everything is fine so like i said we're going to be be working on what we built before so we're going to be refactoring the final example that i showed you previously in the uh previous episode so let's start with something we're going to be taking the data or the calls that we have here related to the connection just for the sake of cleaning that up we're going to be extracting that information and creating a new functional called new tv and this is going to be important when i show you the other implementation and that happens to be using pgx now remember the idea of defining transaction or rather using transactions is a way to indicate how multiple statements either sql statement or rather yeah obviously everything is equal but i mean a select statement or update or insert or things that change or read and are executed as an all or nothing kind of action so if any of those actions fails everything fails so in order for all of them to succeed all of them have to succeed and the whole point of using transactions is when you want to modify different or call different sql statements you can execute those as one in a single way now i have this and that i want to use refactor because i want to clean it up a little bit and just please bear with me right here so if you remember when you are dealing with errors you need to be using the new api that includes the wrap function the wrap verb that i covered previously in another episode so i have the new db right here and i want to clean it but i have it i want to clean this up so i'm going to have dv ernie of db and you know typically everything goes fine i'm going to remove a few things just to make this a little bit easier to follow again most of these things we cover in the previous episode so don't worry too much about it it's just i want to make it the the smallest example as possible as we can um so we again we have if an error just print the error new db and error message and return so everything seems to be working everything seems to be compiling nothing is and it is not doing anything in particular so let me go to my 2021 11 26 so right here like i said i have an extended library example and the other one as well so if i run this it's not going to be doing anything because we haven't implemented the transactions again like i said the whole point of the implemented transaction is to execute a bunch of different instructions as all of them together if one of them fails you revert the whole thing so in sql there are three commands that are executed for dealing with transactions will be begin commit and roll back if you're familiar with sql when you're executing those commands you need to begin which is basically beginning of the whole transaction if everything was fine you call commit so everything that happened before that it will be safe into the database if for whatever reason you think something went wrong you can call rollback and that literally will not execute or save any of the changes that you previously called in the lines above of your sql statements in go the way it works is by using a type called tx which literally means transaction so the way it works in there is a function called begin dx and begin remember when we were discussing previously in the you know introduction to database sql remember that context the context package was added a few years after this implementation of the standard library was available so now we have a few different functions that apply to the type that receive a context type from the context package so we're going to be using all of them like we did previously so in this case we're going to be defining and calling begin tx to define a transaction and what this returns is uh it returns a transaction and an error and also receives a tx options and this depends on your driver how you want to somehow isolate the transaction and again this goes back to the way the drivers are implemented and the database engines are again implemented and documented so in this case we're going to be using the default configuration just to keep it simple and like like i said it's going to be written a type dx and an error and we're going to be doing our typical error checking so db begin x and just failing and returning and that's it now like i said in sql in relational databases begin commit and rollback are our friends so we have the begin instruction what we will be doing either next we'll be calling either commit if everything was fine or roll back if anything was wrong now what could go wrong is up to the database not something that we're you can also maybe validate the input that you're receiving in some cases if that's what you want to do but typically what you do when you are using rollback or commit is if any of the database instructions fails when you're literally accessing and calling the database is when you call commit or when you call rollback now let's go back to the example that is showing you before or another example but the schema that we had before is that we have a create table users with name and birth year all right so what we're going to be doing we are going to be doing a typical let me extract that out into another function so we can make it more let's call it insert users and what insert users is going to be doing the transaction stuff it's going to be receiving the database type and maybe a slice of users that we're going to be defining here let's call it users and it's going to be returning an error now obviously type user does not exist so let's go define it we have type user struct you have a name a string and a birth year which in this case would be an end eight which is equivalent to the small int in postgres so we have a slice of users and then four we're going to be insert inserting all of those users in a transaction and i want to use this example that is going to be working for in the beginning and then i will show you how this thing works and nicely when you are dealing working with transactions you have again tx exec context context background insert into users name birth year values and again we are using the placeholder dollar placeholder and the values as well which will be user name oh what the heck happened user dot name user dot birth year and this returns a if you remember the documentation it returns let me clean this up so it doesn't complain so it returns a sequel result and an error and sql result will be the id and the error will be the error for now we don't care about the result we only care about the error if r is different than nil we return error thump error f tx exec context and then we wrap it and return and return nil now we haven't really called commit all rollback yet so we're going to be doing is if uh we called tx commit because we assume everything seems to be working right here if err and commit returns an error if error is different than nil we do as something similar we do the checking thump error and tx commit the wrap err and whatnot so with this in place what we need to check is what is going to happen if any of these statements fail so that's when rollball rolled rollback comes in oh one thing i have here is incorrect so i need to fix this to return an error f wrapper and then error and then if everything is fine just commit the message and whatnot now typically the paradigm for when you are dealing with transactions you need to call rollback uh no matter what and and and this is one of those uh kind of confusing things in the beginning because the documentation is not clearly explaining that and this could depend on the driver but most of the times what you can do is after you begin the transaction you can invoke defer with the error or rather with the rollback uh that will be executed in case anything goes wrong and this doesn't mean that when the the conclusion of when this function completes it will call rollback i mean it will call rollback but it won't roll back the transaction because the transaction is already committed so what this means is that even if if if something goes wrong roblox will be called because commit failed and therefore the deferred is invoke okay now let me run this for you and you will notice that this should be working nothing should be going wrong so i have my go around if i do a select from users i don't have anything installed installed right oh you know why because i didn't call insert user so if err insert users db you have a slice of users errors different than nil your typical you know error checking fm print ln insert users error and then we can add a user which in this case will be named mario birth year uh 1 1900. so if i run this again a constantly oh overflows if in eight okay let's change it to in years for now i'll revisit this in a moment so everything goes fine and now it's inserted into the the database now let's go back to the way the database schema is implemented for this table specifically and you notice that name is and not null and birth year is a nullable type so what this means in practice is that we can we can use pointers for both of them but what is going to happen in the database is that if we send a nail in the name it will trigger an error so in order to show you an example of that let me use uh change the types that we used before for name and for birth here we're using pointers now and what we're going to be doing just to use a you know a simple helper functions we're going to be using some anonymous functions right here that take an input which will be you know a string and return a return a pointer of what we had before so we have name birth here change this one to uppercase fix this one and let's call it b in 64 77 24 that doesn't make sense uh now in 64 and b and if i lick steak like i do this see if i know a new name mario new birth year 1900 let's put mario one if i run this again it will work if i run the the select it will work but here's the interesting thing about all of this if i go ahead and take an invalid value in the context of the database because the constraints that i have right here it say my name has to be not null if i decide to pass in a nil value what is going to happen is that it's going to be failing okay and this instruction that i define above that is in the slice of users will not be called which will be this exact so to rephrase it we have we're trying to insert two records into the users table if one of them fail or if any of them fail nothing is actually inserted into the database and this is the whole point of transactions in the relational databases like you take a bunch of different instructions and is all or nothing and this is the whole point that we're trying to demonstrate right here again i go here and i run my and i might run my command my main and it says uh my error the name violence then not null constraint which is the thing that we have in our schema it says it's not now and if we go back into and show you the content content of these users it does not show that now why is this important like i said it's all or nothing let me refactor this a little bit and let's say we are not using transactions let's say we are not using transactions we are using your typical um database exec context i'm going to comment this how to use for demonstration purposes what is happening now if i change it is that i'm receiving a slice of users and i'm inserting each one of them using the database type and calling the insert if one of them failed whatever happened before that will still be inserted into the database now again we have the users if i run this again you will notice that now sure i'm getting the error but if i go back to the users now i have three users and that's not something that we want to do and depends on the business logic that we have we're trying to achieve sort of like a one single execution of all the calls that we have in our program so that's why transactions are really important so let me show you another example using pex specifically the api that is included in that package and it's i think in my opinion a little bit easier to follow and cleaner so let's see so typically not all the drivers provide a way to access databases relational databases that is outside of what database sequel pro really provides but pgx is a different an interesting example of how a package could be implemented for accessing databases so what i did here what i have here for this third-party package specifically this module rather in this example is i'm going to be taking what we did before we're going to be refactoring a little bit to not use database sql in the standard library for what rather use the pgx actual package another package i mean the type that is coming in that package so one thing i'm going to be doing is taking getting rid of this called this package import we remove the you know the empty import and the other thing that i'm going to be doing is going to be renaming this con neodb to neocon and the reason being is that we're going to be returning a pgx type and everything sort of stays the same except for the pgx called to connect and it receives the same dsn nothing nothing that changes on the end and maybe we can just change pgx connect just to make it uh clear we call con and we call con now what's happening here is that ah i'm missing something so remember your the documentation is your friend but i remember that this one is using the the methods that are defined in the pgx api they actually all of them define a context as the first argument you know like the the ones that we saw previously in database sql now all of them will already do that before all right already because that's sort of the new convention that we have to use for accessing or try to use in context-based methods and types used to pass in you know cancellation errors propagation those kind of things but anyways so let's go back to the what where con has been used so khan will be renaming this just to make it clear and similarly to close now he receives a context as well now the biggest difference with what the way it is with database sql is that you can still use begin tx commit and roll back however the cool thing about the cone type specifically is that now it defines equal define it defines two different functions all right two different methods that wrap the actual transaction in one function so you don't have to deal explicitly with uh like you know the need of calling commit or rollback or those kind of things so i think it makes things a little bit easier and i want to show you the way it is right now so let me just refactor this thing so we are receiving a con right now this variable now is called con and rollback is still a context i'm just trying to remove all the warnings that you can see that my editor is literally just you know highlighting those errors and whatnot so what i have already defined is uh this will be the equivalent of what this is not correct let's again documentation is our friend so we have a context pgx error dx so what is the problem oh because context.context is that doesn't exist so we have context background and we do what the heck is going on with my editor anyways so context background and now everything seems to be working there is nil we don't need this one is that how it is begin oh i see so again the apis are different pgx dx options which will be use the default options if i build this everything is building okay now that i refactor everything to use the pgx con type i want to show you what i wanted to show you in the beginning so everything is basically the same implementation right you have a begin dx a rollback and you are calling exec for inserting the users the values that we defined previously the cool thing about con when you're using this type is that you can actually use two functions either begin func or begin dx funk or begin t i mean there are actually three of them yep three of them so what happens with this is that um when you div when you use this function you pass in you indicate a function um as well that it will be used as a way to if you return error to either commit or roll back the transaction it also uses the context as well to determine whether commit or roll back the the transaction as well the the one i want to show you i mean both of them are practically the same is just the difference will be that for example begin the x is actually using an options and begin i'm sorry begin tx func is using a is receiving a tx options argument for defining transaction options like isolation or read level those kind of things i'm making funk is the one that is actually not receiving that so for now let's use this one in particular so we have context background and the type will be funk tx pgx dx and return an error and that's basically e here we have an error right here error is different than nil return thump error f con begin function the wrapper and what we're going to be doing as you may be guessing already is literally use copy all right cut all of these move it up here and return the value if if there is an error we just determine that error and everything goes away so it's sort of like a much more cleaner not cleaner shorter so this is the x why is the x complaining uh oops of course this shouldn't be here rather inside this one so what i did again to recap we have a function yep i think so it seems to be an error let me see return oh of course if there is no error you return nil so again to recap so we have a function called a method in the cone type called begin funk that could be used for either begin funk or begin funk tx begin funk or begin tx funk rather dx1 receives an argument for defining options for the transaction and the other one will not receive any arguments for a transaction it will be using the default configuration but it does it literally wraps the transaction in a function if the function returns a non-nil value it will roll back the transaction if it doesn't it will just commit the transaction as expected it still uses the context as a way to deal with error propagation or cancellation and everything stays basically the same now if i run this let's compile it if it seems to be working let me select my own users what is going to be happening is um one of them will be failing because the transaction error so let me go back and fix that for for for us so a new name let's call it pgx1 and this px0 so if i run this again you will see that now name is what is saying violates non-nail value why why is it why did the idea didn't i save this one oops typical roku mistake so it was using a different um folder different files so if i run this again everything will be fine so what i was doing running before i was using this to leave example instead of the one for third party which is the pgx example so again pgx0 pgx1 everything should be working everything is fine and dandy and let's jump into the final conclusion conclusion right here because what i want to show you and what i want to do is discuss finally is that again transactions are really important when you're trying to do multiple actions in the relational database that you consider as all of them have to execute it or none of them should be executed if any errors happen you should be really careful when dealing with transactions because if you as soon as you open a transaction or as soon as you begin a transaction you should be using that transaction type as a way to communicate with your database you shouldn't be calling the database that you previously was called for for creating the transaction three uh depending on your driver you could be you could have access to an api like the one i will show you with begin tx and and that's it i mean hopefully all of this was useful and thank you for watching any comments any thoughts about transactions in postgres please let me know i will be covering also like i said in the previous episode my sequel in the near future so stay safe take care and i will talk to you next time see ya

Channel: Mario Carrion

Views: 474

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

Id: BLr2V6zB5k4

Length: 26min 57sec (1617 seconds)

Published: Fri Nov 26 2021