Jarett Forzano

Please don't read my blog posts

Dirty repository design pattern

Introduction

In software development, a database is basically mandatory when you’re creating any application that needs persistent storage. There are numerous kinds of database management systems and architectures you can use. A few examples are relational, non-relational, and graph. While the service is running, it usually talks to the database through a connection pool and opens a session (or transaction) around each request.

There are two kinds of database operations you can have: DDL (creates tables and modifies schema) or DML (reads and modifies the data inside those tables). DDL is straightforward and usually runs at build or migration time, not while the app is serving requests. DML queries are used to interact with the tables created through the DBMS. These requests can be called anytime, anywhere.

Issues with DML code

A common issue comes from engineers not abstracting away their database code behind a facade. A facade is a design concept that hides the internal logic behind a simple method. It’s mostly used to hide many different components in a neatly packaged utility that can be reused. Without this concept, the DML code that is used to access the database can become bloated and hard to read. That gets worse once you start using typed, OOP-style Python.

In Python, it is common to use ORMs (Object Relational Mappers) as an abstraction between code and the database. What many people don’t know is that these ORMs serve many purposes. They not only provide a clearly typed language for the user to interact with, but also handle many things that you would need to handle yourself if writing raw SQL.

Raw SQL is easy to get wrong in dangerous ways, like concatenating user input into a query string (SQL injection). A lot of ORMs parameterize queries for you so you are less likely to ship those sorts of vulnerabilities while you are slugging code at 3 AM.

That ORM code can get pretty bulky when you start covering joins and advanced aggregate methods. Here is the kind of query that often ends up pasted straight into a route: fetching tasks for someone, ordered by their most recent comment.

async def recent_for_assignee(self, assigned_to: str, limit: int) -> list[Task]:
    last_commented = (
        select(func.max(Comment.created_at))
        .select_from(Comment)
        .where(Comment.task_id == Task.id)
        .correlate(Task)
        .scalar_subquery()
    )
    statement = (
        select(Task)
        .where(Task.assigned_to == assigned_to, Task.status != "archived")
        .order_by(
            last_commented.desc().nulls_last(), Task.time_created.desc(), Task.id.desc()
        )
        .limit(limit)
    )
    return list(await self._transaction.scalars(statement))

Notice that’s still a chunk of ORM for only one query. Oftentimes you need to make several query calls inside of a single route for complex operations.

You can try to just make a million helper methods. The issues that arise with that are: 1) not clear to other developers, 2) not enforced, and 3) can cause massive util files. As an engineer once told me in the past, “Why are these files named utils if the methods inside are only used once, isn’t that more of a service?” Which is a good point; generally utils are meant to be used multiple times.

A topic for another day, but I think engineers struggle to know when a component is considered a util, a service, or should just be hardcoded in a method. It’s hard to gauge since, like most things software-related, it’s about having good taste for the code that you write (or generate at this point). Back to the main topic…

In this blog post I am going to cover a design pattern that was created to minimize the amount of DML code that gets exposed outside of where the session is created. This concept is good because it not only teaches you what sensible coupling looks like, but also improves code hygiene and gives you a start on separation of concerns. This isn’t verbatim with how the repository pattern is defined, which is why I call it the dirty repository design pattern. I’ll explain more about the differences at the end.

Repository design pattern

The repository design pattern is a pattern designed to take all of the random miscellaneous DML code and combine it into objects that encapsulate CRUD operations.

For example, let’s say we have a table in our database called Task.

# src.database.models
class Task(Base):
    __tablename__ = "tasks"

    id: Mapped[UUID] = mapped_column(primary_key=True, default=uuid4)
    name: Mapped[str]
    assigned_to: Mapped[str]
    status: Mapped[str] = mapped_column(default="open")
    time_created: Mapped[datetime]

This table is pretty simple in nature. We only have a few attributes attached to it. The next step is to create the repository.

class TaskRepository:
    def __init__(self, transaction: AsyncSession) -> None:
        self._transaction = transaction

The purpose of this repository is to now encapsulate all the DML actions you would do to the task table. This gets pretty cool since now we can build out everything upfront.

class TaskRepository:
    def __init__(self, transaction: AsyncSession) -> None:
        self._transaction = transaction

    async def create_task(self, name: str, assigned_to: str) -> Task:
        task = Task(
            name=name,
            assigned_to=assigned_to,
            time_created=datetime.now(),
        )
        self._transaction.add(task)
        await self._transaction.flush()
        return task

    async def get_task(self, task_id: UUID) -> Task | None:
        return await self._transaction.get(Task, task_id)

    # Note: fill out other data-access operations (list, update, delete)

Now, the cool part about this is that if you are using FastAPI you can use a feature called Depends. It lets you inject a dependency into a route. So you can create the database session and expose the repository when the service receives a request. This is great because it lowers the amount of setup that is required when you want to make API calls, sometimes reducing the route body to a single repository call. Here’s a high-level example.

from collections.abc import AsyncGenerator

from fastapi import Depends, FastAPI
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine

DATABASE_URL = "postgresql+asyncpg://user:pass@localhost:5432/app"

engine = create_async_engine(DATABASE_URL)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)

app = FastAPI()


async def get_transaction() -> AsyncGenerator[AsyncSession, None]:
    async with SessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise


def get_task_repository(
    transaction: AsyncSession = Depends(get_transaction),
) -> TaskRepository:
    return TaskRepository(transaction)


@app.post("/tasks")
async def create_task(
    name: str,
    assigned_to: str,
    tasks: TaskRepository = Depends(get_task_repository),
) -> Task:
    return await tasks.create_task(name=name, assigned_to=assigned_to)

Notice how the route stays thin: it takes a repository through Depends and calls one method. No ORM query code lives in the route itself. The cool part about this is that it continues to scale.

You can create context managers around the session to enforce atomic behavior through transactions, which is where ACID comes in (Atomicity, Consistency, Isolation, Durability). I can make another blog post for this, but all you need to know now is that ACID transactions are what most relational databases give you by default. It allows us to keep multiple DML actions inside of one transaction, rolling back if one of them fails.

A lot of non-relational systems lean more BASE (Basically Available, Soft State, Eventually Consistent): they prioritize availability and tolerate temporary inconsistency instead of offering the same transaction guarantees.

How this cleans up database code

Just to reiterate the benefits of this:

  1. ORM / SQL query code no longer needs to live inside your routes.
  2. It’s easier to manage database transactions.
  3. It’s better code hygiene and it’s easier for people to read.

There’s also the other benefit I like to think of, which is that it has purpose. Software engineering is unique in that spaghetti code can work just as well as, if not better than, code that is well architected. The reason I like to use design patterns and concepts to organize my code is that it gives the code a reason to belong. Whenever I go to write some DML code I immediately say to myself, “First make a repository,” and then I have a clear mental model of what needs to be created.

Code generation is important too. When you are generating code you want to have constructs that your model follows. If you are clearly labeling your design patterns, it will be easier to read what is generated in the future. It also has a benefit of looking better. Modular code just looks nicer and people tend to bias towards it more. Like if you open up a PR and there’s just a fat block of code in the codebase, I would argue that is more likely to get an eyebrow raised than if you were to separate it into a repository. Then in the future if someone thought your code sucked, they’d know exactly what needed to be optimized.

It’s also good for models since it’s easier for them to find where your queries are. Like if you need to optimize a query, the model knows where to look immediately, as opposed to doing blind searches to find a utility hidden in the codebase.

Conclusion

You should be using design patterns when writing in any OOP language. It’s important and makes your code look sexy. TBH I ain’t reviewing your code, but just a word of advice. The repository design pattern should be used when writing DML code. Routes shouldn’t be building ORM queries; that should all be blanketed behind the repositories that you create. How this differs from the formal pattern is in Additional resources below.

Additional resources

The only decent article I found that explains some of this is the GeeksforGeeks: Repository Design Pattern. You will see how they have an additional layer of abstraction where they first define an ABC for the repository to inherit. This is because engineers are paranoid that they will need to tear out their entire database one day. So the idea is that you can keep the older DB code and slowly migrate over to the newer database. This only happens when you scale too much and you need to swap storage engines or move a workload onto something like Apache Iceberg for analytics.

But I think your co-workers would get annoyed if you started making a million ABC classes with only one implementation. So I don’t think it’s worth it. I would probably work backwards and create the ABC class as you are migrating. It’s just a skeleton, so it’s really easy to make.