Drupal 7 - The New Database Layer | robin

Drupal 7 - The New Database Layer

Submitted by robin on August 31, 2008 - 12:39am

Have I ever told you how much I love data? After listening to Larry Garfield's talk Drupal Databases: The Next Generation, I now love data more than ever!

The database layer for Drupal 7 has been completely rewritten to take advantage of PHP's PDO extenion. Not only does it allow Drupal to work with many different database types it also simplifies how developers interact with the database.

For instance, a module developer won't write INSERT, UPDATE, and DELETE statements as of D7. Instead you get to use a lovely object. It clears many messes:

The old way;

<?php
 db_query("INSERT INTO {test} (name,age) VALUES ('%s', %d)", 'Yoko', 29);
?>

The new way;

<?php
  $query = db_insert('test');
  $query->fields(array(
    'name' => 'Yoko',
    'age' => '29',
  ));
  $query->execute();
?>

You do not have to worry about %s or %d, whether you need single quotes, or even typos in your SQL. Drupal's new database layer takes care of all of those issues.

That is just the beginning. The new database layer is also a fluent API which means the functions are "fully chainable":

<?php
  $query = db_select('test');
  $name_field = $query->addField('name', 'test');
  $age_field = $query->addField('age', 'test', 'age');
  $query->orderBy('name');
      
  $count = $query->countQuery()->execute()->fetchField();
      
  $this->assertEqual($count, 4, "Counted the correct number of records.");
?>

It gets even better because we have db_merge() which allows developers to be lazy about INSERT vs. UPDATE: Drupal takes care of that issue for us.

db_rewrite_sql() can be removed because we will have hook_query_alter() (this is so cool). You can access the query and alter it with the same ease as altering a form (OMG I can not get over this).

<?php
function dbtest_query_alter(SelectQuery $query) {

  // you might add a range
  if ($query->hasTag('db_test_alter_add_range')) {
    $query->range(0, 2);
  }

  // or add a join
  if ($query->hasTag('db_test_alter_add_join')) {
    $people_alias = $query->join('test', 'people', "test_task.pid=people.id");
    $name_field = $query->addField('name', 'people', 'name');
    $query->condition($people_alias . '.id', 2);
  }
...
?>

One thing you can do right now to prepare for the new database layer in D7 is to start passing your arguments in db_query() in an array.

Like this:

<?php
$params = array($nid,$uid);

db_query("SELECT nid, title FROM {node} n WHERE n.bid = %d AND n.uid = %d",$params); 
?>

This simple step will make it easier for you to upgrade to D7. The D7 version will provide support for our current use of db_query and the other db calls, but they are eventually going away.

I have only covered a couple of the changes that come with the new database layer. Considering the ease of adding database drivers to PDO, the potential for Drupal's growth is huge. chx added a little speech at the end of the talk. His arms waving wildly, he discussed Drupal's enterprise potential once we can take advantage of sqllite and other database types. He looked like he was bent on world domination. Pretty freakin' cool!