Should there be a abstraction layer between database and model?

Question

What I see a lot is that people use a Object Relational Mapper (ORM) for doing SQL stuff when working in a MVC environment. But if i really have complex queries I would like to write this whole query myself. What is the best practice for this kind of situation?

1. Having a Abstraction Layer between your model and the database with the complex queries
2. Still using the model with creating specific methodes that handle the queries

Or is there any other way that might be better? please tell me :)

Answer 1

Consider the Single Responsibility Principle. Specifically, the question would be...

"If I put data access logic in my model, what will that mean when I need to change something?"

Any time you need to change business logic, you're also changing the objects which maintain data access logic. So the data access logic also needs to be re-tested. Conversely, any time you need to change data access logic, you're also changing the objects which maintain business logic. So the business logic also needs to be re-tested.

As the logic expands, this becomes more difficult very quickly.

The idea behind the Single Responsibility Principle is to separate the dependencies of different roles which can enact changes to the application. (Keep in mind that "roles" doesn't map 1-to-1 with "people." One person may have multiple roles, but it's still important to separate those roles.) It's a matter of simpler support. If you want to make a change to a database query (say, for performance reasons) which shouldn't have any visible affect on anything else in the system, then there's no reason to be changing objects which contain business logic.