# 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](https://github.com/sreekeshiyer/flask-games-api) 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
1. Code from Part 1
1. A [Supabase](https://supabase.io) Account
1. [Postman](https://www.postman.com/downloads/) (or any REST Client)

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

### What is Supabase? 

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637853969027/H34AFG6jD.png)
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](https://app.supabase.io/), login with *GitHub* and create a new Supabase project.
![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637854081747/am7YcKSeP.png)

### 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.

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637854254554/0XKw7hUSD.png)

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*. 

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637854472105/Bbxy-90fJ.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637854821419/vhXVEjwXP.png)
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**.


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637855218049/EEMbR6lmE.png)
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. 


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637855453453/fc89cJwL2.png)
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
![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637863248579/fREqJ-w81.png)

You can find these in Supabase settings => API
![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637856225918/7h_G_uKBV.png)

### 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](https://github.com/supabase-community/supabase-py).

Install this module in your virtual env using pip - 
```bash
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.

```python
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.

```python
# 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 - 


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637857770094/fTPQpHwC7w.png)

### 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).

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

That's pretty much it. Now run the app using -
```bash
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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637858309957/D5aT_m_ja.png)

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

```python
# 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.
```python
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. 

```python
@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 -  

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637866350465/oIBDvilko.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637866386228/9JpBHouXH.png)

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

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1637866482608/SswJ6dV9d.png)

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!