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

By

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:

@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:

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:

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"))
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.

@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:

{"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:

{"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

Related