From the command line in the directory containing main.go, run the code by typing go run with a dot argument to mean “run the package in the current directory.”

$ go run .
Connected!
Use the go get to add the github.com/go-sql-driver/mysql module as a dependency for your own module. Use a dot argument to mean “get dependencies for code in the current directory.”

$ go get .
go get: added github.com/go-sql-driver/mysql v1.6.0
From the command line in the directory containing main.go, run the code by typing go run with a dot argument to mean “run the package in the current directory.”

$ go run .
Connected!
Use the go get to add the github.com/go-sql-driver/mysql module as a dependency for your own module. Use a dot argument to mean “get dependencies for code in the current directory.”

$ go get .
go get: added github.com/go-sql-driver/mysql v1.6.0
Into main.go, beneath the import code you just added, paste the following Go code to create a database handle.

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",
    }
    // 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 top of the file should now look like this:

package main

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

    "github.com/go-sql-driver/mysql"
)
This section contains the code for the application you build with this tutorial.

package main

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

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

var db *sql.DB

type Album struct {
    ID     int64
    Title  string
    Artist string
    Price  float32
}

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",
    }
    // 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!")

    albums, err := albumsByArtist("John Coltrane")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Albums found: %v\n", albums)

    // Hard-code ID 2 here to test the query.
    alb, err := albumByID(2)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Album found: %v\n", alb)

    albID, err := addAlbum(Album{
        Title:  "The Modern Sound of Betty Carter",
        Artist: "Betty Carter",
        Price:  49.99,
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("ID of added album: %v\n", albID)
}

// albumsByArtist queries for albums that have the specified artist name.
func albumsByArtist(name string) ([]Album, error) {
    // An albums slice to hold data from returned rows.
    var albums []Album

    rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    defer rows.Close()
    // Loop through rows, using Scan to assign column data to struct fields.
    for rows.Next() {
        var alb Album
        if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
            return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
        }
        albums = append(albums, alb)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    return albums, nil
}

// albumByID queries for the album with the specified ID.
func albumByID(id int64) (Album, error) {
    // An album to hold data from the returned row.
    var alb Album

    row := db.QueryRow("SELECT * FROM album WHERE id = ?", id)
    if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }
    return alb, nil
}

// addAlbum adds the specified album to the database,
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
    result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
From the command line in the directory containing main.go, run the code.

$ go run .
Connected!
Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]
Run the go mod init command, giving it your new code’s module path.

$ go mod init example/data-access
go: creating new go.mod: module example/data-access
Into main.go, paste the following code to import the driver package.

package main

import "github.com/go-sql-driver/mysql"
Beneath albumByID, paste the following addAlbum function to insert a new album in the database, then save the main.go.

// addAlbum adds the specified album to the database,
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
    result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    id, err := result.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }
    return id, nil
}
From the command prompt, create a directory for your code called data-access.

$ mkdir data-access
$ cd data-access

Recommend

Go Tutorial: Accessing a relational database Get a database handle and connect Run the code

Go Tutorial: Accessing a relational database Get a database handle and connect Write the code

Go Tutorial: Accessing a relational database Find and import a database driver

Go Tutorial: Accessing a relational database Set up a database

Go Tutorial: Accessing a relational database Create a folder for your code

Go Tutorial: Getting started with generics Completed code

Go Tutorial: Getting started with generics Declare a type constraint Write the code

Go Tutorial: Getting started with generics Add a generic function to handle multiple types Write the code

Go Tutorial: Getting started with generics Add non-generic functions Run the code

Go Tutorial: Getting started with generics Add non-generic functions Write the code

Go Tutorial: Getting started with generics Create a folder for your code

Go Setting up and using gccgo C Interoperability Function names

Go Setting up and using gccgo C Interoperability Types

Go Setting up and using gccgo Imports

Go Setting up and using gccgo Using gccgo

Go Setting up and using gccgo Building Build commands

Setting up and using gccgo Building Gold

Installing Go from source Optional environment variables

Installing Go from source Keeping up with releases

Installing Go from source Install additional tools

Installing Go from source Testing your installation

Installing Go from source Install Go

Installing Go from source (Optional) Switch to the master branch

Installing Go from source Fetch the repository

Installing Go from source Install Go compiler binaries for bootstrap Bootstrap toolchain using gccgo

Installing Go from source Install Go compiler binaries for bootstrap Bootstrap toolchain from cross-compiled source

Go Managing dependencies Specifying a module proxy server

Go Managing dependencies Removing a dependency

Go Managing dependencies Getting a specific commit using a repository identifier

Go Managing dependencies Developing and testing against unpublished module code Requiring external module code from your own repository fork

Go Managing dependencies Developing and testing against unpublished module code Requiring module code in a local directory

Go Managing dependencies Synchronizing your code’s dependencies

Go Managing dependencies Discovering available updates

Go Managing dependencies Getting a specific dependency version

Go Managing dependencies Adding a dependency

Go Managing dependencies Naming a module

Go Managing dependencies Enabling dependency tracking in your code

Go Managing dependencies Locating and importing useful packages

Go Module version numbering Major version Example

Go Module version numbering Patch version Example

Go Module version numbering Minor version Example

Go Module version numbering Pre-release version Example

Go Managing module source Organizing code in the repository

Go go.mod file reference retract Notes

Go go.mod file reference retract Example

Go go.mod file reference retract Syntax

Go go.mod file reference exclude Notes

Go go.mod file reference exclude Example

Go go.mod file reference exclude Syntax

Go go.mod file reference replace Notes

Go go.mod file reference replace Examples

Go go.mod file reference replace Syntax

Go go.mod file reference require Syntax

Go go.mod file reference go Syntax

Go go.mod file reference module Notes

Go go.mod file reference module Examples