Connecting a Flask App to Supabase

Welcome to Part 2 of the Flask Series.

If you have been following along, you'd have a simple API in Flask which returns your favorite games. If you haven't, simply clone this GitHub repository and install flask and dotenv packages.

What we did the last time, was displaying a list of our favorite games from a list in a separate games file, which isn't really the ideal thing to do. In this thread, let's connect our application to a PostgreSQL database.

Prerequisites

Code from Part 1
A Supabase Account
Postman (or any REST Client)

Before we begin with Python Code, I'd like to take you through Supabase.

What is Supabase?

Supabase is an Open Source Backend as a Service. It provides a Postgres Database on the fly, it has a very easy-to-use UI and it provides its PostgREST API and client-libraries on various languages (thanks to the Community support) for easy database operations.

Installing pgadmin and setting up a database in that and then connecting to it, is a PAIN. Let's just do it the easy way. Click here, login with GitHub and create a new Supabase project.

Supabase Setup

Choose your default organization (already created for you), enter a name and a secure password for your project. Then select the region which is closest to you for less delay.

And here we are. In less than 5 minutes, we have our Postgres database set up. Now, click on Table Editor and then Click on Create New Table.

Create a new 'games' table. You can add columns/attributes to this table using the Add Column button. Finally, save this.

Great! Now our table is ready. All we need to do here now, is to add some rows to this table. To do this, simply click on Insert Row.

You just have to enter the name of the game and the no. of hours you've played it. I'll just add 4-5 of these.

Here's what the table looks like after a few entries and now you know I game a lot XD

Setting up environment variables

Add environment variables to your .flaskenv file

You can find these in Supabase settings => API

Using the supabase_py module

The python supabase-py module (and many other libraries for different languages) was created (and is still in development) by the awesome Supabase Community. You can find the docs for this module here.

Install this module in your virtual env using pip -

python -m pip install supabase_py

Let's go ahead and connect our app to Supabase! Create a new file store.py and import supabase. Then, create a supabase Client variable which we can use for our database operations.

import os
from supabase import create_client, Client

url: str = os.environ.get("SUPABASE_URL")
key: str = os.environ.get("SUPABASE_ANON_KEY")

supabase: Client = create_client(url, key)

Fetching Games Data from the Database

Next up, in the same file, simply create a new function which returns data of all games from our database.

# Function to Fetch All Games
def find_all_games():
    data = supabase.table("games").select("*").execute()
    # Equivalent for SQL Query "SELECT * FROM games;"
    return data['data']

games = find_all_games()

You can go ahead and print this on the console to see what it looks like -

Returning data from the API

In app.py, import the games variable from the store instead of our hardcoded games file (you can actually go ahead and delete that, we aren't using it anymore).

#from games import games   ## Delete this line
from store import games

That's pretty much it. Now run the app using -

flask run

If you now go to the route, you'll see a list of games show up, exactly what we added in our database!

/games

Our API has dynamic data coming from a database!

Function to Add a New game to the Database

In store.py, let's create a new function to add a new game to the database. We just take in title and hours as the parameters and insert it into the database

# Function to add a new game
def add_game_to_DB(title, hours) -> dict:

    game = {
        "title": title,
        "hours": hours
    }
    data = supabase.table("games").insert(game).execute()
    # Equivalent to the SQL Insert

    return data['data']

A new route for Adding a New Game

Now here's where things get a little interesting. To send data over the API, we need to send a POST Request to the API. To do that, we need to create a new endpoint which accepts POST requests and inserts this data into the database.

Before we create a new route, let's just import request and Response to deal with the request data that we pass in. We'll also import the new function we just created.

from flask import request, Response
from store import add_game_to_DB

We will now create a route which takes a POST request, grabs the data from the request body and adds the data to the database.

@app.route('/games/add', methods=['POST'])
def add_game():
    data = request.get_json()
    try:
        title = data['title']
        hours = data['hours']
        if title and hours:
            data = add_game_to_DB(title, int(hours))
            return jsonify(data), 201
    except:
        return Response('''{"message": "Bad Request"}''', status=400, mimetype='application/json')

You can identify a few changes here. We add a methods parameter to the route method, specifying that the route only responds to POST requests.

We are also doing some data validation here, hence the try-except blocks. So if any of the values for hours or title are missing, we return a 400-Bad Request in the response.

If everything is good, we call the add_game_to_DB function and send it the game details. We return the data we get as our response.

Testing the add_game route

To put this to test, open up your REST Client. I'm using Postman in this case.

Create a New Request and add the game details like so -

Click on Send and you should see a response like this -

We can go back to Supabase and check if the data was inserted correctly.

There you go, we now have a fully functional API where we get dynamic data from our database and also add to it. I hope you had a good time working with Flask

I also hope you enjoyed working with Supabase. Trust me there's a lot more you can explore :)

That's it for this thread folks, next up, we will deploy our application to Heroku. I'm sure that'll be exciting! See you then, thanks for reading!

Connecting a Flask App to Supabase

Comments (1)

Flask API - Dev to Deployment

Deploying a Flask Application to Heroku

More from this blog

Simplifying Reinforcement Learning Terminologies - AWS DeepRacer Edition

Setting up a REST API in Python for DynamoDB

Integrated AWS Workflows with SageMaker Notebooks

How AWS Cloud9 has become my side project buddy

The Linux Backdoor Saga

Prerequisites

What is Supabase?

Supabase Setup

Setting up environment variables

Using the supabase_py module

Fetching Games Data from the Database

Returning data from the API

Function to Add a New game to the Database

A new route for Adding a New Game

Testing the add_game route

Command Palette

Comments (1)

Flask API - Dev to Deployment

Deploying a Flask Application to Heroku

More from this blog

Prerequisites

What is Supabase?

Supabase Setup

Setting up environment variables

Using the supabase_py module

Fetching Games Data from the Database

Returning data from the API

Function to Add a New game to the Database

A new route for Adding a New Game

Testing the add_game route