Querying MySQL in Go

Recently I’ve been experimenting with Go, mainly due to its excellent concurrency support. Being a web developer my natural instinct was to integrate it in existing projects – connecting with MySQL was a initial choice. The following is the Go code to connect with MySQL.

 
package main
 
import (
    "fmt"
    "log"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)
 
func main() {
    db, err := sql.Open("mysql", "USER:PASSWORD@/DATABASE")
    defer db.Close()
 
    if err != nil {
        log.Fatal(err.Error())
    }
 
    err = db.Ping()
    if err != nil {
        log.Fatal(err.Error())
    }
 
 
    rows, err := db.Query("SELECT COUNT(*) AS total FROM wp_posts")
    defer rows.Close()
 
    if err != nil {
        log.Fatal(err.Error())
    }
 
    for rows.Next() {
        var total int
        if err := rows.Scan(&total); err != nil {
                log.Fatal(err.Error())
        }
        fmt.Printf("Total rows found = %d\n", total)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err.Error())
    }
 
 
}

To connect with MySQL from Go you will need to use the database/sql package, as well as a driver for the specific database you want to use, MySQL in our case. Note that the package database/sql provides a generic interface around SQL (or SQL-like) databases and must be used in conjunction with a database driver for your specific needs. See https://golang.org/s/sqldrivers for a list of drivers.

Add the following to the top of your Go source file:

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

Notice that we’re loading the MySQL driver anonymously, aliasing its package qualifier to a “blank” _ so none of its exported names are visible to our code. Under the hood, the driver registers itself as being available to the database/sql package, but in general nothing else happens. Not doing this and importing the MySQL driver normally will result in a “imported and not used” error; as Go ensures that imports that are not used to not clutter the source code.

Next we open the database connection.

 db, err := sql.Open("mysql", "USER:PASSWORD@/DATABASE")
 defer db.Close()
 
 if err != nil {
    log.Fatal(err.Error())
 }

Note that sql.Open() does not establish any connections to the database, nor does it validate driver connection parameters. Instead, it simply prepares the database abstraction for later use. The first actual connection to the underlying database will be established lazily, when it’s needed for the first time. To check immediately that the database we are connecting is available and accessible use db.Ping(). You may have noticed the defer db.Close() statement. A defer statement defers the execution of a function until the surrounding function returns. The deferred call’s arguments are evaluated immediately, but the function call is not executed until the surrounding function returns. A defer statement is often used with paired operations like open and close, connect and disconnect to ensure that resources are released in all cases, no matter how complex the control flow.

err = db.Ping()
if err != nil {
    log.Fatal(err.Error())
}

Next we query the database and print the results.

    // Here using a sample WordPress database
    rows, err := db.Query("SELECT COUNT(*) AS total FROM wp_posts")
    defer rows.Close()
 
    if err != nil {
        log.Fatal(err.Error())
    }
 
    for rows.Next() {
        var total int
        if err := rows.Scan(&total); err != nil {
                log.Fatal(err.Error())
        }
        fmt.Printf("Total rows found = %d\n", total)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err.Error())
    }

Here’s what’s happening in the above code:

1. We’re using db.Query() to send the query to the database and check the error, as usual.
2. We defer rows.Close().
3. We iterate over the rows with rows.Next().
4. We read the columns in each row into variables with rows.Scan().
5. We check for errors after we’re done iterating over the rows.
6. We print each row.

This is pretty much the only way to do it in Go. You can’t get a row as a map like in other dynamic languages, for example. That’s because everything is strongly typed. You need to create variables of the correct type and pass pointers to them, as shown.

Leave a Reply

Your email address will not be published. Required fields are marked *