Setting up Google Cloud SQL with Flask

Setting up a database can be very tricky, yet some pretty simple and scalable solutions are available and one such solution is Google Cloud SQL. Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, and administer your relational PostgreSQL and MySQL databases in the cloud. Setting it up can be both tricky and simple, confused? I am here to clear the confusion.

First of all, you need a Google Cloud Platform (GCP) account. If you don’t want one, then create an account by going to this link here. You will also get $300 free credits for one year when you signup. You also need to set up your billing account to be able to use some of the GCP services which include Cloud SQL. Don’t worry you won’t get charged for this tutorial if you have free credits.

Now, you need to enable Cloud SQL Admin API from the marketplace. You can do that by click on this link here.
Now, let’s jump into our GCP dashboard and then search for Cloud SQL. Your CLoud SQL page should look something like this.

Now, click on CREATE INSTANCE and then for this tutorial we would be selecting the MySQL option. Now, you should be greeted with the following page.

Now, fill up the details. It is always advised to generate the password because it creates a random string and note the password because you can not retrieve it if you forget it and also change the region to your nearest one geographically and then click on create. That’s all. Now, the next page should look something like this.

Now, you first need to create a database. To do that simply, click on the Databases option on the navigation bar at the left and then click on Create database and then provide the name of your database. For this tutorial, our database name will be testing. After you have done that, you should see your database listed there. That all for the setup part. Now let’s actually see how can connect to this database from your flask app.

Obviously, you will be needing Python. In this tutorial, we are currently on the latest python version that is 3.8
You also need to install flask, flask-sqlalchemy and mysqlclient. To install those, simply run the following command in your terminal.

pip3 install Flask Flask-SQLAlchemy mysqlclient

Note: If you are on a Linux machine, then your need to have libmysqlclient-dev installed before you install mysqlclient. To install it simply run sudo apt-get install libmysqlclient-dev in your terminal.

With all your pre-requisites done, let’s actually jump right into the code. For simplicity purposes, I will be writing all the code in a single file but as you know, if you are planning on building a mid-size application even, then you need to have separate files for routes, models, etc.
Here, the entire code will be written inside a single file called app.py. I have added explanations for every line in my code in the form of inline comments.

Python3

# imports
from flask import Flask, request, make_response
from flask_sqlalchemy import SQLAlchemy

# initializing Flask app
app = Flask(__name__)

# Google Cloud SQL (change this accordingly)
PASSWORD ="your database password"
PUBLIC_IP_ADDRESS ="public ip of database"
DBNAME ="database name"
PROJECT_ID ="gcp project id"
INSTANCE_NAME ="instance name"

# configuration
app.config["SECRET_KEY"] = "yoursecretkey"
app.config["SQLALCHEMY_DATABASE_URI"]= f"mysql + mysqldb://root:{PASSWORD}@{PUBLIC_IP_ADDRESS}/{DBNAME}?unix_socket =/cloudsql/{PROJECT_ID}:{INSTANCE_NAME}"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"]= True

db = SQLAlchemy(app)

# User ORM for SQLAlchemy
class Users(db.Model):
    id = db.Column(db.Integer, primary_key = True, nullable = False)
    name = db.Column(db.String(50), nullable = False)
    email = db.Column(db.String(50), nullable = False, unique = True)

@app.route('/add', methods =['POST'])
def add():
    # getting name and email
    name = request.form.get('name')
    email = request.form.get('email')

    # checking if user already exists
    user = Users.query.filter_by(email = email).first()

    if not user:
        try:
            # creating Users object
            user = Users(
                name = name,
                email = email
            )
            # adding the fields to users table
            db.session.add(user)
            db.session.commit()
            # response
            responseObject = {
                'status' : 'success',
                'message': 'Successfully registered.'
            }

            return make_response(responseObject, 200)
        except:
            responseObject = {
                'status' : 'fail',
                'message': 'Some error occurred !!'
            }

            return make_response(responseObject, 400)

    else:
        # if user already exists then send status as fail
        responseObject = {
            'status' : 'fail',
            'message': 'User already exists !!'
        }

        return make_response(responseObject, 403)

@app.route('/view')
def view():
    # fetches all the users
    users = Users.query.all()
    # response list consisting user details
    response = list()

    for user in users:
        response.append({
            "name" : user.name,
            "email": user.email
        })

    return make_response({
        'status' : 'success',
        'message': response
    }, 200)


if __name__ == "__main__":
    # serving the app directly
    app.run()

You need to change the following placeholders in the code 

• PASSWORD: the password you set for your database while creating the instance
• PUBLIC_IP_ADDRESS: your GCP instances public IP (can be found in the overview page)
• DBNAME: name of the database that you created later on (for this tutorial it was ‘testing’)
• PROJECT_ID: your GCP project ID
• INSTANCE_NAME: you Cloud SQL instance name

Your code is ready but it still can’t access your database. This is where most people get it wrong. They keep on checking the documentations and their codebase but they find no mistake and they get completely frustrated and give up, but I hope you are not the one to give up, right? So, let us see what is left to be done.

The reason why your database isn’t accessible by your code is that GCP by default blocks all incoming connections from unknown sources (for security purposes). So, what you need to do right now, is to add your systems public IP address to the Authorised Network. To do that, first, go to your Cloud SQL instance page and click on the edit button. You should see a page like this

Here, click on the Add network button under public IP. There, you need to enter your Public IP address. If you don’t know your Public IP then go to this link. After you have entered your Public IP, you are good to go.
Now, your app should be able to connect to your database from your system. Now, open up your terminal inside your project directory and type python inside it. This should open up the python interpreter. Now, simply type the following lines in it to create your table from the ORM.

Python3

from app import db
db.create_all()

That’s all. Now you are ready to test out your app. Go ahead and using any API request tool to check if it works or not. You can use the famous Postman to test it out.
The ability to host your database on an external service is very crucial for student developers, otherwise, it is impossible to showcase your projects.