Intro to Golang and Mysql

The usual next step for any developer learning a new language is to move into database manipulation. Seeing as how golang is an amazing (easily one of my favorites) language and how MySQL is one of the most used databases so far. This article covers how to run insertion and retrieval MySQL queries using golang.

Not this does not cover the use of an ORM

Prerequisites

• You have golang min v1.1 installed
• Mysql (or MariaDB) installed
• Basic understanding of Go and MYSQL queries

Initialization

Begin by creating a new project folder outside the GOPATH, this way you can use go's modules. You can call this new folder gosqltest. In the root of the project folder, create a gosqltest.go file with the following contents. This would serve as our app's entry point for the app

package main

func main(){

}

Next, you need to initialize the project using go mod. Do that by running go mod init ownername/gosqltest at the root of the project. This should enable and setup modules on your project.

Dependencies

We are going to be using the go-sql-driver as the only external dependency in this tutorial. It allows use use mysql as our driver for go's database library. Now run $ go get -u github.com/go-sql-driver/mysql on your project to add it as a dependency

Implementation

Start by importing the libraries at the top of your entry file:

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

In the main function, open an SQL connection to your database with the code

db, err := sql.Open("mysql", "user:[email protected]/dbname")
if err != nil{
    fmt.Println("Could not open a connection to the database: " + err.Error())
}
defer db.Close()

The string user:[email protected]/dbname is the "address" of your database in DSN (Data Source Name), if you wanted to connect to a remote database with this format you would pass in user:[email protected](host:port)/dbname.

Next, we are going to run a query to insert some data into a table named tutorial. Append the following code into the main func:

//INSERT INTO tutorial table
statement, err := db.Prepare("INSERT INTO tutorial(name,value) VALUES (?,?)")
if err != nil {
    fmt.Println("An error occurred:" + err.Error())
    return
}
if _, err := statement.Exec("keyfor1", "valuefor1"); err != nil {
    fmt.Println("An error occurred:" + err.Error())
    return
}

Breakdown of what this does: -Prepare an SQL statement and check for errors -Execute the statement using "statement.Exec(...parameter)". Is fills the position of the "?" with the passed values accordingly

Now if you build and run this, then check your tutorial table using SELECT * FROM tutorial you should the keyfor1,valuefor1 as a row.

If you wanted to use golang to retrieve these values with a select statement, you would use code similar to this:

result, err := db.Query("SELECT name,value FROM tutorial")
for result.Next(){
    var name,value string
    err = result.Scan(&name, &value)
    if err != nil {
        panic(err.Error())
    }
    fmt.Println(name + " " + value)
}

The difference between db.Prepare and db.Query is that the Query method simply runs the passed string as a query without substituting values and all that. Now if you build and run this you should get the entries in your tutorial table, which in this case would be:

keyfor1 valuefor1

Conclusion

This tutorial should serve as an into to MySQL and golang, it's up to you (and a lot of research) to find out how to properly use the basic principles here for your project.

A good thing to note is that the database/sql library automatically handles pooling for us, so every call to the Open func fetches a connection from an already created connection pool.

CHEERS!