Database abstraction layer in Drupal 10

Same name and namespace in other branches

1. 8 core/lib/Drupal/Core/Database/database.api.php \database
2. 4 includes/database.inc \database
3. 5 includes/database.inc \database
4. 6 includes/database.inc \database
5. 7 includes/database/database.inc \database
6. 9 core/lib/Drupal/Core/Database/database.api.php \database

Allow the use of different database servers using the same code base.

Overview

Drupal's database abstraction layer provides a unified database query API that can query different underlying databases. It is generally built upon PHP's PDO (PHP Data Objects) database API, and inherits much of its syntax and semantics. Besides providing a unified API for database queries, the database abstraction layer also provides a structured way to construct complex queries, and it protects the database by using good security practices.

Drupal provides 'database drivers', in the form of Drupal modules, for the concrete implementation of its API towards a specific database engine. MySql, PostgreSQL and SQLite are core implementations, built on PDO. Other modules can provide implementations for additional database engines, like MSSql or Oracle; or alternative low-level database connection clients like mysqli or oci8.

For more detailed information on the database abstraction layer, see https://www.drupal.org/docs/8/api/database-api/database-api-overview.

Querying entities

Any query on Drupal entities or fields should use the Entity Query API. See the entity API topic for more information.

Simple SELECT database queries

For simple SELECT queries that do not involve entities, the Drupal database abstraction layer provides the functions \Drupal::database()->query() and \Drupal::database()->queryRange(), which execute SELECT queries (optionally with range limits) and return result sets that you can iterate over using foreach loops. (The result sets are objects implementing the \Drupal\Core\Database\StatementInterface interface.) You can use the simple query functions for query strings that are not dynamic (except for placeholders, see below), and that you are certain will work in any database engine. See Dynamic SELECT queries below if you have a more complex query, or a query whose syntax would be different in some databases.

Note: \Drupal::database() is used here as a shorthand way to get a reference to the database connection object. In most classes, you should use dependency injection and inject the 'database' service to perform queries. See Database connection objects below for details.

To use the simple database query functions, you will need to make a couple of modifications to your bare SQL query:

• Enclose your table name in {}. Drupal allows site builders to use database table name prefixes, so you cannot be sure what the actual name of the table will be. So, use the name that is in the hook_schema(), enclosed in {}, and Drupal will calculate the right name.
• Instead of putting values for conditions into the query, use placeholders. The placeholders are named and start with :, and they take the place of putting variables directly into the query, to protect against SQL injection attacks.
• LIMIT syntax differs between databases, so if you have a ranged query, use \Drupal::database()->queryRange() instead of \Drupal::database()->query().

For example, if the query you want to run is:

SELECT e.id, e.title, e.created FROM example e WHERE e.uid = $uid
  ORDER BY e.created DESC LIMIT 0, 10;

you would do it like this:

$result = \Drupal::database()
  ->queryRange('SELECT e.id, e.title, e.created
  FROM {example} e
  WHERE e.uid = :uid
  ORDER BY e.created DESC', 0, 10, array(
  ':uid' => $uid,
));
foreach ($result as $record) {

  // Perform operations on $record->title, etc. here.
}

Note that if your query has a string condition, like:

WHERE e.my_field = 'foo'

when you convert it to placeholders, omit the quotes:

WHERE e.my_field = :my_field
... array(':my_field' => 'foo') ...

Dynamic SELECT queries

For SELECT queries where the simple query API described in Simple SELECT database queries will not work well, you need to use the dynamic query API. However, you should still use the Entity Query API if your query involves entities or fields (see the Entity API topic for more on entity queries).

Note: \Drupal::database() is used here as a shorthand way to get a reference to the database connection object. In most classes, you should use dependency injection and inject the 'database' service to perform queries. See Database connection objects below for details.

The dynamic query API lets you build up a query dynamically using method calls. As an illustration, the query example from Simple SELECT database queries above would be:

$result = \Drupal::database()
  ->select('example', 'e')
  ->fields('e', array(
  'id',
  'title',
  'created',
))
  ->condition('e.uid', $uid)
  ->orderBy('e.created', 'DESC')
  ->range(0, 10)
  ->execute();

There are also methods to join to other tables, add fields with aliases, isNull() to query for NULL values, etc. See https://www.drupal.org/developing/api/database for many more details.

One note on chaining: It is common in the dynamic database API to chain method calls (as illustrated here), because most of the query methods modify the query object and then return the modified query as their return value. However, there are some important exceptions; these methods (and some others) do not support chaining:

• join(), innerJoin(), etc.: These methods return the joined table alias.
• addField(): This method returns the field alias.

Check the documentation for the query method you are using to see if it returns the query or something else, and only chain methods that return the query.

INSERT, UPDATE, and DELETE queries

INSERT, UPDATE, and DELETE queries need special care in order to behave consistently across databases; you should never use \Drupal::database()->query() to run an INSERT, UPDATE, or DELETE query. Instead, use functions \Drupal::database()->insert(), \Drupal::database()->update(), and \Drupal::database()->delete() to obtain a base query on your table, and then add dynamic conditions (as illustrated in Dynamic SELECT queries above).

Note: \Drupal::database() is used here as a shorthand way to get a reference to the database connection object. In most classes, you should use dependency injection and inject the 'database' service to perform queries. See Database connection objects below for details.

For example, if your query is:

INSERT INTO example (id, uid, path, name) VALUES (1, 2, 'path', 'Name');

You can execute it via:

$fields = array(
  'id' => 1,
  'uid' => 2,
  'path' => 'path',
  'name' => 'Name',
);
\Drupal::database()
  ->insert('example')
  ->fields($fields)
  ->execute();

Transactions

Drupal supports transactions, including a transparent fallback for databases that do not support transactions. To start a new transaction, call startTransaction(), like this:

$transaction = \Drupal::database()
  ->startTransaction();

The transaction will remain open for as long as the variable $transaction remains in scope; when $transaction is destroyed, the transaction will be committed. If your transaction is nested inside of another then Drupal will track each transaction and only commit the outer-most transaction when the last transaction object goes out of scope (when all relevant queries have completed successfully).

Example:

function my_transaction_function() {
  $connection = \Drupal::database();

  // The transaction opens here.
  $transaction = $connection
    ->startTransaction();
  try {
    $id = $connection
      ->insert('example')
      ->fields(array(
      'field1' => 'string',
      'field2' => 5,
    ))
      ->execute();
    my_other_function($id);
    return $id;
  } catch (Exception $e) {

    // Something went wrong somewhere, so roll back now.
    $transaction
      ->rollBack();

    // Log the exception to watchdog.
    watchdog_exception('type', $e);
  }

  // $transaction goes out of scope here.  Unless the transaction was rolled
  // back, it gets automatically committed here.
}
function my_other_function($id) {
  $connection = \Drupal::database();

  // The transaction is still open here.
  if ($id % 2 == 0) {
    $connection
      ->update('example')
      ->condition('id', $id)
      ->fields(array(
      'field2' => 10,
    ))
      ->execute();
  }
}

Database connection objects

The examples here all use functions like \Drupal::database()->select() and \Drupal::database()->query(), which can be called from any Drupal method or function code. In some classes, you may already have a database connection object in a member variable, or it may be passed into a class constructor via dependency injection. If that is the case, you can look at the code for \Drupal::database()->select() and the other functions to see how to get a query object from your connection variable. For example:

$query = $connection
  ->select('example', 'e');

would be the equivalent of

$query = \Drupal::database()
  ->select('example', 'e');

if you had a connection object variable $connection available to use. See also the Services and Dependency Injection topic.

See also

https://www.drupal.org/developing/api/database

Entity API

Schema API

File

core/lib/Drupal/Core/Database/database.api.php, line 10

Hooks related to the Database system and the Schema API.

Functions

Classes

Interfaces

Traits