Using Cloud SQL with Ruby

This part of the 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 Ruby 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
    

macOS 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
    

macOS 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 [YOUR_INSTANCE_NAME] 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. Before using ./cloud_sql_proxy for the first time, you must create a directory where the proxy sockets will live:

    sudo mkdir /cloudsql
    sudo chmod 0777 /cloudsql
    
  2. Start the Cloud SQL Proxy using the connectionName from the previous step.

    ./cloud_sql_proxy -instances="[YOUR_INSTANCE_CONNECTION_NAME]" -dir=/cloudsql
    

    Replace [YOUR_INSTANCE_CONNECTION_NAME] with the Instance Connection Name of your Cloud SQL instance.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.

  3. Next you will need to create a new Cloud SQL user with an associated database.

    CONSOLE

    1. Create a new database using the GCP Console for your Cloud SQL instance [YOUR_INSTANCE_NAME]. For example, you can use the name [MYSQL_DATABASE].
    2. Create a new user using the GCP Console for your Cloud SQL instance [YOUR_INSTANCE_NAME].

    MYSQL CLIENT

    1. In a separate command-line tab, use the MySQL client or similar program to connect to your instance. When prompted, use the root password you configured.
      mysql --socket "/cloudsql/[YOUR_INSTANCE_CONNECTION_NAME]" --user root --password
      
    2. Create the required databases, users, and access permissions in your Cloud SQL database using the commands below. Replace [MYSQL_USER] and [MYSQL_PASSWORD] with your username and password.
      CREATE DATABASE [MYSQL_DATABASE];
      CREATE USER '[MYSQL_USER]' IDENTIFIED BY '[MYSQL_PASSWORD]';
      GRANT ALL ON . TO '[MYSQL_USER]';
      

Configuring settings

  1. Go to the getting-started-ruby/2-cloud-sql directory, and copy the database.example.yml file:

     cp config/database.example.yml config/database.yml
    
  2. To configure your database, edit config/database.yml.

    mysql_settings: &mysql_settings
      adapter: mysql2
      encoding: utf8
      pool: 5
      username: [MYSQL_USER]
      password: [MYSQL_PASS]
      database: [MYSQL_DATABASE]
      socket: /cloudsql/[YOUR_INSTANCE_CONNECTION_NAME]

    • Replace [MYSQL_USER] and [MYSQL_PASS] with your Cloud SQL instance username and password that you created previously.

    • Replace [MYSQL_DATABASE] with the name of the database that you created previously.

    • Replace [YOUR_INSTANCE_CONNECTION_NAME] with the Instance Connection Name of your Cloud SQL instance.

  3. To prepare App Engine with Cloud SQL, edit app.yaml.

    beta_settings:
      # The connection name of your instance on its Overview page in the Google
      # Cloud Platform Console, or use `[YOUR_PROJECT_ID]:[YOUR_REGION]:[YOUR_INSTANCE_NAME]`
      cloud_sql_instances: [YOUR_INSTANCE_CONNECTION_NAME]

    • Replace [YOUR_INSTANCE_CONNECTION_NAME] with the Instance Connection Name of your Cloud SQL instance.

Installing dependencies

In the 2-cloud-sql directory, and enter this command:

bundle install

Creating a database and tables

  1. Create the database and run migrations to create the required table:

    bundle exec rake db:migrate
    

    When this step completes, you will see a message indicating that the CreateBooks migration succeeded:

    == 20150706182833 CreateBooks: migrating ===============================
    -- create_table(:books)
    -> 0.4526s
    == 20150706182833 CreateBooks: migrated (0.4528s) ======================
    

    The CreateBooks migration creates a new books table with columns to store the book title, author, publication date, and description:

    class CreateBooks < ActiveRecord::Migration
      def change
        create_table :books do |t|
          t.string :title, required: true
          t.string :author
          t.date :published_on
          t.text :description
          t.timestamps null: false
        end
      end
    end
    

Running the app on your local machine

  1. Start a local web server:

    bundle exec rails server
    
  2. In your web browser, enter this address:

    http://localhost:3000

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. Compile JavaScript assets for production:

    RAILS_ENV=production bundle exec rake assets:precompile
    
  2. Deploy the sample app:

    gcloud app deploy
    
  3. 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 GCP 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 app's components and how they fit together.

Bookshelf app deployment process and structure

Understanding the code

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

Listing books

When you visit the app's home page, you are routed to the index action of the BooksController class. This is configured in config/routes.rb:

Rails.application.routes.draw do

  # Route root of application to BooksController#index action
  root "books#index"

  # Restful routes for BooksController
  resources :books

end

The BookController#index action fetches a list of books from the Cloud SQL database. The app lists at most 10 books on each web page, so the fetched list depends on which page the user is viewing. For example, suppose there are 26 books in the database, and the user is on the third page (/?page=3). In that case, params[:page] is equal to 3, which is assigned to the page_number variable. Then a list of 6 books, starting at offset 20, is fetched and assigned to @books.

class BooksController < ApplicationController

  PER_PAGE = 10

  def index
    page_number = params[:page] ? params[:page].to_i : 1
    book_offset = PER_PAGE * (page_number - 1)
    @books      = Book.limit(PER_PAGE).offset(book_offset)
    @next_page  = page_number + 1 if @books.count == PER_PAGE
  end

The Book class is a simple ActiveRecord model that represents an individual book in the books table:

class Book < ActiveRecord::Base
  validates :title, presence: true
end

In routes.rb, the resources :books call configures RESTful routes for creating, reading, updating, and deleting books that are routed to the corresponding actions in the BooksController class.

After BooksController.index fetches a list of books, the embedded Ruby code in books/index.html.erb renders the list:

<% @books.each do |book| %>
  <div class="media">
    <%= link_to book_path(book) do %>
      <div class="media-body">
        <h4><%= book.title %></h4>
        <p><%= book.author %></p>
      </div>
    <% end %>
  </div>
<% end %>

<% if @next_page %>
  <nav>
    <ul class="pager">
      <li><%= link_to "More", books_path(page: @next_page) %></li>
    </ul>
  </nav>
<% end %>

Displaying book details

When you click an individual book on the web page, the BookController#show action fetches the book, specified by its ID, from the books table:

def show
  @book = Book.find params[:id]
end

Then the embedded Ruby code in show.html.erb displays the book's details:

<div class="media">
  <div class="media-body">
    <h4><%= @book.title %> | &nbsp; <small><%= @book.published_on %></small></h4>
    <h5>By <%= @book.author || "unknown" %></h5>
    <p><%= @book.description %></p>
  </div>
</div>

Creating books

When you click Add book on the web page, the BooksController#new action creates a new book. The embedded Ruby code in new.html.erb points to _form.html.erb, which displays the form for adding a new book:

<%= form_for @book do |f| %>
  <div class="form-group">
    <%= f.label :title %>
    <%= f.text_field :title %>
  </div>
  <div class="form-group">
    <%= f.label :author %>
    <%= f.text_field :author %>
  </div>
  <div class="form-group">
    <%= f.label :published_on, "Date Published" %>
    <%= f.date_field :published_on %>
  </div>
  <div class="form-group">
    <%= f.label :description %>
    <%= f.text_area :description %>
  </div>
  <button class="btn btn-success" type="submit">Save</button>
<% end %>

When you submit the form, the BooksController#create action saves the book in the database. If the new book is saved successfully, the book's page is displayed. Otherwise, the form is displayed again along with error messages. The book_params method uses strong parameters to specify which form fields are allowed. In this case, only book title, author, publication date, and description are allowed:

def create
  @book = Book.new book_params

  if @book.save
    flash[:success] = "Added Book"
    redirect_to book_path(@book)
  else
    render :new
  end
end

private

def book_params
  params.require(:book).permit(:title, :author, :published_on, :description)
end

Editing books

When you click Edit book on the web page, the BooksController#update action fetches the book from the database. The embedded Ruby code in edit.html.erb points to _form.html.erb, which displays the form for editing the book:

def update
  @book = Book.find params[:id]

  if @book.update book_params
    flash[:success] = "Updated Book"
    redirect_to book_path(@book)
  else
    render :edit
  end
end

When you submit the form, the BooksController#update action saves the book in the database. If the new book is saved successfully, the book's page is displayed. Otherwise, the form is displayed again along with error messages.

Deleting books

When you click Delete Book on the web page, the BooksController#destroy action deletes the book from the database and then displays the list of books:

def destroy
  @book = Book.find params[:id]
  @book.destroy
  redirect_to books_path
end
Was this page helpful? Let us know how we did:

Send feedback about...