The Bridge to the Database: The Repository Pattern in repository.py

[home]

The Developer's Diary

The Bridge to the Database: The Repository Pattern

Building the crucial adapter that isolates our application from the database.

So far in our architectural journey, we've defined our application's entry point, its configuration, its core domain models, and the services that orchestrate its use cases. Now we must address a fundamental question: where does the data actually live? This brings us to the persistence layer, and specifically to our implementation of the Repository Pattern in app/data/repository.py.

What is the Repository Pattern?

The repository is an architectural pattern that mediates between the domain and data mapping layers. In simpler terms, it provides an in-memory, collection-like interface for accessing our domain objects (like `Client` or `Assessment`). The rest of our application interacts with this repository, without needing to know anything about the underlying database technology.

Novice Coder (NC): "This seems like a crazy amount of boilerplate. I have to write a function here that takes a User object, pulls out all the data, writes SQL, and then does the reverse to load it? Why can't my service just talk to SQLite directly?"

Experienced Coder (IC): "You're describing the exact reason this pattern is so valuable! This is the Repository Pattern. It decouples our application's core from the persistence mechanism. The service layer depends on an abstraction (an `AbstractRepository` defined in the core), not this concrete `SQLiteRepository`. This means we can swap SQLite for PostgreSQL, or more importantly, use a fake `InMemoryRepository` for our unit tests, and the service layer never knows the difference. The process you described, converting between objects and database rows, is a manual form of Object-Relational Mapping (ORM). It's work, but it's work that buys us incredible flexibility."

This decoupling is the repository's superpower. It acts as a boundary, protecting our application's core logic from the messy details of data storage.

Our Hypothetical repository.py

For the SpLD app, we'll create a `SQLiteRepository` that implements an abstract interface. This interface will define the contract that our service layer depends on.

# app/data/repository.py

import sqlite3
from typing import List

# We import the abstract contract and the domain model
from ..interfaces.repository import AbstractRepository
from ..core.models import Assessment

class SQLiteRepository(AbstractRepository):
    """
    A concrete implementation of the repository that uses SQLite
    for data persistence.
    """
    def __init__(self, db_path: str):
        self.db_path = db_path
        # We could establish the connection here or per method
        self._conn = sqlite3.connect(db_path)
        self._create_tables()

    def _create_tables(self):
        cursor = self._conn.cursor()
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS assessments (
                assessment_id INTEGER PRIMARY KEY AUTOINCREMENT,
                client_id INTEGER NOT NULL,
                assessment_date TEXT NOT NULL,
                report_summary TEXT,
                hypotheses TEXT
            )
        """)
        self._conn.commit()

    def add_assessment(self, assessment: Assessment) -> Assessment:
        """
        Adds a new assessment to the database.
        This is a manual ORM process.
        """
        cursor = self._conn.cursor()
        
        # Convert list of hypotheses to a string for storage
        hypotheses_str = ",".join(assessment.hypotheses)

        cursor.execute(
            "INSERT INTO assessments (client_id, assessment_date, hypotheses) VALUES (?, ?, ?)",
            (assessment.client_id, assessment.assessment_date.isoformat(), hypotheses_str)
        )
        self._conn.commit()
        
        # Get the ID of the newly inserted row and update the model
        assessment.assessment_id = cursor.lastrowid
        return assessment

    def get_assessment(self, assessment_id: int) -> Assessment:
        # Implementation for fetching an assessment would go here
        pass

    def list_assessments_for_client(self, client_id: int) -> List[Assessment]:
        # Implementation for listing assessments would go here
        pass

Breaking it down:

• Implements an Interface: The `SQLiteRepository` inherits from `AbstractRepository`. This enforces a contract, ensuring that any repository we create will have the same methods (`add_assessment`, `get_assessment`, etc.) that the `HistoryService` expects.
• Handles Low-Level Details: This is the *only* place in our application that knows about `sqlite3`. It handles connections, cursors, and writing SQL queries.
• Manual ORM: The `add_assessment` method demonstrates the Object-Relational Mapping process. It takes an `Assessment` object, extracts its data, and maps it to the columns of the `assessments` table. It also handles the reverse: after inserting the record, it gets the new `assessment_id` from the database and updates the object.
• Data Conversion: Notice how the `hypotheses` list is converted into a comma-separated string for storage. The repository is responsible for handling these kinds of transformations needed to fit our rich domain models into a relational database structure.

The repository is the final major component of our application's core architecture. With it in place, we have a complete, decoupled, and testable system. The UI will talk to the Service, the Service will use the Repository to load and save Models, and the Repository will handle the nitty-gritty of talking to the database. Each component has a clear responsibility, creating a clean and maintainable codebase.