Turn a Toy FastAPI CRUD Demo into a Persistent, Tested API

When you take a FastAPI prototype from a quick-and-dirty in-memory list to a real relational database, you have to trade those simple .append() calls for proper database session handling. The big payoff?

Restarting your Uvicorn dev server won’t wipe out all the test records you just spent ten minutes creating. Using SQLModel here makes life a lot easier because it sits right between your Pydantic schemas and SQLAlchemy persistence, meaning you don't have to duplicate your field types across two different model definitions.

• What you'll need: Python 3.10+, FastAPI 0.100+, SQLModel 0.0.14+, pytest, and httpx.
• Database: SQLite (file-based, so you can skip Docker setup for now and get moving right away).
• Core Pattern: FastAPI’s dependency injection (Depends) to manage the lifecycle of your database sessions.
• Bugs we're avoiding: Accidental field wipes during PATCH requests, plus test state bleeding over between runs.

The Problem: Data Vanishing Every Time Uvicorn Reloads

Almost every beginner tutorial starts out with something like fake_db = []. It's great for proving that your endpoints actually parse JSON, but it leaves you with a false sense of security.

The second that Python process restarts or Uvicorn hot-reloads, everything stored in memory evaporates. If you want this to behave like a real-world app, your data storage needs to live completely independent of your HTTP server's process.

SQLModel is tailor-made for this. It lets a single class act as both a Pydantic schema for request validation and an SQLAlchemy model for database mapping. You write the schema once, but you still have to structure your app so database connections open when a request arrives and reliably close the moment it finishes.

Step 1: Setting Up the Engine and Models

In any clean API setup, you don't want clients tampering with internal database fields. We'll split things into a shared base model and a creation payload; if you later need to hide sensitive columns on reads, you can toss in a dedicated response model as well. Splitting them up right now stops callers from doing sneaky things like trying to dictate their own id during a POST request – a solid rule of thumb for standard REST design.

Drop your data shapes and the SQLite engine setup into a new models.py file:

# models.py

from sqlmodel import Field, SQLModel, Session, create_engine

# 1. Base Model: Fields shared across all interactions
class TaskBase(SQLModel):
    title: str
    course: str
    priority: str = "medium"
    done: bool = False

# 2. Database Table Model
class Task(TaskBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

# 3. Input Model (POST)
class TaskCreate(TaskBase):
    pass

# 4. Partial Input Model (PATCH)
class TaskUpdate(SQLModel):
    title: str | None = None
    course: str | None = None
    priority: str | None = None
    done: bool | None = None

# 5. Database Configuration
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

# Permit SQLite connection use across FastAPI request threads in this example
engine = create_engine(sqlite_url, echo=True, connect_args={"check_same_thread": False})

def create_db_and_tables():
    SQLModel.metadata.create_all(engine)

def get_session():
    with Session(engine) as session:
        yield session

Pay special attention to get_session. Because it's a generator using yield, FastAPI can borrow the session for the route, wait until the response goes out, and then cleanly close it – even if your route throws an unhandled error midway through.

Step 2: Wiring Routes with Dependency Injection

Now we can gut the mock list inside main.py and bring in the SQLModel session. We'll inject that database connection directly into the route handlers using Depends(get_session).

# main.py
from contextlib import asynccontextmanager
from fastapi import FastAPI, Depends, HTTPException, status
from sqlmodel import Session, select
from models import Task, TaskCreate, TaskUpdate, create_db_and_tables, get_session

@asynccontextmanager
async def lifespan(app: FastAPI):
    create_db_and_tables()
    yield

app = FastAPI(title="Persistent Task API", lifespan=lifespan)

@app.post("/tasks/", response_model=Task, status_code=status.HTTP_201_CREATED)
def create_task(task: TaskCreate, session: Session = Depends(get_session)):
    db_task = Task.model_validate(task)
    session.add(db_task)
    session.commit()
    session.refresh(db_task)
    return db_task

@app.get("/tasks/{task_id}", response_model=Task)
def read_task(task_id: int, session: Session = Depends(get_session)):
    task = session.get(Task, task_id)
    if not task:
        raise HTTPException(status_code=404, detail="Task not found")
    return task

Keeping 404s Explicit

Take a look at read_task. If someone asks for an ID that isn't in SQLite, session.get() simply hands back None.

You have to check for this and throw an explicit HTTPException. If you skip this check, FastAPI tries to validate None against the Task response model and blows up with an ugly 500 Internal Server Error instead of a clean, predictable 404.

Handling Partial Updates (PATCH) Without Wiping Data

PATCH endpoints are usually where basic prototype code falls apart. With a PATCH, a client only passes along the specific keys they want to update. If you dump that dictionary straight onto your database object without checking, every field the user left blank defaults to None, silently wiping out existing data.

# main.py (continued)
@app.patch("/tasks/{task_id}", response_model=Task)
def update_task(task_id: int, task_update: TaskUpdate, session: Session = Depends(get_session)):
    db_task = session.get(Task, task_id)
    if not db_task:
        raise HTTPException(status_code=404, detail="Task not found")
    
    # Extract only the fields explicitly provided by the client payload
    update_data = task_update.model_dump(exclude_unset=True)
    
    for key, value in update_data.items():
        setattr(db_task, key, value)
        
    session.add(db_task)
    session.commit()
    session.refresh(db_task)
    return db_task

Setting exclude_unset=True inside model_dump() does the heavy lifting here: it strips out any keys the user didn't explicitly include in their JSON payload. That way, our loop only touches the attributes being changed and leaves the rest of the row alone.

Step 3: Automated Testing with TestClient and pytest

Clicking buttons inside Swagger UI at /docs is fun for an initial sanity check, but it gets old fast and doesn't scale. We want automated tests to prove dependency injection is working. And crucially, we don't want test runs spamming junk records into our real database.db file, which is where FastAPI's dependency overrides save the day.

Swapping Out the Database Session During Tests

Create a test_main.py file. Here, we tell SQLModel to build a temporary in-memory SQLite database that lives and dies purely during the test suite.

# test_main.py
import pytest
from fastapi.testclient import TestClient
from sqlmodel import SQLModel, Session, create_engine
from sqlmodel.pool import StaticPool

from main import app
from models import get_session

# Use an in-memory SQLite database to ensure test isolation
sqlite_url = "sqlite://"
engine = create_engine(
    sqlite_url, 
    connect_args={"check_same_thread": False}, 
    poolclass=StaticPool
)

def override_get_session():
    with Session(engine) as session:
        yield session

# Override the application's dependency payload
app.dependency_overrides[get_session] = override_get_session
client = TestClient(app)

@pytest.fixture(name="session")
def session_fixture():
    SQLModel.metadata.create_all(engine)
    yield Session(engine)
    SQLModel.metadata.drop_all(engine)

def test_create_and_read_task(session: Session):
    # 1. Test POST operation
    response = client.post(
        "/tasks/",
        json={"title": "Setup API", "course": "Backend 101", "priority": "high"}
    )
    assert response.status_code == 201
    data = response.json()
    assert data["title"] == "Setup API"
    assert data["done"] is False
    assert "id" in data
    task_id = data["id"]

    # 2. Test GET operation utilizing the generated ID
    get_response = client.get(f"/tasks/{task_id}")
    assert get_response.status_code == 200
    assert get_response.json()["title"] == "Setup API"

def test_read_nonexistent_task(session: Session):
    response = client.get("/tasks/999")
    assert response.status_code == 404

Passing StaticPool tells SQLite to hold onto one shared connection in memory throughout the test run so the tables don't vanish between queries. Then the fixture tears everything down with drop_all as soon as the test finishes, handing the next test a completely clean slate.

Running and Checking Everything

To try out the new setup, pull down the dependencies and run the test suite to make sure the session overrides play nice:

pip install fastapi uvicorn httpx sqlmodel pytest
pytest test_main.py -v

When you get green checkmarks across the board, spin up your server:

uvicorn main:app --reload

Head over to [http://127.0.0.1:8000/docs](http://127.0.0.1:8000/docs) and send a test POST request. You’ll see a brand new database.db file pop up in your project directory right away.

Now hit CTRL+C in your terminal to shut Uvicorn down completely, spin it back up, and run a GET request for /tasks/1. Your data is still right there waiting for you, totally safe from server restarts.