Database API

Drupal's PDO-based database abstraction layer: query builders, placeholders and drivers that let one set of code run on MySQL, PostgreSQL or SQLite.

Learn DrupalDrupal glossary
Edited by Founder and CEO, DXPR Last updated

The Database API is the layer of Drupal code that talks to the database. A module asks the database service for a connection and then builds queries with methods such as select() and insert(), instead of calling one database engine directly. The same code can then run on any database that has a Drupal driver.

How the Database API works in Drupal 11

The connection is a Drupal\Core\Database\Connection object. In a class, inject the database service. In a static method or procedural code, \Drupal::database() returns the same connection. Before services exist, Database::getConnection() is the way in. Connection details live in the $databases array in settings.php.

Simple reads that do not involve entities use query() or queryRange(). You write the SQL with two changes. Table names go in curly braces, so a table name prefix can be applied. Values become named placeholders that start with a colon. Use queryRange() for limits, because the LIMIT syntax differs between databases.

Queries that are built up step by step use the dynamic API: select(), then fields(), condition(), orderBy() and range(), and finally execute(). Writes use insert(), update() and delete(). The connection also offers merge(), upsert() and startTransaction().

Content is different. The topic states that any query on entities or fields should use the Entity Query API, covered under Entity API.

Example

A service that lists the ten newest rows of a custom table for one user. The connection comes in through dependency injection:

use Drupal\Core\Database\Connection;

        class ExampleReader {

          public function __construct(protected Connection $database) {}

          public function latest(int $uid): array {
            return $this->database->select('example', 'e')
              ->fields('e', ['id', 'title', 'created'])
              ->condition('e.uid', $uid)
              ->orderBy('e.created', 'DESC')
              ->range(0, 10)
              ->execute()
              ->fetchAll();
          }

        }

Common mistakes

  • Building SQL with string concatenation. A value pasted into the query text opens the door to SQL injection. Use placeholders or the condition methods.
  • Leaving out the braces around a table name. The query then ignores the site's table prefix and fails on a prefixed database.
  • Chaining methods that do not return the query. join() returns the alias of the joined table and addField() returns the field alias, so chaining after them breaks.
  • Copying an old snippet that calls db_query() or db_select(). Those procedural wrappers are not defined in Drupal 11.4. Call the same methods on the connection object.

For marketing and content teams

You will rarely see the Database API, but it shows up in reports, imports and integrations that a custom module builds. If a developer proposes reading or changing your content with direct SQL, ask why the Entity API does not fit.

Sources

Questions about the Database API

For simple reads that do not involve entities or fields, the core documentation points to query() with placeholders. For entities and fields it says to use the Entity Query API. Writes go through insert(), update() or delete(), never query().

A placeholder such as :uid stands in for a value, and you pass the real value in a separate array. Drupal sends the value apart from the SQL text, which protects against SQL injection. Do not put quotes around a placeholder.

Core ships drivers for MySQL, PostgreSQL and SQLite, built on PDO, and Drupal 11.4 also contains an experimental MySQLi driver. Contributed or custom modules can add drivers for other engines. The driver key in the $databases array in settings.php picks one.

Related terms

All Drupal terms from A to Z