Saurabh Pillai wrote:
Do you guys call database queries right from service layer or you have one more layer for DB access? If you have DB access layer, what is the benefit? What type of decoupling does it provide?
You should have another data access layer. That's what the M stands for in MVC.
There are 2 important purposes in my view
1) Decoupling : As you mentioned, its important to separate areas of concern so that maintainability is easy. In case there are newer queries to be added, current queries to be modified, there is an upgrade in the db technology itself(say from MSSQL Server to Oracle), the service layer does not need to know anything. The service layer will be content calling a few methods in the data access layer and that is it.
2) Testing : Unit testing would be much easier. You can test the methods in data access layer without dependence on service program and make sure that from the basic query level you dont have any error in the system.