How To Get Results Using Golang with MySQL Database

How To Get Results Using Golang with MySQL Database

Golang, via the standard Go-SQL driver package has excellent support for relational databases like MySQL as well as NoSQL databases like MongoDB and Redis.

Using the standard Go SQL driver package, you can easily communicate with databases, including MySQL, MongoDB or any of the other databases within the list of Golang database drivers.

The SQL standard package provides a generic interface that exposes many common methods and interfaces that you can use to access and work with databases.

The database package contains a connection pool and is thread safe, if used correctly. The database package must be used in conjunction with a driver package that supports the specific database.

Connecting to MySQL

For example, if you need to connect to MySQL then you would use the generic SQL package that exists in the Golang standard library and also a MySQL specific package which makes use of the standard library package methods and interfaces.

People who write SQL database drivers in Golang usually expose their drivers via the database SQL package. The entry point code exists in the SQL driver Go file.

Basically, what it does is execute the Register function code before anything else in the package, inside the init() function, another function from the SQL package called sql.Register(), which takes two arguments. The first argument is a string representing the type of driver that you would like to use, in this case "mysql" and the second argument is an object which represents the actual driver which interacts with the database and handles the particular database functionality.

 sql.Register("mysql", &MySQLDriver{}) 

The SQL package is the standard database SQL package which exists in the Go standard library. Because the init() function gets executed before anything else, you know for sure that whenever you include package MySQL in your code, this code will get executed right away.

 import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
   )

The underscore symbol_, in the import
statement, is a way to silently load the package, which means you load the package, but at the same time you indicate that methods will not be called directly from this package. Without the underscore _, you will get you will get an imported but not used error message.

The action of loading the package, even if it's silent, will invoke the init() function of the package. The init() function of the MySQL driver will register the MySQL driver objects with the database SQL package
and this will enable you to use database SQL package functions and methods directly in your code and have that code then translate into concrete implementations in the MySQL driver package.

Re-use the Database Handler

Declaring db as a pointer to the database handler, enables the database handler to be re-used when you need to it, instead of creating a new one.

var db *sql.DB

On of the first functions to use when working with databases, is sql.Open(), which is used to obtain a database object, the database object is what you use to execute queries and commands against your database. The first argument to sql.Open() is the database driver you're trying to load. In this case the name of the driver is "mysql", the second argument is a connection string we would use to connect to the database.

db, err = sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")

The sql.Open() function will return a handler to our database connection which you can then use to issue queries and commands to your database.

The sql.Open() function will also return an error object, which would be nil, if no errors were observed.

On the MySQL driver Github page, you can find a full description of the connection strings which you can use as an argument to the sql.Open() function.

It takes a username followed by the password, then the protocol which you would use for the connection, followed by the database name.

Now you are ready to have fun with Golang and MySQL.

To read data with the standard database SQL package, you would use the Query() method, to retrieve a result set of multiple rows, whereas if you need to only retrieve a single row from your query, you would use use a QueryRow() method.

The Query() method returns a pointer to an object type called Rows, which is basically a struct that supports some useful methods, the Rows object type can be used to scan and iterate over the data returned from your query.

For this basic example, in your code, you would create a struct, which would hold the data returned from your query.

type Product struct {
ProductCode, Name, Description, ImgURL string
Weight, Price                          float64
Stock                                  int
  }

You would start by calling sql.Open() with the appropriate parameters, to get the database handler.

db, err = sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname"). 

Use defer db.Close(), in order to be sure your database connection gets closed.

When calling sql.Open(), there is also an option to include the parseTime=true parameter, which enables translating date and date/time fields from your database to the Golang time object type. Using the parseTime=true parameter changes the output type of DATE and DATETIME values to time.Time instead of []byte / string.
In essence, you would be telling the MySQL driver that you would like to convert any MySQL date or date/time fields to the Golang time object by appending parseTime equals true as a property to your connection string.

Now you can create a function to retrieve product information and scan the results into your Product struct.

func getProduct(productCode string) (err error){
...
var p Product
err := db.QueryRow("select productCode, name, description, imageURL, stock, weight, price from products WHERE productCode = ?", productCode).Scan(&p.ProductCode, &p.Name, &p.Description, &p.ImgURL, &p.Stock, &p.Weight, &p.Price)
...

QueryRow() executes a query that is expected to return at most one row. QueryRow() always returns a non-nil value. Errors are deferred until Row's Scan method is called.

In the SQL statement above, the "?" is used in a parameterized query to replace the ProductCode variable.

Next, all that's left to do, for this simple example, is to print out the results of our query:

fmt.Printf("Product Code: %s\n Image URL: %s\n Product Name: %s\n Price: $%.2f\n Weight: %.2f\n Number in stock: %d\n", p.ProductCode, p.ImgURL, p.Name, p.Price, p.Weight, p.Stock)

Our getProduct() function takes a string, productCode and returns an error, which would be nil, when there is no error.

When you call getProduct("g43") for example, the database query is executed, looking for productCode = to "g43" and returns the results ...

Golang with MySQL Code Listing

Hopefully this simple example is enough to help you understand how to use databases with Golang.