# 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](https://timberry.dev/fastapi-with-apikeys).

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](https://www.sqlite.org/index.html). 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:

```bash
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:

```sql
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:

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

```python
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 :)