This tutorial introduces the basics of accessing a relational database withGo and thedatabase/sql package in its standard library.

You’ll get the most out of this tutorial if you have a basic familiarity withGo and its tooling. If this is your first exposure to Go, please seeTutorial: Get started with Gofor a quick introduction.

Thedatabase/sql package you’llbe using includes types and functions for connecting to databases, executingtransactions, canceling an operation in progress, and more. For more detailson using the package, seeAccessing databases.

In this tutorial, you’ll create a database, then write code to access thedatabase. Your example project will be a repository of data about vintagejazz records.

In this tutorial, you’ll progress through the following sections:

1. Create a folder for your code.
2. Set up a database.
3. Import the database driver.
4. Get a database handle and connect.
5. Query for multiple rows.
6. Query for a single row.
7. Add data.

Note: For other tutorials, seeTutorials.

Prerequisites

• An installation of theMySQLrelational database management system (DBMS).
• An installation of Go. For installation instructions, seeInstalling Go.
• A tool to edit your code. Any text editor you have will work fine.
• A command terminal. Go works well using any terminal on Linux and Mac,and on PowerShell or cmd in Windows.

Create a folder for your code

To begin, create a folder for the code you’ll write.

1. Open a command prompt and change to your home directory.

   On Linux or Mac:

   $ cd

   On Windows:

   C:\> cd %HOMEPATH%

   For the rest of the tutorial we will show a $ as the prompt. Thecommands we use will work on Windows too.

2. From the command prompt, create a directory for your code calleddata-access.

   $ mkdir data-access$ cd data-access

3. Create a module in which you can manage dependencies you will add duringthis tutorial.

   Run thego mod init command, giving it your new code’s module path.

   $ go mod init example/data-accessgo: creating new go.mod: module example/data-access

   This command creates a go.mod file in which dependencies you add will belisted for tracking. For more, be sure to seeManaging dependencies.

   Note: In actual development, you’d specify a module path that’smore specific to your own needs. For more, seeManaging dependencies.

Next, you’ll create a database.

Set up a database

In this step, you’ll create the database you’ll be working with. You’ll usethe CLI for the DBMS itself to create the database and table, as well as toadd data.

You’ll be creating a database with data about vintage jazz recordings on vinyl.

The code here uses theMySQL CLI,but most DBMSes have their own CLI with similar features.

1. Open a new command prompt.

2. At the command line, log into your DBMS, as in the following example forMySQL.

   $ mysql -u root -pEnter password:mysql>

3. At themysql command prompt, create a database.

   mysql> create database recordings;

4. Change to the database you just created so you can add tables.

   mysql> use recordings;Database changed

5. In your text editor, in the data-access folder, create a file calledcreate-tables.sql to hold SQL script for adding tables.

6. Into the file, paste the following SQL code, then save the file.

   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);

   In this SQL code, you:

   • Delete (drop) a table calledalbum. Executing this command first makesit easier for you to re-run the script later if you want to start overwith the table.

   • Create analbum table with four columns:title,artist, andprice.Each row’sid value is created automatically by the DBMS.

   • Add four rows with values.

7. From themysql command prompt, run the script you just created.

   You’ll use thesource command in the following form:

   mysql> source /path/to/create-tables.sql

8. At your DBMS command prompt, use aSELECT statement to verify you’vesuccessfully created the table with data.

   mysql> select * from album;+----+---------------+----------------+-------+| id | title         | artist         | price |+----+---------------+----------------+-------+|  1 | Blue Train    | John Coltrane  | 56.99 ||  2 | Giant Steps   | John Coltrane  | 63.99 ||  3 | Jeru          | Gerry Mulligan | 17.99 ||  4 | Sarah Vaughan | Sarah Vaughan  | 34.98 |+----+---------------+----------------+-------+4 rows in set (0.00 sec)

Next, you’ll write some Go code to connect so you can query.

Find and import a database driver

Now that you’ve got a database with some data, get your Go code started.

Locate and import a database driver that will translate requests you makethrough functions in thedatabase/sql package into requests the databaseunderstands.

1. In your browser, visit theSQLDriverswiki page to identify a driver you can use.

   Use the list on the page to identify the driver you’ll use. For accessingMySQL in this tutorial, you’ll useGo-MySQL-Driver.

2. Note the package name for the driver – here,github.com/go-sql-driver/mysql.

3. Using your text editor, create a file in which to write your Go code andsave the file as main.go in the data-access directory you created earlier.

4. Into main.go, paste the following code to import the driver package.

   package mainimport "github.com/go-sql-driver/mysql"

   In this code, you:

   • Add your code to amain package so you can execute it independently.

   • Import the MySQL drivergithub.com/go-sql-driver/mysql.

With the driver imported, you’ll start writing code to access the database.

Get a database handle and connect

Now write some Go code that gives you database access with a database handle.

You’ll use a pointer to ansql.DB struct, which represents access to aspecific database.

Write the code

1. Into main.go, beneath theimport code you just added, paste the followingGo code to create a database handle.

   var db *sql.DBfunc main() {    // Capture connection properties.    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"    // 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!")}

   In this code, you:

   • Declare adb variable of type*sql.DB.This is your database handle.

     Makingdb a global variable simplifies this example. Inproduction, you’d avoid the global variable, such as by passing thevariable to functions that need it or by wrapping it in a struct.

   • Use the MySQL driver’sConfig– and the type’sFormatDSN-– to collect connection properties and format them into a DSN for a connection string.

     TheConfig struct makes for code that’s easier to read than aconnection string would be.

   • Callsql.Opento initialize thedb variable, passing the return value ofFormatDSN.

   • Check for an error fromsql.Open. It could fail if, forexample, your database connection specifics weren’t well-formed.

     To simplify the code, you’re callinglog.Fatal to endexecution and print the error to the console. In production code, you’llwant to handle errors in a more graceful way.

   • CallDB.Ping toconfirm that connecting to the database works. At run time,sql.Open might not immediately connect, depending on thedriver. You’re usingPing here to confirm that thedatabase/sql package can connect when it needs to.

   • Check for an error fromPing, in case the connection failed.

   • Print a message ifPing connects successfully.

2. Near the top of the main.go file, just beneath the package declaration,import the packages you’ll need to support the code you’ve just written.

   The top of the file should now look like this:

   package mainimport (    "database/sql"    "fmt"    "log"    "os"    "github.com/go-sql-driver/mysql")

3. Save main.go.

Run the code

1. Begin tracking the MySQL driver module as a dependency.

   Use thego getto add the github.com/go-sql-driver/mysql module as a dependency for yourown module. Use a dot argument to mean “get dependencies for code in thecurrent directory.”

   $ go get .go: added filippo.io/edwards25519 v1.1.0go: added github.com/go-sql-driver/mysql v1.8.1

   Go downloaded this dependency because you added it to theimportdeclaration in the previous step. For more about dependency tracking,seeAdding a dependency.

2. From the command prompt, set theDBUSER andDBPASS environment variablesfor use by the Go program.

   On Linux or Mac:

   $ export DBUSER=username$ export DBPASS=password

   On Windows:

   C:\Users\you\data-access> set DBUSER=usernameC:\Users\you\data-access> set DBPASS=password

3. From the command line in the directory containing main.go, run the code bytypinggo run with a dot argument to mean “run the package in thecurrent directory.”

   $ go run .Connected!

You can connect! Next, you’ll query for some data.

Query for multiple rows

In this section, you’ll use Go to execute an SQL query designed to returnmultiple rows.

For SQL statements that might return multiple rows, you use theQuery methodfrom thedatabase/sql package, then loop through the rows it returns. (You’lllearn how to query for a single row later, in the sectionQuery for a single row.)

Write the code

1. Into main.go, immediately abovefunc main, paste the following definitionof anAlbum struct. You’ll use this to hold row data returned from thequery.

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

2. Beneathfunc main, paste the followingalbumsByArtist function to querythe database.

   // 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}

   In this code, you:

   • Declare analbums slice of theAlbum type you defined. This will holddata from returned rows. Struct field names and types correspond todatabase column names and types.

   • UseDB.Query toexecute aSELECT statement to query for albums with thespecified artist name.

     Query’s first parameter is the SQL statement. After theparameter, you can pass zero or more parameters of any type. These providea place for you to specify the values for parameters in your SQL statement.By separating the SQL statement from parameter values (rather thanconcatenating them with, say,fmt.Sprintf), you enable thedatabase/sql package to send the values separate from the SQLtext, removing any SQL injection risk.

   • Defer closingrows so that any resources it holds will be released whenthe function exits.

   • Loop through the returned rows, usingRows.Scan toassign each row’s column values toAlbum struct fields.

     Scan takes a list of pointers to Go values, where the columnvalues will be written. Here, you pass pointers to fields in thealb variable, created using the& operator.Scan writes through the pointers to update the struct fields.

   • Inside the loop, check for an error from scanning column values into thestruct fields.

   • Inside the loop, append the newalb to thealbums slice.

   • After the loop, check for an error from the overall query, usingrows.Err. Note that if the query itself fails, checking for an errorhere is the only way to find out that the results are incomplete.

3. Update yourmain function to callalbumsByArtist.

   To the end offunc main, add the following code.

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

   In the new code, you now:

   • Call thealbumsByArtist function you added, assigning its return value toa newalbums variable.

   • Print the result.

Run the code

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}]

Next, you’ll query for a single row.

Query for a single row

In this section, you’ll use Go to query for a single row in the database.

For SQL statements you know will return at most a single row, you can useQueryRow, which is simpler than using aQuery loop.

Write the code

1. BeneathalbumsByArtist, paste the followingalbumByID function.

   // 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}

   In this code, you:

   • UseDB.QueryRowto execute aSELECT statement to query for an album with thespecified ID.

     It returns ansql.Row. To simplify the calling code(your code!),QueryRow doesn’t return an error. Instead,it arranges to return any query error (such assql.ErrNoRows)fromRows.Scan later.

   • UseRow.Scan to copycolumn values into struct fields.

   • Check for an error fromScan.

     The special errorsql.ErrNoRows indicates that the query returned norows. Typically that error is worth replacing with more specific text,such as “no such album” here.

2. Updatemain to callalbumByID.

   To the end offunc main, add the following code.

   // 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)

   In the new code, you now:

   • Call thealbumByID function you added.

   • Print the album ID returned.

Run the code

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}]Album found: {2 Giant Steps John Coltrane 63.99}

Next, you’ll add an album to the database.

Add data

In this section, you’ll use Go to execute an SQLINSERT statement to add anew row to the database.

You’ve seen how to useQuery andQueryRow with SQL statements thatreturn data. To execute SQL statements thatdon’t return data, you useExec.

Write the code

1. BeneathalbumByID, paste the followingaddAlbum function to insert a newalbum in the database, then save the main.go.

   // addAlbum adds the specified album to the database,// returning the album ID of the new entryfunc 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}

   In this code, you:

   • UseDB.Exec toexecute anINSERT statement.

     LikeQuery,Exec takes an SQL statement followedby parameter values for the SQL statement.

   • Check for an error from the attempt toINSERT.

   • Retrieve the ID of the inserted database row usingResult.LastInsertId.

   • Check for an error from the attempt to retrieve the ID.

2. Updatemain to call the newaddAlbum function.

   To the end offunc main, add the following code.

   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)

   In the new code, you now:

   • CalladdAlbum with a new album, assigning the ID of the album you’readding to analbID variable.

Run the code

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}]Album found: {2 Giant Steps John Coltrane 63.99}ID of added album: 5

Conclusion

Congratulations! You’ve just used Go to perform simple actions with arelational database.

Suggested next topics:

• Take a look at the data access guide, which includes more informationabout the subjects only touched on here.

• If you’re new to Go, you’ll find useful best practices described inEffective Go andHow to write Go code.

• TheGo Tour is a great step-by-stepintroduction to Go fundamentals.

Completed code

This section contains the code for the application you build with this tutorial.

package mainimport (    "database/sql"    "fmt"    "log"    "os"    "github.com/go-sql-driver/mysql")var db *sql.DBtype Album struct {    ID     int64    Title  string    Artist string    Price  float32}func main() {    // Capture connection properties.    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"    // 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 entryfunc 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}