Paginating lists

If you have an endpoint which returns a list with hundreds or thousands of records, then for performance or usability reasons you might want to implement some form of pagination on the endpoint — so that it only returns a subset of the records in a single HTTP response.

To help demonstrate how to do this, in this chapter we’re going to update the GET /v1/movies endpoint so that it supports the concept of ‘pages’ and a client can request a specific ‘page’ of the movies list by using our page and page_size query string parameters. For example:

// Return the 5 records on page 1 (records 1-5 in the dataset)
/v1/movies?page=1&page_size=5

// Return the next 5 records on page 2 (records 6-10 in the dataset)
/v1/movies?page=2&page_size=5

// Return the next 5 records on page 3 (records 11-15 in the dataset)
/v1/movies?page=3&page_size=5

Basically, changing the page_size parameter will alter the number of movies shown on each ‘page’, and increasing the page parameter by one will show you the next ‘page’ of movies in the list.

The LIMIT and OFFSET clauses

Behind the scenes, the simplest way to support this style of pagination is by adding LIMIT and OFFSET clauses to our SQL query.

The LIMIT clause allows you to set the maximum number of records that a SQL query should return, and OFFSET allows you to ‘skip’ a specific number of rows before starting to return records from the query.

Within our application, we’ll just need to translate the page and page_size values provided by the client to the appropriate LIMIT and OFFSET values for our SQL query. The math is pretty straightforward:

LIMIT = page_size
OFFSET = (page - 1) * page_size

Or to give a concrete example, if a client makes the following request:

/v1/movies?page_size=5&page=3

We would need to ‘translate’ this into the following SQL query:

SELECT id, created_at, title, year, runtime, genres, version
FROM movies
WHERE (to_tsvector('simple', title) @@ plainto_tsquery('simple', $1) OR $1 = '') 
AND (genres @> $2 OR $2 = '{}')     
ORDER BY %s %s, id ASC
LIMIT 5 OFFSET 10

Let’s start by adding some helper methods to our Filters struct for calculating the appropriate LIMIT and OFFSET values.

If you’re following along, go ahead and update the internal/data/filters.go file like so:

package data

...

type Filters struct {
    Page         int
    PageSize     int
    Sort         string
    SortSafelist []string
}

...

func (f Filters) limit() int {
    return f.PageSize
}

func (f Filters) offset() int {
    return (f.Page - 1) * f.PageSize
}

...

Updating the database model

As the final stage in this process, we need to update our database model’s GetAll() method to add the appropriate LIMIT and OFFSET clauses to the SQL query.

package data

...

func (m MovieModel) GetAll(title string, genres []string, filters Filters) ([]*Movie, error) {
    // Update the SQL query to include the LIMIT and OFFSET clauses with placeholder
    // parameter values.
    query := fmt.Sprintf(`
        SELECT id, created_at, title, year, runtime, genres, version
        FROM movies
        WHERE (to_tsvector('simple', title) @@ plainto_tsquery('simple', $1) OR $1 = '') 
        AND (genres @> $2 OR $2 = '{}')     
        ORDER BY %s %s, id ASC
        LIMIT $3 OFFSET $4`, filters.sortColumn(), filters.sortDirection())

    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    // As our SQL query now has quite a few placeholder parameters, let's collect the
    // values for the placeholders in a slice. Notice here how we call the limit() and
    // offset() methods on the Filters struct to get the appropriate values for the
    // LIMIT and OFFSET clauses.
    args := []any{title, pq.Array(genres), filters.limit(), filters.offset()}

    // And then pass the args slice to QueryContext() as a variadic parameter.
    rows, err := m.DB.QueryContext(ctx, query, args...)
    if err != nil {
        return nil, err
    }

    // The remaining code doesn't need to change.
    ...
}

Once that’s done we should be ready to try this out.

Restart the server, then go ahead and make the following request with a page_size=2 parameter:

$ curl "localhost:4000/v1/movies?page_size=2"
{
    "movies": [
        {
            "id": 1,
            "title": "Moana",
            "year": 2016,
            "runtime": "107 mins",
            "genres": [
                "animation",
                "adventure"
            ],
            "version": 1
        },
        {
            "id": 2,
            "title": "Black Panther",
            "year": 2018,
            "runtime": "134 mins",
            "genres": [
                "sci-fi",
                "action",
                "adventure"
            ],
            "version": 2
        }
    ]
}

That’s looking good. Our endpoint is now returning the first two movie records only from our database (using the default sort order of ascending movie ID).

Next, try requesting the second page of results. If you’ve been following along, this page should include the one remaining record in our system, like so:

# IMPORTANT: This URL must be surrounded with double quotes to work correctly.
$ curl "localhost:4000/v1/movies?page_size=2&page=2"
{
    "movies": [
        {
            "id": 4,
            "title": "The Breakfast Club",
            "year": 1985,
            "runtime": "97 mins",
            "genres": [
                "comedy"
            ],
            "version": 5
        }
    ]
}

If you try to request the third page, you should get an empty JSON array in the response like so:

$ curl "localhost:4000/v1/movies?page_size=2&page=3"
{
    "movies": []
}