Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    
I think one should write queries against views and not against tables. I tend to think people who use query builders tend not to write views or ask DBAs to create them for them. In doing so they don't leverage all the power of the RDBMS. – Tulains Córdova Feb 2 at 1:38
1  
@user61852: Other than possibly some security and filtering for free, what can queries against views provide that queries against tables cannot also provide? – Robert Harvey Feb 2 at 5:15
4  
@RobertHarvey The same thing that programming to interfaces instead of concrete classes. Decoupling and flexibility. The design of the underlying tables could chance as long as the "contract", the view, remains "simulating" the same columns as ever. – Tulains Córdova Feb 2 at 8:35
    
@user61852 Fair enough. – Robert Harvey Feb 2 at 15:36
    
@RobertHarvey I turned that into an answer. – Tulains Córdova Feb 2 at 15:39

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

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

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

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.

share|improve this answer
    
For me , this is also a main benefit. Another one is that with abstracting into methods you can give the methods more meaningful names and even create a Domain Specific Language from this, making the intent much more clear. You can also pass the query builder around as well and let different components add the it specific bits to it. Last but not least I found it allowed me to encapsulate patterns behind meaningful-named methods.... I have found some query builders where adding columns stupidly overwrote earlier ones,which kind of renders a lot of the above useless... – malte Mar 4 at 17:13

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

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?

share|improve this answer

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.

share|improve this answer
    
How about a DBA who says, "This query is running poorly, let's work together and improve it." It may have worked just fine at first, but now is running into problems. If all that is needed is an index, why bother the dev with that? – JeffO Feb 2 at 16:46
    
That's a total different situation, and is perfectly OK. – Tulains Córdova Feb 2 at 17:58
    
I just feel like involving the DBA every time a query pulls a single record from a table or view creates a bottleneck in the development process. – JeffO Feb 2 at 18:55

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.