App Engine Standard and SQLAlchemy

Lets admit it. Google wants you to use their super duper NoSQL Datastore product as the default storage option for new app engine projects. While datastore is quite nice it also has its own drawbacks and shortcomings. One of the big ones is that you actually have to learn it and try to wrap your head around it! That takes time. Time that is better spent delivering business value instead. Lets take what we already know and use plain ol’ MySQL that Google so nicely packages for us under the fancy name of Cloud SQL.

But even if you want to use SQL there are still a few unknowns left. How do you connect to a Cloud SQL instance from your local computer? Or how do you use a local instance of MySQL while developing?

Part 1: Setup Cloud SQL

First you have to create a new Cloud SQL instance. An instance is basically a server where you can specify tons of options on how MySQL should be set up and run. Lets create the smallest instance possible with default values for our example.

# create smallest possible instance
$ gcloud sql instances create cloudy-mysql \
    --database-version=MYSQL_5_7 \
    --region=us-east1 \
    --tier=db-f1-micro

# set root password
$ gcloud sql users set-password root % --instance cloudy-mysql --password pa$$w0rd

# connect to your cloud sql instance and create a sample database
$ gcloud sql connect cloudy-mysql -u root
mysql> CREATE DATABASE blog;
mysql> QUIT;

Part 2: Use SQLAlchemy with Google App Engine

Now that we have setup MySQL both remotely and hopefully locally (not part of this post) lets write a simple JSON API using Flask web framework.

Proxy connection to remote instance

If you don’t have a local MySQL server installed you can proxy a connection to your Cloud SQL instance using Cloud SQL Proxy utility. Download it and put it somewhere in your path.

# find your connection name
$ gcloud sql instances describe cloudy-mysql | grep connectionName -

# start sql proxy with a tcp connection
$ cloud_sql_proxy -instances my-gcp-project-id:us-east1:cloudy-mysql=tcp:3306

# now you can connect to proxied Cloud SQL instance locally
$ mysql -h 127.0.0.1 -u root -p

Install Python MySQL client

There are many ways to install Python MySQL client locally. Most of the examples out there recommend using a OS package manager (yum, apt). What many people don’t realise is that it’s quite simple and you can do it using only pip.

# install MySQLdb Python client
$ pip install --user mysqlclient

The problem and common misconception is that the package name is not mysqldb but mysqlclient and when you do a pip search mysqldb you won’t get any matches. Likewise pip install mysqldb will also fail.

For Google App Engine the Python client is already installed but you have to explicitly enable it in app.yaml config file.

libraries:
- name: MySQLdb
  version: latest

There are quite many built-in third-party libraries that you can use with the Google App Engine Python runtime. Just don’t forget to enable them.

Add some code

Now that we have set up a Cloud SQL instance and we know how to connect to it - both locally and remote - it’s time to create tiny little app connecting the dots. For this example lets use the best of both worlds - Flask for our web and SQLAlchemy for our SQL needs - by using the excellent Flask-SQLAlchemy package. We will also use Marshmallow package for simple serialization to JSON.

Create a requirements.txt file with package dependencies

Flask-SQLAlchemy
marshmallow

Install them by running

$ pip install -t lib -r requirements.txt

Create an app.yaml configuration file.

runtime: python27
api_version: 1
threadsafe: true

handlers:
- url: /.*
  script: main.app

env_variables:
  CLOUDSQL_CONNECTION_NAME: your-project-id:us-east1:cloudy-mysql
  CLOUDSQL_USER: root
  CLOUDSQL_PASSWORD: pa$$w0rd

libraries:
- name: MySQLdb
  version: latest

Now lets create main.py file

import os
from flask import Flask, jsonify
from datetime import datetime, timedelta
from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields

def gen_connection_string():
    # if not on Google then use local MySQL
    if not os.getenv('SERVER_SOFTWARE', '').startswith('Google App Engine/'):
        return 'mysql://root@localhost/blog'
    else:
        conn_name = os.environ.get('CLOUDSQL_CONNECTION_NAME' '')
        sql_user = os.environ.get('CLOUDSQL_USER', 'root')
        sql_pass = os.environ.get('CLOUDSQL_PASSWORD', '')
        conn_template = 'mysql+mysqldb://%s:%s@/blog?unix_socket=/cloudsql/%s'
        return conn_template % (sql_user, sql_pass, conn_name)

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = gen_connection_string()
db = SQLAlchemy(app)

# SQLAchemy model
class Post(db.Model):
    __tablename__ = 'posts'
    id = db.Column(db.Integer, primary_key=True)
    author = db.Column(db.Unicode(80), nullable=False)
    title = db.Column(db.Unicode(255), nullable=False)
    body = db.Column(db.UnicodeText())
    created_at = db.Column(db.DateTime, default=datetime.utcnow)

    def __repr__(self):
        return '<Post %r>' % self.title

# Marshmallow serialization schema
class PostSerializer(Schema):
    id = fields.Integer()
    author = fields.Str()
    title = fields.Str()
    body = fields.Str()
    created_at = fields.DateTime()

@app.route('/')
def index():
    return 'hello from google app engine + sqlalchemy!'

@app.route('/posts')
def get_posts():
    # Fetch all the posts
    posts = Post.query.all()
    # Create a serialization schema
    schema = PostSerializer(many=True)
    # Return all posts as json
    return jsonify({ 'items': schema.dump(posts).data })

def bootstrap_db():
    # Create db tables if they don't exits. NOT SAFE FOR PRODUCTION USE!
    db.create_all()
    # create a couple of posts if there are none
    if Post.query.count() == 0:
        p1 = Post(author=u'john',
                title=u'Do you know your Latin?',
                body=u'Lorem ipsum dolor sit amet',
                created_at=datetime.utcnow() + timedelta(days=-1))
        p2 = Post(author=u'mary',
                title=u'Easy way to a better life',
                body=u'Eat healthy, move more, lift heavy, laugh.')
        db.session.add_all([p1, p2])
        db.session.commit()

bootstrap_db()

Lets run the app locally with extra debugging enabled to see how it flies.

# start dev server with extra debugging
$ dev_appserver.py --dev_appserver_log_level debug .

# fetch all posts from another terminal
$ curl localhost:8080/posts

Lets deploy it to GCP if everything worked as expected.

$ gcloud app create
$ gcloud app deploy
$ gcloud app browse

This wasn’t too hard but there were a few steps we needed to take to make everything work. Hopefully now you have a little more understanding of Cloud SQL and how it works together with App Engine.