Use Cloud SQL for PostgreSQL with Rails 7 on App Engine

Get started developing Ruby on Rails apps that run on the App Engine flexible environment . The apps you create run on the same infrastructure that powers all of Google's products, so 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 SQL for PostgreSQL with a new Rails app. You can also use this tutorial as a reference for configuring existing Rails apps to use Cloud SQL for PostgreSQL.

This tutorial supports and requires Ruby 3.0 or later.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Verify that billing is enabled for your Google Cloud project .

Enable the Cloud SQL Admin API.

Enable the API

Install the Google Cloud CLI.

If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity .

To initialize the gcloud CLI, run the following command:

gcloud  
init

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

Verify that billing is enabled for your Google Cloud project .

Enable the Cloud SQL Admin API.

Enable the API

Install the Google Cloud CLI.

If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity .

To initialize the gcloud CLI, run the following command:

gcloud  
init

Preparing a Cloud SQL for PostgreSQL instance

Set up a Cloud SQL for PostgreSQL instance for this tutorial.

Create a PostgreSQL instance . In this tutorial, the name of the instance is rails-cloudsql-instance .
Create a database in the instance . In this tutorial, the name of the production database is cat_list_production .
Set the postgres user password for the instance .

Setting up your local environment for Rails

To set up your local environment for this tutorial:

Install Ruby version 3.0 or later.
Install the Rails 7 gem.
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, create and deploy a Rails app by using Cloud SQL for PostgreSQL. The following sections guide you through configuring, connecting to Cloud SQL for PostgreSQL, and deploying an app.

Create a new app to list cats

Run the rails new command to create a new Rails app. This app stores a list of cats in Cloud SQL for PostgreSQL.
```
  rails 
  
 new 
  
 cat_sample_app 
 
```
Go to the directory that contains the generated Rails app.
```
  cd 
  
 cat_sample_app 
 
```

Run the app locally

To run the new Rails app on your local computer:

Start a local web server:

  bundle 
  
 exec 
  
 bin 
 / 
 rails 
  
 server

In a browser, go to http://localhost:3000/

The sample app displays the Rails logo with the Rails and Ruby versions.

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.

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 
 / 
 20230922063608 
 _create_cats 
 . 
 rb 
 create 
  
 app 
 / 
 models 
 / 
 cat 
 . 
 rb 
 invoke 
  
 test_unit 
 create 
  
 test 
 / 
 models 
 / 
 cat_test 
 . 
 rb 
 create 
  
 test 
 / 
 fixtures 
 / 
 cats 
 . 
 yml 
 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 
 create 
  
 app 
 / 
 views 
 / 
 cats 
 / 
 _cat 
 . 
 html 
 . 
 erb 
 invoke 
  
 resource_route 
 invoke 
  
 test_unit 
 create 
  
 test 
 / 
 controllers 
 / 
 cats_controller_test 
 . 
 rb 
 create 
  
 test 
 / 
 system 
 / 
 cats_test 
 . 
 rb 
 invoke 
  
 helper 
 create 
  
 app 
 / 
 helpers 
 / 
 cats_helper 
 . 
 rb 
 invoke 
  
 test_unit 
 invoke 
  
 jbuilder 
 create 
  
 app 
 / 
 views 
 / 
 cats 
 / 
 index 
 . 
 json 
 . 
 jbuilder 
 create 
  
 app 
 / 
 views 
 / 
 cats 
 / 
 show 
 . 
 json 
 . 
 jbuilder 
 create 
  
 app 
 / 
 views 
 / 
 cats 
 / 
 _cat 
 . 
 json 
 . 
 jbuilder

Open the file config/routes.rb 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

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

Save the file and close it.
Test the Rails app as instructed before .

Using Cloud SQL for PostgreSQL with App Engine

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

Set up Cloud SQL for PostgreSQL

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

Add the pg and appengine gems to the Gemfile file.

  bundle 
  
 add 
  
 pg 
 bundle 
  
 add 
  
 appengine

The Rails Gemfile contains the following additional gem entries:

  gem 
  
 "appengine" 
 , 
  
 "~> 0.6" 
 gem 
  
 "pg" 
 , 
  
 "~> 1.2"

To configure the Rails app to connect with 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

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 .

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

  production 
 : 
  
 adapter 
 : 
  
 postgresql 
  
 encoding 
 : 
  
 unicode 
  
 pool 
 : 
  
 5 
  
 timeout 
 : 
  
 5000 
  
 username 
 : 
  
 "[YOUR_POSTGRES_USERNAME]" 
  
 password 
 : 
  
 "[YOUR_POSTGRES_PASSWORD]" 
  
 database 
 : 
  
 "cat_list_production" 
  
 host 
 : 
  
 "/cloudsql/[YOUR_INSTANCE_CONNECTION_NAME]"

Where:

[YOUR_POSTGRES_USERNAME] represents your Cloud SQL for PostgreSQL instance username.
[YOUR_POSTGRES_PASSWORD] represents your Cloud SQL for PostgreSQL instance 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 App Engine flexible environment.

Deploying the app to the App Engine flexible environment

App Engine flexible environment uses a file called app.yaml to describe an app's deployment configuration. If this file isn't present, the gcloud CLI 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 app 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.

Generate a new secret key.

  bundle 
  
 exec 
  
 bin 
 / 
 rails 
  
 secret

Copy the generated secret key.
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.

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

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.

Create an App Engine app.
```
  gcloud 
  
 app 
  
 create 
 
```
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 by running this command:

  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.

List available projects.
```
  gcloud 
  
 projects 
  
 list 
 
```
In the output, find the project you want to use to deploy your app, and copy the project number.
Add a new member to your project IAM policy for the role roles/editor to run database migrations. Replace PROJECT_ID with your Google Cloud project ID and replace PROJECT_NUMBER with the project number you copied in the previous step.
```
 gcloud  
projects  
add-iam-policy-binding  
 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.

Migrate the Cloud SQL for PostgreSQL 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 
 : 
 8 
 dae9032f8b02004 
  
 successfully 
  
 registered 
 == 
  
 20230922063608 
  
 CreateCats 
 : 
  
 migrating 
  
 ======================================= 
 -- 
  
 create_table 
 ( 
 :cats 
 ) 
  
 - 
>  
 0 
 . 
 021 
 9 
 s 
 == 
  
 20230922063608 
  
 CreateCats 
 : 
  
 migrated 
  
 ( 
 0 
 . 
 0220 
 s 
 ) 
  
 ============================== 
  
  
 CLEANUP

To verify the database migration, enter the following URL in your browser:
```
  https 
 : 
 // 
  VERSION_ID 
 
 - 
 dot 
 - 
  PROJECT_ID 
 
 . 
  REGION_ID 
 
 . 
 r 
 . 
 appspot 
 . 
 com 
 
```
Replace the following:
- VERSION_ID : The new version of the app that you deployed previously. To get a list of versions, use gcloud app versions list . The last defaultservice version item is the latest deployment.
- PROJECT_ID : Your Google Cloud project ID
- REGION_ID : A code that App Engine assigns to your app

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 
  
  VERSION 
 
 = 
 1

The new version of the app is now accessible from the following URL:

  https 
 : 
 // 
  PROJECT_ID 
 
 . 
  REGION_ID 
 
 . 
 r 
 . 
 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 Explorer located in the Google Cloud console.

You can learn more about reading logs using the gcloud CLI .

Clean up resources

After you finish the tutorial, you can clean up the resources that you created so that they stop using quota and incurring charges. 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 that you created for the tutorial.

To delete the project:

In the Google Cloud console, go to the Manage resources page.
Go to Manage resources
In the project list, select the project that you want to delete, and then click Delete .
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:

In the Google Cloud console, go to the Versions page for App Engine.
Go to Versions
Select the checkbox for the non-default app version that you want to delete.
Note:The only way you can delete the default version of your App Engine app is by deleting your project. However, you can stop the default version in the Google Cloud console . This action shuts down all instances associated with the version. You can restart these instances later if needed.
In the App Engine standard environment, you can stop the default version only if your app has manual or basic scaling.
To delete the app version, click Delete .

Delete a Cloud SQL instance

To delete a Cloud SQL instance:

In the Google Cloud console, go to the Instances page.
Go to Instances
Click the name of the SQL instance you that want to delete.
To delete the instance, click Delete , and then follow the instructions.

What's next

Learn how to run the Ruby Bookshelf sample app in the App Engine flexible environment .
Learn how to run the Ruby Bookshelf sample app on Compute Engine .
Learn how to run the Ruby Bookshelf sample app on Google Kubernetes Engine .

Use Cloud SQL for PostgreSQL with Rails 7 on App Engine Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Preparing a Cloud SQL for PostgreSQL instance

Setting up your local environment for Rails

Create a new app to list cats

Run the app locally

Generate scaffolding for a list of cats

Using Cloud SQL for PostgreSQL with App Engine

Set up Cloud SQL for PostgreSQL

Deploying the app to the App Engine flexible environment

Configure the Rails secret key in the app.yaml file

Configure the Cloud SQL instance in the app.yaml file

Create an App Engine flexible environment app

Deploy a new version

Grant required permission for the appengine gem

Migrate your Rails database

Migrate traffic to new version

Reading App Engine logs

Clean up resources

Delete project

Delete an App Engine version

Delete a Cloud SQL instance

What's next

Use Cloud SQL for PostgreSQL with Rails 7 on App Engine

Configure the Rails secret key in the `app.yaml` file

Configure the Cloud SQL instance in the `app.yaml` file

Grant required permission for the `appengine` gem