Single-record SQL queries

The pattern for SELECTing 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 pointer to a new zeroed Snippet struct.
    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 nil, ErrNoRecord
        } else {
            return nil, err
        }
    }

    // If everything went OK then return the Snippet object.
    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 application isn’t concerned with the underlying datastore or reliant on datastore-specific errors 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.URL.Query().Get("id"))
    if err != nil || id < 1 {
        app.notFound(w)
        return
    }

    // Use the SnippetModel object'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) {
            app.notFound(w)
        } else {
            app.serverError(w, err)
        }
        return
    }

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

...

Let’s give this a try. Go to your web browser and visit http://localhost:4000/snippet/view?id=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 id=99) to verify that they return a 404 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) {
    app.notFound(w)
} else {
    app.serverError(w, err)
}

Prior to Go 1.13, the idiomatic way to do this was to use the equality operator == to perform the check, like so:

if err == models.ErrNoRecord {
    app.notFound(w)
} else {
    app.serverError(w, err)
}

But, while this code still compiles, it’s now 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.

In contrast, the errors.Is() function works by unwrapping errors as necessary before checking for a match.

Basically, if you are running Go 1.13 or newer, prefer to use errors.Is(). It’s a sensible way to future-proof your code and prevent bugs caused by you — or any packages that your code imports — deciding to wrap errors in the future.

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 your 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) {
    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 nil, ErrNoRecord
        } else {
             return nil, err
        }
    }

    return s, nil
}