Recipe: Google App Engine, Cloud SQL and sqlalchemy

Here is a recipe on how I made those thing work together both on a linux development environment and in production. The important thing to remember is: just like recipes for brownies, there are other recipes to achieve the same thing.

The following steps assume you have a Google cloud account with the proper permissions.

Ingredients

Here is the list of the components with which I had success.

Google App Engine standard
Cloud SQL with MySQL 5.7
python2 with
- sqlalchemy 1.1.9
- MySQL-python 1.2.5

Step 1 – Create the database server

Go to the SQL menu, select “Create an instance”, select MySQL and then “MySQL Second Generation”. Proceed through the rest of the options.

Step 2 – Note the instance connection name

You should now be on the instance list page. (If not, go back to the Google Cloud Platform (GCP) console, from the main menu open “SQL”.) Select your instance. In the “Properties” section, look for “Instance connection name”. Note it.

Step 3 – Create the database

Still from the SQL menu, select your database server, then click on “databases”, then on “New database”. Proceed.

Step 4 – Enable Cloud SQL API

Step 4 to 6 are necessary to work with a development environment not on GCP (Google Cloud Platform). If it doesn’t concern you, move along to step 7.

This specific step is necessary to allow the cloud_sql_proxy to work.

From the main menu, select API manager, then dashboard, then “Google Cloud SQL API”.

If you have never done this before, at the top of the page there should be an “Enable” button. Click on it. (If you see a “Disable” button, then it’s already enabled and you don’t need to do anything.)

Step 5 – Create an access token

This step is still about getting cloud_sql_proxy ready to go.

From the main menu, access “IAM & Admin”, then “Service accounts”. Click “Create service account”. Give it a name. As role, select “Cloud SQL” > “Cloud SQL Client”. Select “Furnish a new private key” and then “JSON”. Click create.

You will be prompted with a file download. Save the file and move it to the your development machine.

Step 6 – Get cloud_sql_proxy

You may follow the detailed official instructions here, or stick with my minimized steps.

Download the proxy:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64

Rename the proxy to use the standard filename:

mv cloud_sql_proxy.linux.amd64 cloud_sql_proxy

Make the proxy executable:
```
chmod +x cloud_sql_proxy
```
Create the directory where the proxy sockets will live:
```
sudo mkdir /cloudsql; sudo chmod 777 /cloudsql
```

Open a new ~~terminal~~ tmux window and start the proxy.

./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> \
                  -credential_file=<PATH_TO_KEY_FILE> &

PATH_TO_KEY_FILE is the JSON file you downloaded a moment ago. INSTANCE_CONNECTION_NAME is the name of your Cloud SQL database server that you noted at step 2.

After you have executed the command, you can run “ls” on “/cloudsql” and you should see your instance connection name.

Step 7 – app.yaml

You should have the following library in thew app.yaml file.

libraries:
- name: MySQLdb
  version: "1.2.5"

Step 8 – Writing sqlalchemy connection string

If you followed the steps for the development environment, the connection string is the same as in production.

from sqlalchemy import create_engine

create_engine(
    'mysql://{username}:{password}@/{db_name}?charset=utf8'
    '&unix_socket=/cloudsql/{instance_connection_name}'
    .format(username='TODO', password='TODO', db_name='TODO',
            instance_connection_name='TODO'))
engine.connect()