Build your first REST API with Flask and PostgreSQL (2023)

REST APIs are everywhere. Behind almost every web and mobile app, there's a REST API powering database interactions, shared functionality with other clients, and more.

In this post, we'll show you how to make your own REST API using Flask, to store home automation data in PostgreSQL and have access to it from anywhere!

There are many alternatives to Flask and REST API development, including Django, Quart, and FastAPI, among others. They each have their pros and cons. We've compared Flask and Django before in this blog.

The goal of the project

The REST API we'll build will help you store home automation data. Specifically, the temperature of different rooms in your house. Even if you don't use home automation, learning how to do this will show you many useful things, such as:

• How to write a REST API that receives data from clients.
• How to save data into PostgreSQL.
• How to read data and query it to retrieve exactly what you need using PostgreSQL.
• How to store secrets your REST API uses, but you don't want to share with others.

REST APIs are organized with endpoints: addresses to which clients can send requests. With each request, clients can include data. The REST API can then use that data and store it in the database, or return some other data back to the client.

Here are the endpoints our REST API will have:

• POST /api/room will let clients send us the room name, and we will create a room in the database against which we can store temperatures.
• POST /api/temperature will let clients send us the room and the temperature, and we'll store it in the database after doing some formatting.
• GET /api/room/<int:room_id> will let clients send us a room identifier, and we'll return the average temperature for a room since the room was created. Alternatively, clients will be able to specify a term (in days) and we'll respond appropriately.
• GET /api/average will let clients request the average temperature across all rooms, since we started gathering data.

As you can see, there aren't many endpoints. The Python code will fit in a single file!

Let's get started 💪

How to install Flask for a Python project

Whenever you start a Python project, you'll likely want to use a virtual environment. If you need a refresher, we've got our blog post, Working with Python virtual environments: the complete guide that tells you everything you need!

Let's create our virtual environment and install Flask:

python -m venv .venvsource .venv/bin/activate # different in windowspip install flask

How to run the Flask app and connect to the database

Let's begin writing our Flask code! I'll create a file called app.py:

from flask import Flaskapp = Flask(__name__)

This is the most basic Flask app you can write. It doesn't do anything!

But it does run, if you go to the console and type:

flask run

You should see some output that looks like this:

 * Environment: production WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. * Debug mode: off * Running on http://127.0.0.1:5000 (Press CTRL+C to quit)

That's Flask running! Now it's wired to be able to accept requests coming from clients, and do something about it.

Two issues:

1. We're using the "production" mode, which is not recommended. Using "development" mode makes things a bit easier.
2. We haven't told it what to do with incoming requests!

Let's fix those one at a time.

How to start a Flask app in 'development' mode

Let's create a file called .flaskenv in our project, beside app.py.

In it, write these two lines:

FLASK_APP=appFLASK_DEBUG=1

These are environment variable style arguments. Our Flask app will be able to read them, and configure itself accordingly. But first, we need to install one more dependency:

pip install python-dotenv

Now if we do:

flask run

You should see this output:

 * Serving Flask app 'app' (lazy loading) * Environment: development * Debug mode: on * Running on http://127.0.0.1:5000 (Press CTRL+C to quit) * Restarting with stat * Debugger is active! * Debugger PIN: 145-515-717

Brilliant! We're ready to continue onto step 2: telling the app what to do with incoming requests!

But first, let's create our PostgreSQL database. For easy-to-create development database, I often use ElephantSQL. It's a great PostgreSQL provider, and you can get free small databases from them.

An alternative to using ElephantSQL is to install PostgreSQL locally in your computer, but that's a bit more work.

So create an ElephantSQL account if you don't have one already, and create a "Tiny Turtle (free)" database. Choose a region for the database that is physically close to you, so that requests are faster.

Now that you have created a database, grab the URL:

To access the database URL in our code, let's add this to a new file called .env:

DATABASE_URL=your_url

Next, install the Flask library that allows us to connect to PostgreSQL databases, psycopg2:

pip install psycopg2-binary

Then we can go into app.py and connect to the database:

import osimport psycopg2from dotenv import load_dotenvfrom flask import Flaskload_dotenv() # loads variables from .env file into environmentapp = Flask(__name__)url = os.environ.get("DATABASE_URL") # gets variables from environmentconnection = psycopg2.connect(url)

With this, we're ready to start querying the database! Let's work on creating new rooms first.

How to create a PostgreSQL table using Flask and insert data

There are two queries we'll need:

• One to create the table if it doesn't already exist. We'll need to know which columns we want the table to have.
• One to insert a row into the table. For this we'll need the data we want to insert in the row.

To create the table we'll use this query:

CREATE TABLE IF NOT EXISTS rooms (id SERIAL PRIMARY KEY, name TEXT);

And to insert data we'll use:

INSERT INTO rooms (name) VALUES (%s) RETURNING id;

We'll get this query to return the id column that was inserted, so that we can send it back to the client of our API. That way they can use the id in subsequent requests to insert temperatures related to the new room.

Let's save the two queries as constants in our file. Put these at the top, after the imports.

CREATE_ROOMS_TABLE = ( "CREATE TABLE IF NOT EXISTS rooms (id SERIAL PRIMARY KEY, name TEXT);")INSERT_ROOM_RETURN_ID = "INSERT INTO rooms (name) VALUES (%s) RETURNING id;"

Now let's tell the app how to handle incoming requests.

When a client makes a request to the /api/room endpoint, we will expect the room name to be sent to us. We will then insert a row into the table and return the new room id:

from flask import Flask, request...@app.post("/api/room")def create_room(): data = request.get_json() name = data["name"] with connection: with connection.cursor() as cursor: cursor.execute(CREATE_ROOMS_TABLE) cursor.execute(INSERT_ROOM_RETURN_ID, (name,)) room_id = cursor.fetchone()[0] return {"id": room_id, "message": f"Room {name} created."}, 201

A few new things here!

• We tell Flask what endpoint to accept data in using a decorator. You can read this series to learn more about decorators.
• We expect the client to send us JSON data, which we retrieve from the incoming request using request.get_json().
• We connect to the database and use a cursor to interact with it. Here we use context managers so we don't have to remember to close the connection manually.
• We create the table (since it only runs IF NOT EXISTS), and insert the record.
• We get the result of running our query, which should be the inserted row id.
• We return a Python dictionary, which Flask conveniently converts to JSON.
• The return status code is 201, which means "Created". It's a way for our API to tell the client succinctly the status of the request.

Next let's work on adding new temperature readings to the newly created room. For this, we'd expect the client to send a request that contains the temperature reading and the room id.

Then we would:

1. Create the temperature readings table, with its 3 columns (room id, temperature reading, and optional date).
2. If the date is provided, use it. Otherwise use the current date.
3. Insert the temperature reading into the table.
4. Return a success message to the client.

Let's define the SQL queries that we would use for this. First, to create the temperature readings table:

CREATE TABLE IF NOT EXISTS temperatures (room_id INTEGER, temperature REAL, date TIMESTAMP, FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE);

This uses a FOREIGN KEY constraint to link the table to the rooms table. All this does is ensure referential integrity (i.e. can't enter a room_id for a room that doesn't exist). Also using ON DELETE CASCADE means that if we delete a room, all its referenced temperatures will be deleted too.

To insert temperatures:

INSERT INTO temperatures (room_id, temperature, date) VALUES (%s, %s, %s);

Add these as constants too. This is what my constants section now looks like:

CREATE_ROOMS_TABLE = ( "CREATE TABLE IF NOT EXISTS rooms (id SERIAL PRIMARY KEY, name TEXT);")CREATE_TEMPS_TABLE = """CREATE TABLE IF NOT EXISTS temperatures (room_id INTEGER, temperature REAL, date TIMESTAMP, FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE);"""INSERT_ROOM_RETURN_ID = "INSERT INTO rooms (name) VALUES (%s) RETURNING id;"INSERT_TEMP = ( "INSERT INTO temperatures (room_id, temperature, date) VALUES (%s, %s, %s);")

Next, let's define our endpoint. It's similar to the one to create room, but since the incoming date is optional we need to use today's date if not provided:

@app.post("/api/temperature")def add_temp(): data = request.get_json() temperature = data["temperature"] room_id = data["room"] try: date = datetime.strptime(data["date"], "%m-%d-%Y %H:%M:%S") except KeyError: date = datetime.now(timezone.utc) with connection: with connection.cursor() as cursor: cursor.execute(CREATE_TEMPS_TABLE) cursor.execute(INSERT_TEMP, (room_id, temperature, date)) return {"message": "Temperature added."}, 201

Now that we've got our endpoints to create rooms and insert data, let's move onto reading data from the database!

How to retrieve data from PostgreSQL for your REST API

The simplest endpoint that retrieves data from PostgreSQL is our global average endpoint, /api/average.

This endpoint will calculate and return the average of all temperature readings in the temperatures table, with this query:

SELECT AVG(temperature) as average FROM temperatures;

However, it would also be interesting to return to our clients the number of days that this average is based on. To calculate how many different days we have stored data for, we'll use this query:

SELECT COUNT(DISTINCT DATE(date)) AS days FROM temperatures;

Here, we use DATE(date) to turn the date column into a PostgreSQL DATE. Then when we use DISTINCT with that, it selects only the different individual dates. If we didn't do this, since we store hours, minutes, and seconds in our table, every row would be different even if the date is the same (since the times would differ).

Let's store these two queries as constants:

GLOBAL_NUMBER_OF_DAYS = ( """SELECT COUNT(DISTINCT DATE(date)) AS days FROM temperatures;""")GLOBAL_AVG = """SELECT AVG(temperature) as average FROM temperatures;"""

And then we can define the endpoint that calls the two queries:

@app.get("/api/average")def get_global_avg(): with connection: with connection.cursor() as cursor: cursor.execute(GLOBAL_AVG) average = cursor.fetchone()[0] cursor.execute(GLOBAL_NUMBER_OF_DAYS) days = cursor.fetchone()[0] return {"average": round(average, 2), "days": days}

This endpoint doesn't require any data from the client, and returns the average temperature and the number of different days that it was calculated from.

Note that this endpoint doesn't return a status code. The default status code is 200, which means "OK". It's a suitable status code for this type of response!

How to search for data from PostgreSQL for your REST API

For our next endpoint, the client will send us the room_id they want to get data for, and we'll respond with:

• The room name.
• The room's all-time-average temperature.
• How many days the average is calculated from.

So this is similar to the global average, but for a specific room!

We'll use three queries, one for each data point. To get the room's name:

SELECT name FROM rooms WHERE id = (%s)

To get the all time average for a room:

SELECT AVG(temperature) as average FROM temperatures WHERE room_id = (%s);

And to calculate how many days of data are stored for the room:

SELECT COUNT(DISTINCT DATE(date)) AS days FROM temperatures WHERE room_id = (%s);

Let's add these as constants to app.py:

ROOM_NAME = """SELECT name FROM rooms WHERE id = (%s)"""ROOM_NUMBER_OF_DAYS = """SELECT COUNT(DISTINCT DATE(date)) AS days FROM temperatures WHERE room_id = (%s);"""ROOM_ALL_TIME_AVG = ( "SELECT AVG(temperature) as average FROM temperatures WHERE room_id = (%s);")

Then let's define our endpoint, /api/room/<int:room_id>. Here we use a dynamic URL segment so that the user can include the room ID in the URL, such as /api/room/2:

@app.get("/api/room/<int:room_id>")def get_room_all(room_id): with connection: with connection.cursor() as cursor: cursor.execute(ROOM_NAME, (room_id,)) name = cursor.fetchone()[0] cursor.execute(ROOM_ALL_TIME_AVG, (room_id,)) average = cursor.fetchone()[0] cursor.execute(ROOM_NUMBER_OF_DAYS, (room_id,)) days = cursor.fetchone()[0] return {"name": name, "average": round(average, 2), "days": days}

Calculate the average value in a date range with PostgreSQL

So far, all our data-fetching endpoints have worked on all database records. As such, they could be including very old data. I think that often, clients may want to only look at the last 30 days or the last 7 days. Let's add functionality for terms, lengths of time that a client can request.

In the /api/room<int:room_id> endpoint, we'll optionally accept a term query string parameter^[1]. This term will have an associated value, which is either "week" or "month". If the value is "week", we'll retrieve data from the last 7 days. If it's "month", from the last 30 days.

To do this we'll need a database query that can take in the number of days, and retrieve the appropriate data:

SELECT DATE(temperatures.date) as reading_date, AVG(temperatures.temperature)FROM temperaturesWHERE temperatures.room_id = (%s)GROUP BY reading_dateHAVING DATE(temperatures.date) > (SELECT MAX(DATE(temperatures.date))-(%s) FROM temperatures);

This is a rather complex query, because it uses a subquery to only fetch records that have a date row with value greater than the latest row in the database, minus the number of days in our requested term.

This query will also return one row per date in the table, together with the average temperature for each date. This is different from previous queries, where we retrieved the final average from the database. With this data, we still need to do a little bit of processing in our Python code to calculate the average temperature for the term.

The code below is what would get the room name and the temperatures per day, for a 7-day term. Then it calculates the average for the whole term by adding up the temperature each day and dividing it by the number of days returned. Note that we can't divide by term, because it's possible the database will not have a temperature reading for each day. Doing it this way we make sure that the average is calculated based on the data we actually have.

with connection: term = 7 with connection.cursor() as cursor: cursor.execute(ROOM_NAME, (room_id,)) name = cursor.fetchone()[0] cursor.execute(ROOM_TERM, (room_id, term)) dates_temperatures = cursor.fetchall()average = sum(day[1] for day in dates_temperatures) / len(dates_temperatures)

Let's define a function that takes the room_id and the term, and runs this code.

def get_room_term(room_id, term): terms = {"week": 7, "month": 30} with connection: with connection.cursor() as cursor: cursor.execute(ROOM_NAME, (room_id,)) name = cursor.fetchone()[0] cursor.execute(ROOM_TERM, (room_id, terms[term])) dates_temperatures = cursor.fetchall() average = sum(day[1] for day in dates_temperatures) / len(dates_temperatures) return { "name": name, "temperatures": dates_temperatures, "average": round(average, 2), }

The term parameter could contain the string "week" or "month", and we then use the terms dictionary to get the appropriate value.

We can then call this get_room_term() function in our endpoint:

@app.get("/api/room/<int:room_id>")def get_room_all(room_id): term = request.args.get("term") if term is not None: return get_room_term(room_id, term) else: with connection: with connection.cursor() as cursor: cursor.execute(ROOM_NAME, (room_id,)) name = cursor.fetchone()[0] cursor.execute(ROOM_ALL_TIME_AVG, (room_id,)) average = cursor.fetchone()[0] cursor.execute(ROOM_NUMBER_OF_DAYS, (room_id,)) days = cursor.fetchone()[0] return {"name": name, "average": round(average, 2), "days": days}

And with this, we're finished creating our API! You can see the finished API code here: https://github.com/tecladocode/rooms-temp-rest-api.

What's next?

While developing your REST APIs, you should be continuously testing it! It's relatively straightforward to test your APIs using tools like Postman or Insomnia REST.

We cover all this and much more in our complete course, REST APIs with Flask and Python. If you want to dive deeper and build professional-grade REST APIs, please consider enrolling! Use this link for the best price available.

If you want to share your REST API with your users, you'll want to deploy it to a publicly-available server. There are many free options for this, such as Render.com (my recommendation) or Heroku.

In order to deploy, you'll need to host your code in GitHub, so you should also learn a little bit about that!

Like I mentioned above, all this and more is covered in our complete course!