Querying for data

When executing an SQL statement that returns data, use one of the Query methods provided in the database/sql package. Each of these returns a Row or Rows whose data you can copy to variables using the Scan method. You’d use these methods to, for example, execute SELECT statements.

When executing a statement that doesn’t return data, you can use an Exec or ExecContext method instead. For more, see Executing statements that don’t return data.

The database/sql package provides two ways to execute a query for results.

• Querying for a single row – QueryRow returns at most a single Row from the database. For more, see Querying for a single row.
• Querying for multiple rows – Query returns all matching rows as a Rows struct your code can loop over. For more, see Querying for multiple rows.

If your code will be executing the same SQL statement repeatedly, consider using a prepared statement. For more, see Using prepared statements.

Caution: Don’t use string formatting functions such as fmt.Sprintf to assemble an SQL statement! You could introduce an SQL injection risk. For more, see Avoiding SQL injection risk.

Querying for a single row

QueryRow retrieves at most a single database row, such as when you want to look up data by a unique ID. If multiple rows are returned by the query, the Scan method discards all but the first.

QueryRowContext works like QueryRow but with a context.Context argument. For more, see Canceling in-progress operations.

The following example uses a query to find out if there’s enough inventory to support a purchase. The SQL statement returns true if there’s enough, false if not. Row.Scan copies the boolean return value into the enough variable through a pointer.

func canPurchase(id int, quantity int) (bool, error) {
    var enough bool
    // Query for a value based on a single row.
    if err := db.QueryRow("SELECT (quantity >= ?) from album where id = ?",
        quantity, id).Scan(&enough); err != nil {
        if err == sql.ErrNoRows {
            return false, fmt.Errorf("canPurchase %d: unknown album", id)
        }
        return false, fmt.Errorf("canPurchase %d: %v", id, err)
    }
    return enough, nil
}

Note: Parameter placeholders in prepared statements vary depending on the DBMS and driver you’re using. For example, the pq driver for Postgres requires a placeholder like $1 instead of ?.

Handling errors

QueryRow itself returns no error. Instead, Scan reports any error from the combined lookup and scan. It returns sql.ErrNoRows when the query finds no rows.

Functions for returning a single row

Querying for multiple rows

You can query for multiple rows using Query or QueryContext, which return a Rows representing the query results. Your code iterates over the returned rows using Rows.Next. Each iteration calls Scan to copy column values into variables.

QueryContext works like Query but with a context.Context argument. For more, see Canceling in-progress operations.

The following example executes a query to return the albums by a specified artist. The albums are returned in an sql.Rows. The code uses Rows.Scan to copy column values into variables represented by pointers.

func albumsByArtist(artist string) ([]Album, error) {
    rows, err := db.Query("SELECT * FROM album WHERE artist = ?", artist)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    // An album slice to hold data from returned rows.
    var albums []Album

    // 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, &alb.Quantity); err != nil {
            return albums, err
        }
        albums = append(albums, alb)
    }
    if err = rows.Err(); err != nil {
        return albums, err
    }
    return albums, nil
}

Note the deferred call to rows.Close. This releases any resources held by the rows no matter how the function returns. Looping all the way through the rows also closes it implicitly, but it is better to use defer to make sure rows is closed no matter what.

Note: Parameter placeholders in prepared statements vary depending on the DBMS and driver you’re using. For example, the pq driver for Postgres requires a placeholder like $1 instead of ?.

Handling errors

Be sure to check for an error from sql.Rows after looping over query results. If the query failed, this is how your code finds out.

Functions for returning multiple rows

Handling nullable column values

The database/sql package provides several special types you can use as arguments for the Scan function when a column’s value might be null. Each includes a Valid field that reports whether the value is non-null, and a field holding the value if so.

Code in the following example queries for a customer name. If the name value is null, the code substitutes another value for use in the application.

var s sql.NullString
err := db.QueryRow("SELECT name FROM customer WHERE id = ?", id).Scan(&s)
if err != nil {
    log.Fatal(err)
}

// Find customer name, using placeholder if not present.
name := "Valued Customer"
if s.Valid {
    name = s.String
}

See more about each type in the sql package reference:

• NullBool
• NullFloat64
• NullInt32
• NullInt64
• NullString
• NullTime

Getting data from columns

When looping over the rows returned by a query, you use Scan to copy a row’s column values into Go values, as described in the Rows.Scan reference.

There is a base set of data conversions supported by all drivers, such as converting SQL INT to Go int. Some drivers extend this set of conversions; see each individual driver’s documentation for details.

As you might expect, Scan will convert from column types to Go types that are similar. For example, Scan will convert from SQL CHAR, VARCHAR, and TEXT to Go string. However, Scan will also perform a conversion to another Go type that is a good fit for the column value. For example, if the column is a VARCHAR that will always contain a number, you can specify a numeric Go type, such as int, to receive the value, and Scan will convert it using strconv.Atoi for you.

For more detail about conversions made by the Scan function, see the Rows.Scan reference.

Handling multiple result sets

When your database operation might return multiple result sets, you can retrieve those by using Rows.NextResultSet. This can be useful, for example, when you’re sending SQL that separately queries multiple tables, returning a result set for each.

Rows.NextResultSet prepares the next result set so that a call to Rows.Next retrieves the first row from that next set. It returns a boolean indicating whether there is a next result set at all.

Code in the following example uses DB.Query to execute two SQL statements. The first result set is from the first query in the procedure, retrieving all of the rows in the album table. The next result set is from the second query, retrieving rows from the song table.

rows, err := db.Query("SELECT * from album; SELECT * from song;")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

// Loop through the first result set.
for rows.Next() {
    // Handle result set.
}

// Advance to next result set.
rows.NextResultSet()

// Loop through the second result set.
for rows.Next() {
    // Handle second set.
}

// Check for any error in either result set.
if err := rows.Err(); err != nil {
    log.Fatal(err)
}