Go - SQL Databases in Golang with the database/sql package

this video is about the database SQL package in go and how we can connect our go applications to relational databases so we're going to connect go to a postgresql database that's running in a Docker container in this video we're then going to create a table in that database and then run some queries to fetch data and insert data into that table and this is a foundational video we'll use Concepts that we learn about in this video in future videos on how to use go with htmx and also with react applications so let's get started and this is the package that we're going to use in this video it's the database SQL package that's built into the go standard library and this package provides a generic interface around SQL databases so the interface that's provided by this package can be used with all sorts of different relational databases for example postgres and MySQL in this video we're going to look at postgres specifically and we're going to use this postgres Docker image from Docker Hub so what I'm going to do is copy the docker pool command and we're going to use that command in the terminal that I'm going to bring onto the page now and I'm going to paste that in here and that's going to pull the postgresql database image to our local system once that's completed we can then start up a Docker container from this image containing the postgres database so in order to do that I'm going to go back to the documentation and I'm going to scroll down a little bit here and we're going to go to the section on how to use the image and there's a sample Docker run command here that contains some arguments to the Run command for example we give the container a name and we set some environment variables and other information so I'm going to do is go back to the terminal here and I'm going to run that Docker run command and let's give the container a name of postgres Dash container here and we're going to set the postgres password environment variable here so it's going to be post progress password I'm going to set that equal to let's just say secret in this application we're then going to create a port mapping on this container so on Port 5432 on our host we're going to that into the same port on the container and that's going to allow us to connect to a postgres from our local machine into that Docker container that runs the database the final argument we're going to pass is the Dash D argument that's going to start the container in detached mode and that will start the container in the background and finally we specify the container or rather the image we want to start this container from and that's the postgres image that we just pulled from Docker Hub so we can run this command and we get back the image ID or the container ID here I'm going to clear the terminal and we can run Docker PS and we can now see that the postgres container is running on our system so what I'm going to do now just before we start writing some code is I'm going to go into the database here that we've started and I'm going to create a database on that database server so that we can connect to that database and then run the commands and queries that we're going to run from our go application so we've got this container running it's got the name of PG container I'm going to copy that and what we're going to run is the docker exec command and we're going to run this and enter interactive mode with the dash TI flag and then we can paste the name of the container that we want to connect into here and the command that we're going to run in the container is the create DB command and what we do here is we create a database on the running postgresql database server using this create DB command and we are going to specify the user of postgres and the name of our database I'm going to call that go postgres test so what's going to happen here it's going to create this database with this name on the server so let's run this command and we can see what happens we don't actually need the ti flag here either because we're running a one-off command but once that's completed we can run another command here so I'm going to clear out the create DB command and I'm going to run the psql command and again when we're connecting to the postgres command line tool we need to specify the user I'm going to specify postgres here and then what we can do is we can switch over to our database Now by default when you connect using psql the database that we're connected to here is called postgres we can use the slash C command and psq and specify another database name so the one that we created was called go PG test we can now connect to that and we can see that it actually does exist after we use this create DB command so let's now quit out of P SQL and we're going to go back to the terminal and actually let's go to vs code where we're going to start writing some goal code now I have a very simple go file here it's called main.go and it contains an empty main function what we're going to do is connect to the database so what we need to look at here is the SQL package from go and if you look at the sidebar here we can search for a particular function and the one I'm going to look for it's a function called open so let's find that and we can go to the documentation this function will open a database as specified by the database driver name which is the first argument to this function and what this function is going to return is a pointer to an sql.db object that represents our database now the first argument is the driver name this is dependent on which database you're connecting to we're connecting to postgres SQL so we need to get a postgres specific driver for go and the package I'm going to use for that is this PQ package and this is not built into goes standard Library so we're going to have to run this command go get in order to pull that into our application but this PQ package is a pure go postgres driver for the go SQL package so we're going to pull that into our application so we can connect to postcode SQL I'm going to copy this command here and we're going to go back to vs code and I'm going to start up a terminal here so I've got that terminal open at the bottom what we need to do now is initialize a Godot mod file that's going to track the external dependencies that we're using in this goal application so what I'm going to run here is a command and it's the go mod and net command and the module that we have is the main module so I'm going to specify that main module and that creates the go dot mod file that you see here on the left hand sidebar and currently we have no external dependencies but what we're going to do now is paste in that goget command that we got from the PQ package so let's paste that in and that's going to download that package source code from GitHub and it's then going to make that available in our application and if we now open the go dot mod file you can see that we're requiring this PQ package so let's start writing some code in this file here I'm going to close go.mod and in the main function we're going to initiate a connection to the running postgres database and to do that we're going to use this open function that we saw in the documentation so let's get started with that now what I'm going to do at the top of this function is Define a connection string this is a postgres connection string and you can see that this hard codes things like the password in the string not the best practice in a real application but we're just going to do that in this video just to make things very simple so this connection string contains all the details that we need to connect to postgres it has the username it has the password and we have the host which is just localhost because of that Port mapping into the docker container and finally we have the name of the database that we're going to connect to and that's the gopg test database once we've got that we can call the sql.open function from the go SQL module so if I save this file you can see it Auto Imports the SQL module the first argument to open was the name of the driver and because we're connecting to postgresql we're going to specify postgres here and the second argument to that is going to be the connection string now if we look at the documentation here you can see that this returns a pointer to an sql.db object and also an error so let's get those objects as the return statement we're going to create a DB and we're also going to create an error here and that's going to be equal to what's returned from the open function and what we're also going to do is we're going to defer the db.close function so if we're opening our connection to the database here it is best practice to close that connection as well and this should be a capital letter so what we're doing here is deferring the close function so when this main function finishes its execution it's going to run the db.close function to close off the database connection and that can be important for freeing up resources and small app like this now let's check now if we have an error here so what I'm going to do is create an if statement I'm going to check if the error is not equal to nil and in the case where we have an error we're going to call the log dot fatal function and what we're going to pass through to that is the error itself so we're going to log that to the terminal and I'm going to quit out of the application and finally we can check if we have a connection to the database so I'm going to create another if statement here and we're going to check if the DB dot ping function returns an error if that error is not equal to nil then what we're going to do is again we're going to call the log dot fatal function and we're going to pass that error through but if we don't get an error here we know that the database.ping function which is just uh basically a health check against the database we know that that's returned successfully and that we have established that connection so let's save the file and on the terminal at the bottom I'm going to use the go run command and we can pass the main.go file to that and we can see that we have an error here unknown driver postgres so what we have here as it says in the terminal here before gotten the import of that PQ module they were not directly using that module in this code anywhere but what we're going to do is we're going to create an import here and because we're importing this package solely for its side effects we're going to use an underscore import and then from the go dot mod file I'm going to copy the reference to this library and we're going to paste that into the top here after that underscore I'm going to surround that in a string as well let's now try and rerun this application on the terminal at the bottom and you can see that the main function has completed the execution there's been no errors so the database.ping function was executed successfully and we have a connection to the database in this small application what we're going to do now is create a table in this database called gopg test and we're going to create a table that stores data on products in an imaginary e-commerce system so the schema that we're going to work with I'm going to paste that comment and here we have a product table that contains five Fields an ID name price available and date created so I'm going to do is cut this comment out and I'm going to create a function down below the main function here and what this function is going to do is it's going to be responsible for creating the product table now in a real application you might use migrations but we're just going to do this here so we can demonstrate some of the features of this DB object that we've created and go so I'm going to paste the schema in here and what we're going to do is we're going to create a query and that's going to be a string that is going to be responsible for creating the table with these fields now just so that we're doing this very quickly I'm going to paste the query in here and the query contains some SQL here we're creating a table if it doesn't already exist called Product in this database and we're then setting the fields in that table for example the ID field which is going to be a Serial primary key so this is the primary key of this database table and it's going to Auto increment we also have the name field for the product name that's our varcar field with a 100 character limit and we also have a price field which is a numeric field that can contain six numbers two of which can be a decimal and finally two extra fuels we have an available field this is a simple Boolean field and finally the created field is a timestamp data type it's going to default the created field to the current time when that item is inserted into the table so that's a query and this quite obviously contains these fields so I'm going to remove this comment and what we're going to do below this query is we're going to use our database object to actually create this table if it doesn't exist in other words we're going to execute this query against the postgres database so to do that we need to actually use this DB object that we have here this is what we got back from the sql.open function so what we're going to do in our new function here is we're going to take a pointer to that as an argument so let's take an argument here called DB and that's going to be a pointer to an sql.db object and when we pass that in as an argument we can then use the DB object in this function to actually execute a query now the function that we're going to use here is the db.exec function this is a function that will execute a query without returning any rows so this is important if you're performing some sort of query that's not supposed to return any data to you you can use the db.exec function for that but if you are fetching data you should use another function called db.query that we're going to see a little bit later on now the argument that we're passing in here is the query which is the string representing the SQL that we're going to execute so that's the query that we built up on line 28 to 34 and the DB dot exec function that it Returns the sql.result object and it returns an error we don't need the result but we do need to check if there's an error from this return statement so what we're going to do just below here again is copy the F error equals no we can copy that and we're going to paste that just below this statement here so we're checking if the error is not equal to now and if it's not then what we're going to do is again log dot fatal and quit the program and print the errors to the terminal so this is our function it creates a query string and then it uses the DB dot exec function to execute that query against the database we then check for any errors and that is the complete task that this function is going to perform the only thing left to do in order to execute this and create this table is actually call the function from our main function so what we're going to do right at the bottom here is we're going to call that function so let's copy the name of the function and we're going to call that and pass in that database object and this is actually a pointer to the database object that's returned from the sql.open function we're passing that in and that's accepted by our new function and we can then use that DB to execute queries against the postcode SQL so now that we've got this setup will it work if we run the go run main.gov command at the bottom is this going to return anything no because we haven't printed anything to the terminal but what we can do if we bring up the other terminal that we had earlier on we can run the psql command again to get the command line access to the database and we can then connect to our gopg test database and there is another command that you can use in psql called slash DT and that's going to display the tables that are in this database if we run that we can now see that we have this table here with the name of product so this table has now been created in the database and now that we have the product table we could execute a query such as select all from that table called Product and at the moment we have no rows but you can see the five columns that exist in that table and these match what we specified in this create table statement here so that has been created in our postgres database what we're now going to do is we're going to actually insert data into this table using another function in go and we're going to see more about how to use this DB object so what we're going to do is we're going to create a go struct that represents a product and the fields that we need to insert we don't need the ID field because that has the serial type and we also don't need the created field because that defaults to the current date time in the database so we're going to create a struct that contains these three fields that represent a new item that we might want to add to the database so I'm going go to the very top of the main function and if we were using a real application we might want to break this out into multiple files but for now I'm going to create a type called Product and that is a struct that's going to contain these three Fields so we're going to have the name of the product and that's going to be a string and we're also going to have the price which is going to be a float 64. and the final field that we need in this struct is the available field and that's going to be our build type so this struct will encapsulate the data that we need to create a new product in the database what we're going to do now is go down and create another function that's going to take in the database pointer and we're going to do that right at the bottom here and we're going to change the name of that from create product table to insert product now this takes that pointer to ansql.db object but what we're also going to pass in here is the data itself that we want to insert into the database so we're going to pass another argument here called Product and that's going to be of type product which is that structure that we just created and from this function we're also going to return an integer that represents the primary key of the product that was created in the postgres table and that's because when we call this function from our main function we want to get back that primary key so we can then perform further queries to get that data by primary key just to show how that would be done as well so let's now write the body of the function we're going to set up another string variable called query and we're going to use an insert into a statement here and the table that we're inserting the data into is called Product and the fields that we have data that we want to insert into are the name field the price field and the available field so we're inserting data into the product table and we have data for each of these columns on a new line I'm going to specify the values that we're going to insert and rather than directly putting the values from this product into the insert statement we're going to reference these using this syntax here and this parameterizes the data that we're inserting and this can be helpful to avoid things like SQL injection in your code so for each of these three columns we're going to insert a particular value here and we reference these parameters and then what we're going to do is also return the ID from this command so when we insert the product into the table we want to return the ID column back to the caller back to this go application and that ID is ultimately what we're going to return from this function so now that we have the query set up we're going to create a variable in the function called PK and that's going to be of type int and this is going to be the value in which we're going to store The Returned ID this is a variable that's currently null but we're going to store The Returned ID from the insert statement into this variable so how do we do that what we can do is use a function called db.query row and the query row function as it says here it will execute a query that is expected to return at most one row and it always returns a non-nil value any errors are deferred until the rows scan method is called we're going to see the scan method right now but what we're going to pass into query row is the query itself which is the string that we built up above and because because we have these parameterized values we now need to pass them into the query row function so that when the postgres driver is executing this query it can fill in these values with the actual values from our product so in order to do that we're going to reference the product here and the first parameter was the name the second was the product's price and the final parameter was the products available field so all we're doing here when we call query row as we're passing in the query and any parameters that we have in that query string are filled in using the arguments to query row so this row is going to execute the query that we have above and that query is going to insert the product and then it's going to return the primary key ID of that product to the application and we want to scan that returned ID into this PK variable in order to do that we can chain a DOT scan function onto this query row function and this is going to copy the columns from the Matched row into the values that are pointed at by the destination arguments so we're going to scan the returned ID into this primary key variable that we created above using this syntax here this is a pointer to that primary key so when we get back the ID from inserting that product that ID is going to be scanned into this variable that we can then use throughout the rest of the program now the scan function remember as it said earlier this will return an error if there's any issues and what we need to do is check for that by creating this error variable here and again I'm just going to copy the error not equal to No statement copy that down into this function and if there is an error here we can again call log dot fatal otherwise what we're going to do is return the primary key that contains our new ID so just to summarize very quickly we create a query here that inserts the product into the database table and we parameterize the values that we're passing in for the name the price and the available columns we call the query row function and pass in the values from the structural argument and finally we scan the result into this primary key variable that we set up above and that result comes from The Returned ID of the new product we return that primary key to the caller so now what we actually need to do is call the insert product function so let's scroll up to the main function and I'm just going to do this at the bottom here just underneath the create product table function what we're going to do to start with is create a new product by using that structure that we have so let's just give the product a name or book and we can give it a price of 1555 and we'll set it to true in other words it's going to be available this is the last field in the structure I'm going to set that to true so this is our product what we need to do now is get back the primary key and call the insert product function we pass in the pointer to the database and we also pass in the product itself this is going to call this function that we just wrote here it's going to insert that product into the table and return its primary key and that's what we're getting back here and we can inspect that primary key by calling the format dot printf function and we can set the ID equal to that numerical PK there and we pass the pkn as an argument and I'm also going to add a new line to this this is going to print the ID to the terminal when we run the go program so let's go to the terminal and we're going to run this application and you can see we get back the ID of one and if I run this again we're going to get back another ID and because postgres has that serial data type in the ID column this is auto incrementing from one to two and so on so that appears to be working let's actually look at the database table and we're going to see if this is working now I still have the psql command line tool open here when we ran this select all from product query earlier we got back no rows but if I rerun that now and clear the terminal you can see that we have two rows in the database table now and both of these products have the name of book and the price of 1555 that's coming from that data that we created in the go app using this structure here so that has been inserted into the database and you can also see the created column here that automatically has that time stamp when the data was inserted so let's move on to querying for data in the database now there are two functions that we can use in order to query for data and fetch it back into our go Application we've already seen this function here called query row and as it says here is expected to return at most one row from the database table now there's another function called db.query which can return many rows now what I'm going to do is first of all we're going to see how we can take a primary key for example this one here and we can then execute a query that's going to get back that particular Row from the database using the query row function so I'm just going to add some code here to the bottom of our main function I'm going to clear the format.print if statement and I'm going to create three variables here for name available and price and we're going to scan the results of a query row function into these variables and the query string here is going to use a select statement we're going to get the name field the available field and the price field from this table and the table of course is called product and we can then pass in our where Clause we're going to check for the ID being equal to this parameter that we're going to pass in when we actually execute the query so the parameter is going to be the ID that we want to fetch back from the product table and we're only going to get back these three columns from the table so let's again create an error variable here and we're going to set that equal to the db.query row function we pass the query that we created above into the query row function and then we can call the dot scan function after that and we can scan the results into the three variables that we created above here so I'm going to copy the name variable here we can reference that we can also reference the available variable and finally the price variable as well and this should just be an equal sign here so what we're calling is DB dot query row and we're scanning the three columns that we get back from that single row into these three variables that we created we can check below if the error is not equal to nil and we can exit the program if it is not now otherwise what I'm going to do is paste three statements in here and that's the format dot printf function and we're going to print out the name the available and price values that we're getting back from this query row function so remember the variables that we're referencing here these are the three variables above that we scanned the results of that query into so let's now execute this program and we can see what we're going to get back here we have an error on line 35 that we have a PK that's declared but not used and that's my mistake when we call query row here we need to pass in this named parameter referencing the ID that we want to actually Fetch and that's going to be the primary key that we get back from the insert product function so we need to pass that in as a second argument to query Row in order to fill out that value let's now try this again on the command line and if we run main.go we can see we get back some output for these values the name is book The Available field is true and the price is 15.55 so this is a different way of doing things we have a primary key of an object in the database that we want to fetch we use the query row function because we know that when we fetch something by its primary key that's only going to return at most one row to the caller so we can use Query Row for that and scan the column results into these variables above now what happens if we're asking for a primary key that does not exist so if I hard code a different primary key here and I'm also going to need to comment out the code with this primary key and the product here so let's comment those out and run this again we get this error from the SQL module and that says that there are no rows in the results hit so we can handle this specific error in the if statement that's checking whether or not the error is equal to now so if the error is not equal to now we can then do another check here on the error we can check if it's equal to an sql.air no rows so when we get this error we can handle that logic any way we want so what I'm going to do in the case of no rows is I'm going to print to the terminal no rows were found with the ID and I'm going to hard code that same ID that we passed in so let's try this again on the command line and we're hopefully going to see this message of no rows found with the id11 and that's what we get because we're now explicitly checking for the sql.ear no rows and we can handle that specific error in this block of code and of course if you don't get any rows back from a query you might not want to exit out of your application you can handle that in a better manner so one last thing to cover in this video how do we get back multiple rows from the table and then how do we iterate over each of these rows what I'm going to do to start with is clear out this code from the main function here and I'm going to clear everything up to this create product table statement at the top let's create a slice for the data that we're going to get back from the database here and this is going to be a slice of product objects and when we query the database for some of this data we're going to append the results that we get back into this slice so that's the empty slice at the moment what we're going to do now is create a query here using the DB dot query function notice I will not using query rule the query function will execute a query that returns rows this is typically a select statement so this is expected to return rows to us the query that we're going to execute is this select statement here where we get the name the available and the price fields from the product table and we're not doing any we're filtering here this is going to get back these columns for every single Row in the product table now once we have that statement we can check if the error is not equal to nil and if it's not we can then defer this rows.close function this is very important when we're about to iterate over these rows that are returned from the database for this query we need to call rows.close in order to prevent any potential memory leaks now what we're going to do is to the data slice here we're going to create a product from the results of this query and we're going to append that product to the data slice for each row in the results so what we need to do here at the top is we're going to create another three variables representing the data that we want to extract from this query for name available and price and then below that we're going to iterate over the rows so we can use a for Loop here and we can call for rows.next and for each row that's going to iterate over the result set and we can then for each row called the rows.scan function and scan the data that we're getting back for that row into the variables that we have above for name for available and for price now the scan function might return an error so we're going to check for that here so we get back the potential error from the scan function and we can check if that's not equal to no but if it's not equal to no what we're going to do is we're going to create our product structure from the data and these three variables after we scan the results from that row into those variables and we're going to append to this structure that we have at the top here called Data so that contains Nothing by default but the results that we get back from the database we are going to use the append function on a slice and go we can pass in the existing data and then what we're going to do is create a product struct from the data that we have here for the name for the available field and from the price field as well and then after the for Loop finishes what I'm going to do is print out the data to the terminal so that we can see what that looks like when we run the command so let's quickly go over this before we run this code we set up the empty slice and then we execute a query that gets back all of the results from that product database table and stores that in a variable called rows with the fair closing that data and then we iterate over each row in the data and for each row we are scanning the results from that row into the three variables that we have here and then we're creating that product structure from the results and appending it to the slice let's now execute the query at the bottom and we can see what results we get back and we're getting back a list containing three structs and that's because there are three objects in the database which we can verify if we run this select statement in psql again so we have three rows and when we run the application we get back those rows represented as structures after we can invert them here and use the append function so the important takeaway from this is that when we want to get back a single Row from the database we can use the database.query row function and that's either going to return one row if we find any data or it's going to return no rows if we don't have any data we also have the database.query function that we have here and if we expect to get back multiple records potentially from the table that's the function that we should use in order to do that and as well as query and query row we also have the db.exec function in go and this exact function will execute a query without returning any rows so if you don't need to get back any rows for example you're performing an insert statement that doesn't return the ID you can use the db.exec function in order to do that but that's all for this video there's much more that we could cover in a future video if you're interested for example we could look at database transactions in goal we could look at prepared statements we could dive into orms that are available in go and we could also look at libraries such as sqlx and sqlc that can work in different ways with databases and go but thank you for watching this video if you've enjoyed the video please like And subscribe to the channel and we'll see you in the next video

Channel: BugBytes

Views: 27,342

Keywords:

Id: Y7a0sNKdoQk

Length: 31min 16sec (1876 seconds)

Published: Mon Sep 04 2023