Database abstraction layer

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 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.

For more detailed information on the database abstraction layer, see https://www.drupal.org/developing/api/database.

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 db_query() and db_query_range(), 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.

As a note, db_query() and similar functions are wrappers on connection object methods. In most classes, you should use dependency injection and the database connection object instead of these wrappers; 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 db_query_range() instead of db_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 = db_query_range('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).

As a note, db_select() and similar functions are wrappers on connection object methods. In most classes, you should use dependency injection and the database connection object instead of these wrappers; 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 = db_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 have a

WHERE e.foo IS NULL 

condition, 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.

@section_insert INSERT, UPDATE, and DELETE queries INSERT, UPDATE, and DELETE queries need special care in order to behave consistently across databases; you should never use db_query() to run an INSERT, UPDATE, or DELETE query. Instead, use functions db_insert(), db_update(), and db_delete() to obtain a base query on your table, and then add dynamic conditions (as illustrated in Dynamic SELECT queries above).

As a note, db_insert() and similar functions are wrappers on connection object methods. In most classes, you should use dependency injection and the database connection object instead of these wrappers; 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');
db_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

$txn = db_transaction(); 

The transaction will remain open for as long as the variable $txn remains in scope; when $txn 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 out of scope (when all relevant queries have completed successfully).

Example:

function my_transaction_function() {
  // The transaction opens here.
  $txn = db_transaction();

  try {
    $id = db_insert('example')
      ->fields(array(
        'field1' => 'mystring',
        'field2' => 5,
      ))
      ->execute();

    my_other_function($id);

    return $id;
  }
  catch (Exception $e) {
    // Something went wrong somewhere, so roll back now.
    $txn->rollback();
    // Log the exception to watchdog.
    watchdog_exception('type', $e);
  }

  // $txn goes out of scope here.  Unless the transaction was rolled back, it
  // gets automatically committed here.
}

function my_other_function($id) {
  // The transaction is still open here.

  if ($id % 2 == 0) {
    db_update('example')
      ->condition('id', $id)
      ->fields(array('field2' => 10))
      ->execute();
  }
}

Database connection objects

The examples here all use functions like db_select() and db_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 db_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 = db_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 8

Hooks related to the Database system and the Schema API.

Functions

Classes

Interfaces

Traits