#Golang MySQL Tutorial

As you continue your Golang learning journey, it becomes almost inevitable that you will have to interact with some form of database.

In this tutorial I’ll be demonstrating how you can connect to a MySQL database and perform basic SQL statements using Go.

##Why MySQL?

MySQL is one of the most well-known and well-used database technologies available to developers at the present point in time. It has an absolutely massive community around it and it’s quite possibly powering half the web as the main database technology for Wordpress.

It’s incredibly easy to spin up a MySQL instance locally and thus it’s perfect for building some decent applications on top of.

Note - Technology choice shouldn’t be based on popularity, there may be scenarios where you need to consider alternatives such as CockroachDB or NoSQL databases.

##Video Tutorial

If you prefer following a video, then this tutorial is available in video format here:

##Text Tutorial

In order to do this we’ll be using https://github.com/go-sql-driver/mysql as our MySQL driver. Go-SQL-Driver is a lightweight and fast MySQL driver that supports connections over TCP/IPv4, TCP/IPv6, Unix domain sockets or custom protocols and features automatic handling of broken connections.

Github Repo: go-sql-driver/mysql

##Connection Pooling

If you are building high-performance database applications, connection-pooling is an absolute must.

Thankfully, the opensource package that we’ll be using for the basis of this tutorial features automatic connection-pooling thanks to its use of the database/sql standard package.

This essentially means that, every time you query your database, you are using a connection from a pool of connections that have been set up on application startup. These connections are reused, time and time again, and this subsequently means you aren’t creating and destroying a new connection every time you perform a query.

##Implementation

We’ll begin by connecting to a database we’ve set up on our local machine and then go on to perform some basic insert and select statements.

###Connecting to a MySQL database

Let’s create a new main.go file. Within this, we’ll import a few packages and set up a simple connection to an already running local database. For the purpose of this tutorial, I’ve started MySQL using phpmyadmin and I’ve created a database called test to connect to and create tables within.

We’ll use sql.Open to connect to our database and set up our automatic connection pool, this will return either db or an err that we can handle.

package main

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

func main() {
    fmt.Println("Go MySQL Tutorial")

    // Open up our database connection.
    // I've set up a database on my local machine using phpmyadmin.
    // The database is called testDb
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/test")

    // if there is an error opening the connection, handle it
    if err != nil {
        panic(err.Error())
    }

    // defer the close till after the main function has finished
    // executing
    defer db.Close()

}

###Performing Basic SQL Commands

So, now that we’ve created a connection, we need to start submitting queries to the database.

Thankfully, db.Query(sql) allows us to perform any SQL command we so desire. We can simply construct the query string and pass it in as a parameter.

package main

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

func main() {
    fmt.Println("Go MySQL Tutorial")

    // Open up our database connection.
    // I've set up a database on my local machine using phpmyadmin.
    // The database is called testDb
    db, err := sql.Open("mysql", "root:password1@tcp(127.0.0.1:3306)/test")

    // if there is an error opening the connection, handle it
    if err != nil {
        panic(err.Error())
    }

    // defer the close till after the main function has finished
    // executing
    defer db.Close()

    // perform a db.Query insert
    insert, err := db.Query("INSERT INTO test VALUES ( 2, 'TEST' )")

    // if there is an error inserting, handle it
    if err != nil {
        panic(err.Error())
    }
    // be careful deferring Queries if you are using transactions
    defer insert.Close()


}

##Populating Structs from Results

Retrieving a set of results from the database is all well and good, but we need to be able to read these results or populating existing structs so that we can parse them and modify them easily. In order to parse a number of rows we can use the .Scan(args...) method which takes in any number of arguments and allows us to populate a composite object.

/*
 * Tag... - a very simple struct
 */
type Tag struct {
    ID   int    `json:"id"`
    Name string `json:"name"`
}

package main

import (
    "database/sql"
    "log"

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

func main() {
    // Open up our database connection.
    db, err := sql.Open("mysql", "root:pass1@tcp(127.0.0.1:3306)/tuts")

    // if there is an error opening the connection, handle it
    if err != nil {
        log.Print(err.Error())
    }
    defer db.Close()

    // Execute the query
    results, err := db.Query("SELECT id, name FROM tags")
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

    for results.Next() {
        var tag Tag
        // for each row, scan the result into our tag composite object
        err = results.Scan(&tag.ID, &tag.Name)
        if err != nil {
            panic(err.Error()) // proper error handling instead of panic in your app
        }
        // and then print out the tag's Name attribute
        log.Printf(tag.Name)
    }

}

In this example we retrieved 2 columns from the tags database and then used .Scan to populate our tag object.

Note - If you retrieve 3 fields from the database and Scan only has 2 parameters, it will fail. They need to match up exactly.

##Querying a Single Row

Say we wanted to query a single row this time and had an ID and again wanted to populate our struct. We could do that like so:

var tag Tag
// Execute the query
err = db.QueryRow("SELECT id, name FROM tags WHERE id = ?", 2).Scan(&tag.ID, &tag.Name)
if err != nil {
    panic(err.Error()) // proper error handling instead of panic in your app
}

log.Println(tag.ID)
log.Println(tag.Name)

##Conclusion

In this tutorial, we managed to set up a connection to a MySQL and then perform some simple queries to that database and marshal the returned responses into a struct or an array of structs. This should hopefully give you everything you need in order to take things further and build your own Go applications on top of MySQL.

For a more abstracted approach to database interactions, consider our ORM tutorial which covers GORM.

If you found this tutorial helpful or require more assistance then please don’t hesitate to let me know in the comments section below:

###Recommended Reading:

• Creating a RESTful JSON API with Go
• Understanding Go Error Handling
• GORM ORM Tutorial

##Ready to Go Further?

This tutorial is part of the Go Learning Path — a curated route from beginner to advanced Go developer, covering databases, REST APIs, testing, gRPC and more.