Adding an SQLite backend to FastAPI

While recently migrating my blog (again), I've revisited some posts including my tutorial: A simple Python FastAPI template with API key authentication.

That tutorial set out a very basic template for a FastAPI app that used API keys, but to keep it simple it used hard-coded database functions that simply checked for API keys in a local Dictionary. But my plan was always to extrapolate from that first step however, and slowly improve the template. In this post, we'll actually implement a database lookup function using SQLite3. If you want to follow along, just grab the code from the previous post.

The first thing we'll do is actually create a database. SQLite3 is rarely used in production systems (although sometimes it is!) but hopefully you can see from this example how easy it would be to start building something similar using Postgres for example. Within the app directory (remember we're using code from the previous post), create a new database file with this command:

sqlite3 db.sqlite

We can now populate the database. We'll keep it simple by creating a users table to store the API keys for Bob and Alice, our test users from before:

CREATE TABLE users(userid text, name text, apikey text);
INSERT INTO users VALUES('7oDYjo3d9r58EJKYi5x4E8','Bob','e54d4431-5dab-474e-b71a-0db1fcb9e659');
INSERT INTO users VALUES('mUP7PpTHmFAkxcQLWKMY8t','Alice','5f0c7127-3be9-4488-b801-c7b6415b45e9');

db.py

If you recall from the previous post, our db.py file contained hard-coded user IDs and API keys, and then two functions. check_api_key would check for the existence of the API key in the "database" (which was really just a Dictionary), and get_user_from_api_key would then retrieve the user object itself. I used this two-step approach to demonstrate the logic of things, but seeing as we're upgrading to using a real database, let's also make this more efficient.

We'll replace db.py entirely with the following:

import sqlite3

def check_api_key(api_key: str):
    with sqlite3.connect('db.sqlite') as conn:
        cur = conn.cursor()
        cur.execute('select name from users where apikey = ?', [api_key])
        row = cur.fetchone()
        if row:
            return(row)
    return False

Now we just have a check_api_key function. It makes the connection to the database, and searches for a row based on the provided API key. If an API key matches, it returns that row (in other words, the user object).

If no API key matches, we return False. Now we need to update how we call this function from auth.py.

auth.py.

We've changed the logic of how these two parts of the program talk to each other. Here's our updated auth.py:

from fastapi import Security, HTTPException, status
from fastapi.security import APIKeyHeader
from db import check_api_key

api_key_header = APIKeyHeader(name="X-API-Key")

def get_user(api_key_header: str = Security(api_key_header)):
    user = check_api_key(api_key_header)
    if user:
        return user
    raise HTTPException(
        status_code=status.HTTP_401_UNAUTHORIZED,
        detail="Missing or invalid API key"
    )

Comparing this to the previous version, you can see that we now just define the get_user function. This is the only function that gets called by our secure route, and we've removed the need for the separate get_user_from_api_key function. (Note that we've also removed it from the import line).

All that get_user has to do now is call the check_api_key function from our database and check the response. As long as it's not false, it will return the user object. If the response is false however, we know that the API key was missing or invalid, so we return the correct HTTP code just like before.

What's next for our little FastAPI app? I was already thinking along these lines in the previous post, but if we've handled authentication, we should start thinking about authorisation. What are Bob and Alice permitted to do within our app, how do we store that in a database and apply the logic in our app?

Maybe next time I migrate my blog, I'll write the follow up :)