Go MySQL Tutorial [Part 2: Connection and Configuration]

Photo by Tobias Fischer on Unsplash

Reminder:

This little tutorial guides you through setting up a MySQL/MariaDB server and simple programming in Go. The idea is to present the minimum needed to have a working application. It is part of a small series of articles I wanted to write about databases.

Install your MySQL server, click here.
Open a connection to your MySQL server, click here.
MySQL data transfer with Go, coming soon
MySQL transactions with Go, coming soon

Connecting to MySQL with Go

In Go, accessing SQL-type databases is done through the database/sql package, which provides a generic interface to SQL databases. But this package doesn't work on its own. It's only an interface. You need to use another package that will serve as a driver. For my part, I use this one: github.com/go-sql-driver/mysql.

So, the imports in Go code look like this:

package main

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

For those unfamiliar, the underscore character (_) before "github.com/go-sql-driver/mysql" indicates that the package is included, but we won't use any symbols from this package. So what's the point of including this package? The answer isn't that simple. It turns out that in this package, there's an init() function that registers the "MySQL" driver with the "database/sql" package. This allows making queries through the generic interface provided by "database/sql". I might write a short article about this approach in the future.

In any case, now we can connect to the SQL database (I'll let you define the variables adminUser, adminPass, dbHost and dbPort as you see fit):

var (
    adminUser = "root"
    adminPass = "pass"
    dbHost = "127.0.0.1"
    dbPort = 3306
)

func main() {
        dbSrc := fmt.Sprintf("%s:%s@tcp(%s:%d)/?parseTime=true", adminUser, adminPass, dbHost, dbPort)

        db, err := sql.Open("mysql", dbSrc)
        if err != nil {
                log.Fatalf("sql.Open failed: %v", err)
        }
        defer db.Close()

The sql.Open() function takes as arguments the driver name, here "mysql", which, as I mentioned, was "registered" by the "github.com/go-sql-driver/mysql" package. It also takes a string representing the connection parameters. Here the format is "user:password@dbaddr/dbname". This seems quite explicit, but if you have difficulties, there's a wiki with more details accessible here.

Well, to be completely precise, I wasn't entirely accurate. Contrary to what its name suggests, the sql.Open() function doesn't actually open the connection. It only validates the connection parameters. If you want to make sure the connection is properly established, you need to use the Ping() function as follows:

    // sql.Open() does not establish any connections to the database ...
    err = db.Ping()
    if err != nil {
        log.Fatalf("db.Ping failed: %v", err)
    }

Creating a database

The rest is quite simple if you know SQL programming. To start, we can create a database:

       _, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + dataBaseName)
        if err != nil {
                log.Fatalf("db.Exec(CREATE DATABASE) failed: %v", err)
        }

We don't need to look at the content returned by the db.Exec() function here. Now we can, following the same pattern, create a table:

       _, err = db.Exec("USE " + dataBaseName)
        if err != nil {
                log.Fatalf("db.Exec(USE) failed: %v", err)
    }

        _, err = db.Exec(`CREATE TABLE IF NOT EXISTS ` + tableName + ` (
                                                id                      INT(11)                 NOT NULL AUTO_INCREMENT,
                                                data            VARCHAR(32),
                                                creation        DATETIME,
                                                PRIMARY KEY (id)
                        )`)
        if err != nil {
                log.Fatalf("db.Exec(CREATE TABLE) failed: %v", err)
        }

No surprises here either. You just need to remember to specify that you want to create the table in the newly created database. That's the purpose of the SQL "USE" command.