Lets say we have a table 'A' with 4 referenced tables : Table B,C,D,E

In our Data Acces Layer we could write a method that returns Table A containg every entity of B,C,D,E by joining in order to reuse that method in bussnies layer.

Is the better aproach just creating a new method for every query you need? For example if you need information from Table B you create a method that returns Table A joined with B, if you need information from C you create a method that returns Table A joined with C, this breaks the DRY concept ,I belive

Which one is considered a best practice and when should I use one or the other.

  • None is "best practice". You have to find a balance for your context, this is nothing you decide about in thin air.
    – Doc Brown
    Sep 23 at 21:59
  • Dry is about repeating knowledge. Checking user.Age > 18 in multiple different places is a dry violation. Multiple methods that query the same tables is not. That’s still a code duplication, but not a dry violation.
    – Rik D
    Sep 24 at 5:53

The joins required for a SQL query are usually driven by the use case. You could end up with one repository method per use case, which would result in a large number of methods that are only called once or twice in the application. While there is nothing wrong with this, it does tend to make for cluttered and unorganized data access code.

There is no general or easy solution. A good Object-Relational Mapper (ORM) can give you lots of flexibility to configure and fine tune these queries, but that comes at the cost of increased complexity and configuration. This could be justifiable, though, given the number of queries and use cases.

Using ORM API calls outside your data access layer ends up coupling the ORM to areas of your application that should remain ignorant of how data is persisted. This coupling makes refactoring and testing harder. Numerous strategies have been developed to mitigate this, but they all have their drawbacks.

You could decide that more coupling to the data access layer is justified. For example, in an MVC application the controller could have direct knowledge of the data access code beyond dealing with interfaces or other abstractions. The controller could make calls to the ORM or hard code the table names as arguments to data access methods.

Still other strategies involve building additional abstractions over an ORM or existing data access code — layering abstraction on top of abstraction. Query builders are an example of such an abstraction, which provide an object-oriented way to create SQL queries. I've still found these to be leaky abstractions, though. Names of tables and columns inadvertently propagate throughout your code even as you attempt to hide theses details.

Command-Query Responsibility Segregation (CQRS) is a design pattern that at least attempts to clean up this problem. The model used to modify data is separate from the model used to retrieve data. This can give you more flexibility to couple persistence knowledge in an area of the application specializing in retrieval of data. These classes can implement interfaces to facilitate mocking and stubbing for unit tests, but again, this increases complexity.

Whether you pollute the code base with details of your ORM or table names, crank up complexity by introducing more abstractions over your data access tier, or create an enormous number of repository methods, you must make sacrifices. Just make sure the benefits outweigh the drawbacks, and try to be consistent. Even if the code is not ideal, if it is consistent it is at least predictable, which means it is livable.


You could have a string array parameter and build the query based on that parameter.

public A getA(List<String> joins) {
  Query query = ...
  joins.forEach(join -> query.join(join))
  // quality of life could be improved with query.joinAll(joins)

This is way oversimplified. You would probably need table, alias and condition, making joins some type of Join parameter. But the main point is that you need some abstraction over the query string to make this trivially painless. But doing the abstraction yourself might prove to be a challenge. You could do it as a project if you really wanted. Otherwise use some library.

If you come from JS/TS check out NestJS/TypeOrm combo to see how an ORM query builder could make this surprisingly easy. (you would do something along the lines of bRepository.find({ relations: ['a', 'c']}) and it would work out of the box).

If you come from Java, Hibernate is an option, but my memories of it are quite a lot worse than with TypeOrm, although I haven't used it in years. I was also playing with JDBI recently and it seems to be quite good, but I haven't used it enough to really have an opinion.

If you use something else, look on google for query builders/ORMs for your language and try some out as these tools seem to be hit and miss, each one with a bit different philosophy from the other :)

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.