Quick guide to the new Drupal 7 database layer

This post is my effort to guide you through the whole new abstraction layer for accessing the database server, which comes with the Drupal 7 core distribution. In this blog-post I’ll try to explain to you guys (girls) how this new layer works, without diving into all the details, in order to give you a clear view on this new system.

First of all I’ll explain the benefits of this new system to you. Drupal needed a database system that could easily support multiple database servers in a unified way which preserves the syntax power of SQL. The system was also build to enforce the security checks. There are 6 different types of Query’s which can be called in this database system: Insert, Update, Delete, Merge, Static and Dynamic.

Now lets get it started!

Settings.php

We should start by taking a look at the renewed database definition in the settings.php. In most cases you will be using a drupal site with only one database. For this you should use the following structure.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb’,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘localhost’,
);
?>

The first thing you should notice are the values “default” in the $database array. The first one is the CONNECTION KEY, the second one is the TARGET.

The connection key is a unique identifier for a database connection, there must ALWAYS be a ‘default’ connection key available.

The target is used to define master/slave database structures. If the master (default) isn’t available, the system will search for the slave database. It is also possible to flag a query to run on the slave database. To make this a bit clearer I’ll give you a more complicated database structure.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['default']['slave'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>

In this case, the first database is the default database, the second is the slave databases.

It is also possible to define separate database structures.

<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['extra']['default'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>

Note that no matter how many connections are defined in the settings file, These connections will not be used by Drupal until they are actually opened.

Select Query

So far for the database connections, now lets take a look at the actual use of query’s in this new database layer. For the regular select query’s not much changes. Here is an example of a select query with a short explanation.

$result = db_query(“SELECT nid, title FROM {node} WHERE type = :type”, array(
‘:type’ => ‘page’,
));

The db_query function uses three arguments, first one is the query string, the second one are the values used to fill up the placeholders. The third one will be explained at the next example.

Note that the placeholder (:type) doesn’t use quotes. Another thing you should take in to account is to put your database names between {}. This is needed for the database system to attache a prefix string if this is defined in your settings.

Now for the third argument of the db_query we will take a look at the following code:

<?php
$result = db_query(“SELECT nid, title FROM {node}”, array(), array(
‘target’ => ’slave’,
));
?>

The third argument is an array of configuration directives to detect the way the query should run. In this case the query runs on the slave database. I won’t go into the details here, because (as I said before) in this blog post I will only pick up the basics of the new database system.

The following is just a handy guide about the way you can use the database query’s results. (Not 100% relevant to this post, but this might come in handy for some of you. The others should just scroll through the code, as if it doesn’t exist).

$result = db_query(“SELECT nid, title FROM {node}”);
foreach ($result as $record) {
// Do something with each $record
}

$record = $result->fetch(); // Use the default fetch mode.

$record = $result->fetchObject(); // Fetch as a stdClass object

$record = $result->fetchField($column_index); // Fetch only one field.

$number_of_rows = $result->rowCount(); //Count the results.

Insert Query

Now we’ve arrived to he fun part. The INSERT, DELETE and UPDATE query’s require that you use the query builder object in order to behave consistently across all different databases. This is where the new object-oriented query API comes in.

The compact INSERT form is the following:

<?php
$nid = db_insert(‘node’)
->fields(array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
))
->execute();
?>

This will result in the following query:

INSERT INTO {node} (title, uid, created) VALUES (’Example’, 1, 1221717405);

Note: If you don’t call the execute() method, the query will not run!

The insert query object can also be used with multiple values. To insert multiple rows you shouldn’t only use fields() but also values(). In this case fields() only defines the fields, but doesn’t put any content into the selected fields. The values() may be called multiple times in order to add more than one line to your database.

<?php
$query = db_insert(’node’)

->fields(array(‘title’, ‘uid’, ‘created’))
->values(array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
)))
->execute();
?>

Using a ‘foreach’ the code will look like this:

<?php
$values = array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
);
$query = db_insert(‘node’)->fields(array(‘title’, ‘uid’, ‘created’));
foreach ($values as $record) {
$query->values($record);
}
$query->execute();
?>

Update Query

Next stop is the UPDATE query. The update query is pretty straight forward, if you understand how the insert query’s work, it shouldn’t be a problem to understand the update query. Here it goes:

<?php
$num_updated = db_update(‘node’)
->fields(array(
‘uid’ => 5,
’status’ => 1,
))
->condition(‘created’, REQUEST_TIME - 3600, ‘>=’)
->execute();
?>

This will result in the following query:

UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405;

Not much to explain here, so let’s go to the DELETE query’s.

Delete Query

Again the same story here. The DELETE query is probabily the easiest form of the query object:

<?php
$num_deleted = db_delete(‘node’)
->condition(‘nid’, 5)
->execute();
?>

This will result in the following query:

DELETE FROM {node} WHERE nid=5;

Merge Query

Finally we’ve got to the last one. The MERGE query. This one is a bit more complicated. If you would strip this one down to it’s original form, you will finde that a merge query is actually just the combination of an insert and an update query. In php it would be something like this:

<?php
if (db_query(”SELECT COUNT(*) FROM {example} WHERE id=:id”, array(’:id’ => $id)->fetchField()) {
// Run an update using WHERE id = $id
}
else {
// Run an insert, inserting $id for id
}
?>

In the new database API structure the merge query’s are build up like this:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->execute();
?>

Here the “example” table is used. The specified key field ‘name’ has the value of $name. Now two things could happen.

First option: If the $name value exists in the database, then fields “field1” and “field2” will get an update with the correspondingvalues.

Second option: If the $name value doesn’t exist in the database, a new row will be created in which “name” gets the value $name, “field1” gets the value $field1 and “field2” gets the value $field2.

In some cases the values you want to set will have to be different, according to the fact that the key field does or doesn’t already exist. This can be handled in two ways.

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->update(array(
‘field1′ => $alternate1,
))
->execute();
?>

In this case, if the “name” already exists the value of “field1” will be $alternate1, and the value of “field2” will be $value2. If the “name” doesn’t allready exist, $value1 and $value2 will be used.

It is also possible to use expressions. I’ll give you an example in which, if the ‘name’ already exists, the “value1” field will become the current value +1:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->expression(‘field1′, ‘field1 + :inc’, array(‘:inc’ => 1))
->execute();
?>

Note that expression() can be used multiple times, 1 time for each field.

Field updates can also be limited, if the row already exists. In this case, if the “name” already exists, only “field2” will be updated, and “field1” will be ignored:

<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->updateExcept(‘field1′)
->execute();
?>

This is the end of my quick guide into the new Drupal 7 database layer. Hope you’ve enjoyed it. For more detailed information about this system, I would like to refere to the official drupal database API: http://drupal.org/developing/api/database