Accessing MySQL using Go Example

In this example, I will be accessing a relational database (MySQL) using the Go programming language. I have followed the tutorial from the official Go website. Unfortunately, on my first try, I wasn't able to connect to my database. I'm writing this to supplement what was written on the Go website.

First of all, I will not be talking about setup and installation of MySQL and Go. I am assuming that the reader has sufficient knowledge in setting up MySQL and Go. I'm using Visual Studio Code along with it's Go extensions to edit my code.

MySQL setup

Create a database called recordings. In the recordings schema, create the table and values as seen below. Would be easier if you have MySQL Workbench but you can also go command line or however you like. Don't forget to use recordings.

DROP TABLE IF EXISTS album;
CREATE TABLE album (
  id         INT AUTO_INCREMENT NOT NULL,
  title      VARCHAR(128) NOT NULL,
  artist     VARCHAR(255) NOT NULL,
  price      DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO album
  (title, artist, price)
VALUES
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

Next is to create your username and password. I have created "golang" as my username and password in MySQL.

main.go

package main

import (
	"database/sql"
	"fmt"
	"log"

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

var db *sql.DB

func main() {
	// Capture connection properties.
	cfg := mysql.Config{
		User:   os.Getenv("DBUSER"),
		Passwd: os.Getenv("DBPASS"),
		Net:    "tcp",
		Addr:                 "127.0.0.1:3306",
		DBName:               "recordings",
		AllowNativePasswords: true, // if not included, mysql native password authentication error is generated
	}
	// Get a database handle.
	var err error
	db, err = sql.Open("mysql", cfg.FormatDSN())
	if err != nil {
		log.Fatal(err)
	}

	pingErr := db.Ping()
	if pingErr != nil {
		log.Fatal(pingErr)
	}
	fmt.Println("Connected!")
}

The above code is the same as the one in the Go Dev Tutorial. Except for one line, AllowNativePasswords: true. This line was missing in the Go Dev Tutorial and caused an error on my first go run .. This led me to some googling around. I found interesting stuff and I've collated them here. Hopefully it will help others who are starting to learn the Go languange. By the way, this should show Connected!.

Environment Variables

I'm on a Windows 10 machine so to set an environment variable on the command line, do set DBUSER=golang. If you don't want to use the command line because you are using Visual Studio Code. You'd want to run the code on the VSCode terminal, do $env:DBUSER = "golang". That's how to set an environment variable in powershell.

Addr

So I've tried this code using two MySQL databases. On MySQL 5.7.33, Addr: "127.0.0.1:3306" works fine. On MySQL 5.6.43, Addr: "localhost:3306" works fine. But if you swap the hostnames, you get and access denied for user error.

AllowNativePasswords

If you don't want to use AllowNativePasswords then you could replace cfg := mysql.Config() with cfg := mysql.NewConfig(). Something like below:

cfg := mysql.NewConfig()
cfg.User = os.Getenv("DBUSER")
cfg.Passwd = os.Getenv("DBPASS")
cfg.Net = "tcp"
cfg.Addr = "127.0.0.1:3306"
cfg.DBName = "recordings"

There you have it. Now you don't have to do too much googling around. The complete project can be cloned from github.com/jpllosa/go-relational-database.