Connect App Engine & Flask & SQLAlchemy to Cloud Sql

Question

I'm having trouble deploying a website I made using Flask and SQLAlchemy to Google App Engine. When run locally my web app can succesfully connect to my Cloud SQL, but when deployed tp App Engine, it just can't. The error logged in the Logs Explorer is the following.

sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (2003, "Can't connect to MySQL server on 'my sql instance ip address' (110)")"

Which basically tells me nothing. I guess the problem might be that my local machine is whitelisted on the SQL instance. But my App Engine app (which lives in the same project as the SQL instance) should automatically be whitelisted too, right?

On my Flask App configuration I have the following:

"""Flask configuration."""
from os import environ, path
import os
from dotenv import load_dotenv

basedir = path.abspath(path.dirname(__file__))
load_dotenv(path.join(basedir, '.env'))

CLOUD_SQL_USERNAME = environ.get('CLOUD_SQL_USERNAME')
CLOUD_SQL_PASSWORD = environ.get('CLOUD_SQL_PASSWORD')
SQL_PUBLIC_IP_ADDRESS = environ.get('SQL_PUBLIC_IP_ADDRESS')
CLOUD_SQL_PORT = environ.get('CLOUD_SQL_PORT')
CLOUD_SQL_DATABASE_NAME = environ.get('CLOUD_SQL_DATABASE_NAME')
PROJECT_ID = environ.get('PROJECT_ID')
SQL_INSTANCE_NAME = environ.get('SQL_INSTANCE_NAME')
MAPBOX_TOKEN = environ.get('MAPBOX_TOKEN')
CLOUD_SQL_CONNECTION_NAME = environ.get('CLOUD_SQL_CONNECTION_NAME')

def gen_connection_string():
    # if not on Google then use normal connection
    if not os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
        return f"mysql+mysqldb://{CLOUD_SQL_USERNAME}:{CLOUD_SQL_PASSWORD}@{SQL_PUBLIC_IP_ADDRESS}:{CLOUD_SQL_PORT}/{CLOUD_SQL_DATABASE_NAME}?unix_socket=/cloudsql/{PROJECT_ID}:{SQL_INSTANCE_NAME}"
    else:
        return f'mysql+mysqldb://{CLOUD_SQL_USERNAME}:{CLOUD_SQL_PASSWORD}/{SQL_INSTANCE_NAME}?unix_socket=/cloudsql/{CLOUD_SQL_CONNECTION_NAME}'

class Config:
    """Base config."""
    SECRET_KEY = environ.get('SECRET_KEY')
    STATIC_FOLDER = 'static'
    TEMPLATES_FOLDER = 'templates'
    SQLALCHEMY_DATABASE_URI = gen_connection_string()
    SQLALCHEMY_TRACK_MODIFICATIONS = True

class ProdConfig(Config):
    FLASK_ENV = 'production'
    DEBUG = False
    TESTING = False

class DevConfig(Config):
    FLASK_ENV = 'development'
    DEBUG = True
    TESTING = True

This works like a charm when run locally, but it doesn't when actually deployed.

I'm a bit lost since I'm just a 16 years old newbie and App Engine is way too complicated for me. I would appreciate any help :)

EDIT: This is my current app.yaml file:

runtime: python38
entrypoint: python main.py

handlers:
  # This configures Google App Engine to serve the files in the app's static
  # directory.
- url: /static
  secure: always
  static_dir: static

  # This handler routes all requests not caught above to your main app. It is
  # required when static routes are defined, but can be omitted (along with
  # the entire handlers section) when there are no static files defined.
- url: /.*
  secure: always
  script: auto

I've tried adding the environmental variables to the app.yaml file with no luck. It still fails.

Answer 1

I'm an engineer on the Cloud SQL Developer Relations Team at Google.

The first issue I see in the logs is that App Engine is trying to connect using the public IP address instead of with the Unix socket, so it's trying to use the first connection string instead of the second. So I suspect that checking the value of the SERVER_SOFTWARE environment variable isn't working the way you'd expect. The docs for the Python 3 App Engine runtime suggest using

if os.getenv('GAE_ENV', '').startswith('standard'):

You'll want to make sure that your App Engine service account has Cloud SQL Client permissions. There's info on how to do that here.

Also, the officially recommended way to connect to Cloud SQL is to use the Cloud SQL Auth Proxy, or the newly released Cloud SQL Python Connector. Both of these options will allow you to easily connect without having to add your IP address to the allowlist and the database connection parameters should be the same both locally and deployed.

P.S. That's pretty cool that you're working with App Engine as a 16 year old newbie. This stuff is tricky even to those of us who've been doing it for a while. :)