Creating a cloud-based Pokédex: Local testing with Cloud SQL Proxy
Published: 2020-07-09 15:56:07

Previously in this series, I went over [what I used to build my first Python web app](https://dev.to/googlecloud/creating-a-cloud-based-pokedex-overview-o8j). In this post, we're taking a look at how to locally test a Flask app with [Cloud SQL Proxy](https://cloud.google.com/sql/docs/mysql/sql-proxy).

<hr/>

Locally testing a Flask app that's connected to a Cloud SQL instance can be a little tricky. It's not as simple as calling `python3 main.py`.

As an example, let's walk through creating a Cloud SQL instance and testing my Pokédex project locally.

Clone my repo to get started.
{% github abbycar/pokedex-project no-readme %}

### A look at Cloud SQL Proxy

Cloud SQL Proxy allows you to locally communicate securely with your Cloud SQL instance.

The proxy handles data encryption to and from your database using Transport Layer Security (TLS). If you're not familiar with TLS, you can see it in action as the padlock in your address bar. SSL certificates establish authority, verifying client and server identities.

It also handles authentication with Cloud SQL, removing the need to provide static IP addresses.

![Alt text of image](https://cloud.google.com/sql/images/proxyconnection.svg)



When you eventually deploy your app to App Engine, your app uses the Cloud SQL Proxy that’s built in to the App Engine environment.  


When testing things locally, you need to download your own copy of the Cloud SQL Proxy.



### Enable Google Cloud products

Since this app uses Google Cloud Platform (GCP) products, we need to create a GCP project.

Get access to GCP by [creating a new project](https://console.cloud.google.com/projectselector/appengine/create) with [billing enabled](https://cloud.google.com/billing/docs/how-to/modify-project#confirm_billing_is_enabled_on_a_project). If this is your first time trying out GCP, you can start a [free trial](https://cloud.google.com/free/).

You also need to install the [Google Cloud SDK](https://cloud.google.com/sdk/docs/) and enable the following APIs:

* [Cloud SQL API](https://console.cloud.google.com/apis/library/sql-component.googleapis.com)
* [Cloud Storage API](https://console.cloud.google.com/apis/library/storage-component.googleapis.com)

Once you’ve done all that, open a terminal and enter the following command to get credentials for authenticating with GCP services.


`$ gcloud auth application-default login`


### Create a MySQL Cloud SQL instance 

Let’s start by creating a second generation MySQL instance in your terminal. 

The following command takes a few minutes to complete. Check up on the creation status at the [instances page](https://console.cloud.google.com/sql/instances).

```bash
$ gcloud sql instances create <MY-INSTANCE> --root-password=<ROOT_PASSWORD> --database-version=MYSQL_5_7 --region=us-central
```

Now that you have an instance, add a new database named `pokemon`. This is where we store all pokemon and user info.



```bash
$ gcloud sql databases create pokemon --instance=<MY-INSTANCE>
```

Your new database now shows up in your Cloud SQL instance.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/frqbxrn9ewfazolivq3a.png)

You can connect to your database with your root user account and password. Optionally, create a new user. A host set to % means the user has an unrestricted host name.


```bash
$ gcloud sql users create USERNAME \
    --host=% --instance=<MY-INSTANCE> --password=PASSWORD
```

### Connect to your database with Cloud SQL Proxy


Open a terminal at the sample code folder and download an executable Cloud SQL Proxy with the following commands. For non-linux commands, check out the Cloud SQL Proxy quickstart.

```bash
$ wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy`

$ chmod +x cloud_sql_proxy
```

Run the proxy and connect to your instance. You can find your instance connection name [here](https://console.cloud.google.com/sql/instances/my-instance/overview).


`./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:3306`

Keep the Cloud SQL Proxy running in the current terminal and continue along in a new one. Without this connection, the app won’t be able to retrieve data from the pokemon database.


### Configure your environment

For local testing, update your config.py. It’s best to store these variables as environment variables in app.yaml, however, app.yaml variables aren’t reflected until deploying to App Engine. I’ll go over app.yaml and the deployment process later in this series.

A hot new product Google Cloud added recently is [Secret Manager](https://cloud.google.com/secret-manager). You should check it out if you want to store sensitive info in the cloud instead of in a file in your code.

{% gist https://gist.github.com/abbycar/d7ed347afe98578fc02387a450e3782f file=app.yaml %}


### Create a virtual environment

Since the Python packages and modules you’ve installed probably aren’t the same as what my app needs, a virtual environment gives my app the perfect place to run. 

To get one up and running, enter the `virtualenv` command and activate it.

```bash
$ virtualenv -p python3 venv
$ source env/bin/activate
```


You should now see `(venv)` next to your file path in the terminal.
Now install the necessary packages and modules.

```bash
$ pip install -r requirements.txt
```


### Load MySQL data with a .csv

Since the database is currently empty, testing my app now will fail.


In my repo I have all pokemon data stored in the [pokemon-db.csv file](https://github.com/abbycar/pokedex-project/blob/master/pokemon-db.csv). Use this to initially populate the database.

Call the following command to create your relational tables. At this point they’ll be empty and ready for some data.

```bash
$ python3 pokeview/models.py
```

In a new terminal, connect to your MySQL instance and load the provided pokemon-db.csv file. **Your Cloud SQL Proxy must be running in a different terminal for this to work**.

```
$ mysql -u username -p --host 127.0.0.1 

LOAD DATA LOCAL INFILE 'pokemon-db.csv' INTO TABLE pokedex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
```

After loading the CSV, your pokedex table should now have the original 151 pokemon in it.

### Run it!

It’s time for all that setup work to pay off!
Run your app to start up a local webserver.

```bash
$ python3 main.py
```

In your web browser, go to http://localhost:8080 where the app is running. The port is configured via the main.py file.