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

When deployed, your application uses the Cloud SQL Proxy that is built in to the App Engine environment to communicate with your Cloud SQL instance. However, to test your application locally, you must install and use a local copy of the Cloud SQL Proxy in your development environment.

Learn more about the Cloud SQL Proxy.

To perform basic administrative tasks on your Cloud SQL instance, you can use the MySQL Client.

Install the SQL proxy

Download and install the Cloud SQL Proxy. The Cloud SQL Proxy is used to connect to your Cloud SQL instance when running locally.

Linux 64-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Linux 32-bit

  1. Download the proxy:
    wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

OS X 64-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

OS X 32-bit

  1. Download the proxy:
    curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
    
  2. Make the proxy executable:
    chmod +x cloud_sql_proxy
    

Windows 64-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe and select "Save link as..." to download the proxy, renaming it to cloud_sql_proxy.exe.

Windows 32-bit

Right-click https://dl.google.com/cloudsql/cloud_sql_proxy_x86.exe and select "Save link as..." to download the proxy, renaming it to cloud_sql_proxy.exe.
If your operating system is not included here, you can also compile the proxy from source.

Create a Cloud SQL instance

  1. Create a Cloud SQL for MySQL Second Generation instance. Name the instance library or similar. It can take a few minutes for the instance to be ready. After the instance is ready, it should be visible in the instances list.
  2. Now use the Cloud SDK from command line to run the following command. Copy the value shown for connectionName for the next step.
    gcloud sql instances describe [YOUR_INSTANCE_NAME]

    The connectionName value is in the format [PROJECT_NAME]:[REGION_NAME]:[INSTANCE_NAME].

Initialize your Cloud SQL instance

  1. Start the Cloud SQL Proxy using the connectionName from the previous step.

    Linux/Mac OS X

    ./cloud_sql_proxy -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:3306

    Windows

    cloud_sql_proxy.exe -instances="[YOUR_INSTANCE_CONNECTION_NAME]"=tcp:3306

    Replace [YOUR_INSTANCE_CONNECTION_NAME] with the value of connectionName that you recorded in the previous step.

    This step establishes a connection from your local computer to your Cloud SQL instance for local testing purposes. Keep the Cloud SQL Proxy running the entire time you test your application locally.

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 instance connection name into the cloudSQLConfig struct.

    You can find your instance connection name in the Google Cloud Platform Console in the details for your Cloud SQL instance. Instance connection names use the format project:region:cloudsql-instance.

    For example, suppose your instance connection name is myproject:us-central1:library, your username is goapp, and your password is secret123. Then the Cloud SQL portion of your config.go file would look like this:

    DB, err = configureCloudSQL(cloudSQLConfig{
        Username: "goapp",
        Password: "secret123",
        Instance: "myproject:us-central1:library",
    })
    
  6. Save and close config.go.

  7. Open app/app.yaml for editing.

  8. Uncomment the line that starts with cloud_sql_instances.

  9. Replace INSTANCE_CONNECTION_NAME with your instance connection string. This should match the value in the cloudSQLConfig struct in config.go.

  10. Save and close app/app.yaml.

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...