i will provide an answer based on the readme file of a custom SQL builder of mine (Dialect)
(plain text follows, removed library-specific references)
- Support multiple DB vendors (eg. MySQL, PostgreSQL, SQLite, MS SQL / SQL Server, Oracle, DB2, .. )
- Easily extended to new DBs ( prefereably through a, implementation-independent, config setting )
- Modularity and implementation-independent transferability
- Flexible and Intuitive API
- grammar-based templates
- custom soft views support
- db abstraction, modularity and transferability
- prepared templates
- data escaping
i think the above features and requirements sketch the reasons one would use an SQL abstraction builder
Most of the above features are supported by most SQL builders (although i dont think all listed are supported, to my knowledge)
- CMS platform able to work (with no change of underlying code) with multiple DB vendors
- Custom application code where DB vendor is apt to change and/or dB schemas are dynamic (this means many queries cannot be hard-coded but still need to be abstracted enough so code is robust to changes)
- Prototyping with another DB than one used in production (would require duplicate code base at least for some of the code)
- Application code is not tightly-coupled to specific DB provider and/or implementation (even within the same DB vendor, e.g different versions of DB vendor), thus is more robust, flexible and modular
- Many usual cases of queries and data escaping are handled by the framework itself and usualy this is both optimal and faster
Finaly, an example of a use-case i had. i was building an application where the underlying DB schema (wordpress) was not well-suited for the type of data queries that needed to be done, plus some of the WP tables (e.g posts) had to be used (so having completely new tables for all application data was not an option).
In that case being able to make an MVC-like application where the model could be queried by custom / dynamic conditions made query hard-coding almost a nightmare. Imagine having to support querying maybe up to 2-3 tables with joins and filtering the conditions to see what table to join with what and also take care of the aliases required and so on.
Clearly this was a query abstraction use-case and, even more, it needed (or at least greatly benefited from) having an ability to define custom soft views (a conglomerate of joined tables as if they were one custom table suitable for the model). Then it was much easier, cleaner, modular and flexible. In another aspect, the application (code) also used the query abstraction layer as a (db schema) normalisation tool. As some say, it was future-proof.
If, tomorrow, the people decide they need some extra options or data, it is very easy to add that to the model in a couple of lines and work fine. Additionaly, if, tommorow, the people decide they dont want to use wordpress anymore (as the application is loosely-coupled to wordpress as a plugin), it is also relatively easy to change (just the definition of) the models in a couple lines of code to adapt to the new schema.
See what i mean?