Blog

If you’ve written modules for Drupal 6, switching to the new Drupal 7 Database API won’t be too difficult. The new DB API is based on the PHP PDO extension, which gives it the ability to work with various databases like MySQL, PostgreSQL, MSSQL, and potentially Oracle. Whether it’s easier to use is subjective — getting used to the new syntax takes time, and writing SQL queries is still more familiar for many developers.

Here’s a brief excerpt from the official documentation:

The primary mechanism for defining a database connection in Drupal is the $databases array in settings.php. As the name implies, $databases allows you to define multiple database connections. It also supports defining multiple targets. A connection is not opened (i.e., a connection object is not created) until a piece of code executes a database query for the first time.

The most common form of a query in Drupal is a static query. A static query is passed directly to the database as-is. Only SELECT queries can be static.

Use static queries only for very simple operations. For more complex, dynamically constructed, or modifiable queries, you should use dynamic queries.

The simplest way to execute a static query is via the query method:

<?php $result = $conn->query("SELECT nid, title FROM {node}");
?>

The preferred approach is to use the procedural wrapper:

We’ve now reached perhaps the most exciting part of Drupal’s Database API: dynamic queries. These are called "dynamic" because Drupal appends the query string on the fly. All INSERT, UPDATE, DELETE, or MERGE queries can be dynamic. SELECT queries can be either static or dynamic. However, it is recommended to use dynamic queries even for SELECT operations.

Select queries in Drupal support extenders. An extender allows you to add functionality to a SELECT query at runtime. This functionality can either be a new method or can override the behavior of an existing one.

This is achieved using object-oriented programming design patterns. Extenders implement the Decorator Pattern, attaching additional responsibilities to a dynamic object by providing a flexible subclass-like extension of query methods.

An important feature of dynamic SELECT queries is that other modules can alter these queries on the fly. This allows modules to insert their own instructions into a query, influencing its behavior or applying changes during execution—such as enforcing node access permissions. There are three components involved in altering queries on the fly: tagging, meta data, and hook_query_alter().

A SELECT query will always return a result that contains zero or more rows. There are several ways to process query results, and you can choose the one that best fits your needs.

The most common way is using a foreach() loop:

Insert queries should always use the query builder. Some databases require special handlers for LOB (Large Object, such as text in MySQL) and BLOB (Binary Large Object) fields, so an abstraction layer is necessary for individual DB drivers to implement these handlers.

Insert queries start with the db_insert() function:

Update queries should always use the query builder. Different databases have specific handlers for LOB (Large Object, such as TEXT in MySQL) and BLOB (Binary Large Object) fields, so an abstraction layer is required for individual database drivers to implement these specifics.

Update queries must start with the db_update() function:

Delete queries should use the query builder. They begin with the db_delete() function:

<?php $query = db_delete('node', $options);
?>

This delete query will remove records from the node table. Note that you do not need to wrap the table name in curly braces—Drupal's query builder handles that automatically. Delete queries use a Fluent API, meaning all methods (except execute()) return the query object itself, just like update and insert queries.