Using Cloud SQL for MySQL with Rails 5

It's easy to get started developing Ruby on Rails apps that run on the App Engine flexible environment. Because the apps you create run on the same infrastructure that powers all of Google's products, you can be confident that they can scale to serve all of your users, whether there are a few or millions of them.

This tutorial assumes you are familiar with Rails web development. It walks you through setting up Cloud SQLfor MySQL with a new Rails app. You can also use this tutorial as a reference for configuring existing Rails apps to use Cloud SQL for MySQL.

This tutorial requires Ruby 2.3.4 or newer.

Before you begin

Check off each step as you complete it.

  1. check_box_outline_blank check_box Create a project in the Google Cloud Platform Console.
    If you haven't already created a project, create one now. Projects enable you to manage all Google Cloud Platform resources for your app, including deployment, access control, billing, and services.
    1. Open the GCP Console.
    2. In the drop-down menu at the top, select Create a project.
    3. Click Show advanced options. Under App Engine location, select a United States location.
    4. Give your project a name.
    5. Make a note of the project ID, which might be different from the project name. The project ID is used in commands and in configurations.
  2. check_box_outline_blank check_box Enable billing for your project, and sign up for a free trial.

    If you haven't already enabled billing for your project, enable billing now, and sign up for a free trial. Enabling billing allows the app to consume billable resources such as running instances and storing data. During your free trial period, you won't be billed for any services.

  3. check_box_outline_blank check_box Install the Cloud SDK.

    If you haven't already installed the Cloud SDK, install and initialize the Cloud SDK now. The Cloud SDK contains tools and libraries that enable you to create and manage resources on GCP.

  4. check_box_outline_blank check_box Enable APIs for your project.

    This takes you to the GCP Console and automatically enables the APIs used by this tutorial. The APIs used are: Cloud SQL Administration.

Preparing Cloud SQL for a MySQL instance

To set up Cloud SQL for a MySQL instance for this tutorial:

  1. Create a Second Generation instance. In this tutorial the name for the instance is rails-cloudsql-instance.

  2. Create a database in the instance. In this tutorial the name for the production database is cat_list_production.

  3. Set a root user password for the instance.

Setting up your local environment for Rails

To set up your local environment for this tutorial:

  1. Install Ruby version 2.3.4 or newer.

  2. Install the Rails 5 gem.

  3. Install the Bundler gem.

For additional information on installing Rails and its dependencies, see the official Getting started with Rails guide.

After you complete the prerequisites, you can create and deploy a Rails app using Cloud SQL for MySQL. The following sections guide you through configuring, connecting to Cloud SQL for MySQL, and deploying an app.

Create a new app to list cats

  1. Run the rails new command to create a new Rails app. This app stores a list of cats in Cloud SQL for MySQL.

    rails new cat_sample_app
    
  2. Go to the directory that contains the generated Rails app.

    cd cat_sample_app
    

Run the application locally

To run the new Rails application on your local computer:

  1. Install dependencies by using Bundler:

    bundle install
    
  2. Start a local web server:

    bundle exec bin/rails server
    
  3. In a web browser, go to http://localhost:3000/

A Yay! You're on Rails! message from the app displays on the page.

Screenshot of new Rails app running

Generate scaffolding for a list of cats

Generate scaffolding for a resource named Cat that is used to form a list of cats with their name and age.

  1. Generate the scaffolding.

    bundle exec rails generate scaffold Cat name:string age:integer
    

    The command generates a model, controller, and views for the Cat resource.

    invoke  active_record
    create    db/migrate/20170804210744_create_cats.rb
    create    app/models/cat.rb
    invoke    rspec
    create      spec/models/cat_spec.rb
    invoke  resource_route
    route    resources :cats
    invoke  scaffold_controller
    create    app/controllers/cats_controller.rb
    invoke    erb
    create      app/views/cats
    create      app/views/cats/index.html.erb
    create      app/views/cats/edit.html.erb
    create      app/views/cats/show.html.erb
    create      app/views/cats/new.html.erb
    create      app/views/cats/_form.html.erb
    invoke    jbuilder
    create      app/views/cats/index.json.jbuilder
    create      app/views/cats/show.json.jbuilder
    create      app/views/cats/_cat.json.jbuilder
    invoke  assets
    invoke    js
    create      app/assets/javascripts/cats.js
    invoke    scss
    create      app/assets/stylesheets/cats.scss
    invoke  scss
    create    app/assets/stylesheets/scaffolds.scss
    
  2. Open the config/routes.rb file to see the following generated content.

    Rails.application.routes.draw do
      resources :cats
      get 'cats/index'
      # For details on the DSL available within this file, see http://guides.rubyonrails.org/routing.html
    
    end
  3. Add root 'cats#index' to the file.

    Rails.application.routes.draw do
      resources :cats
      get 'cats/index'
    
      # For details on the DSL available within this file, see http://guides.rubyonrails.org/routing.html
      root 'cats#index'
    end
  4. Save the file and close it.

  5. Test the Rails app as instructed before.

Using Cloud SQL for MySQL with App Engine

Cloud SQL for MySQL is a fully managed database service to set up, maintain, manage, and administer your relational MySQL databases in Google Cloud Platform (GCP). You can use Cloud SQL in a Rails app like any other relational database.

Set up Cloud SQL for MySQL

To begin using Cloud SQL with your Rails app in production:

  1. Add the mysql2 and appengine gems to the Gemfile file.

    bundle add mysql2
    bundle add appengine
    

    The Rails Gemfile contains the following additional gem entries:

    # Added at 2017-08-07 11:54:06 -0700 by USER:
    gem "mysql2", "~> 0.4.8"
    
    # Added at 2017-08-07 11:54:12 -0700 by USER:
    gem "appengine", "~> 0.4.1"
  2. To configure the Rails app to connect to Cloud SQL, open the config/database.yml file. The following boilerplate database settings for SQLite are displayed:

    # SQLite version 3.x
    #   gem install sqlite3
    #
    #   Ensure the SQLite 3 gem is defined in your Gemfile
    #   gem 'sqlite3'
    #
    default: &default
      adapter: sqlite3
      pool: 5
      timeout: 5000
    
    development:
      <<: *default
      database: db/development.sqlite3
    
    # Warning: The database defined as "test" will be erased and
    # re-generated from your development database when you run "rake".
    # Do not set this db to the same as development or production.
    test:
      <<: *default
      database: db/test.sqlite3
    
    production:
      <<: *default
      database: db/production.sqlite3
  3. Configure the Cloud SQL instance connection name for the App Engine production environment.

    1. Retrieve the instance connection name.

      gcloud sql instances describe rails-cloudsql-instance
      
    2. Copy the value next to connectionName.

  4. Modify the database.yml production database configuration to the following:

    production:
      adapter: mysql2
      pool: 5
      timeout: 5000
      username: "[YOUR_MYSQL_USERNAME]"
      password: "[YOUR_MYSQL_PASSWORD]"
      database: "cat_list_production"
      socket:   "/cloudsql/[YOUR_INSTANCE_CONNECTION_NAME]"

    Where:

    • [YOUR_MYSQL_USERNAME] represents your MySQL username.
    • [YOUR_MYSQL_PASSWORD] represents your MySQL password.
    • [YOUR_INSTANCE_CONNECTION_NAME] represents the instance connection name that you copied in the previous step.

The Rails app is now set up to use Cloud SQL when deploying to the App Engine flexible environment.

Deploying the app to the App Engine flexible environment

The App Engine flexible environment uses a file called app.yaml to describe an app's deployment configuration. If this file isn't present, the Cloud SDK tries to guess the deployment configuration. However, you should define the file to provide required configuration settings for the Rails secret key and Cloud SQL.

To configure the sample app for deployment to App Engine, create a new file named app.yaml at the root of the Rails application directory and add the following:

entrypoint: bundle exec rackup --port $PORT
env: flex
runtime: ruby

Configure the Rails secret key in the app.yaml file

When a Rails app is deployed to the production environment, set the environment variable SECRET_KEY_BASE with a secret key to protect user session data. This environment variable is read from the config/secrets.yml file in your Rails app.

  1. Generate a new secret key.

    bundle exec bin/rails secret
    
  2. Copy the generated secret key.

  3. Open the app.yaml file that you created earlier, and add an env_variables section. The env_variables defines environment variables in the App Engine flexible environment. The app.yaml file should look similar to the following example with [SECRET_KEY] replaced with your secret key.

    entrypoint: bundle exec rackup --port $PORT
    env: flex
    runtime: ruby
    
    env_variables:
      SECRET_KEY_BASE: [SECRET_KEY]

Configure the Cloud SQL instance in the app.yaml file

Next, configure the App Engine flexible environment to use a specified Cloud SQL instance by providing the Cloud SQL instance connection name in the app.yaml configuration file.

  1. Open the app.yaml file, and add a new section named beta_settings.

  2. Define a nested parameter cloud_sql_instances with the instance connection name as the value.

    The app.yaml should look similar to the following:

    entrypoint: bundle exec rackup --port $PORT
    env: flex
    runtime: ruby
    
    env_variables:
      SECRET_KEY_BASE: [SECRET_KEY]
    
    beta_settings:
      cloud_sql_instances: [YOUR_INSTANCE_CONNECTION_NAME]

Create an App Engine flexible environment app

If this is the first time you are deploying an app, you need to create an App Engine flexible environment app and select the region where you want to run the Rails app.

  1. Create an App Engine app.

    gcloud app create
    
  2. Select a region that supports App Engine flexible environment for Ruby apps. Read more about Regions and zones.

Deploy a new version

Next, deploy a new version of the Rails app described in the app.yaml file without redirecting traffic from the current default serving version.

  1. Precompile the Rails assets.

    bundle exec bin/rails assets:precompile
    
  2. After the assets finish compiling, deploy a new version of the app.

    gcloud app deploy --no-promote
    

    It can take several minutes to finish the deployment. Wait for a success message. You can view deployed versions in the App Engine version list.

After you deploy the new version, if you attempt to access this new version, it shows the following error message because you haven't migrated the database.

Screenshot of new Rails app error message

Grant required permission for the appengine gem

Next, grant access to the cloudbuild service account to run production database migrations with the appengine gem.

  1. List available projects.

    gcloud projects list
    
  2. In the output, find the project you want to use to deploy your app, and copy the project number.

  3. Add a new member to your project IAM policy for the role roles/editor to run database migrations. Replace [YOUR-PROJECT-ID] with your GCP project ID and replace[PROJECT_NUMBER] with the project number you copied in the previous step.

    gcloud projects add-iam-policy-binding [YOUR-PROJECT-ID] \
      --member=serviceAccount:[PROJECT_NUMBER]@cloudbuild.gserviceaccount.com \
      --role=roles/editor
    

Migrate your Rails database

Rails database migrations are used to update the schema of your database without using SQL syntax directly. Next you migrate your cat_list_production database.

The appengine gem provides the Rake task appengine:exec to run a command against the most recent deployed version of your app in the production App Engine flexible environment.

  1. Migrate the Cloud SQL for MySQL cat_list_production database in production.

    bundle exec rake appengine:exec -- bundle exec rake db:migrate
    

    You should see output similar to:

    ---------- EXECUTE COMMAND ----------
    bundle exec rake db:migrate
    Debuggee gcp:787021104993:8dae9032f8b02004 successfully registered
    == 20170804210744 CreateCats: migrating =======================================
    -- create_table(:cats)
       -> 0.0219s
    == 20170804210744 CreateCats: migrated (0.0220s) ==============================
    
    ---------- CLEANUP ----------
    
  2. To verify the database migration, go to:

    [YOUR-VERSION]-dot-[YOUR-PROJECT-ID].appspot.com
    

The following is displayed for a successful deployment:

Screenshot of new Rails app running

Migrate traffic to new version

Finally, direct traffic to your newly deployed version by using the following command:

gcloud app services set-traffic default --splits [YOUR-VERSION]=1

The new version of the app is now accessible from:

https://[YOUR-PROJECT-ID].appspot.com

Reading App Engine logs

Now that you have deployed your Rails app, you might want to read the logs. You can read the app logs by using the Logs Viewer located in the Google Cloud Platform Console.

You can learn more about reading logs using the Cloud SDK.

Clean up resources

After you've finished the Using Cloud SQL for MySQL with Rails 5 tutorial, you can clean up the resources you created on Google Cloud Platform so they won't take up quota and you won't be billed for them in the future. The following sections describe how to delete or turn off these resources.

Delete project

The easiest way to eliminate billing is to delete the project you created for the tutorial.

To delete the project:

  1. In the GCP Console, go to the Projects page.

    Go to the Projects page

  2. In the project list, select the project you want to delete and click Delete .
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

Delete an App Engine version

To delete an app version:

  1. In the GCP Console, go to the Versions page for App Engine.

    Go to the Versions page

  2. Select the checkbox for the non-default app version you want to delete.
  3. Click Delete to delete the app version.

Delete a Cloud SQL instance

To delete a Cloud SQL instance:

  1. In the GCP Console, go to the SQL Instances page.

    Go to the SQL Instances page

  2. Click the name of the SQL instance you want to delete.
  3. Click Delete to delete the instance.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...