4

Say that I have a repository that is used as READ-ONLY data provides (never for the writes/updates) - the query part of the CQS, NO event sourcing, hitting relational DB.

One of the essential rules - application logic is dictating the interface and the models to be restored from the repositories, no exposure of the underlying relational model is allowed.

Again, the repositories are NOT working the DDD aggregates, just plain POCOs.

Consider the following structure (dictated by application logic):

Customer
    ID
    Name
    Address
        Street
        Number
        PostCode
        Country
            ID
            IsoCode
            Name
    Company
        ID
        Name
        VatNumber
        Status
            ID
            Code
            Name
    Status
        ID
        Code
        Name

Consider following customer repository interface and operation (again, dictated by application logic):

public interface ICustomerRepository
{
    Customer Get(int ID);
}

When the implementation of ICustomerRepository.Get is called it should fetch data from DB and populate/return the Customer structure presented above.

Simple properties like a customer Customer > ID and Customer > Name are, usually, easily mapped from the underlying DB.

For the referenced data like Customer > Address > Country Customer > Company, Company > Status, Customer > Status, not so trivial, and that is a subject of this question.

Variant 1 - I can use the DB view that lists all referenced data and use some kind of convention mapping to restore the referenced data.

Variant 2 - Within customer repository - I can fetch the referenced IDs and then use other repositories (country, company, status, ...) in order to assemble the referenced data by fetching the data from those other repositories. Note that this approach would cause multiple DB hits.

Variant 3 - Same as second, but now repositories implement some kind of caching strategy (which complicates implementation overall) so that multiple DB hits are minimized.

Variant 4 - Use some ORM framework that can do this with minimal manual intervention/mapping - likely doable, but I am also STRONGLY INTERESTED IN NO-ORM ALTERNATIVES here.

Variant 5 - Something else...

What approaches have you been using??? Need some good suggestions how to tackle this load of the referenced data, minimize the impact of performance and complexity

  • I belive this question is too broad and opinon-based. The question corners itself by saying there should be no exposure of underlying relational model. But my experience is that some leakage is often inevitable, usually for purposes of performance and space optimizations. – Euphoric Oct 29 '18 at 11:33
  • Thank you for you interest. In terms of the clean architecture, the repository must expose structure dictated by the application/domain (and not the other way around). On the other hand, if the DB is properly designed, the app required structure will be pretty much aligned to DB structure. Let's say that some leakage is allowed, what would be the approach? – Dusan Oct 29 '18 at 11:54
  • Just a note. If you are implementing a relational data model, hitting the DB to solve joins is unavoidable. ORMs will perform one query per join mapped onto the persistence object model. They are likely to implement a 2nd level cache to improve performance. Some drivers will do it too. On the other hand, views don't do magic. Joins should be solved anyway. Depending the RDBMS views are going to execute the underlying query over and over (once per request) and calculate all the fields whether you need'em all or not. In other words, you are moving load from app to the DB. Not a big deal. – Laiv Oct 29 '18 at 13:53
3
+50

There is definitely an interesting problem at play here; but I think you're looking at it the wrong way.
Your question is, at some points, dangerously close to becoming an XY problem, as you seem to be indirectly asserting that caching (Y) is the solution to loading referenced data (X).

Some short feedback on why I'm not a fan of your suggested solutions, mostly just pointing out the inherent bottleneck/problem.

Variant 1 - I can use the DB view that list all referenced data and use some kind of convention mapping to restore the referenced data.

Not quite sure I follow; but are you suggesting to load all related data from the database and then pick the data you actually need afterwards? Because that's a severe waste of bandwidth.

Variant 2 - Within customer repository - I can fetch the referenced IDs and then use other repositories (country, company, status, ...) in order to assemble the referenced data by fetching the data from those other repositories. Note that this approach would cause multiple DB hits.

As you mentioned, multiple DB hits are a big problem here.

On the plus side, this is the cleanest approach purely from a development perspective. If data retrieval was an instant operation with no performance bottlenecks, this would be the best way to structure your code. Sadly, due to real life considerations related to the data retrieval method, we can't do this because it creates a massive bottleneck.

I would only consider variant 2 if you need absolute freedom in storing your entities, e.g. the ability to store them all in different ways/locations. When your data is stored in different locations, multiple hits are a logical inevitability, so it's no longer a problem that can be avoided.

Variant 3 - Same as second, but now repositories implement some kind of caching strategy (which complicates implementation overall) so that multiple DB hits are minimized.

Caching introduces more problems to the scenario.

You say that the repositories are considered read-only; but I assume this data can be changed by someone at some point in time. Because if it's never changed, I would consider not using an external database and instead having a local hardcoded file to use - it will lower maintenance costs and improve readability.

So if it's possible that this data changes at some point, application A's cache can become invalid if application B changes the underlying data - how is application A going to find out if this is the case, if not by calling the database, the exact operation you seek to avoid by having the cache in the first place?

We also get into minor issues such as memory usage - though you didn't quite mention how much data you're talking about.

If you're already considering caching the data locally, why not simply copy the entire database locally in an easy-to-parse format, and only refresh the cache at particular intervals? But again, this strongly relies on factors such as the projected size of the data, how often it tends to change, and how quickly you want your users to be aware of data changes.

Variant 4 - Use some ORM framework that can do this with minimal manual intervention/mapping - likely doable, but I am also STRONGLY INTERESTED IN NO-ORM ALTERNATIVES here.

I would suggest Entity Framework here, or use it as an example, but I'll refrain from doing so since you want an ORM-free solution.


What you need here is pre-emptive loading (more commonly referred to as "eager loading"). In other words, when you do the initial fetch, you should already know what related data you want to retrieve. This was, you can ensure that you only have to call the database once, have all the needed data in memory, do not risk any issues with data being outdated, and avoid loading unnecessary data.

No ORM can do this job for you. It's impossible to get what you want unless you specify exactly what you want. Unless you tell it to load everything, but that's not good either. ORMs may simplify the code, but the leg work (deciding which data should be loaded) will always be up to you.

However, and this is something that I've had to learn the hard way, repositories are an antipattern when you want to load multiple related entities at once.

The problem is that repositories tend to encapsulate each entity into a neat little box of their own, which is the stellar opposite of looking at the entities as a web of relations.

This is a discussion trap, we can argue about pro's and cons all day. So let me cut that short and give you a fair summary of what I think most people will agree with:

  • Repositories are good for single-entity operations, e.g. CRUD operations on a particular entity type.
  • Repositories are not built for multi-entity operations, e.g. retrieving a complex object graph.

For the latter, which I often shorthandedly refer to as "reports", what you really want is to create a new repository altogether, one that is tailored to retrieve the data the way you want it. For example:

  • If you introduce the idea of a "customer record", i.e. a customer and all their related data; then you can also introduce a CustomerRecordRepository, which inherently loads all data for the customer.
  • Similarly, you can introduce a "customer location", i.e. a customer and their address; and the subsequent CustomerLocationRepository.
  • ...

In short, you need a unique repository for every unique composition of loaded entities, if you want to be able to retrieve all data in a single efficient query (= no unnecessary data, guaranteed up to date).

Note
I still call it a "repository", because it will probably live alongside your existing repositories. However, you could also simply make it a "query object". It's the same thing but with a different name. When you look at the core implementation, a query object is effectively the same as a single repository method.
If you "report" repositories commonly end up having a single method, it would be better to call them a query object, though the rest of the implementation remains the same.

Note 2
When I say "a unique repository for every unique composition of loaded entities", that not a given. You could put these methods (each with a different combination of retrieved entities) in a single repository (e.g. ReportRepository), but unless there are only a handful of methods, this will quickly grow to unmaintainable proportions.


I just want to add a minor mention here that Entity Framework can actually help you in this regard. The Include() statement, which exists specifically to load related entities, can easily be parametrized and thus allow for easily loading additional data without needing to create a near-duplicate method every time you want to load an additional related entity.

However, since no exposure of the underlying relational model is allowed, this means (at best) that you can make your internal repository code more reusable; but you'll still have to abstract the include logic since you don't wan't callers to have direct access to/knowledge about the relational model.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.