Working with Database in Drupal 7 - Lesson 1 - Drupal DB API

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:

Drupal 7 Database API provides a standard, intuitive abstraction layer for accessing a database server. This API was designed to preserve the syntax and power of SQL as much as possible, while also:

• Supporting multiple database servers easily;
• Allowing developers to implement more advanced database features such as transactions;
• Providing a structured interface for dynamically building queries;
• Ensuring security and best practices through built-in mechanisms;
• Offering a clean interface for modules to intercept and alter site queries;

• The main documentation for the database API is embedded directly in code comments. The handbook supplements the API documentation with introductory guidance for module developers who want to interact with database servers from an administrator-friendly perspective. The handbook may not cover all API capabilities.

• The Database API is built with object-oriented programming concepts in mind, so familiarity with OOP is helpful. However, most common operations are available via procedural wrappers, allowing developers to choose the style they prefer.

Main Concept of the Drupal Database API

The database abstraction layer (referred to as the db layer) in Drupal is based on the PHP PDO library. PDO provides a unified, object-oriented API for accessing different database systems. However, it does not abstract SQL dialect differences between database systems.

Drivers

Since different databases require different interactions, the Drupal db layer requires a specific driver for each database type. Drivers are located in the includes/database/driver directory, where driver is the unique key for the driver — typically the lowercase name of the database such as "mysql", "pgsql", or a custom name like "mycustomdriver".

Each driver includes several classes that extend base system classes. These driver classes may override any behaviors necessary for supporting the database properly. The driver-specific classes follow the naming pattern: the base class name followed by an underscore and the lowercase driver name. For example, the MySQL-specific version of InsertQuery is InsertQuery_mysql.

Database Connection

A connection is an instance of the DatabaseConnection class, which extends the PDO class. Each database Drupal connects to has a single connection and an associated object. The connection object may be a subclass for the specific driver.

You can access the connection object like this:

To get the currently active connection:

Most of the time, you won’t need to get the connection object directly. Instead, use procedural wrappers (like db_query()) that do it for you. The main reason to get the connection manually is when you're working with multiple databases and want to avoid changing the active connection globally.

To set the active connection explicitly, use:

Database Queries

A query is an SQL statement sent to the database via a connection. Drupal’s DB API supports six types of queries: Static, Dynamic, Insert, Update, Delete, and Merge. Some queries can be written as SQL strings, but others are best handled via the object-oriented query builder. The term “query object” refers to the query builder for any query type.

Creating a Query

Query objects are typically created via SELECT operations. They are always instances of DatabaseStatement or its subclasses. DatabaseStatement extends PDOStatement.

Drupal uses prepared statements for all queries. A prepared statement is a query template where values are inserted at runtime. It separates SQL syntax from user-provided values, improving security and performance. A prepared statement can be reused for multiple queries.

Drupal doesn’t expose the prepared statement mechanism directly. Instead, developers pass SQL with placeholders and execute it via the query object, letting Drupal handle preparation and execution internally.