# Keeping the Data Code in One Place

A data module is the one file in an API that holds every SQL statement, with functions that take a connection and give back plain values (an id, a dictionary, a list, or None for a row that isn't there), so the routes call it and never write SQL themselves.

Journeyman · FastAPI · REST API · Updated November 19, 2026

By Gregor Dzierzon

## After this lesson you can

- Write data module functions that take a connection and return an id, a dictionary, a list of dictionaries, or None for a missing row
- Explain why an API's routes call a data module instead of writing SQL, and turn the module's None into a 404 in a route

## Key takeaways

- SQL written straight into routes repeats the same queries, column lists and row handling in every route, and mixes two jobs in one function.
- A data module holds every SQL statement in one file, behind functions named for what the app needs, such as add_set and get_set.
- Each function takes an open connection and gives back plain Python values, such as a new row's id, a dictionary, a list of dictionaries, or None when the row isn't there.
- A route calls the data module and decides what its answer means to a caller. None becomes a 404, and the route never writes SQL.
- One file of queries is one place to read, to test without a server, and to change when a table changes.

Open a Python API at work and you'll usually find its SQL in a file of its own, named something like `users_data.py` or `orders_repository.py`, and routes that never mention a table. This lesson shows the problem that file solves, and how to write one.

This chapter's example is Rep Counter, a gym's workout log. Members save each workout with its date, and each workout's sets: an exercise, the weight in pounds and the reps. The log kept its workouts in a Python list, and every restart lost them. Now they go in a SQLite database, `rep_counter.db`, with two tables: `workouts` (`id`, `member`, `day`) and `sets` (`id`, `workout_id`, `exercise`, `weight`, `reps`).

## SQL in every route

The quickest way to get there is to write the SQL right inside each route:

```python
@app.get("/workouts/{workout_id}")
def read_workout(workout_id: int):
    conn = sqlite3.connect("rep_counter.db")
    row = conn.execute("SELECT id, member, day FROM workouts WHERE id = ?", (workout_id,)).fetchone()
    conn.close()
    if row is None:
        raise HTTPException(status_code=404, detail=f"There's no workout {workout_id}.")
    return {"id": row[0], "member": row[1], "day": row[2]}
```

For one route, that works. Rep Counter needs five, and they all repeat the same things: the column list, turning a row into a dictionary by position, opening and closing. Rename a column, and you hunt through every route for it. And each route now does two jobs: talking to the database, and talking HTTP.

## One module for every query

A restaurant's waiters take orders and bring plates; only the cooks touch the stove. When the kitchen changes a recipe, no waiter needs to know. A **data module** is the kitchen: one file that holds every SQL statement, behind functions named for what the app needs. Keeping each job in its own place is called **separation of concerns**. Other courses and teams call the same file a *repository* or a *data access object* (DAO). Here's part of Rep Counter's, `workouts_data.py`:

```python
def list_workouts(conn, member):
    rows = conn.execute(
        "SELECT id, member, day FROM workouts WHERE member = ? ORDER BY day", (member,)
    ).fetchall()
    return [dict(row) for row in rows]


def add_set(conn, workout_id, exercise, weight, reps):
    cursor = conn.execute(
        "INSERT INTO sets (workout_id, exercise, weight, reps) VALUES (?, ?, ?, ?)",
        (workout_id, exercise, weight, reps),
    )
    return cursor.lastrowid


def get_set(conn, set_id):
    row = conn.execute(
        "SELECT id, workout_id, exercise, weight, reps FROM sets WHERE id = ?", (set_id,)
    ).fetchone()
    if row is None:
        return None
    return dict(row)
```

Every function follows the same rules:

- It takes an open connection, `conn`, as its first parameter. Where the connection comes from isn't its business; the next lesson answers that.
- It gives back plain Python values: a new row's id (`cursor.lastrowid`), a dictionary (`dict(row)`, which works when the connection's `row_factory` is `sqlite3.Row`), a list of them, or `None` when the row isn't there.
- Every value goes in through a `?` placeholder, as *Placeholders, and Why SQL Is Never Built from Strings* showed.

Because the functions need only a connection, you can try them with no server at all. This script, `try_data.py`, uses a database in memory:

```python
import sqlite3

import workouts_data

conn = sqlite3.connect(":memory:")
conn.row_factory = sqlite3.Row
conn.executescript("""
    CREATE TABLE workouts (id INTEGER PRIMARY KEY, member TEXT NOT NULL, day TEXT NOT NULL);
    CREATE TABLE sets (id INTEGER PRIMARY KEY, workout_id INTEGER NOT NULL REFERENCES workouts (id),
                       exercise TEXT NOT NULL, weight REAL NOT NULL, reps INTEGER NOT NULL);
    INSERT INTO workouts (member, day) VALUES ('Luis', '2026-11-03'), ('Priya', '2026-11-05'), ('Luis', '2026-11-10');
""")

set_id = workouts_data.add_set(conn, 1, "squat", 135, 8)
print(set_id)
print(workouts_data.get_set(conn, set_id))
print(workouts_data.get_set(conn, 99))
print(workouts_data.list_workouts(conn, "Luis"))
```

```text
1
{'id': 1, 'workout_id': 1, 'exercise': 'squat', 'weight': 135.0, 'reps': 8}
None
[{'id': 1, 'member': 'Luis', 'day': '2026-11-03'}, {'id': 3, 'member': 'Luis', 'day': '2026-11-10'}]
```

`executescript` runs several statements in one go. The weight comes back as `135.0` because the column is `REAL`.

## Routes that never write SQL

With the queries in the data module, a route shrinks to its own job: read the request, call the module, and decide what the answer means to the caller.

```python
@app.get("/workouts/{workout_id}")
def read_workout(workout_id: int):
    conn = connect()
    workout = workouts_data.get_workout(conn, workout_id)
    conn.close()
    if workout is None:
        raise HTTPException(status_code=404, detail=f"There's no workout {workout_id}.")
    return workout
```

`connect()` opens `rep_counter.db` with `sqlite3.Row` rows; the next lesson, *A Database Connection for Every Request*, replaces it and the `close()` with a connection FastAPI hands each request. The data module says "there's no such row" with `None`, and only the route knows that means 404 Not Found: "there's nothing at that address". `GET /workouts/1` answers 200 OK:

```json
{"id":1,"member":"Luis","day":"2026-11-03"}
```

`GET /workouts/9` answers 404, in the error shape from *One Error Shape for the Whole API*:

```json
{"status":404,"title":"Not Found","detail":"There's no workout 9."}
```

## Why it pays off

- **One place to read.** Every question the API asks the database is in one file.
- **One place to test.** The module's functions run on a `:memory:` database in a second, with no server.
- **One place to change.** When a column is renamed, one file changes, and no route does.

This is the course's way for every API from here on: SQL in the data module, HTTP in the routes.

## Do it: finish two functions of the data module

Rep Counter's data module needs its first two workout functions.

- `conn`: an open connection whose rows read by name (its `row_factory` is `sqlite3.Row`), with the `workouts` table already made: `id INTEGER PRIMARY KEY`, `member TEXT NOT NULL`, `day TEXT NOT NULL`.
- `member`: the member's name, such as `"Luis"`.
- `day`: the workout's date as text, such as `"2026-11-17"`.
- `workout_id`: the id of a workout to read.

**Your job:**

- Write `add_workout(conn, member, day)`. It inserts one row into `workouts` with `?` placeholders and returns the new row's id.
- Write `get_workout(conn, workout_id)`. It returns that workout as a dictionary with the keys `id`, `member` and `day`, or `None` when there's no workout with that id.

Worked through: on an empty table, `add_workout(conn, "Luis", "2026-11-17")` returns `1`, and then `get_workout(conn, 1)` returns `{"id": 1, "member": "Luis", "day": "2026-11-17"}`. `get_workout(conn, 99)` returns `None`.

Practiced live on the lesson page: write the Python right there and check it.

## Check yourself

2 questions to try on the lesson page.

## So what

When every query lives in one data module, the routes stay about HTTP, the SQL stays about data, and a change to a table is a change to one file.

## Key terms

- [Data Access Object (DAO)](https://www.ask-codey.com/glossary/data-access-object-dao)
- [Repository Pattern](https://www.ask-codey.com/glossary/repository-pattern)
- [Separation of Concerns](https://www.ask-codey.com/glossary/separation-of-concerns)

## Related

- [Reading Rows: SELECT, WHERE and ORDER BY](https://www.ask-codey.com/lessons/fastapi-sql-select)
- [A Database Connection for Every Request](https://www.ask-codey.com/lessons/fastapi-data-get-db-dependency)
- [Exercise: Workout Log API](https://www.ask-codey.com/lessons/exercise-fastapi-workout-log-api)

Source: https://www.ask-codey.com/lessons/fastapi-data-module
