Exercise: Workout Log API

Move a gym's in-memory FastAPI workouts API onto SQLite by writing its two migration scripts, a data module that holds every SQL statement, a get_db dependency that gives each request its own connection, and routes that call them, tested on a throwaway database through app.dependency_overrides.

Journeyman · FastAPI · REST API · Updated

By

Rep Counter's members log every workout and every set in the gym's API, and every time the app restarts, all of it disappears: the workouts live in a Python list. You'll move the API onto SQLite, the way this chapter built it: migration scripts for the tables, one data module for the SQL, a connection for every request, and routes that never write SQL. Tests check every answer, and look inside a throwaway database to see what was really saved.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-fastapi-workout-log-api, also linked from the Exercise box beside the lesson. Clone it into your python-course folder. In VS Code's terminal:

cd ~/python-course
git clone https://github.com/ask-codey/exercise-fastapi-workout-log-api.git

Git makes a folder, exercise-fastapi-workout-log-api, with the exercise code inside. Get the Exercise Code with Git walks through cloning step by step, if you'd like a reminder. Open that folder in VS Code (File > Open Folder), open its terminal with Terminal > New Terminal, and make the exercise's virtual environment. On Windows (PowerShell):

python -m venv .venv
.venv\Scripts\Activate.ps1
pip install -r requirements.txt

On a Mac (zsh):

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

The prompt starts with (.venv) once the virtual environment is on. The exercise's README says what each command does, and what to do if PowerShell won't run the second one. SQLite needs nothing installed: it comes with Python.

The folder holds:

  • migrations/0001_create_workouts.sql and migrations/0002_create_sets.sql: the migration scripts, for you to write. The comment in each lists the table's columns, exactly.
  • workouts_data.py: the data module, for you to finish. Each function's docstring says what it takes and gives back.
  • database.py: DATABASE_PATH, open_db(path) and migrate(conn), ready; get_db() is for you to finish.
  • routers/workouts.py: the routes, working on the list in data.py, for you to move onto the database. The comment at its top lists every request.
  • data.py: the in-memory workouts the routes use now. Once every route uses the database, you can delete it.
  • main.py: the app, the lifespan that runs the migrations when it starts, and the error handlers that give every error one shape. Ready to use.
  • tests/test_workouts.py: the tests, which make a throwaway database for every test. You don't need to change it.
  • requirements.txt and pytest.ini: the packages, at exact versions, and where pytest finds the tests and your code.

What to do

The tables, exactly as the scripts' comments list them:

workouts   id INTEGER PRIMARY KEY, member TEXT NOT NULL, day TEXT NOT NULL
sets       id INTEGER PRIMARY KEY, workout_id INTEGER NOT NULL (REFERENCES workouts (id), ON DELETE CASCADE),
           exercise TEXT NOT NULL, weight REAL NOT NULL, reps INTEGER NOT NULL

The requests, for the four workouts the tests start with: 1 (Luis, 2026-11-03, three sets), 2 (Priya, 2026-11-05, one set), 3 (Luis, 2026-11-10, one set) and 4 (Luis, 2026-12-01, no sets):

GET    /workouts?member=Luis&start=2026-11-01&end=2026-11-30
                         200, Luis's workouts from start to end, both days included, oldest first, without sets:
                         [{"id": 1, "member": "Luis", "day": "2026-11-03"}, {"id": 3, "member": "Luis", "day": "2026-11-10"}]
GET    /workouts/1       200, workout 1 with its sets in the order they were added:
                         {"id": 1, "member": "Luis", "day": "2026-11-03",
                          "sets": [{"id": 1, "exercise": "squat", "weight": 135.0, "reps": 8}, ...]}
POST   /workouts         {"member": "Ana", "day": "2026-11-17"}: 201,
                         {"id": 5, "member": "Ana", "day": "2026-11-17", "sets": []}
POST   /workouts/3/sets  {"exercise": "squat", "weight": 140, "reps": 8}: 201,
                         {"id": 6, "exercise": "squat", "weight": 140.0, "reps": 8}
DELETE /workouts/1       204, no body, and workout 1's sets are deleted with it
Any of them, /workouts/99    404: {"status": 404, "title": "Not Found", "detail": "There's no workout 99."}
  1. Write the migrations. One CREATE TABLE in each script, with exactly the columns above.
  2. Finish workouts_data.py: add_workout, workouts_between, get_workout (with its sets, or None), add_set and delete_workout (True or False). Every value goes in through a ? placeholder, and nothing in it commits.
  3. Finish get_db in database.py: open with open_db(DATABASE_PATH), yield the connection, commit on success, roll back and raise again on an error, and always close.
  4. Move each route onto the database: take conn: sqlite3.Connection = Depends(get_db, scope="function"), call the data module, and raise the 404 when it gives back None or False. The routes stay plain def, and none of them writes SQL.

Check your work

Run the tests from the exercise folder, with the virtual environment on:

pytest

No server needs to be running, and your own rep_counter.db is never touched: before every test, the tests make a new database file in tmp_path, run your migrations on it, add the four workouts, and swap get_db for one that opens that file. At first every test stops with The starting rows couldn't be added (no such table: workouts): the migrations are your first job. Then 6 of the 17 pass, a few the in-memory list happens to get right, and each failing test's message says what it sent, what it expected and what came back. Test 17 uses your own get_db, pointed at a throwaway file, to check that it commits.

To try the API yourself, start it with fastapi dev main.py and open http://127.0.0.1:8000/docs. Add a workout, stop the app with Ctrl+C, start it again, and the workout is still there. If you started the app before your scripts were written, delete rep_counter.db first: a database runs each migration only once.

Hints

  • The migrations come first, written as Tables and Rows: CREATE TABLE and INSERT and Related Tables and Foreign Keys write tables.
  • The data module is the Do It from Keeping the Data Code in One Place, grown. cursor.lastrowid is a new row's id, dict(row) makes a dictionary, and cursor.rowcount says how many rows a DELETE removed. get_workout runs two queries: the workout, then its sets.
  • Dates written year-month-day compare correctly as text, so day >= ? AND day <= ? finds the days from start to end. Sort with ORDER BY day, id.
  • get_db is the one from A Database Connection for Every Request, with open_db(DATABASE_PATH) as its first line.
  • The route that adds a set checks the workout is there first, with get_workout, so a set is never saved for a workout that isn't.

When you're done

Every test is green: pytest's last line says 17 passed. On the docs page, add a workout for yourself, add two sets to it, and read it back with GET /workouts/{workout_id}. Then stop the app, start it again, and read it once more: it's still there, which no version of this API could do before.

Now look at your routes. Not one of them mentions a table or a column. If the gym renamed reps to repetitions, which files would change, and would any route?

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here. Hand In Your Work with Git walks through each step.

  1. On GitHub, click +, then New repository. Name it (for example exercise-fastapi-workout-log-api), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In VS Code's terminal, in your exercise folder, send your work to your new repo. Point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-fastapi-workout-log-api.git
    git add .
    git commit -m "My solution"
    git push -u origin main
    
  3. Paste your repo's address, such as https://github.com/your-name/exercise-fastapi-workout-log-api, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

An API whose SQL lives in one module, whose tables grow by migrations, and whose tests run on a throwaway copy of the real thing is an API a team can keep working on for years, and you've now built one.

Key terms

Related