Database systems can implement very different approaches. Just compare SQL vs. NoSQL. And for the latter compare key-value stores with graph databases. So you'll not find a silver bullet that will offer a single API with all the benefits of these databases. So you'll have to narrow down your scope.

Considering the limits, filtering, ordering, many of those are related to the queries of the database. So I think that you definitively need to take care of this in your API. If you don't, you might suffer. Not because of extra calls between client and API, but because if you force doing it on client side, you will force your app to solicit the database server much more than needed. It's one variant of the traditional issue of client vs. server side processing.

But unless you want to write your own general purpose API, I'd strongly advise to design your persistence layer starting from your application architecture, and especially how your architecture will handle the mapping between your objects in memory and the data objects on the database (e.g. object-relational mapping).

The problem has some similarities with serializing/deserializing challenges: i.e. how to map relations between objects, composition of objects, containers of objects, etc... Once you have the clear picture, you should design your database layer with the operation needed to support the primitives of your object mapping.

One good start would be to consider the proxy design pattern for the mapping in-memory proxies that control database objects. The approach I propose you here is very different from developing a universal database wrapper. But it has the advantage of allowing you to get the best from the underlying database architecture, i.e. take benefit of graph and document databases.

Database systems can implement very different approaches. Just compare SQL vs. NoSQL. And for the latter compare key-value stores with graph databases. So you'll not find a silver bullet that will offer a single API with all the benefits of these databases. So you'll have to narrow down your scope.

Considering the limits, filtering, ordering, many of those are related to the queries of the database. So I think that you definitively need to take care of this in your API. If you don't, you might suffer. Not because of extra calls between client and API, but because if you force doing it on client side, you will force your app to solicit the database server much more than needed. It's one variant of the traditional issue of client vs. server side processing.

But unless you want to write your own general purpose API, I'd strongly advise to design your persistence layer starting from your application architecture, and especially how your architecture will handle the mapping between your objects in memory and the data objects on the database (e.g. object-relational mapping).

The problem has some similarities with serializing/deserializing challenges: i.e. how to map relations between objects, composition of objects, containers of objects, etc... Once you have the clear picture, you should design your database layer with the operation needed to support the primitives of your object mapping.

One good start would be to consider the proxy design pattern for the mapping in-memory proxies that control database objects. The approach I propose you here is very different from developing a universal database wrapper. But it has the advantage of allowing you to get the best from the underlying database architecture, i.e. take benefit of graph and document databases.