When it comes to a large applications with huge database containing milions of records, you soon realize, plain selects, updates, inserts and deletes simply are not enough.
So you start thinking in a different way. You create procedures and triggers to take care of more complicated stuff directly in the database and this is not very good. Databases offer great performance when performing CRUD operations. Long procedures? Not so much.
The problem with procedures
Now imagine, that you switch to a database which does not support the concept of procedures? What are you going to do? You are forced to move the procedures to your code base instead, where you can be pretty sure that once you program it in let's say Java, it will always stay there, no matter which database engine you choose.
Not to mention, your procedures are usually part of your business logic and it is not a good idea to have your business logic splatered across your codebase and database.
Ideally, you should always have a mediator between the database and the client implementing its own business rules. Providing direct access to database is not a good idea, because when you do so, the one with access has direct access to the tables and can do pretty much anything with the data there is.
- Takes longer to develop: Of course, you are creating a new system, that is going to be more time consuming than simply giving the client a database connection string and let him write the queries.
- More complex: Complexity of a system > complexity of a database query.
- The server does more work: Not necessarily. With good design, caching,... you can move the load from the database server to the one of the mediator.
- Slower: In terms of development? Yes. In terms of speed when retrieving data? No. You can optimize your mediator using caches (such as - popular as of January 2016 - Redis, Elasticsearch) and actually make it deliver data faster than a plain database query.
- Migrating to other platforms is easier: Migrating to a new database engine? Definitely. Migrating the whole mediator to a new language? Not really.
- The Business Logic is also needed when calling directly the database. It won't take much longer to develop: As explained previously, the procedures problem.
- Security: With proper authorization having mediator is definitely much more secure than giving a user direct access to the database, because you restrict him to the end points which run only the queries you want to.
- Maintainability: One of the best benefits of having a mediator. If there is a bug in an API your clients call, you fix it, push the fix to your VCS repository, build your mediator from the currect version of VCS containing the fix and all your clients are suddenly using the fix, without them needing to download an update. This is simply impossible to do, if the queries are stored directly in the client applications. In that case, clients are forced to update their application.