Why Replace the JSON File? - From JSON to SQLite | Add a Database to Your API

Learn why JSON breaks as a storage mechanism and what SQLite and SQLModel offer instead

The problem with JSON storage

Your expense tracker saves data to a JSON file. That works fine for a handful of expenses — but it has real limits.

Every query loads everything. When GET /expenses?category=food runs, your app reads the entire file into memory, loops through every expense, and filters in Python. As your data grows, that gets slower — and there is no way around it with a flat file.

Aggregation requires Python loops. The GET /summary endpoint loops through all expenses and adds up totals in Python. These calculations belong in the database, not your application code.

Concurrent writes risk corruption. If two requests arrive at exactly the same moment and both try to write the file, one can overwrite the other. The atomic write pattern you added in Course 2 reduces this risk, but it does not eliminate it.

What SQLite gives you

SQLite is a relational database stored in a single file on disk. You send queries, and the database engine handles filtering, sorting, and aggregating — returning only the rows you asked for.

The difference between expenses.json and expenses.db is not the file extension. It is what understands the file. JSON is just text. SQLite's database engine understands the structure inside expenses.db and can query it efficiently.

No server to install. No separate process to run. Just a file on disk — but one that a real database engine can query.

What SQLModel gives you

SQLModel is a library built by the same person who created FastAPI. It lets you define database tables as Python classes — the same way you already define models with Pydantic.

Your Expense class already has the right fields, types, and constraints. You only need to change the base class from BaseModel to SQLModel and add table=True:

class Expense(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    description: str = Field(min_length=1)
    amount: float = Field(gt=0)
    category: Literal["food", "transport", "entertainment", "utilities", "other"]
    date: Optional[str] = None

That class is both a Pydantic model and a database table. FastAPI uses it to validate incoming requests. SQLModel uses it to read and write rows in SQLite. You no longer need separate models for input (Expense), output (ExpenseOut), and storage — one class handles all three roles.

SQLite's limits

SQLite is the right tool for personal projects, prototypes, and single-user APIs. It stores everything in one file, which means only one writer at a time. If you need multiple users writing simultaneously, the upgrade path is PostgreSQL — and SQLModel works identically with both. You only change the connection URL.