Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

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

share|improve this answer
    
Shouldn't something like the DB quirk situation be resolved beforehand? I mean finding out what DB your client is using and choosing the proper frameworks/libraries accordingly is something that should be handled before you write a single line of code. –  Glenn Nelson Mar 3 '12 at 12:48
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
I don't think the SQL dialects differ across RDBMSes. And in PHP there's PDO which does the sanitization for u –  Anna K. Mar 3 '12 at 2:12
7  
SQL dialects do differ, that is why they are called dialects. As for PDO, the abstraction layer simply hides this mess from us. –  Glenn Nelson Mar 3 '12 at 2:16
    
@GlennNelson Anna meant any one dialect, using different backends (PSQL/MySQL/SQLite...) –  Izkata Mar 3 '12 at 2:35
2  
@AnnaK. The dialect may not change, but sometimes the features are different. For example, MySQL (with the MyISAM engine) doesn't support Foreign Key restrictions, while PostGres does. Either the dialect will have to handle such a thing itself (which requires full knowledge of the data structure, like the Django ORM does), or, more likely: the user has to be smart about how they use it, which could make it look like the dialect changes, depending on circumstances. –  Izkata Mar 3 '12 at 2:37
1  
+1 for letting a well-built tool do your escaping and sanitizing for you. If it can also do validating, then even better. –  Dan Ray Mar 13 '12 at 14:45
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.