RESTful API Design with Flask and SQLAlchemy

Providing public or even private RESTful API services seems relatively painless when you first conceive the idea, but when you begin to dig deep and truly consider the possibilities, it can become overwhelming. In this first installation, I’ll attempt to demystify the complexities of RESTful web services by providing concrete design principles for manipulating database data.

I’d like to point out that this tutorial assumes familiarity with Flask, a micro-framework for the Python programming language, as well as SQLAlchemy, Python’s most highly regarded ORM layer.

One of the most critical parts of any RESTful API design is that your core components work well together. If you start gluing components together because they don’t cooperate as expected, that’s a telltale sign you’re headed down the wrong path. We’ve chosen Flask and SQLAlchemy because they are lightweight and easy to hit the ground running. SQLAlchemy takes minor configuration to get connected, but after that, manipulating models is a cakewalk. The last Python component we will be using is flask-restful, a layer on top of Flask that simplifies handling of REST HTTP requests. I will not be walking you through how to setup Flask or SQLAlchemy, but rather help you understand basic REST design principles with the help of Python and friends.

If you’ve noticed, we’re not complicating our end goal by polluting our API with unnecessary components. It may be true that you need additional middleware layers for proper authentication, but authorizing model requests will be easily handled in each of our REST endpoints, as you will soon see.

In this installation, we are going to design an API for accessing and deleting customer orders. Each order also includes one or more order items. The goal of this installation is to familiarize you with how similar database data relates to RESTful endpoints when designed correctly.

Our endpoint hierarchy:

/orders

/orders/[order_id]

/orders/[order_id]/items

/orders/[order_id]/items/[order_item_id]

If you look closely, you will notice that we are nesting the endpoints based on their relationship. This gives our code a natural schema for authorizing access to database models. If we follow this structure throughout our entire API, understanding how data is accessed will be understood at a glance. And that’s how we eliminate the enormous headache of understanding which request gets access to what. It’s intuitive, as will be our directory and file structure.

When we eliminate the need to consider how every endpoint works, our mind is freed up to reason about things that matter. The more consistency you have, the less documentation you need to write in order to understand the codebase, and naturally, the cleaner the codebase becomes. Consistency is the key to a good codebase. Without it, you may as well hand your keyboard to a pigeon.

Now that we have our endpoints in mind, our directory and file structure should mimic them. Assuming you have Flask and SQLAlchemy already set up, here’s our directory layout:

+-- project_dir
| +-- app.py
| +-- db.py
| +-- serve.py
| +-- endpoints/
| | +-- api.py
| | +-- orders/
| | | +-- api.py
| | | +-- routes.py
| | | +-- items/
| | | | +-- api.py
| | | | +-- routes.py
| +-- model/
| | +-- order.py
| | +-- order_item.py

We now have a high-level view of our structure, and I can walk you through the functionality of each file and how it relates to REST.

Inside each API directory (orders and orders/items), you will notice a handful of common files. They all have a purpose that needs to be adhered to in order to make effective use of the authorization schema under which we assume each request is operating.

Let’s start with endpoints/api.py. The purpose of this file is to define a parent class for all API requests within and below the directory in which it resides. The reason we do this is because when we align our file structure with the representation of the REST endpoints, and then we tack on an ORM for accessing the data, they all play nicely together, and the hierarchy is applied the same within each layer. This will become more evident once we see the model API implementations.

We may have the following code that acts as a controller for all child API requests. All direct handling of HTTP request methods is provided in this class, and additional but optional functions will be applied at a later point.

Behold our endpoints/api.py:

from ..db import db_connect

from flask_restful import Resource
from http import client

class Api(Resource):

The above code may need some explaining since it seems rather vague at this point. All arguments passed into a request function such as “get”, “delete”, etc, are all validated by the route system which I have not yet covered. These arguments are not query string or encoded parameters from the request, but rather always passed in as part of the request path, and thus are fully validated. Without validation passing, the route is never executed, and a 404 will be returned. Flask makes guarantees about route validation that you can and should depend on.

Furthermore, we have designed a basic method that retrieves the entire database query needed for any request, be it a single GET, DELETE, PATCH, or POST, as well as an entire page of data. A very much needed thank you to SQLAlchemy for designing such an easy to use ORM that allows for a proper hierarchy, one which you have not really seen the power of quite frankly, that allows us to write minimalistic code that is also clear and concise.

Take a look at our endpoints/orders/api.py:

from ...model.order import Order
from ..api import Api

class OrderApi(Api):

As you can see, the order API thus far is very basic, and rightfully so. All of the heavy lifting will be done by the parent api.py, and our sub-directory api.py files that represent a single model only have to define the bare necessities. We are free to add additional functions that allow for ordering and filtering of the query, but we will leave that to the next installation. For now, let us move on.

We have designed our first API, but you may be wondering how we get access to it. This question brings us to endpoints/orders/routes.py. Within this file, we will define all the routes for the order API.

from ..app import api
from . import OrderApi

api.add_resource(
OrderApi,
"/orders/",
"/orders"
)

The api.add_resource() function allows us to define the resource class that will handle the request, and each following argument is a request path pattern. In the first path pattern, you will notice a validation for part of the path that defines an integer, and if it matches, assigns it to the “id” argument, which will then be passed to the request method for the resource. In our case, we only care about GET and DELETE, as it’s all we have implemented up to this point.

If we submit a GET request to /orders/1, our OrderApi class will create a query for the Order table and then after, it will apply a filter for the “id” matching “1”. Upon finding it, it will return it. If it doesn’t exist, a 404 response will be returned.

If we submit a GET request to /orders, no “id” argument is passed, thus a request for all orders will be returned. We do not currently have logic for paginating the records, but it is easily applied and will be explained thoroughly in the next installation.

If we submit a DELETE request to /orders/1, the same exact logic that is applied to a GET request is applied. The difference is that if the query returns a model instance, we then delete it and return a response.

It’s time to jump into the next order of business, and that’s applying the same logic we applied in the OrderApi to our OrderItemApi.

A glance at our endpoints/orders/items/api.py:

from ....model.order_item import OrderItem
from ...api import Api

class OrderItemApi(Api):

Similar to OrderApi, OrderItemApi is also minimal. Let’s discuss the differences. The first thing you may notice is that we have a join from OrderItem -> Order. The join condition is using the “order_id” argument passed into the method, but where did it come from? Well, similar to the way that the root API class handles the “id” argument, which is mapped as part of the request path, in our routes.py for the order item API, we validate the “order_id” argument as part of the path.

This allows us to join the parent Order model, and if we had authorization data inside of it, such as a “user_id”, we could join on that and verify that the order items we’re accessing belong to an order that we own. This may not sound important just yet, but it’s all part of the confluence that is our REST API as a whole and can be applied as many levels deep as necessary.

Here’s our endpoints/orders/items/routes.py:

from ...app import api
from . import OrderItemApi

api.add_resource(
OrderItemApi,
"/orders//items/",
"/orders//items"
)

As you can see, we have the additional “order_id” argument as part of both paths. This is the glue that links our order item to the order, and if the order isn’t owned by us, the query yields no result which ultimately leads to a graceful 404 response.

Hopefully, this article has given you some insight on how important API design is at the root level, and that staying consistent in your application rules will help you from writing unnecessary code that would have otherwise served only to confuse the maintainer.

That sums up this installation. In the next one, we will be exploring handling of PATCH and POST requests, validating incoming parameters, and extending the query functions for dynamic filtering, ordering, and proper pagination.