Single-record SQL queries

The pattern for executing a SELECT statement to retrieve a single record from the database is a little more complicated. Let’s explain how to do it by updating our SnippetModel.Get() method so that it returns a single specific snippet based on its ID.

To do this, we’ll need to run the following SQL query on the database:

SELECT id, title, content, created, expires FROM snippets
WHERE expires > UTC_TIMESTAMP() AND id = ?

Because our snippets table uses the id column as its primary key, this query will only ever return exactly one database row (or none at all). The query also includes a check on the expiry time so that we don’t return any snippets that have expired.

Notice too that we’re using a placeholder parameter again for the id value?

Open the internal/models/snippets.go file and add the following code:

package models

import (
    "database/sql"
    "errors" // New import
    "time" 
)

...

func (m *SnippetModel) Get(id int) (Snippet, error) {
    // Write the SQL statement we want to execute. Again, I've split it over two
    // lines for readability.
    stmt := `SELECT id, title, content, created, expires FROM snippets
    WHERE expires > UTC_TIMESTAMP() AND id = ?`

    // Use the QueryRow() method on the connection pool to execute our
    // SQL statement, passing in the untrusted id variable as the value for the
    // placeholder parameter. This returns a pointer to a sql.Row object which
    // holds the result from the database.
    row := m.DB.QueryRow(stmt, id)

    // Initialize a new zeroed Snippet struct.
    var s Snippet

    // Use row.Scan() to copy the values from each field in sql.Row to the
    // corresponding field in the Snippet struct. Notice that the arguments
    // to row.Scan are *pointers* to the place you want to copy the data into,
    // and the number of arguments must be exactly the same as the number of
    // columns returned by your statement.
    err := row.Scan(&s.ID, &s.Title, &s.Content, &s.Created, &s.Expires)
    if err != nil {
        // If the query returns no rows, then row.Scan() will return a
        // sql.ErrNoRows error. We use the errors.Is() function check for that
        // error specifically, and return our own ErrNoRecord error
        // instead (we'll create this in a moment).
        if errors.Is(err, sql.ErrNoRows) {
            return Snippet{}, ErrNoRecord
        } else {
            return Snippet{}, err
        }
    }

    // If everything went OK, then return the filled Snippet struct.
    return s, nil
}

...

Behind the scenes of rows.Scan() your driver will automatically convert the raw output from the SQL database to the required native Go types. So long as you’re sensible with the types that you’re mapping between SQL and Go, these conversions should generally Just Work. Usually:

• CHAR, VARCHAR and TEXT map to string.
• BOOLEAN maps to bool.
• INT maps to int; BIGINT maps to int64.
• DECIMAL and NUMERIC map to float.
• TIME, DATE and TIMESTAMP map to time.Time.

If you try to run the application at this point, you should get a compile-time error saying that the ErrNoRecord value is undefined:

$ go run ./cmd/web/
# snippetbox.alexedwards.net/internal/models
internal/models/snippets.go:82:25: undefined: ErrNoRecord

Let’s go ahead and create that now in a new internal/models/errors.go file. Like so:

$ touch internal/models/errors.go

package models

import (
    "errors"
)

var ErrNoRecord = errors.New("models: no matching record found")

As an aside, you might be wondering why we’re returning the ErrNoRecord error from our SnippetModel.Get() method, instead of sql.ErrNoRows directly. The reason is to help encapsulate the model completely, so that our handlers aren’t concerned with the underlying datastore or reliant on datastore-specific errors (like sql.ErrNoRows) for its behavior.

Using the model in our handlers

Alright, let’s put the SnippetModel.Get() method into action.

Open your cmd/web/handlers.go file and update the snippetView handler so that it returns the data for a specific record as a HTTP response:

package main

import (
    "errors" // New import
    "fmt"
    "html/template"
    "net/http"
    "strconv"

    "snippetbox.alexedwards.net/internal/models" // New import
)

...

func (app *application) snippetView(w http.ResponseWriter, r *http.Request) {
    id, err := strconv.Atoi(r.PathValue("id"))
    if err != nil || id < 1 {
        http.NotFound(w, r)
        return
    }

    // Use the SnippetModel's Get() method to retrieve the data for a
    // specific record based on its ID. If no matching record is found,
    // return a 404 Not Found response.
    snippet, err := app.snippets.Get(id)
    if err != nil {
        if errors.Is(err, models.ErrNoRecord) {
            http.NotFound(w, r)
        } else {
            app.serverError(w, r, err)
        }
        return
    }

    // Write the snippet data as a plain-text HTTP response body.
    fmt.Fprintf(w, "%+v", snippet)
}

...

Let’s give this a try. Restart the application, then open your web browser and visit http://localhost:4000/snippet/view/1. You should see a HTTP response which looks similar to this:

You might also want to try making some requests for other snippets which are expired or don’t yet exist (like an id value of 99) to verify that they return a 404 page not found response:

Additional information

Checking for specific errors

A couple of times in this chapter we’ve used the errors.Is() function to check whether an error matches a specific value. Like this:

if errors.Is(err, models.ErrNoRecord) {
    http.NotFound(w, r)
} else {
    app.serverError(w, r, err)
}

In very old versions of Go (prior to 1.13), the idiomatic way to compare errors was to use the equality operator ==, like so:

if err == models.ErrNoRecord {
    http.NotFound(w, r)
} else {
    app.serverError(w, r, err)
}

But, while this code still compiles, it’s safer and best practice to use the errors.Is() function instead.

This is because Go 1.13 introduced the ability to add additional information to errors by wrapping them. If an error happens to get wrapped, a entirely new error value is created — which in turn means that it’s not possible to check the value of the original underlying error using the regular == equality operator.

The errors.Is() function works by unwrapping errors as necessary before checking for a match.

There is also another function, errors.As() which you can use to check if a (potentially wrapped) error has a specific type. We’ll use this later on this book.

Shorthand single-record queries

I’ve deliberately made the code in SnippetModel.Get() slightly long-winded to help clarify and emphasize what is going on behind the scenes of the code.

In practice, you can shorten the code slightly by leveraging the fact that errors from DB.QueryRow() are deferred until Scan() is called. It makes no functional difference, but if you want it’s perfectly OK to re-write the code to look something like this:

func (m *SnippetModel) Get(id int) (Snippet, error) {
    var s Snippet
    
    err := m.DB.QueryRow("SELECT ...", id).Scan(&s.ID, &s.Title, &s.Content, &s.Created, &s.Expires)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return Snippet{}, ErrNoRecord
        } else {
             return Snippet{}, err
        }
    }

    return s, nil
}