How to use Go with MySQL

Looking for the best database to use for your Golang projects? MySQL is one of the most popular tools to work with SQL databases. Is it the best one for you? You won’t know until you try, but we are sure it’s on your shortlist so we’ve created this tutorial to help you get started easily.

In this article, we are diving into connecting to MySQL DBs in Go, so if you are using or learning Golang and want to try working with SQL databases, keep reading. We will help you set up your MySQL to work smoothly with Golang.

Scroll on to find out how to get and install Golang — MySQL driver, create and then connect to a database, as well as learn basic DB actions with our hands-on Go + MySQL examples.

Getting started with Go MySQL driver

Make sure you have MySQL on your machine by running mysql --version command in Terminal.

If you have MySQL, you will see the MySQL version printed in your Terminal window.

Next, run the installation command for the Golang MySQL driver:

go get -u github.com/go-sql-driver/mysql

You don’t have to use this specific Go MySQL driver, but it’s a popular one, so we are using it for our tutorial. You can find detailed information about this driver on its page on GitHub.

Now, let’s connect to MySQL.

Make sure to create your application in the folder where you’ve have all your Golang installation files or use commands go mod init test-sql and go mod tidy to create go.mod and go.sum files allowing you to run your Go app in a folder other than the one you’ve installed Go in.

With that out of the way, you are ready to connect. Create a main.go file and insert the following code:

package main
import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    db, err := sql.Open("mysql", "root:<yourMySQLdatabasepassword>@tcp(127.0.0.1:3306)/test")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
    fmt.Println("Success!")
}

You can use a code editor of your choice to write this code in. We are using CodeRunner, a quick code editor with handy syntax highlighters, IDE features, and support for over 25 languages, including Golang.

Just copy the above code and insert your MySQL database password in this line:

"root:@tcp(127.0.0.1:3306)/test")

When you get this done, you are ready to run your Go application in Terminal.

First, move to the folder containing your main.go program using change directory command cd:

Next, type in go run main.go:

Your program will print “Success!” on the screen if everything runs smoothly.

Since we will be editing the code in this app to run other actions with your database, we recommend to save the code snippet in SnippetsLab, an app that helps keep your code snippets organized:

Be sure to save the code without your login credentials though! Use a password manager to keep your info safe. For thai project, we saved our MySQL database password in Secrets:

The app allows you to organize and keep safe passwords, sensitive notes, credit card information, bank account details, and more.

Creating a MySQL database

Okay, it’s time to go over a few examples of using MySQL in your Golang projects. But before we learn how to use Golang to insert into your SQL database, let’s first create a starter database.

There are a number of ways you can do this, one of them is using Docker. For this article, we went with a no-code solution though — offered by TablePlus. The app allows you to create, manage, and query databases in MySQL, PostgreSQL, SQLite.

We created a DB for our Golang queries titled 123begin. Next, we’ve created a table titled testtable2. Remember these values because you’ll need them to insert and query your database.

Working with MySQL database

Now that we have our table, we can try and insert some data — a database object.

Here is the code for inserting an object into a table in your database:

package main
import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    db, err := sql.Open("mysql", "root:<password>@tcp(127.0.0.1:3306)/123begin")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
    insert, err := db.Query("INSERT INTO testtable2 VALUES('23')")
    if err !=nil {
        panic(err.Error())
    }
    defer insert.Close()
    fmt.Println("Yay, values added!")
}

Add this code to your main.go file. You can copy your previous code to SnippetsLab to access it for other projects.

Now, run go run main.go command in Terminal in the folder with your Go project. If everything goes right, you should have Yay, values added! printed back:

With the new value added, we can have a Golang app do an SQL DB query to check that your value has indeed been added to the table.

Here’s the code we used for this:

package main
import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)
type Testtable2 struct {
    id int `json:"id"`
}
func main() {
    db, err := sql.Open("mysql", "root:<password>@tcp(127.0.0.1:3306)/123begin")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
    results, err := db.Query("SELECT id FROM testtable2")
    if err !=nil {
        panic(err.Error())
    }
    for results.Next() {
        var testtable2 Testtable2
        err = results.Scan(&testtable2.id)
        if err !=nil {
            panic(err.Error())
        }
        fmt.Println(testtable2.id)
    }
}

Replace testtable2, 123begin, and id with respective values for your database table, database name, and the column you are querying.

Run go run main.go in Terminal:

We have here 23 printed back to us, which is the value we’ve inserted into our table in the previous step, so our query has been a success.

Go MySQL driver issues

Nothing is perfect, so if you are writing code, you may have a few typos or incorrect commands creep in.

One of the more popular mishaps would be running your go run main.go command in Terminal in the wrong folder. Make sure you move to the folder containing your Go project to allow for your Golang app to run.

Use cd (change directory) command to move to the right folder. You can just drag and drop the right folder into your Terminal window for the path to appear after the cd command. Press Enter.

Your Go app won’t run without go.mod and go.sum, so use ls command in Terminal to make sure your folder contains go.mod and go.sum files. If the files are missing, create them using the commands we’ve provided in the Getting started… section of this blog post.

If you’ve encountered a genuine bug with your MySQL database, MySQL actually accepts bug reports on this official page.

Conclusion

In the above tutorial, we’ve demonstrated how you can establish a connection between your Golang app and a database in MySQL using the Go MySQL driver. We hope now you can work with your MySQL databases within your Go projects seamlessly and create smart apps easily.

Expand your coding toolkit by trying some of the tools we’ve used for this tutorial:

• add the snippets we’ve provided to your collection in a code notepad app (we used SnippetsLab for the task),

• save your database password in a safe place — you can try Secrets, the app we’ve used for this tutorial,

• create and manage your databases in TablePlus,

• use CodeRunner to write, edit, and debug your code.

CodeRunner, SnippetsLab, Secrets, and TablePlus plus numerous other productivity tools are available on the one-stop productivity service Setapp. Find apps for managing your time and schedule, backing up data, working with code, editing images, generating mockups, and more on Setapp. Try the service for free for 7 days with a Setapp free trial!