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

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 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.