Connection to Cloud SQL - PostgreSQL

Before you begin

1. Before you use this code sample, you need to have Composer installed or downloaded into this folder. Download instructions can be found here. Once you've installed composer, use it to install required dependencies by running composer install.
2. Create a PostgreSQL Cloud SQL Instance by following these instructions. Note the connection string, database user, and database password that you create.
3. Create a database for your application by following these instructions. Note the database name.
4. Create a service account with the 'Cloud SQL Client' permissions by following these instructions. Download a JSON key to use to authenticate your connection.

Running Locally

To run this application locally, download and install the cloud_sql_proxy by following the instructions here.

Instructions are provided below for using the proxy with a TCP connection or a Unix domain socket. On Linux or macOS, you can use either option, but the Windows proxy requires a TCP connection.

Launch proxy with Unix Domain Socket

NOTE: this option is currently only supported on Linux and macOS. Windows users should use the TCP option.

To use a Unix socket, you'll need to create a directory and give access to the user running the proxy:

sudo mkdir /cloudsql
sudo chown -R $USER /cloudsql

Use these terminal commands to initialize environment variables:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
export INSTANCE_CONNECTION_NAME='<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>'
export INSTANCE_UNIX_SOCKET='/cloudsql/<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>'
export DB_USER='<DB_USER_NAME>'
export DB_PASS='<DB_PASSWORD>'
export DB_NAME='<DB_NAME>'

Note: Saving credentials in environment variables is convenient, but not secure - consider a more secure solution such as Secret Manager to help keep secrets safe.

Then use the following command to launch the proxy in the background:

./cloud_sql_proxy -dir=/cloudsql --instances=$INSTANCE_CONNECTION_NAME --credential_file=$GOOGLE_APPLICATION_CREDENTIALS &

Launch proxy with TCP

To run the sample locally with a TCP connection, set environment variables and launch the proxy as shown below.

Linux / Mac OS

Use these terminal commands to initialize environment variables:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
export INSTANCE_CONNECTION_NAME='<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>'
export INSTANCE_HOST='127.0.0.1'
export DB_USER='<DB_USER_NAME>'
export DB_PASS='<DB_PASSWORD>'
export DB_NAME='<DB_NAME>'

Note: Saving credentials in environment variables is convenient, but not secure - consider a more secure solution such as Secret Manager to help keep secrets safe.

Then use the following command to launch the proxy in the background:

./cloud_sql_proxy -instances=$INSTANCE_CONNECTION_NAME=tcp:5432 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &

Windows/PowerShell

Use these PowerShell commands to initialize environment variables:

$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
$env:INSTANCE_HOST="127.0.0.1"
$env:DB_USER="<DB_USER_NAME>"
$env:DB_PASS="<DB_PASSWORD>"
$env:DB_NAME="<DB_NAME>

Note: Saving credentials in environment variables is convenient, but not secure - consider a more secure solution such as Secret Manager to help keep secrets safe.

Then use the following command to launch the proxy in a separate PowerShell session:

Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<project-id>:<region>:<instance-name>=tcp:5432 -credential_file=<CREDENTIALS_JSON_FILE>"

Testing the application

Execute the following to start the application server:

Navigate towards http://localhost:8080 to verify your application is running correctly.

Google App Engine Standard

Note: App Engine Standard does not support TCP connections to Cloud SQL instances, only Unix socket connections.

To run on App Engine Standard, create an App Engine project by following the setup for these instructions.

First, update app.standard.yaml with the correct values to pass the environment variables into the runtime.

Next, the following command will deploy the application to your Google Cloud project:

$ gcloud app deploy app.standard.yaml

Google App Engine Flex

To run on App Engine Flex, create an App Engine project by following the setup for these instructions.

First, update app.flex.yaml with the correct values to pass the environment variables into the runtime.

To use a TCP connection instead of a Unix socket to connect your sample to your Cloud SQL instance on App Engine, make sure to uncomment the INSTANCE_HOST field under env_variables. Also make sure to remove the uncommented beta_settings and cloud_sql_instances fields and replace them with the commented beta_settings and cloud_sql_instances fields.

Then, make sure that the App Engine default service account <PROJECT-ID>@appspot.gserviceaccount.com has the IAM role Cloud SQL Client.

Also, make sure that the Cloud Build service account cloudbuild@<PROJECT-ID>.iam.gserviceaccount.com has the IAM role Cloud SQL Client.

Next, the following command will deploy the application to your Google Cloud project:

$ gcloud beta app deploy app.flex.yaml