Executing SQL statements

Now let’s update the SnippetModel.Insert() method — which we’ve just made — so that it creates a new record in our snippets table and then returns the integer id for the new record.

To do this we’ll want to execute the following SQL query on our database:

INSERT INTO snippets (title, content, created, expires)
VALUES(?, ?, UTC_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(), INTERVAL ? DAY))

Notice how in this query we’re using the ? character to indicate placeholder parameters for the data that we want to insert in the database? Because the data we’ll be using will ultimately be untrusted user input from a form, it’s good practice to use placeholder parameters instead of interpolating data in the SQL query.

Executing the query

Go provides three different methods for executing database queries:

• DB.Query() is used for SELECT queries which return multiple rows.
• DB.QueryRow() is used for SELECT queries which return a single row.
• DB.Exec() is used for statements which don’t return rows (like INSERT and DELETE).

So, in our case, the most appropriate tool for the job is DB.Exec(). Let’s jump in the deep end and demonstrate how to use this in our SnippetModel.Insert() method. We’ll discuss the details afterwards.

Open your internal/models/snippets.go file and update it like so:

package models

...

type SnippetModel struct {
    DB *sql.DB
}

func (m *SnippetModel) Insert(title string, content string, expires int) (int, error) {
    // Write the SQL statement we want to execute. I've split it over two lines
    // for readability (which is why it's surrounded with backquotes instead
    // of normal double quotes).
    stmt := `INSERT INTO snippets (title, content, created, expires)
    VALUES(?, ?, UTC_TIMESTAMP(), DATE_ADD(UTC_TIMESTAMP(), INTERVAL ? DAY))`

    // Use the Exec() method on the embedded connection pool to execute the
    // statement. The first parameter is the SQL statement, followed by the
    // values for the placeholder parameters: title, content and expiry in
    // that order. This method returns a sql.Result type, which contains some
    // basic information about what happened when the statement was executed.
    result, err := m.DB.Exec(stmt, title, content, expires)
    if err != nil {
        return 0, err
    }

    // Use the LastInsertId() method on the result to get the ID of our
    // newly inserted record in the snippets table.
    id, err := result.LastInsertId()
    if err != nil {
        return 0, err
    }

    // The ID returned has the type int64, so we convert it to an int type
    // before returning.
    return int(id), nil
}

...

Let’s quickly discuss the sql.Result type returned by DB.Exec(). This provides two methods:

• LastInsertId() — which returns the integer (an int64) generated by the database in response to a command. Typically this will be from an “auto increment” column when inserting a new row, which is exactly what’s happening in our case.

• RowsAffected() — which returns the number of rows (as an int64) affected by the statement.

Also, it is perfectly acceptable (and common) to ignore the sql.Result return value if you don’t need it. Like so:

_, err := m.DB.Exec("INSERT INTO ...", ...)

Using the model in our handlers

Let’s bring this back to something more concrete and demonstrate how to call this new code from our handlers. Open your cmd/web/handlers.go file and update the snippetCreatePost handler like so:

package main

...

func (app *application) snippetCreatePost(w http.ResponseWriter, r *http.Request) {
    // Create some variables holding dummy data. We'll remove these later on
    // during the build.
    title := "O snail"
    content := "O snail\nClimb Mount Fuji,\nBut slowly, slowly!\n\n– Kobayashi Issa"
    expires := 7

    // Pass the data to the SnippetModel.Insert() method, receiving the
    // ID of the new record back.
    id, err := app.snippets.Insert(title, content, expires)
    if err != nil {
        app.serverError(w, r, err)
        return
    }

    // Redirect the user to the relevant page for the snippet.
    http.Redirect(w, r, fmt.Sprintf("/snippet/view/%d", id), http.StatusSeeOther)
}

Start up the application, then open a second terminal window and use curl to make a POST /snippet/create request, like so (note that the -L flag instructs curl to automatically follow redirects):

$ curl -iL -d "" http://localhost:4000/snippet/create
HTTP/1.1 303 See Other
Location: /snippet/view/4
Date: Wed, 18 Mar 2024 11:29:23 GMT
Content-Length: 0

HTTP/1.1 200 OK
Date: Wed, 18 Mar 2024 11:29:23 GMT
Content-Length: 39
Content-Type: text/plain; charset=utf-8

Display a specific snippet with ID 4...

So this is working pretty nicely. We’ve just sent a HTTP request which triggered our snippetCreatePost handler, which in turn called our SnippetModel.Insert() method. This inserted a new record in the database and returned the ID of this new record. Our handler then issued a redirect to another URL with the ID interpolated.

Feel free to take a look in the snippets table of your MySQL database. You should see the new record with an ID of 4 similar to this:

mysql> SELECT id, title, expires FROM snippets;
+----+------------------------+---------------------+
| id | title                  | expires             |
+----+------------------------+---------------------+
|  1 | An old silent pond     | 2025-03-18 10:00:26 |
|  2 | Over the wintry forest | 2025-03-18 10:00:26 |
|  3 | First autumn morning   | 2024-03-25 10:00:26 |
|  4 | O snail                | 2024-03-25 10:13:04 |
+----+------------------------+---------------------+
4 rows in set (0.00 sec)

Additional information

Placeholder parameters

In the code above we constructed our SQL statement using placeholder parameters, where ? acted as a placeholder for the data we want to insert.

The reason for using placeholder parameters to construct our query (rather than string interpolation) is to help avoid SQL injection attacks from any untrusted user-provided input.

Behind the scenes, the DB.Exec() method works in three steps:

1. It creates a new prepared statement on the database using the provided SQL statement. The database parses and compiles the statement, then stores it ready for execution.

2. In a second separate step, DB.Exec() passes the parameter values to the database. The database then executes the prepared statement using these parameters. Because the parameters are transmitted later, after the statement has been compiled, the database treats them as pure data. They can’t change the intent of the statement. So long as the original statement is not derived from untrusted data, injection cannot occur.

3. It then closes (or deallocates) the prepared statement on the database.

The placeholder parameter syntax differs depending on your database. MySQL, SQL Server and SQLite use the ? notation, but PostgreSQL uses the $N notation. For example, if you were using PostgreSQL instead you would write:

_, err := m.DB.Exec("INSERT INTO ... VALUES ($1, $2, $3)", ...)