# 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 November 19, 2026

By Gregor Dzierzon

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](https://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:

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

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

On a Mac (zsh):

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

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

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

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

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

- [Database Migration](https://www.ask-codey.com/glossary/database-migration)
- [Depends()](https://www.ask-codey.com/glossary/depends)
- [Dependency with yield](https://www.ask-codey.com/glossary/dependency-with-yield)
- [Dependency Override](https://www.ask-codey.com/glossary/dependency-override)
- [tmp_path](https://www.ask-codey.com/glossary/tmp-path)

## Related

- [Keeping the Data Code in One Place](https://www.ask-codey.com/lessons/fastapi-data-module)
- [A Database Connection for Every Request](https://www.ask-codey.com/lessons/fastapi-data-get-db-dependency)
- [Testing with a Real, Throwaway Database](https://www.ask-codey.com/lessons/fastapi-data-testing-real-database)

Source: https://www.ask-codey.com/lessons/exercise-fastapi-workout-log-api
