Drupal 6 vs Drupal 7 Database Primer - Part 1

With Drupal 7 right around the corner, I have recently put myself to the task of ramping up on what's new, what's changed, and what do I – as a module developer – need to know when I sit down to code my first D7 module (or upgrade one of my D6 modules *shameless plug*). I've spent the last few weeks scouring over the D7 core and API documentation, and let me be the first to tell you if haven't heard it yet (unlikely): better times are ahead. For all of us.

The amount of API changes from D6 to D7 are broad and sweeping. D7 is certainly still Drupal, meaning that it still feels and acts like Drupal. Nodes are still nodes that can have taxonomy and comments, users are still users with roles and permissions, so on and so forth. But nearly everything under the hood and in the UI, for that matter, has been improved upon and/or refactored. From a coder’s perspective, I am soon to be in developer’s heaven when D7 goes stable. From the standpoint of being a framework, D7’s API is more mature and modern. You could almost think of D6 as a kid in highschool and with D7 that kid has graduated and is now ready for the real world. The refactoring that has taken place over the last couple of years and all of the new features now available will be a boon for module and theme developers alike. This also explains just why we have had to wait so long for a stable release. It’s almost as if you took your grocery-getter car to Q from James Bond for an “upgrade”. But, I digress.

This article will be the first in a series exploring D7’s API. For this article (itself a two-part series), I want to talk about what I feel, as a developer, is the most substantial and important change to the Drupal core. The new database abstraction layer (or DBTNG Database: The Next Generation) is a complete overhaul of the database layer we have grown accustomed to in versions D6 and before. Built on PDO, the database layer in Drupal is now truly abstract, meaning that it is database agnostic. This gives Drupal the flexibility to run on any number of different platforms without having to write new code or change any existing code that interacts with the database. This also means that we can expect to see a lot more enterprise-level Drupal sites once D7 hits stable as it’s footprint in the corporate world will now be able to grow substantially. Gravy. So, let’s see what all this means.

First and foremost, gone are the days of having to write ludicrously long and complicated MySQL or Postgres dependent SQL statements to fetch or write data. Gone are the days of having to define your placeholders’ type. And gone are the days of having to worry about what would happen to your data if an insert or update query failed in the middle of executing (yes, we finally get to use transactions!).

Let’s jump right in and start with basic SELECT queries in D7’s new database layer. D7 introduces us to the concepts of static and dynamic queries. Static queries are queries that get passed to the database as is, meaning you query the database with a SQL string you write. Dynamic queries are built by Drupal using a query object and that query object’s methods. A few basic examples may help illustrate the difference.

Example of a static query in D7:

<?php
  $result = db_query(“SELECT nid FROM {node}”);
?>

Well, that should look familiar to anyone who has done any Drupal module development in the past. Our old friend db_query() is still here but is much more powerful now. How so? Glad you asked. First, the way we define placeholders has changed. We no longer have to type hint them with %d or %s, and we also don’t have to worry about remembering to surround string placeholders with quotes. Let’s see the difference:

Example of using placeholders in D6:

<?php
  $result = db_query(“SELECT mail FROM {users} WHERE name = ‘%s’”, “Bob”);
?>

Example of using placeholders in D7:

<?php
  $result = db_query(“SELECT mail FROM {users} WHERE name = :name”, array(‘:name’ => ‘Bob’));
?>

At first, this may seem a little convoluted as we have to write a little more code to accomplish the same task, but the benefits far outweigh the few extra characters I have to type. Placeholders, as seen above, are now defined using the convention :identifier. No need for quotes, no need to worry about the type. The function db_query()’s second parameter is an associative array where the keys are the placeholder identifiers and then assigned their corresponding values. It’s basically the same principle that the Drupal function t() uses with its placeholders. You can also assign a placeholder value to be an array.

An example taken from the official handbook page for D7’s database layer:

<?php
  // This code:
  db_query("SELECT * FROM {node} WHERE nid IN (:nids)", array(':nids' => array(13, 42, 144));

  // Will get turned into this prepared statement equivalent automatically:
  db_query("SELECT * FROM {node} WHERE nid IN (:nids_1, :nids_2, :nids_3)", array(
    ':nids_1' => 13,
    ':nids_2' => 42,
    ':nids_3' => 144,
  ));

  // Which is equivalent to the following literal query:
  db_query("SELECT * FROM {node} WHERE nid IN (13, 42, 144)";
?>

Sweet. Another goodie that db_query() now has thanks to PDO is the ability to define how our results will be retrieved. You can provide db_query() with a third parameter which is a PDO-specific constant that defines the retrieval type.

The types we can define are:

• PDO::FETCH_OBJ — fetches results into an object
• PDO::FETCH_ASSOC — fetches results into an associative array
• PDO::FETCH_NUM — fetches results into an indexed array
• PDO::FETCH_BOTH — fetches result into both an indexed and associative array

By default, results are fetched into an object. Here’s what an example of fetching our results into an associative array looks like:

<?php
  $result = db_query(“SELECT mail FROM {users} WHERE name = :name”, array(‘:name’ => ‘Bob’), array(‘fetch’=> PDO::FETCH_ASSOC));
?>

As you can see, db_query()’s third parameter is an associative array with the key ‘fetch’ where we assign the retrieval type. This array can also have another key defined: ‘target’, which tells Drupal where to retrieve the results from. As of the writing of this article, this is limited to only 2 possible values: “default” and “slave”. Still, though, the potential here makes me giddy.

Additionally, we can also pass the name of a class we want are results to be retrieved in. Say what?!? Oh yes. If we have defined this class, and we pass db_query() the name of our class, results will be retrieved and assigned as properties of that class. If that class has a constructor method, by default it will run after the results are retrieved and the properties defined, but we can also tell db_query() to run the class’ constructor method before the properties are assigned.

Another example from the official handbook page:

<?php
class exampleClass {
  function __construct() {
    // Do something
  }
}

  $result = db_query("SELECT id, title FROM {example_table}", array(), array(
    'fetch' => 'ExampleClass',
  ));
?>

Dynamic queries in D7, on the other hand, are a different creature. We have a new function that we will use for dynamic queries: db_select(). We can pass db_select() three parameters. The first is a database table name. The second is an alias for that table name. The third parameter is identical to db_query()’s third parameter. db_select() returns a query object which has some methods defined that we can use to construct our dynamic query.

An example of a dynamic query in D7:

<?php
  $query = db_select(‘users’, ‘u’);
  $query
    ->condition(‘u.name’, ‘Bob’)
    ->fields(‘u’, array(‘mail’));
  
  $result = $query->execute();
?>

Need to join in another table? No sweat. Like I mentioned earlier in this article gone are the days of having to write cumbersome SQL queries. Here’s how easy it is to do a join with dynamic queries in D7:

<?php
  $table_alias = $query->join('user', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

Our query object’s join method takes a table name as the first parameter, and an alias for that table as the second parameter. The third parameter is the “ON” condition of the join and the last parameter is for placeholder replacement. The join method returns the table alias of the table assigned. We can also pass SELECT query object to the join method as the first argument.

An example from the handbook:

<?php
  $myselect = db_select('mytable')
    ->fields('mytable')
    ->condition('myfield', 'myvalue');
  $alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

I don’t have nearly enough room to go into all the features and capabilities of dynamic queries in this article, suffice to say as you can see from the few simple examples above they are extremely powerful and flexible and you’ll be using them in your own modules a lot very soon. Less code and more bang for the buck is always fine by me.

In the second part of the this article, I will go over insert, update, and delete queries and will introduce you merge queries.

Until next time, happy coding.

Further reading:

• Official D7 Database API Handbook
• D7 Database Abstraction Layer API Documentation
• Static queries
• Dynamic queries