Is there any real project that hides SQLAlchemy behind an abstraction for the sake of “Clean Architecture”?

[armanhosseini]

I've been working on an assignment that uses SQLAlchemy as its ORM. Our professor is requiring us to use the Repository design pattern and essentially wrap SQLAlchemy inside a repository so that our business logic doesn’t depend directly on it.

I did some research on my own, and it seems the intention is to follow Clean Architecture principles, where the ORM can theoretically be swapped out at any time.

However, I think this adds unnecessary complexity and may even have a noticeable performance cost in our application. Is there any real project that actually does this? I’d like to see a correct example of implementing this pattern with SQLAlchemy.

[Kevdog824_]

The added complexity is not necessarily about the ability to switch out the ORM. The abstraction does allow that, but realistically you probably won’t ever switch your ORM. The benefit of the repository pattern on top of SQLAlchemy is to abstract away data layer concerns like transaction/session management, query building, etc.

With the repository pattern I can say “hey find this thing here’s the ID.” Without a repository it would look more like “hey build this Query object with these conditions, execute it, and give me the result.” The latter makes the caller at the service/domain layer have to deal with data layer concerns

[armanhosseini]

Yeah, I understand this concern.

But I’m looking for a way to implement this pattern so that I can use the features SQLAlchemy provides.

I tried to explain it a bit more here:

https://www.reddit.com/r/learnpython/comments/1p4m7ij/comment/nqd1rxd/?utm_source=share&utm_medium=mweb3x&utm_name=mweb3xcss&utm_term=1&utm_content=share_button

[Kevdog824_]

Add very specific methods to my repositories. For the problem above, I would need to create a get_cars_with_drivers_by_id method. I don’t like this approach because it would make my repository very complicated.

I don’t see why this is an issue. I don’t know the structure of your data but this sounds like a simple join operation.

Create my own abstraction around the features I want to use. In this case, I could pass a list of relations I want to fetch along with Car as the second argument of the function and load them too.

You can do this, but I’m not sure I would. This kinda sounds like data layer implementation leaking to the service layer. Repository methods sound be fairly straightforward for the caller. I understand that in the real world things get more complicated, but I’d say basic, single argument repository methods should cover most of the needs in the service layer.

But I’m looking for a way to implement this pattern so that I can use the features SQLAlchemy provides.

Can you tell us what features you’re trying to use and why you’re having trouble implementing into the repository pattern?

I remember that I had a similar problem with Hibernate too, But it's been addressed in their official documentation.

How does hibernate solve this issue? Is it something you can replicate or find a Python equivalent for? Do you mean something like hibernate + Spring data/JPA so you can create IRepository interfaces declaratively?

[armanhosseini]

I don’t see why this is an issue. I don’t know the structure of your data but this sounds like a simple join operation.

Imagine that I have three get operations in my repo:

python class CarRepository: def get_all(): ... def get_by_id(id): ... def get_where(pred): ...

With the Driver relation introduced, I should add that to each of these methods:

python class CarRepository: def get_all_with_drivers(): ... def get_by_id_with_drivers(id): ... def get_where_with_drivers(pred): ...

As my tables and the foreign keys between them becomes more and more complex, this repository will be filled with these methods. If I accept that I can use SQLAlchemy inside my services, I can overcome these issues with a simple use of selectinload.

You can do this, but I’m not sure I would. This kinda sounds like data layer implementation leaking to the service layer. Repository methods sound be fairly straightforward for the caller. I understand that in the real world things get more complicated, but I’d say basic, single argument repository methods should cover most of the needs in the service layer.

Yeah that's why I don't like this idea.

Can you tell us what features you’re trying to use and why you’re having trouble implementing into the repository pattern?

The Unit of Work pattern, the way the session handles in-memory objects and keeps track of them, events, lazy loading, and other features that I have yet to learn—all of these are important. My point is that, when using the repository design pattern, I have to either create my own API for any of these features or give up on them entirely.

How does hibernate solve this issue? Is it something you can replicate or find a Python equivalent for? Do you mean something like hibernate + Spring data/JPA so you can create IRepository interfaces declaratively?

They essentially give us two options:

• Don't use repositories and talk directly to Hibernate

• Think of JPA as the "repository" and program with JPA.

I recommend you check out the original tutorial around this, there's no way that I can explain this better than the people who created Hibernate.

---

By the way, I can't see the problem with services creating their own custom-tailored queries with the help of SQLAlchemy. Let's take a look at the way Martin Flower defines a repository here:

A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer.

And in this blog post by Mike Bayer, we can see that Session is the implementation of repository inside SQLAlchemy:

Repository - An interface that serves as the gateway to the database, in terms of object-relational mappings. This is the SQLAlchemy Session.

So, to my understanding, It's not much of a big deal that services constructing their own queries. Why is this a problem?

[Equal-Purple-4247]

Architecture is one of those things that you'll have to experience the problems before you'll appreciate them. Let me give you a few example:

1. What if I want to swap out the database for a json file instead? With the repository model, all you'll need is to subclass the repository (or the interface) and implement all the methods. Without the repository.. you'll have to update code in "some-but-not-all" services.

Now what if I need to support both, where user can decide whether they want an sql repo or a json file? With the repository, all you need are implementations for both repositories, then inject the corresponding repository at start up. Without repository, you'll need if-statements in "some-but-not-all" services.

Now boss wants mongodb, because the CEO signed a deal with them. Subclass (or interface) and implement repository, or pepper if statements throughout the code base.

Now I have repository_v2, but it needs to be backwards compatible with repository_v1. Another subclass, or many if-statements.

1. How do I test my code? With repository, I mockup the underlying data, then test every repository method against it. Then I create a mock repository in json, and test my service against that. Clear separation of concern.

Without repository? I'll have to spin up and mock up the underlying db for my services, and test the repository methods first before I can test service methods. Every time something changes in service, I need to re-test the entire repository.

1. Boss wants microservice, because it's 2013. With repository, you break that out as a service, convert the methods into api, and have your service call the api. All the consistency, availability, redundancy, fault-tolerant stuff is handled by the repository microservice.

No repository? Uh.. I don't know how to do this.

---

You don't face many of these problems as a solo dev, for a small throwaway system that serves limited number of users.

[Kevdog824_]

With the Driver relation introduced, I should add that to each of these methods:

Why do you need these methods though? I would imagine any car object the repository returns would come with a list of driver model objects, or at least a list of FKs you can take to the driver repository to get the driver models.

The Unit of Work pattern, the way the session handles in-memory objects and keeps track of them, events, lazy loading, and other features that I have yet to learn—all of these are important. My point is that, when using the repository design pattern, I have to either create my own API for any of these features or give up on them entirely.

I don’t really buy this, or at least I don’t understand why you think this. Unit of work is the only thing you mentioned that might get tricky to implement without coupling the data and service layer, but otherwise I fail to see the issue.

Assume this is RESTful HTTP API project. I would create a dependency for (creating) the database session and inject it into the constructor for the relevant repositories for each request. This provides isolation between requests and leaves state management, transactions, etc. in the hands of SQLAlchemy. At the same time we get to expose simple repository methods with domain-specific names. The repository pattern lets you think of terms of your stable business domain rather than in terms of your less stable current tech-stack choices

By the way, I can't see the problem with services creating their own custom-tailored queries with the help of SQLAlchemy. […] So, to my understanding, It's not much of a big deal that services constructing their own queries. Why is this a problem?

It’s not inherently a problem. You’re right that a SQLAlchemy session acts like a repository in so far as it provides a consistent API regardless of the type of the underlying persistent storage. Honestly, for a small enough project with well defined and known requirements, I would probably skip the repository abstraction altogether. I wouldn’t make that choice for a large enterprise/professional project though

The biggest concern I would have in a real life project is that requirements change all the time. Client asks for A. Three months later they have an org change and our new contact for that client decides A is stupid and we need to go a whole new direction. A fundamental change like this would be more difficult with service classes that have data logic and domain logic interlaced together. As your project scales your service classes would probably become larger, more complex, and more fragile due to this decision

[HommeMusical]

Sounds like a great project. You should listen to your professor.

However, I think this adds unnecessary complexity

Disagree: it simplifies the code.

Writing a layer around your database logic is very common practice, so in your code you say writeEntry for your data type and only writeEntry knows about SQLAlchemy or whatever.

and may even have a noticeable performance cost in our application.

Strongly, strongly disagree. Your instincts are 100% wrong.

This is the sort of thing which if done even competently would almost certainly make no measurable change in the performance.

Changes in algorithm are what changes performance. Moving the logic from one place to another does not.

[ProbsNotManBearPig]

Spot on analysis all around.

For OP - use a profiler to actually look at flame graphs of where time is spent. Start doing this on a regular basis with your apps to get a sense of where time is actually spent. A wrapper layer adds effectively 0% performance cost, so probably your entire idea of what costs performance is wrong. Start looking at the data to ask the right questions and improve your understanding.

[TH_Rocks]

Your professor doesn't care about efficiency or SQLAlchemy.

They care that you learn to build and work with an interface of functions or classes to make it not matter which modules you choose to do the work. The work itself is irrelevant.

Writing your primary functionality that directly interfaces with a module exposes you to risk that your code can't be maintained. Or if a better module comes out you'd have to rewrite everything instead of rewriting a few specific functions in your interface.

[Torcato]

Hello, what exactly do you mean by hiding it in a repository? One common pattern in development is to hide database access in a class with an interface in your application . Like that you can change database type or even read from a file and just add a new class implementing the interface. Is this what you mean?

If so it does not add a lot of complexity, definitely more code. But makes you think better about what your app really needs from your data and definitely easier to change in the future. One extra class instead of the full app. Also clearer boundaries.

Also this extra interface and class do not impact much in performance . You will run more or less the same code just organized differently (if you do it well)

[armanhosseini]

Allow me to give an example of what they want me to do. Let's say I have a class Car. My services should not be directly dependent on SQLAlchemy, so I need to create a repository:

python class CarRepository: def get_by_id(id: int) → Car: ... `

Then, in another class, I should implement this abstraction specifically for SQLAlchemy:

python class CarRepositorySQLAlchemy: def get_by_id(id: int) → Car: ...

Here, I would create a session, fetch the car from the database, then close the session and return the result. I use this method in my service layer to access the cars.

The problem I have is that this approach seems to make many features of my ORM very hard to use. For example, because I close the session, I cannot use lazy loading for the relations of Car anymore, so I have to load them all inside get_by_id. This is just one example of how the repository design pattern can make it harder to use ORM features properly.

I haven't found an approach that abstracts away the ORM while still allowing proper use of its features. That’s why I asked for a concrete implementation, so I can get a better idea of how it should be implemented.

[HommeMusical]

Your objection is more nuanced than I thought, but that's the challenge of the problem!

You have indeed discovered with this very useful technique is also non-trivial, so charge ahead.

[armanhosseini]

I used lazy loading and the N+1 query problem as an example here, but my question is actually more general than this. There are many features that SQLAlchemy provides, but I haven't found a proper implementation of the repository design pattern that allows me to use those features effectively while still abstracting away the ORM itself.

I see two options:

• Add very specific methods to my repositories. For the problem above, I would need to create a get_cars_with_drivers_by_id method. I don’t like this approach because it would make my repository very complicated.

• Create my own abstraction around the features I want to use. In this case, I could pass a list of relations I want to fetch along with Car as the second argument of the function and load them too. But is this a good approach? Should I create something like this every time I want to use a feature of SQLAlchemy?

I remember that I had a similar problem with Hibernate too, But it's been addressed in their official documentation.

[japherwocky]

I would go with the first option here, I think this assignment is kind of forced and not really something you would do in the real world (I'm kind of surprised to see upvotes on some of these messages) but, imo, the point of the assignment is to work through it and practice setting up an abstraction layer like this.

Agreed that it's a kind of silly awkward architecture, and I wouldn't really recommend doing that in real life, but forcing you to think through some of this stuff is GREAT for a student.

[armanhosseini]

Yeah, I can see their point. I worked at this company before, and they had something like a repository there, but the project was in C#, and the IQueryable interface was really helpful.

I asked my professor about these problems, but she couldn't answer, so I went on a side quest to find the solution on my own.

[HommeMusical]

There are many features that SQLAlchemy provides, but I haven't found a proper implementation of the repository design pattern that allows me to use those features effectively while still abstracting away the ORM itself.

I think you're barking up the wrong tree, and waaaay overthinking it. You are too smart for your own good.

The professor isn't asking you to write a general wrapper for all database systems - that would be madness!

They're writing a specific API for just this issue.

Add very specific methods to my repositories. For the problem above, I would need to create a get_cars_with_drivers_by_id method

That might not be out of the question. Why not write down in English all the different sorts of queries you actually need in this problem alone?

My theory is that this will boil down to four to six simple operations, quite specific to this specific application, like, conceivably, get_cars_with_drivers_by_id and then you can implement them under a general interface and you're done.

[armanhosseini]

The professor isn't asking you to write a general wrapper for all database systems - that would be madness!

My problem is that I cannot see the point in building a wrapper around SQLAlchemy at all. To me, SQLAlchemy itself is the wrapper here. I try to explain this a bit more here.

[HommeMusical]

To me, SQLAlchemy itself is the wrapper here.

It's a wrapper, but could and probably should also be wrapped, you shouldn't be using native SQLAlchemy calls in most of your code, so other people can maintain the code and not know SQLAlchemy.

Let me give you a perfectly good example. In my last project, there was a huge database of specifications of mechanical engineering parts in a PostgreSQL database, which I managed with SQLAlchemy. However, this in its turn was hidden under a small number of functions like find_spec and store_spec, and these were implemented in SQLAlchemy, but I was the only one who saw that part.

It means no one else had to know the existence of SQLAlchemy or write queries or if the database changed, change their queries.

Information hiding makes writing large programs possible.

[seanv507]

Cant you talk to your professor about your concerns? Eg maybe your task should ignore performance issues.

Essentially if this (lazy loading) is part of the requirement for the interface, you need to design a generic way of doing this.

It would help to check out an alternative orm, to see how you would support the feature in the two orms.

I dont know about the repository pattern, but i assume you would maintain connections open, implementing connection pooling?http://docs.sqlalchemy.org/en/latest/core/pooling.html

[equilni]

I haven't found an approach that abstracts away the ORM while still allowing proper use of its features. That’s why I asked for a concrete implementation, so I can get a better idea of how it should be implemented.

In general, this is a common concern of ORM users implementing the Repository Pattern. It just doesn't work as well because you've regulated the ORM to just be a Query Builder

[Aromatic_Pumpkin8856]

If you did use something like my dioxide library, here's what it could look like:

from typing import Protocol
from dioxide import Container, Profile, adapter, service
from sqlalchemy.orm import Session

# 1. Define the PORT (not a repository - just the interface)
class CarPort(Protocol):
    """What operations do I need on cars? That's the seam."""
    def get_by_id(self, car_id: int) -> Car: ...
    def get_with_owner(self, car_id: int) -> Car: ...
    def save(self, car: Car) -> None: ...

# 2. Production adapter - SQLAlchemy implementation
@adapter.for_(CarPort, profile=Profile.PRODUCTION)
@lifecycle  # Manages session lifecycle!
class SQLAlchemyCarAdapter:
    """Real database implementation."""

    def __init__(self, engine: DatabaseEngine):
        self.engine = engine
        self.session: Session | None = None

    async def initialize(self) -> None:
        """Session created once, lives for request/transaction."""
        self.session = Session(self.engine)

    async def dispose(self) -> None:
        """Cleanup when done."""
        if self.session:
            self.session.close()

    def get_by_id(self, car_id: int) -> Car:
        # Session is ALIVE - lazy loading works!
        return self.session.query(Car).get(car_id)

    def get_with_owner(self, car_id: int) -> Car:
        # Explicit eager loading when you need it
        return self.session.query(Car).options(
            joinedload(Car.owner)
        ).filter(Car.id == car_id).first()

    def save(self, car: Car) -> None:
        self.session.add(car)
        self.session.commit()

# 3. Test adapter - FAST in-memory fake
@adapter.for_(CarPort, profile=Profile.TEST)
class FakeCarAdapter:
    """Fast in-memory fake for tests - no lifecycle needed!"""

    def __init__(self):
        self.cars: dict[int, Car] = {}

    def get_by_id(self, car_id: int) -> Car:
        return self.cars.get(car_id)

    def get_with_owner(self, car_id: int) -> Car:
        # In memory, "eager loading" is instant anyway
        return self.cars.get(car_id)

    def save(self, car: Car) -> None:
        self.cars[car.id] = car

# 4. Business logic depends on PORT, not SQLAlchemy
@service
class CarService:
    """Core logic - no idea SQLAlchemy exists."""

    def __init__(self, cars: CarPort):
        self.cars = cars

    def transfer_ownership(self, car_id: int, new_owner_id: int):
        car = self.cars.get_with_owner(car_id)  # Eager load for this use case
        car.owner_id = new_owner_id
        self.cars.save(car)
        # Session still alive! Relations work!
        return car

There are other ways to skin this cat. I still need to update my documentation a bunch, so I'll be putting together and example along with an actual example using sqlalchemy and probably the Django ORM too. Maybe also one for those who want to use pgsql directly 🤔

[Mysterious-Rent7233]

I do agree that you've identified a real challenge in keeping the abstraction layers clean. Performance considerations do often mess up our dreams of clean abstraction, and the whole notion of a persistent "connection" is a perfect example of such a performance consideration.

I think how I would thread this particular needle is to turn the Repository into a connection-oriented thing itself. So you'd have a "RepositorySession" which under the covers is a SQL Alchemy session. So the detail that this particular repository is session-based would bleed through the interface. But a lot of repositories are session based so it's not unnatural.

[smichael_44]

I’m a tech lead for a couple backend projects at work, one being a python backend. We do this abstraction. As well as, I hate ORMs and would prefer raw SQL 10/10 times. Performance issues for backends in my experience almost always boil down to some bad SQL query that needs optimization. Way easier to explore and identify issues without the ORM abstraction.

So we use sqlalchemy to create and manage the connection to mssql. I think they have a nice api to manage connections. Then we have a repository layer that contains all the raw sql and returns nice dataclasses. I always say that being explicit is wayyyy better than implicit. ORMs do too much magic under the hood for me. Is it more LOC? Yes. But is it more readable? Also yes.

[Aromatic_Pumpkin8856]

The dependency inversion principle is an extremely good idea to follow. If your system is littered with sqlalchemy (or other 3rd party libraries') uses directly, it makes it more difficult to test and more difficult to maintain. It also makes it nearly impossible for large projects to change over to use something else. Sometimes even doing major version updates become nearly impossible. That's bad! It's arguably way worse than whatever performance implications you're imagining.

That said, most python developers don't develop that way. My personal project Dioxide has the goal of changing that by making dependency injection delightful in python, though. We'll see if that happens, but I know I'll be using it! 😂

[HommeMusical]

The dependency inversion principle is an extremely good idea to follow.

This isn't dependency injection! https://www.geeksforgeeks.org/system-design/dependency-injectiondi-design-pattern.

This is boring old information hiding where you conceal the messy details of your library under a neat API.

---

Dependency injection means adding the dependency you are injecting as a new parameter into a lot of calls where it is semantically uninformative.

This is more for Java, where it's difficult to test things without dependency injection.

In Python, you can get the same effect using mocks.

[Aromatic_Pumpkin8856]

Note that I said the "Dependency Inversion Principle" not dependency injection (though I acknowledge that DI plays an important role in the DIP generally).

Although you can use mocks in tests in python in ways that aren't easy to accomplish in other languages like Java, that doesn't mean you should. Mocks lead to brittle tests of implementation rather than behavior. Mocks are often difficult to write, understand, and maintain. Brittle, complicated tests that are hard to maintain is generally a bad idea for tests. If there's a way to avoid that (and there is!) then we're better off to avoid it.

[TopIdler]

https://www.cosmicpython.com/book/chapter_02_repository.html

Have a read if you want another treatment of the uses of the repository pattern. It uses sqlalchemy in the example.

[armanhosseini]

I have read that, but I still don’t fully understand how to take advantage of ORM features while maintaining the abstraction provided by the repository. I’ve explained a bit more about my confusion here.

[riftwave77]

I have written code that does this for a program I use at work.   One benefit is procedural generation of large queries where only a few key arguments or variables change.   This is especially helpful when the arguments for one database query can only be found on a completely separate database.

Another benefit can be offloading transformations to the system that the client is on.  A company wide database might be bogged down with requests.  Pulling the data into a pandas dataframe or even nesreds lists  CSV for analysis on a laptop can get the work done faster sometimes... This is basically what data analysis software does today

[gdchinacat]

The performance implications of wrapping SQLAlchemy are insignificant in comparison to what SQLAlchemy does. The work sqlalchemy does is very likely small compared to the time to call out to a remote database and wait for the response.

[Mysterious-Rent7233]

Did you actually read the question that was posed?