Document usage of static vs dynamic queries

I think this belongs more in the Database API section rather than on the coding standards page. The coding standards page deals with syntax conventions more than how to use the Drupal APIs.

OK... The rest of the main coding standards page is not anything like the new section. Agreed: it should go onto the SQL coding conventions sub-page... not sure when/if that will be updated for D7. But this new section definitely doesn't fit with the rest of the main coding standards page.

Also, I'm moving this to the DB section so that DB maintainers can review what you wrote... No one but me regularly reads the doc queue.

The text is sound, ok to move it to 2497.

The text is really backward. Static queries should be preferred UNLESS you need the capabilities of dynamic queries, and it should be documented why (performance and flexibility; there are queries that the select builder can't handle).

I also think this information should go in the DB API section.

@Crell: hm? On the contrary, dynamic queries are the only option unless you have (1) a purely static query, (2) that don't require alteration or extending.

Dynamic queries are the norm, static queries the well-bounded exception.

Oh wow; we're not just going to have a bikeshed discussion we're going to have an existential bikeshed discussion. Awesome!

The select builder was very consciously designed as an "only when needed" API. In fact, its primary use case was Views and other heavily dynamic code, not normal module authors. Since then we've discovered just how many places in core had their own horribly bad "query builder" for various one-off cases so it ended up more widely used than anticipated, but it is still a much heavier system with more overhead than static queries.

Most queries are purely static and do not require alteration or extending.

There are some query types that you simply cannot do with the query builder. SQL is too complex to completely represent 100% in any reasonably efficient PHP model.

The query builder does have non-trivial overhead. A not-overly-complex query involves somewhere around a dozen function calls, lots of array processing, and string compilation, plus potentially hook invocations, above and beyond the cost of db_query().

Although the query builders are pretty darned slick, there are still some DX gotchas with them, such as not all of the methods of the select builder are chainable. There are perfectly good reasons for that in most cases, but it still is more annoying if you're doing a not-too-interesting query. Maybe we can clean that up a bit in D8, but for now it is what it is.

People's first inclination should be, and we should guide them to, using static queries. There are cases, clear specific cases, where you'd want to use the query builder. But if you don't need it, don't use it. If unsure, default to static unless there is a specific reason to go with the builder.

Drupal 7's performance is pathetic enough as is. Let's not encourage people to make it worse.

On http://blog.worldempire.ch/story/howto-convert-module-dbtng, I used the following to describe when to use a dynamic query:

DBTNG provides a flexible Query Builder, which needs to be used if one or multiple of the following things apply to a specific query:

* Others need to be able or might want to extend the query (Especially if the query used db_rewrite_sql() before)
* If the query is very dynamic and does add different conditions, joins and other things based on something.
* If so called query extenders (see below) are being used.

Feel free to re-use anything from that blog post if it is useful ;)

This is pretty important. :)

I'd suggest adding a table indicating which (My)SQL features can be done without worry in db_query(), and which require db_select(). E.g., http://drupal.org/node/310072 does not include any examples of queries with JOIN statements, so I was under the impression that one must use db_select() for even simple joins.

There is a very incomplete list started in the documentation: http://drupal.org/node/773090 "For portability across supported database engines your code should only use functions known to be part of the ANSI standard and supported across all databases that Drupal supports."

However, it does not include joins or modifiers like GROUP BY.

And given that "all the databases that Drupal supports" should at least consider drivers that are well-supported in contrib such as MSSQL and Oracle, i'm thinking we need a table... but it seems nothing can be that simple here's a comparison of command implementations in MySQL, PostgreSQL, MS SQL Server, and Oracle among others: http://troels.arvin.dk/db/rdbms/

(And it doesn't cover GROUP BY either.)

I have updated http://drupal.org/node/310072 to make it clear that it supports arbitrarily complex query strings and is the preferred way of handling SELECTs.

Oh please :). The addition crell made is very understandable and that is the most important thing, right? I don't think its usefull to add a ton of complex technical stuff in those docs. Most of the developpers are helped with that additon. If someone has a more complex question he/she should ask it in irc.

I'm closing this. If this hurts someone feelings, feel free to reopen... (please don't ;) )

That addresses the original post, but not the woeful state of Functions and operators.

Could we at least have a link to the recommended ANSI standard?

mlncn sure, if you got a link I'll place it in:

"s supported database engines your code should only use functions known to be part of the ANSI standard and su"

i don't know what it should be linked to, or i'd edit the handbook page already. See #14 and #15 for reason / as much as i know.

I'm getting tired of explaining, but you should only use db_query() for *super simple* SELECT queries. Crell is dead wrong in his recommendation here. db_query() is definitely not the preferred way of handling SELECTs.

Anything that is not a straight SELECT aaa FROM bbb WHERE c = :a causes portability troubles. For example, the use of something as simple as the LIKE operator requires db_select(). Even the behavior of ORDER BY is different between database engines (most of them except MySQL requires that (1) ORDER BY use only columns or expressions from the SELECT clause, (2) ORDER BY do no use aliases from the SELECT clause).

See #1182428: Document that db_like() and Database/Connection::escapeLike() do not work when used with db_query() for an example of problems with db_query().

I edited http://drupal.org/node/310072 to at least stop the bleeding.

Ok, that are good reasons to open this again :)

I think #23 makes this not major.
Also removing the backport tag since this is handbook material.

Probably not assigned anymore.

This popped up again in #1881146: [policy] Decide when to use db_select() and when db_query() (or the appropriate connection commands), and I'm still kind of interested in concrete differentiation. What operations make a query non-simple? What are the more discrete performance implications? Do we have some numbers somewhere?

If this isn't nailed down now, things will get worse in someone's eyes. For example, I already converted one of my modules to use mostly db_select(), and now I'm seeing that I probably didn't need to. Before I convert it back, would be good to know What To Do™.

Broadly, I find that the "simple" cases are mostly gone and turned into entities/fields - the exception being (very occasional) truly simple selects/drupal_write_record's with no/rare joins for lookup stuff that is "more than a variable, but not yet an entity". Chances are if you are doing a lot of things in this second category, you may well be doin' it wrong ;)

In other words, the "normal" times I find that I need to build queries they tend to be of the "complex" kind (e.g. programmatically building conditional queries to support gnarly views handler or preprocess needs), justifying using db_select. Even then, half the time it works out better to use a view as the query builder.

In other words, I don't think "simple" necessarily/always equates to "common", so perhaps it isn't wise to promote it as "the norm" (unless my world is an edge case, perhaps).

For Drupal 7, "simple" queries are still very very common.

For Drupal 8... the dust hasn't settled enough for me to say how common they are. I have no idea. :-)

Actually since this is Handbook material, I'm moving this to the appropriate issue queue.

I'd like to side with Crell on this one, especially after seeing some of the insanity that has occurred throughout the D7 cycle, where almost every dev I run into seems to think "db_select is the way of the future, and db_query is an icky Drupal 6-style way to do database queries".

I'm not sure where that line of thought comes from, but from both a performance and usability/stylistic standpoint, db_query should generally be preferred for most any select statement, besides ones that need to be dynamic.

See #1067802: Compare $connection->query() and $connection->select() performance, specifically #1067802-18: Compare $connection->query() and $connection->select() performance, for some more concrete data on performance. For usability/style, simple select statements which are one-liners when using db_query often turn into messy 5+ line statements, and achieve nothing extra, except for making the actual query/statement harder to decipher. Especially for anyone who is more familiar with SQL than with Drupal's DB API (e.g. DBAs or anyone who isn't a Drupal developer but needs to help with DB-related work).

For dynamic queries, or queries that may need to be altered in circumstances where a normal hook or plugin-based change wouldn't suffice, db_select makes sense. For almost everything else, I can't stomach a 20%+ performance decrease / 5% memory usage increase per query at the expense of readability and transparency.