Using Cloud SQL with Go

This part of the Go Bookshelf tutorial shows how the sample app stores its persistent data in Google Cloud SQL.

This page is part of a multi-page tutorial. To start from the beginning and see instructions for setting up, go to Go Bookshelf App.

Creating a Cloud SQL instance

  1. Create a Cloud SQL First Generation instance. For Instance ID, enter library. It can take a few minutes for the instance to be ready. When the instance is ready, it is visible in the instances list.

  2. In the instances list, click your Cloud SQL instance, and then click Access Control.

  3. In the IP address subsection, click Request an IPv4 address to enable remote access to the Cloud SQL instance through an IPv4 address.

  4. In the Authorization subsection, under Authorized Networks, click the add (+) button. In the Network field, enter 0.0.0.0/0. This value allows access by all IP addresses. Click Done, and then click Save.

  5. In the Users subsection, click Create user account. For User name, enter goapp. For Password, enter a strong password of your choice. Click Create.

Configuring settings

  1. Go to the directory that contains the sample code:

    Linux/Mac OS X

    cd $GOPATH/src/github.com/GoogleCloudPlatform/golang-samples/getting-started/bookshelf
    

    Windows

    cd %GOPATH%\src\github.com\GoogleCloudPlatform\golang-samples\getting-started\bookshelf
    

  2. Open config.go for editing.

  3. Optional: Comment out DB = newMemoryDB().

  4. Uncomment the code portion of the section labeled [START cloudsql].

  5. Enter your Cloud SQL username, password, and host into the MySQLConfig struct.

    For example, suppose your IPv4 address is 173.194.230.44, your username is goapp, and your password is secret123. Then the Cloud SQL portion of your config.go file would look like this:

    // [START cloudsql]
    // To use MySQL, uncomment the following lines, and update the username, password and host.
    DB, err = newMySQLDB(MySQLConfig{
        Username: "goapp",
        Password: "secret123",
        Host:     "173.194.230.44",
        Port:     3306,
    })
    // [END cloudsql]
    
  6. Save and close config.go.

Running the app on your local machine

  1. Run the app to start a local web server:

    cd app
    go run app.go auth.go template.go
    
  2. In your web browser, enter this address:

    http://localhost:8080

Now you can browse the app's web pages and add, edit, and delete books.

Press Control+C to exit the local web server.

Deploying the app to the App Engine flexible environment

  1. In the app directory, enter this command to deploy the sample:

    gcloud app deploy
    
  2. In your web browser, enter this address. Replace [YOUR_PROJECT_ID] with your project ID:

    https://[YOUR_PROJECT_ID].appspot.com
    

If you update your app, you can deploy the updated version by entering the same command you used to deploy the app the first time. The new deployment creates a new version of your app and promotes it to the default version. The older versions of your app remain, as do their associated VM instances. Be aware that all of these app versions and VM instances are billable resources.

You can reduce costs by deleting the non-default versions of your app.

To delete an app version:

  1. In the Cloud Platform Console, go to the App Engine Versions page.

    Go to the Versions page

  2. Click the checkbox next to the non-default app version you want to delete.
  3. Click the Delete button at the top of the page to delete the app version.

For complete information about cleaning up billable resources, see the Cleaning up section in the final step of this tutorial.

Application structure

This diagram shows the application's components and how they fit together.

Bookshelf app structure

Understanding the code

This section walks you through the application code and explains how it works.

Handling user submissions with forms

When a user clicks Add Book, the browser navigates to /books/add, and the addFormHandler function in app/app.go renders a form where the user can add a book:

r.Methods("GET").Path("/books/add").
	Handler(appHandler(addFormHandler))
// addFormHandler displays a form that captures details of a new book to add to
// the database.
func addFormHandler(w http.ResponseWriter, r *http.Request) *appError {
	return editTmpl.Execute(w, r, nil)
}

Image of add/edit Form

The form includes text input fields for Title, Author, Date Published and Description and it also includes some others, which are used in other sections of the tutorial. Notice the variable names embedded in the value attributes: for example, {{.Title}}. These are populated from the Go standard text/template library.

<form method="post" enctype="multipart/form-data" action="/books{{if .}}/{{.ID}}{{end}}">
  <div class="form-group">
    <label for="title">Title</label>
    <input class="form-control" name="title" id="title" value="{{.Title}}">
  </div>
  <div class="form-group">
    <label for="author">Author</label>
    <input class="form-control" name="author" id="author" value="{{.Author}}">
  </div>
  <div class="form-group">
    <label for="publishedDate">Date Published</label>
    <input class="form-control" name="publishedDate" id="publishedDate" value="{{.PublishedDate}}">
  </div>
  <div class="form-group">
    <label for="description">Description</label>
    <input class="form-control" name="description" id="description" value="{{.Description}}">
  </div>
  <div class="form-group">
    <label for="image">Cover Image</label>
    <input class="form-control" name="image" id="image" type="file">
  </div>
  <button class="btn btn-success">Save</button>
  <input type="hidden" name="imageURL" value="{{.ImageURL}}">
  <input type="hidden" name="createdBy" value="{{.CreatedBy}}">
  <input type="hidden" name="createdByID" value="{{.CreatedByID}}">
</form>

Handling form submissions

When a user fills in the form and clicks Save, the following code handles the form's HTTP POST /books action. The code inserts the new book into the database by calling the bookshelf.DB.AddBook() function.

r.Methods("POST").Path("/books").
	Handler(appHandler(createHandler))
// createHandler adds a book to the database.
func createHandler(w http.ResponseWriter, r *http.Request) *appError {
	book, err := bookFromForm(r)
	if err != nil {
		return appErrorf(err, "could not parse book from form: %v", err)
	}
	id, err := bookshelf.DB.AddBook(book)
	if err != nil {
		return appErrorf(err, "could not save book: %v", err)
	}
	go publishUpdate(id)
	http.Redirect(w, r, fmt.Sprintf("/books/%d", id), http.StatusFound)
	return nil
}

The AddBook function, defined in db_mysql.go, performs a SQL INSERT operation to add the user's submitted data to the CloudSQL database:

const insertStatement = `
  INSERT INTO books (
    title, author, publishedDate, imageUrl, description, createdBy, createdById
  ) VALUES (?, ?, ?, ?, ?, ?, ?)`

// AddBook saves a given book, assigning it a new ID.
func (db *mysqlDB) AddBook(b *Book) (id int64, err error) {
	r, err := execAffectingOneRow(db.insert, b.Title, b.Author, b.PublishedDate,
		b.ImageURL, b.Description, b.CreatedBy, b.CreatedByID)
	if err != nil {
		return 0, err
	}

	lastInsertID, err := r.LastInsertId()
	if err != nil {
		return 0, fmt.Errorf("mysql: could not get last insert ID: %v", err)
	}
	return lastInsertID, nil
}
// execAffectingOneRow executes a given statement, expecting one row to be affected.
func execAffectingOneRow(stmt *sql.Stmt, args ...interface{}) (sql.Result, error) {
	r, err := stmt.Exec(args...)
	if err != nil {
		return r, fmt.Errorf("mysql: could not execute statement: %v", err)
	}
	rowsAffected, err := r.RowsAffected()
	if err != nil {
		return r, fmt.Errorf("mysql: could not get rows affected: %v", err)
	} else if rowsAffected != 1 {
		return r, fmt.Errorf("mysql: expected 1 row affected, got %d", rowsAffected)
	}
	return r, nil
}

Each SQL statement is prepared when the application starts. For example, this code in the newMySQLDB function prepares the SQL statement for listing books, and stores prepared statement in the db object:


// Prepared statements. The actual SQL queries are in the code near the
// relevant method (e.g. addBook).
if db.list, err = conn.Prepare(listStatement); err != nil {
	return nil, fmt.Errorf("mysql: prepare list: %v", err)
}

When the user edits a book's information after it has been submitted, the UpdateBook method performs a SQL UPDATE operation:

const updateStatement = `
  UPDATE books
  SET title=?, author=?, publishedDate=?, imageUrl=?, description=?,
      createdBy=?, createdById=?
  WHERE id = ?`

// UpdateBook updates the entry for a given book.
func (db *mysqlDB) UpdateBook(b *Book) error {
	if b.ID == 0 {
		return errors.New("memorydb: book with unassigned ID passed into updateBook")
	}

	_, err := execAffectingOneRow(db.update, b.Title, b.Author, b.PublishedDate,
		b.ImageURL, b.Description, b.CreatedBy, b.CreatedByID, b.ID)
	return err
}

When the user clicks Books, the browser navigates to the /books page, and the ListBooks method displays all the books in the Cloud SQL database:

const listStatement = `SELECT * FROM books ORDER BY title`

// ListBooks returns a list of books, ordered by title.
func (db *mysqlDB) ListBooks() ([]*Book, error) {
	rows, err := db.list.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var books []*Book
	for rows.Next() {
		book, err := scanBook(rows)
		if err != nil {
			return nil, fmt.Errorf("mysql: could not read row: %v", err)
		}

		books = append(books, book)
	}

	return books, nil
}

Send feedback about...