Creating a new movie

Let’s begin with the Insert() method of our database model and update this to create a new record in our movies table. Specifically, we want it to execute the following SQL query:

INSERT INTO movies (title, year, runtime, genres) 
VALUES ($1, $2, $3, $4)
RETURNING id, created_at, version

There are a few things about this query which warrant a bit of explanation.

• It uses $N notation to represent placeholder parameters for the data that we want to insert in the movies table. As we explained in Let’s Go, every time that you pass untrusted input data from a client to a SQL database it’s important to use placeholder parameters to help prevent SQL injection attacks, unless you have a very specific reason for not using them.

• We’re only inserting values for title, year, runtime and genres. The remaining columns in the movies table will be filled with system-generated values when the record is inserted — the id will be an auto-incrementing integer, and the created_at and version values will default to the current time and 1 respectively.

• At the end of the query we have a RETURNING clause. This is a PostgreSQL-specific clause (it’s not part of the SQL standard) that you can use to return values from any record that is being manipulated by an INSERT, UPDATE or DELETE statement. In this query we’re using it to return the system-generated id, created_at and version values.

Executing the SQL query

Throughout this project we’ll stick with using Go’s database/sql package to execute our database queries, rather than using a third-party ORM or other tool. We talked about how to use database/sql and its various features, behaviors and gotchas in Let’s Go, so hopefully this will feel familiar to you. Consider it a short refresher!

Normally, you would use Go’s Exec() method to execute an INSERT statement against a database table. But because our SQL query is returning a single row of data (thanks to the RETURNING clause), we’ll need to use the QueryRow() method here instead.

Head back to your internal/data/movies.go file and update it like so:

package data

import (
    "database/sql"
    "time"

    "greenlight.alexedwards.net/internal/validator"

    "github.com/lib/pq" // New import
)

...

// The Insert() method accepts a pointer to a movie struct, which should contain the 
// data for the new record.
func (m MovieModel) Insert(movie *Movie) error {
    // Define a SQL query which inserts a new record in the movies table, and returns
    // the system-generated data.
    query := `
        INSERT INTO movies (title, year, runtime, genres) 
        VALUES ($1, $2, $3, $4)
        RETURNING id, created_at, version`

    // Create an args slice containing the values for the placeholder parameters.
    // Declaring this slice immediately next to our SQL query helps to make it nice
    // and clear *what values are being used where* in the query.
    args := []any{movie.Title, movie.Year, movie.Runtime, pq.Array(movie.Genres)}

    // Use the QueryRow() method to execute the SQL query on our connection pool,
    // passing in the elements of the args slice as variadic arguments and scanning 
    // the system-generated id, created_at and version values into the movie struct.
    return m.DB.QueryRow(query, args...).Scan(&movie.ID, &movie.CreatedAt, &movie.Version)
}

...

That code is nice and succinct, but there are a few important things to mention.

Because the Insert() method signature takes a *Movie pointer as the parameter, when we call Scan() to read in the system-generated data we’re updating the values at the location the parameter points to. Essentially, our Insert() method mutates the Movie struct that we pass to it and adds the system-generated values to it.

The next thing to talk about is the placeholder parameter inputs, which we declare in an args slice like this:

args := []any{movie.Title, movie.Year, movie.Runtime, pq.Array(movie.Genres)}

Storing the inputs in a slice isn’t strictly necessary, but as mentioned in the code comments above it’s a nice pattern that can help the clarity of your code. Personally, I usually do this for SQL queries with more than three placeholder parameters.

Also, notice the final value in the slice? In order to store our movie.Genres value (which is a []string slice) in the database, we need to pass it through the pq.Array() adapter function before executing the SQL query.

Behind the scenes, the pq.Array() adapter takes our []string slice and converts it to a pq.StringArray type. In turn, the pq.StringArray type implements the driver.Valuer and sql.Scanner interfaces necessary to translate our native []string slice to and from a value that our PostgreSQL database can understand and store in a text[] array column.

Hooking it up to our API handler

Now for the exciting part. Let’s hook up the Insert() method to our createMovieHandler so that our POST /v1/movies endpoint works in full. Like so:

package main

...

func (app *application) createMovieHandler(w http.ResponseWriter, r *http.Request) {
    var input struct {
        Title   string       `json:"title"`
        Year    int32        `json:"year"`
        Runtime data.Runtime `json:"runtime"`
        Genres  []string     `json:"genres"`
    }

    err := app.readJSON(w, r, &input)
    if err != nil {
        app.badRequestResponse(w, r, err)
        return
    }

    // Note that the movie variable contains a *pointer* to a Movie struct.
    movie := &data.Movie{
        Title:   input.Title,
        Year:    input.Year,
        Runtime: input.Runtime,
        Genres:  input.Genres,
    }

    v := validator.New()

    if data.ValidateMovie(v, movie); !v.Valid() {
        app.failedValidationResponse(w, r, v.Errors)
        return
    }

    // Call the Insert() method on our movies model, passing in a pointer to the 
    // validated movie struct. This will create a record in the database and update the 
    // movie struct with the system-generated information.
    err = app.models.Movies.Insert(movie)
    if err != nil {
        app.serverErrorResponse(w, r, err)
        return
    }

    // When sending an HTTP response, we want to include a Location header to let the 
    // client know which URL they can find the newly-created resource at. We make an  
    // empty http.Header map and then use the Set() method to add a new Location header,
    // interpolating the system-generated ID for our new movie in the URL.
    headers := make(http.Header)
    headers.Set("Location", fmt.Sprintf("/v1/movies/%d", movie.ID))

    // Write a JSON response with a 201 Created status code, the movie data in the 
    // response body, and the Location header.
    err = app.writeJSON(w, http.StatusCreated, envelope{"movie": movie}, headers)
    if err != nil {
        app.serverErrorResponse(w, r, err)
    }
}

...

OK, let’s try this out!

Restart the API, then open up a second terminal window and make the following request to the POST /v1/movies endpoint:

$ BODY='{"title":"Moana","year":2016,"runtime":"107 mins", "genres":["animation","adventure"]}' 
$ curl -i -d "$BODY" localhost:4000/v1/movies
HTTP/1.1 201 Created
Content-Type: application/json
Location: /v1/movies/1
Date: Wed, 07 Apr 2021 19:21:41 GMT
Content-Length: 156

{
    "movie": {
        "id": 1,
        "title": "Moana",
        "year": 2016,
        "runtime": "107 mins",
        "genres": [
            "animation",
            "adventure"
        ],
        "version": 1
    }
}

That’s looking perfect. We can see that the JSON response contains all the information for the new movie, including the system-generated ID and version numbers. And the response also includes the Location: /v1/movies/1 header, pointing to the URL that will later represent the movie in our system.

Creating additional records

While we’re at it, let’s create a few more records in the system to help us demonstrate different functionality as our build progresses.

If you’re coding along, please run the following commands to create three more movie records in the database:

$ BODY='{"title":"Black Panther","year":2018,"runtime":"134 mins","genres":["action","adventure"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
    "movie": {
        "id": 2,
        "title": "Black Panther",
        "year": 2018,
        "runtime": "134 mins",
        "genres": [
            "action",
            "adventure"
        ],
        "version": 1
    }
}

$ BODY='{"title":"Deadpool","year":2016, "runtime":"108 mins","genres":["action","comedy"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
    "movie": {
        "id": 3,
        "title": "Deadpool",
        "year": 2016,
        "runtime": "108 mins",
        "genres": [
            "action",
            "comedy"
        ],
        "version": 1
    }
}

$ BODY='{"title":"The Breakfast Club","year":1986, "runtime":"96 mins","genres":["drama"]}'
$ curl -d "$BODY" localhost:4000/v1/movies
{
    "movie": {
        "id": 4,
        "title": "The Breakfast Club",
        "year": 1986,
        "runtime": "96 mins",
        "genres": [
            "drama"
        ],
        "version": 1
    }
}

At this point you might also want to take a look at PostgreSQL to confirm that the records have been created properly. You should see that the contents of the movies table now looks similar to this (including the appropriate movie genres in an array).

$ psql $GREENLIGHT_DB_DSN
Password for user greenlight: 
psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

greenlight=> SELECT * FROM movies;
 id |       created_at       |       title        | year | runtime |        genres         | version 
----+------------------------+--------------------+------+---------+-----------------------+---------
  1 | 2021-04-07 21:21:41+02 | Moana              | 2016 |     107 | {animation,adventure} |       1
  2 | 2021-04-07 21:28:28+02 | Black Panther      | 2018 |     134 | {action,adventure}    |       1
  3 | 2021-04-07 21:28:36+02 | Deadpool           | 2016 |     108 | {action,comedy}       |       1
  4 | 2021-04-07 21:28:44+02 | The Breakfast Club | 1986 |      96 | {drama}               |       1
(4 rows)

Additional information

$N notation

A nice feature of the PostgreSQL placeholder parameter $N notation is that you can use the same parameter value in multiple places in your SQL statement. For example, it’s perfectly acceptable to write code like this:

// This SQL statement uses the $1 parameter twice, and the value `123` will be used in 
// both locations where $1 appears.
stmt := "UPDATE foo SET bar = $1 + $2 WHERE bar = $1"
err := db.Exec(stmt, 123, 456)
if err != nil {
    ...
}

Executing multiple statements

Occasionally you might find yourself in the position where you want to execute more than one SQL statement in the same database call, like this:

stmt := `
    UPDATE foo SET bar = true; 
    UPDATE foo SET baz = false;`

err := db.Exec(stmt)
if err != nil {
    ...
}

Having multiple statements in the same call is supported by the pq driver, so long as the statements do not contain any placeholder parameters. If they do contain placeholder parameters, then you’ll receive the following error message at runtime:

pq: cannot insert multiple commands into a prepared statement

To work around this, you will need to either split out the statements into separate database calls, or if that’s not possible, you can create a custom function in PostgreSQL which acts as a wrapper around the multiple SQL statements that you want to run.