This page shows how to connect to a Cloud SQL for MySQL Second Generation instance from an App Engine application, and how to read and write to Cloud SQL. Cloud SQL is a SQL database that lives in Google's cloud.
To learn more about Cloud SQL, see the Cloud SQL documentation. For information on Cloud SQL pricing and limits, see the Cloud SQL Pricing page. App Engine applications are also subject to the App Engine quotas.
Before you begin
- Create or select a Google Cloud project in the Cloud Console
and then ensure that project includes an App Engine application and
billing is enabled:
Go to App EngineThe Dashboard opens if an App Engine application already exists in your project and billing is enabled. Otherwise, follow the prompts for choosing a region and enabling billing.
- Enable the Cloud SQL Admin API.
-
To deploy your app with the
gcloud
tool, you must download, install, and initialize the Cloud SDK:
Download the SDKIf you already have the
gcloud
tool installed and want to configure it to use a Google Cloud project ID other than the one that you initialized it to, see Managing Cloud SDK Configurations.
Configuring the Cloud SQL instance
To create and configure a Cloud SQL instance:
- Create a Cloud SQL Second Generation instance.
- If you haven't already, set the password for the default user on your
Cloud SQL instance:
gcloud sql users set-password root --host=% --instance [INSTANCE_NAME] --password [PASSWORD]
- If you don't want to use the default user to connect, create a user.
-
Record the connection name for the instance:
gcloud sql instances describe [INSTANCE_NAME]
For example:
connectionName: project1:us-central1:instance1
You can also find this value in the Instance details page of the Google Cloud Console.
-
Create a database on your Cloud SQL instance.
gcloud sql databases create [DATABASE_NAME] --instance=[INSTANCE_NAME]
For more information on creating and managing databases, see the Cloud SQL documentation.
Setting up your local environment
Once deployed, your application uses the Cloud SQL Proxy that is built in to the App Engine runtime 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.
To perform basic administrative tasks on your Cloud SQL instance, you can use the administration client for your database or the Cloud Console.
Authenticate the
gcloud
tool to use the proxy to connect from your local machine:gcloud auth application-default login
Install the Cloud SQL proxy:
Linux 64-bit
- Download the proxy:
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
- Make the proxy executable:
chmod +x cloud_sql_proxy
Linux 32-bit
- Download the proxy:
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.386 -O cloud_sql_proxy
- Make the proxy executable:
chmod +x cloud_sql_proxy
macOS 64-bit
- Download the proxy:
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64
- Make the proxy executable:
chmod +x cloud_sql_proxy
macOS 32-bit
- Download the proxy:
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.386
- 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. Rename the file tocloud_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. Rename the file tocloud_sql_proxy.exe
.- Download the proxy:
Run the proxy:
Depending on your language and environment, you can start the proxy using either TCP sockets or Unix sockets.
TCP sockets
Copy your instance connection name from the Instance details page.
For example:
myproject:us-central1:myinstance
.- If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
- Start the proxy.
Some possible proxy invocation strings:
- Using Cloud SDK authentication:
./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306
The specified port must not already be in use, for example, by a local database server. - Using a service account and explicit instance specification (recommended for production environments):
./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306 \ -credential_file=<PATH_TO_KEY_FILE> &
For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.
- Using Cloud SDK authentication:
Unix sockets
- If you are using explicit instance specification, copy your instance connection name from the Instance details page.
- Create the directory where the proxy sockets will live:
sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
- If you are using a service account to authenticate the proxy, note the location on your client machine of the private key file that was created when you created the service account.
- Open a new terminal window and start the proxy.
Some possible proxy invocation strings:
- Using a service account and explicit instance specification (recommended for production
environments):
./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \ -credential_file=<PATH_TO_KEY_FILE> &
- Using Cloud SDK authentication and automatic instance discovery:
./cloud_sql_proxy -dir=/cloudsql &
It is best to start the proxy in its own terminal so you can monitor its output without it mixing with the output from other programs.
For more information about proxy options, see Options for authenticating the proxy and Options for specifying instances.
- Using a service account and explicit instance specification (recommended for production
environments):
-
When using a unix socket to connect to Cloud SQL using the Cloud SQL Proxy, make sure the socket
filename's length does not surpass the system's limit. It depends on the system, but it's usually
between 91-108 characters. On Linux, the length is usually defined as 108, and you can use the
following command to check:
cat /usr/include/linux/un.h | grep "define UNIX_PATH_MAX"
To use the administration client, you can install a local copy and connect either by using the proxy or IP Addresses.
For more information, see Connecting MySQL Client Using the Cloud SQL Proxy and Connecting MySQL Client Using IP Addresses.
Setting connection strings and adding a library
Set up the local environment to support connections for local testing.
For example, for the provided code sample:
export SQLALCHEMY_DATABASE_URI=mysql+pymysql://[USER_NAME]:[PASSWORD]@127.0.0.1:3306/[DATABASE_NAME]
To allow your app to connect to your Cloud SQL instance when the app is deployed, add the user, password, database, and instance connection name variables from Cloud SQL to the related environment variables in the
app.yaml
file:Add the
beta_settings
section to yourapp.yaml
, using your Cloud SQL instance connection name.Add the appropriate Python client library to your application's
requirements.txt
. For example, the provided code sample shows SQLAlchemy with PyMySQL:
Running the sample code
The sample ofmain.py
below uses the Flask
framework to create a visitor log in a Cloud SQL instance. It also uses
SQLAlchemy, which handles connection pooling and closing.
Before you run the sample, create the tables you need and ensure that the database is properly configured:
python create_tables.py
The following sample writes visit information to Cloud SQL and then
reads and returns the last ten visits:
Testing and deploying
To test your application locally:
python main.py
After local testing, deploy your app to App Engine:
gcloud app deploy
To launch your browser and view the app at
http://[YOUR_PROJECT_ID].appspot.com
, run the following command:gcloud app browse
Running Cloud SQL and App Engine in separate projects
If your App Engine application and Cloud SQL instance are in different Google Cloud projects, you must use a service account to allow your App Engine application access to Cloud SQL.
This service account represents your App Engine application and is created by default when you create a Google Cloud project.
- If your App Engine application is in the same project as your Cloud SQL instance, you can skip this section and go to Setting up your local environment. Otherwise, proceed to the next step.
-
Identify the service account associated with your App Engine
application. The default App Engine service account is named
[PROJECT-ID]@appspot.gserviceaccount.com
.You can verify the App Engine service account on the IAM Permissions page. Ensure that you select the project for your App Engine application, not your Cloud SQL instance.
- Go to the IAM & Admin Projects page in the Google Cloud Console.
- Select the project that contains the Cloud SQL instance.
- Search for the service account name.
-
If the service account is already there, and it has a role that includes the
cloudsql.instances.connect
permission, you can proceed to Setting up your local environment.The
Cloud SQL Client
,Cloud SQL Editor
andCloud SQL Admin
roles all provide the necessary permission, as do the legacyEditor
andOwner
project roles. - Otherwise, add the service account by clicking Add.
In the Add members dialog, provide the name of the service account and select a role that include the
cloudsql.instances.connect
permission (any Cloud SQL predefined role other than Viewer will work).Alternatively, you can use the primitive Editor role by selecting Project > Editor, but the Editor role includes permissions across Google Cloud.
If you do not see these roles, your Google Cloud user might not have the
resourcemanager.projects.setIamPolicy
permission. You can check your permissions by going to the IAM page in the Google Cloud Console and searching for your user id.- Click Add.
You should now see the service account listed with the specified role.