Setting Up An API Using Flask, Google’s Cloud SQL And App Engine

• 15 min read
• Apps, API, Frameworks
• Share on Twitter, LinkedIn

About The Author

Wole Oyekanmi is a data scientist who is working on applying machine learning to consumer finance. When he’s not working, he enjoys drumming up new startup … More about Wole ↬

Email Newsletter

Your (smashing) email

Weekly tips on front-end & UX.
Trusted by 200,000+ folks.

A few Python frameworks can be used to create APIs, two of which are Flask and Django. Frameworks comes with functionality that makes it easy for developers to implement the features that users need to interact with their applications. The complexity of a web application could be a deciding factor when you’re choosing which framework to work with.

Django

Django is a robust framework that has a predefined structure with built-in functionality. The downside of its robustness, however, is that it could make the framework too complex for certain projects. It’s best suited to complex web applications that need to leverage the advanced functionality of Django.

Flask

Flask, on the other hand, is a lightweight framework for building APIs. Getting started with it is easy, and packages are available to make it robust as you go. This article will focus on defining the view functions and controller and on connecting to a database on Google Cloud and deploying to Google Cloud.

For the purpose of learning, we’ll build a Flask API with a few endpoints to manage a collection of our favorite songs. The endpoints will be for GET and POST requests: fetching and creating resources. Alongside that, we will be using the suite of services on the Google Cloud platform. We’ll set up Google’s Cloud SQL for our database and launch our app by deploying to App Engine. This tutorial is aimed at beginners who are taking their first stab at using Google Cloud for their app.

Setting Up A Flask Project

This tutorial assumes you have Python 3.x installed. If you don’t, head over to the official website to download and install it.

To check whether Python is installed, launch your command-line interface (CLI) and run the command below:

python -V

Our first step is to create the directory where our project will live. We will call it flask-app:

mkdir flask-app && cd flask-app

The first thing to do when starting a Python project is to create a virtual environment. Virtual environments isolate your working Python development. This means that this project can have its own dependencies, different from other project on your machines. venv is a module that ships with Python 3.

Let’s create a virtual environment in our flask-app directory:

python3 -m venv env

This command creates an env folder in our directory. The name (in this case, env) is an alias for the virtual environment and can be named anything.

Now that we’ve created the virtual environment, we have to tell our project to use it. To activate our virtual environment, use the following command:

source env/bin/activate

You will see that your CLI prompt now has env at the beginning, indicating that our environment is active.

Now, let’s install our Flask package:

pip install flask

Create a directory named api in our current directory. We’re creating this directory so that we have a folder where our app’s other folders will reside.

mkdir api && cd api

Next, create a main.py file, which will serve as the entry point to our app:

touch main.py

Open main.py, and enter the following code:

#main.py
from flask import Flask

app = Flask(__name__)

@app.route('/')
def home():
  return 'Hello World'

if __name__ == '__main__':
  app.run()

Let’s understand what we’ve done here. We first imported the Flask class from the Flask package. Then, we created an instance of the class and assigned it to app. Next, we created our first endpoint, which points to our app’s root. In summary, this is a view function that invokes the / route — it returns Hello World.

Let’s run the app:

python main.py

This starts our local server and serves our app on https://127.0.0.1:5000/. Input the URL in your browser, and you will see the Hello World response printed on your screen.

And voilà! Our app is up and running. The next task is to make it functional.

To call our endpoints, we will be using Postman, which is a service that helps developers test endpoints. You can download it from the official website.

Let’s make main.py return some data:

#main.py
from flask import Flask, jsonify
app = Flask(__name__)
songs = [
    {
        "title": "Rockstar",
        "artist": "Dababy",
        "genre": "rap",
    },
    {
        "title": "Say So",
        "artist": "Doja Cat",
        "genre": "Hiphop",
    },
    {
        "title": "Panini",
        "artist": "Lil Nas X",
        "genre": "Hiphop"
    }
]
@app.route('/songs')
def home():
    return jsonify(songs)

if __name__ == '__main__':
  app.run()

Here, we included a list of songs, including the song’s title and artist’s name. We then changed the root / route to /songs. This route returns the array of songs that we specified. In order to get our list as a JSON value, we JSONified the list by passing it through jsonify. Now, rather than seeing a simple Hello world, we see a list of artists when we access the https://127.0.0.1:5000/songs endpoint.

You may have noticed that after every change, we had to restart our server. To enable auto-reloading when the code changes, let’s enable the debug option. To do this, change app.run to this:

app.run(debug=True)

Next, let’s add a song using a post request to our array. First, import the request object, so that we can process incoming request from our users. We’ll later use the request object in the view function to get the user’s input in JSON.

#main.py
from flask import Flask, jsonify, request

app = Flask(__name__)
songs = [
    {
        "title": "Rockstar",
        "artist": "Dababy",
        "genre": "rap",
    },
    {
        "title": "Say So",
        "artist": "Doja Cat",
        "genre": "Hiphop",
    },
    {
        "title": "Panini",
        "artist": "Lil Nas X",
        "genre": "Hiphop"
    }
]
@app.route('/songs')
def home():
    return jsonify(songs)

@app.route('/songs', methods=['POST'])
def add_songs():
    song = request.get_json()
    songs.append(song)
    return jsonify(songs)

if __name__ == '__main__':
  app.run(debug=True)

Our add_songs view function takes a user-submitted song and appends it to our existing list of songs.

So far, we have returned our data from a Python list. This is just experimental, because in a more robust environment, our newly added data would be lost if we restarted the server. That is not feasible, so we will require a live database to store and retrieve the data. In comes Cloud SQL.

Why Use A Cloud SQL Instance?

According to the official website:

“Google Cloud SQL is a fully-managed database service that makes it easy to set-up, maintain, manage and administer your relational MySQL and PostgreSQL databases in the cloud. Hosted on Google Cloud Platform, Cloud SQL provides a database infrastructure for applications running anywhere.”

This means that we can outsource the management of a database’s infrastructure entirely to Google, at flexible pricing.

Difference Between Cloud SQL And A Self-Managed Compute Engine

On Google Cloud, we can spin up a virtual machine on Google’s Compute Engine infrastructure and install our SQL instance. This means we will be responsible for vertical scalability, replication, and a host of other configuration. With Cloud SQL, we get a lot of configuration out of the box, so we can spend more time on the code and less time setting up.

Before we begin:

1. Sign up for Google Cloud. Google offers $300 in free credit to new users.
2. Create a project. This is pretty straightforward and can be done right from the console.

Create A Cloud SQL Instance

After signing up for Google Cloud, in the left panel, scroll to the “SQL” tab and click on it.

First, we are required to choose an SQL engine. We’ll go with MySQL for this article.

Next, we’ll create an instance. By default, our instance will be created in the US, and the zone will be automatically selected for us.

Set the root password and give the instance a name, and then click the “Create” button. You can further configure the instance by clicking the “Show configuration options” dropdown. The settings allows you to configure the instance’s size, storage capacity, security, availability, backups, and more. For this article, we will go with the default settings. Not to worry, these variables can be changed later.

It might take a few minutes for the process to finish. You’ll know the instance is ready when you see a green checkmark. Click on your instance’s name to go to the details page.

Now, that we’re up and running, we will do a few things:

1. Create a database.
2. Create a new user.
3. Whitelist our IP address.

Create A Database

Navigate to the “Database” tab to create a database.

Create A New User

In the “Host name” section, set it to allow “% (any host)”.

Whitelist IP Address

You can connect to your database instance in one of two ways. A private IP address requires a virtual private cloud (VPC). If you go for this option, Google Cloud will create a Google-managed VPC and place your instance in it. For this article, we will use the public IP address, which is the default. It is public in the sense that only people whose IP addresses have been whitelisted can access the database.

To whitelist your IP address, type my ip in a Google search to get your IP. Then, go to the “Connections” tab and “Add Network”.

Connect To The Instance

Next, navigate to the “Overview” panel and connect using the cloud shell.

The command to connect to our Cloud SQL instance will be pre-typed in the console.

You may use either the root user or the user who was created earlier. In the command below, we’re saying: Connect to the flask-demo instance as the user USERNAME. You will be prompted to input the user’s password.

gcloud sql connect flask-demo --user=USERNAME

If you get an error saying that you don’t have a project ID, you can get your project’s ID by running this:

gcloud projects list

Take the project ID that was outputted from the command above, and input it into the command below, replacing PROJECT_ID with it.

gcloud config set project PROJECT_ID

Then, run the gcloud sql connect command, and we will be connected.

Run this command to see the active databases:

> show databases;

My database is named db_demo, and I’ll run the command below to use the db_demo database. You might see some other databases, such as information_schema and performance_schema. These are there to store table meta data.

> use db_demo;

Next, create a table that mirrors the list from our Flask app. Type the code below on a notepad and paste it in your cloud shell:

create table songs(
song_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255),
artist VARCHAR(255),
genre VARCHAR(255),
PRIMARY KEY(song_id)
);

This code is a SQL command that creates a table named songs, with four columns (song_id, title, artist, and genre). We’ve also instructed that the table should define song_id as a primary key and increment automatically from 1.

Now, run show tables; to confirm that the table has been created.

And just like that, we have created a database and our songs table.

Our next task is to set up Google App Engine so that we can deploy our app.

Google App Engine

App Engine is a fully managed platform for developing and hosting web applications at scale. An advantage of deploying to App Engine is that it enables an app to scale automatically to meet incoming traffic.

The App Engine website says:

“With zero server management and zero configuration deployments, developers can focus only on building great applications without the management overhead.”

Set Up App Engine

There are a few ways to set up App Engine: through the UI of Google Cloud Console or through the Google Cloud SDK. We will use the SDK for this section. It enables us to deploy, manage, and monitor our Google Cloud instance from our local machine.

Install Google Cloud SDK

Follow the instructions to download and install the SDK for Mac or Windows. The guide will also show you how to initialize the SDK in your CLI and how to pick a Google Cloud project.

Now that the SDK has been installed, we’re going to go update our Python script with our database’s credentials and deploy to App Engine.

Local Setup

In our local environment, we are going to update the setup to suit our new architecture, which includes Cloud SQL and App Engine.

First, add an app.yaml file to our root folder. This is a configuration file that App Engine requires to host and run our app. It tells App Engine of our runtime and other variables that might be required. For our app, we will need to add our database’s credentials as environment variables, so that App Engine is aware of our database’s instance.

In the app.yaml file, add the snippet below. You will have gotten the runtime and database variables from setting up the database. Replace the values with the username, password, database name, and connection name that you used when setting up Cloud SQL.

#app.yaml
runtime: python37

env_variables:
  CLOUD_SQL_USERNAME: YOUR-DB-USERNAME
  CLOUD_SQL_PASSWORD: YOUR-DB-PASSWORD
  CLOUD_SQL_DATABASE_NAME: YOUR-DB-NAME
  CLOUD_SQL_CONNECTION_NAME: YOUR-CONN-NAME

Now, we are going to install PyMySQL. This is a Python MySQL package that connects and performs queries on a MySQL database. Install the PyMySQL package by running this line in your CLI:

pip install pymysql

At this point, we are ready to use PyMySQL to connect to our Cloud SQL database from the app. This will enable us to get and insert queries in our database.

Initialize Database Connector

First, create a db.py file in our root folder, and add the code below:

#db.py
import os
import pymysql
from flask import jsonify

db_user = os.environ.get('CLOUD_SQL_USERNAME')
db_password = os.environ.get('CLOUD_SQL_PASSWORD')
db_name = os.environ.get('CLOUD_SQL_DATABASE_NAME')
db_connection_name = os.environ.get('CLOUD_SQL_CONNECTION_NAME')


def open_connection():
    unix_socket = '/cloudsql/{}'.format(db_connection_name)
    try:
        if os.environ.get('GAE_ENV') == 'standard':
            conn = pymysql.connect(user=db_user, password=db_password,
                                unix_socket=unix_socket, db=db_name,
                                cursorclass=pymysql.cursors.DictCursor
                                )
    except pymysql.MySQLError as e:
        print(e)

    return conn


def get_songs():
    conn = open_connection()
    with conn.cursor() as cursor:
        result = cursor.execute('SELECT * FROM songs;')
        songs = cursor.fetchall()
        if result > 0:
            got_songs = jsonify(songs)
        else:
            got_songs = 'No Songs in DB'
    conn.close()
    return got_songs

def add_songs(song):
    conn = open_connection()
    with conn.cursor() as cursor:
        cursor.execute('INSERT INTO songs (title, artist, genre) VALUES(%s, %s, %s)', (song["title"], song["artist"], song["genre"]))
    conn.commit()
    conn.close()

We did a few things here.

First, we retrieved our database credentials from the app.yaml file using the os.environ.get method. App Engine is able to make environment variables that are defined in app.yaml available in the app.

Secondly, we created an open_connection function. It connects to our MySQL database with the credentials.

Thirdly, we added two functions: get_songs and add_songs. The first initiates a connection to the database by calling the open_connection function. It then queries the songs table for every row and, if empty, returns “No Songs in DB”. The add_songs function inserts a new record into the songs table.

Finally, we return to where we started, our main.py file. Now, instead of getting our songs from an object, as we did earlier, we call the add_songs function to insert a record, and we call the get_songs function to retrieve the records from the database.

Let’s refactor main.py:

#main.py
from flask import Flask, jsonify, request
from db import get_songs, add_songs

app = Flask(__name__)

@app.route('/', methods=['POST', 'GET'])
def songs():
    if request.method == 'POST':
        if not request.is_json:
            return jsonify({"msg": "Missing JSON in request"}), 400  

        add_songs(request.get_json())
        return 'Song Added'

    return get_songs()    

if __name__ == '__main__':
    app.run()

We imported the get_songs and add_songs functions and called them in our songs() view function. If we are making a post request, we call the add_songs function, and if we are making a get request, we call the get_songs function.

And our app is done.

Next up is adding a requirements.txt file. This file contains a list of packages necessary to run the app. App Engine checks this file and installs the listed packages.

pip freeze | grep "Flask\|PyMySQL" > requirements.txt

This line gets the two packages that we are using for the app (Flask and PyMySQL), creates a requirements.txt file, and appends the packages and their versions to the file.

At this point, we have added three new files: db.py, app.yaml, and requirements.txt.

Deploy To Google App Engine

Run the following command to deploy your app:

gcloud app deploy

If it went well, your console will output this:

Your app is now running on App Engine. To see it in the browser, run gcloud app browse in your CLI.

We can launch Postman to test our post and get requests.

Our app is now hosted on Google’s infrastructure, and we can tweak the configuration to get all of the benefits of a serverless architecture. Going forward, you can build on this article to make your serverless application more robust.

Conclusion

Using a platform-as-a-service (PaaS) infrastructure like App Engine and Cloud SQL basically abstracts away the infrastructure level and enables us to build more quickly. As developers, we do not have to worry about configuration, backing up and restoring, the operating system, auto-scaling, firewalls, migrating traffic, and so on. However, if you need control over the underlying configuration, then it might be better to use a custom-built service.

References

• “Download Python”
• “venv — Creation of Virtual Environments”, Python (documentation)
• “Download Postman”
• “Cloud SQL”, Google Cloud
• Google Cloud
• “Google Cloud Free Tier”, Google Cloud
• “Creating and Managing Projects”, Google Cloud
• “VPC Overview” (virtual private cloud), Google Cloud
• “App Engine”, Google Cloud
• “Quickstarts” (download Google Cloud SDK), Google Cloud
• PyMySQL documentation

Further Reading

• Penpot’s CSS Grid Layout: Designing With Superpowers
• Uniting Web And Native Apps With 4 Unknown JavaScript APIs
• The Era Of Platform Primitives Is Finally Here
• How To Build A Multilingual Website With Nuxt.js

(ks, ra, al, il, mrn)