Add a Database to Your API

Swap the JSON file in your FastAPI expense tracker for a real SQLite database using SQLModel.

Intermediate

3 hours

4 lessons

Course Content

24 chapters across 4 lessons

From JSON to SQLite

Replace the JSON file with a SQLite database and set up SQLModel

7 chapters

Progress0%

Why Replace the JSON File?

Continue

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

Install SQLModel

Install the SQLModel package and verify the installation

Create the Expense Table Model

Define Expense as a SQLModel table model and move it to a dedicated models.py file

Create the Database Engine

Create database.py with the SQLite engine and table creation function

Write the Session Generator

Add get_session and SessionDep to database.py to give each request its own database session

Wire Up the Database in main.py

Connect the database lifecycle to your app with the lifespan function and SessionDep

Lesson Complete

You finished From JSON to SQLite

Rewrite Every Endpoint

Replace dict and counter logic with database session calls across all five endpoints

7 chapters

Progress0%

How Database Sessions Work

Understand the session lifecycle and how it replaces the old dict and save pattern

Rewrite Create Expense

Replace the dict and counter logic with session.add, session.commit, and session.refresh

Rewrite List Expenses

Replace the Python filter loop with a database query using select and where

Rewrite Get Expense

Replace the dict lookup with session.get and handle the not-found case

Rewrite Delete Expense

Replace dict.pop with session.delete and session.commit

Rewrite Update Expense

Replace dict.update with sqlmodel_update, session.add, session.commit, and session.refresh

Lesson Complete

You finished Rewrite Every Endpoint

Powerful Queries

Add date range filtering, aggregated spending summaries, and full-text search

5 chapters

Progress0%

What SQL Can Do That Python Can't

Understand why database-side filtering and aggregation outperforms Python loops

Filter by Date Range

Add start and end query parameters to GET /expenses using chained where conditions

Monthly Spending Summary

Replace the Python loop in GET /summary with func.sum and group_by

Search Descriptions

Add a search query parameter to GET /expenses using contains

Lesson Complete

You finished Powerful Queries

Update Your Tests

Set up in-memory database fixtures and rewrite all tests for the new storage layer

5 chapters

Progress0%

Testing with an In-Memory Database

Understand why tests need database isolation and how in-memory SQLite provides it

Set Up the Test Fixture

Create a pytest fixture that gives each test an isolated in-memory SQLite database

Rewrite Existing Tests

Update all existing tests to use the client fixture for database isolation

Test the New Queries

Write tests for date range filtering, spending summary aggregation, and description search

Lesson Complete

You finished Update Your Tests