What are the advantages to using SQL query builders?

Question

Are there any advantages to using a query builder, rather than using raw SQL?

E.g.

$q->select('*')
  ->from('posts')
  ->innerJoin('terms', 'post_id')
  ->where(...)

vs:

SELECT * FROM posts WHERE ...

I see that many frameworks use these kind of abstraction layers, but I fail to understand the benefits.

Answer 1

The abstraction of writing the SQL via a framework well, abstracts.

Writing SQL by hand is not all that bad by itself, but you start to get issues with escaping and sanitizing and this turns into a mess. An abstraction layer can take care of all of this behind the scenes allowing your code to be clean and free of lots of mysql_real_escape_string() calls or the like.

Additionally, this brings in the possibility of accounting for different dialects of SQL. Not all databases are built same and there may be variations in keywords or the syntax of a certain functionality. Using an abstraction layer brings in the ability to generate the correct syntax for your variant dynamically.

While an abstraction layer can introduce a performance hit, it is generally negligible compared to the cleanliness and robustness of code you receive in return.

Answer 2

Query builders are a pet hate of mine, so much so I wrote my own Framework (Apeel) to avoid using them!

If you use PDO (which I definately recommend you do) then santising the input is handled for you.

Like someone else said, although they do make it easier to switch between databases they tend to support "Lowest common denominator" functionality and will either not support or have poorer performance for more advanced features.

I've been developing systems with databases since around 1986 and in all that time I've rarely encountered a company actually changing the database they use other than when they needed better performance. If you are changing databases for better performance then it makes a lot more sense to spend your time hand-optimising your queries to get the best out of the new database rather than take the hit of a query builder for the sake of simplicity.

The time spent getting to grips with the qwirks of a query builder (then re-learning when you switch to a better one) would be far more productively spent learning how to optimise your SQL.

Anyway that's why NOT to use one, some people love them though.

Answer 3

I think the practical, day-to-day benefit of query builder - is code reuse and ability to follow DRY principle.

With query builder you can put repeating parts of SQL into methods. And then use these methods to compose complex SQL. An example would be e.g. reusable JOIN clause:

function joinTaskWithClient($queryBuilder) {
    $queryBuilder->join('task', 'contract', 'task.contract_id = contract.id')
                 ->join('contract', 'client', 'contract.client_id = client.id');
}

So the usage would be:

$queryBuilder->select('client.name')
             ->from('client')
             ->where('task.id=:task')->setParameter('task', 42);
joinTaskWithClient($queryBuilder);

As you may note - with query builder you can add parts of SQL in any order (e.g. JOIN part after WHERE one) in contrast to the case when you gather SQL string manually. Also, you may read about builder pattern to see its intent and benefits.

I agree regarding escaping and sanitizing, but this could be achieved w/o query builder as well. Regarding DB type / dialect abstraction - this is quite theoretical and questionable benefit, hardly ever used in practice.

Answer 4

Theoretically? Yes. Glenn Nelson pointed out how they will often help you. (If it is a good query builder).

In practice? Doesn't always live up to the theory and could actually cause problems. Suppose that you are using a query builder against some popular DBMS and everything is peachy. Then a customer asks you to hit their DBMS that has some quirks that your chosen query builder just can't handle. (I hit this problem when I had to work with an older version of Pervasive.)

BUT! What you should absolutely do is separate out the Data Access Layer and make sure you can swap in a new one if needed. That way you can you that cool query builder with all the features but if you need to plug in a new one that uses that odd pseudo-sql for the DB in question.

Answer 5

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)

Requirements

1. Support multiple DB vendors (eg. MySQL, PostgreSQL, SQLite, MS SQL / SQL Server, Oracle, DB2, .. )
2. Easily extended to new DBs ( prefereably through a, implementation-independent, config setting )
3. Modularity and implementation-independent transferability
4. Flexible and Intuitive API

Features

1. grammar-based templates
2. custom soft views support
3. db abstraction, modularity and transferability
4. prepared templates
5. 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)

Use-case examples:

1. CMS platform able to work (with no change of underlying code) with multiple DB vendors
2. 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)
3. Prototyping with another DB than one used in production (would require duplicate code base at least for some of the code)
4. 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
5. 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?

Answer 6

Quite often, some of the argument for these queries indeed is some values rather than constants. Now, many of them has essentially been derived from user form posts. And hence there are many possibilities for SQL injection attacks. So inherently query formation does require full validation.

Now, this is not to say that we don't trust developer, but formation of query might be easy, but repeating all possible validation checks everywhere might just implies that you might miss sometimes incidentally or modify query but don't modify query but don't update the validation check. Some newbie might even know all the dangers of missing out on this. Hence the query builder abstraction is quite essential.

Answer 7

I used to think query builders were GUI apps that allowed you to select tables and make joins graphically while generating SQL under the hood, but now I understand that you also call query builders the APIs that provide a way of not having to create pure SQL queries, so abstracting yourself of potential differences in SQL flavors.

Using such query builders is good, but I tend to think that people who rely heavily on them tend to not ask DBAs: "hey, this is a query I use a lot, please create a view from it".

Don't get me wrong.

I think that you should write queries against views and not tables. Not for security or filtering, which are good reasons, but for the same reason you should code against interfaces and not against concrete classes: decoupling. Views are like "contracts", the same way interfaces are "contracts" in OOP. You can change the underlying tables, but as long as you force the views to show the same "contract" to the programmers, code should not break.

Again, don't get me wrong, you can use query builders to query views, but much views come to exist as a maduration process that is the product of having to write queries and asking your DBA: "man, create this, please".

Am I wrong in thinking that by not writing queries you fail to detect the need of creating certain views?

Another thing that concerns me is novice programmers not mastering SQL, one of the most beautiful pieces of technology created by man, by not having to do so.