I'm trying to design a simple way of abstracting away data persistence behind an interface, but I'm having a hard time figuring out how much fine grained control should be exposed to higher layers.
Limit, Filter, Order, etc.
Defining an interface for a dumb data repository is all fine and dandy for the basic CRUD operations, but what about all the other common database capabilities such as result filtering, limiting, map/reduce and other transformative or conditional operations? Do these types of operations violate the single responsibility principle in the sense that a persistence layer should just be a dumb retrieve/persist black box -and consequently, would this functionality be better implemented on the calling code side (i.e, in a service layer, or even client side)?
Client side transformations
Although it would greatly simplify the persistence layer, personally, I feel that it would be quite wasteful to perform these operations client side. For example, regardless of whether a user queries for a collection of 10 resources or 1000, the database would have to return the entire data set incurring bandwidth and caching costs, among other things. Also many of these queries are probably heavily optimized natively, and this would throw all of that out the window.
A smart database abstraction
On the other hand, if I were to expose this database functionality in the persistence layer, it would greatly increase the complexity of the interface and it would probably just end up looking like a 1:1 wrapper of the database driver (which is handy for mock testing purposes I suppose, but kind of defeats the purpose of writing the abstraction in the first place).
My question is: is there a general rule of thumb when designing a database abstraction layer for what functionality to expose and what to keep hidden?