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

Name	Location	Description
db_and Deprecated	core/includes/database.inc	Returns a new DatabaseCondition, set to "AND" all conditions together.
db_close Deprecated	core/includes/database.inc	Closes the active database connection.
db_condition Deprecated	core/includes/database.inc	Returns a new DatabaseCondition, set to the specified conjunction.
db_delete Deprecated	core/includes/database.inc	Returns a new DeleteQuery object for the active database.
db_driver Deprecated	core/includes/database.inc	Retrieves the name of the currently active database driver.
db_escape_field Deprecated	core/includes/database.inc	Restricts a dynamic column or constraint name to safe characters.
db_escape_table Deprecated	core/includes/database.inc	Restricts a dynamic table name to safe characters.
db_insert Deprecated	core/includes/database.inc	Returns a new InsertQuery object for the active database.
db_like Deprecated	core/includes/database.inc	Escapes characters that work as wildcard characters in a LIKE pattern.
db_merge Deprecated	core/includes/database.inc	Returns a new MergeQuery object for the active database.
db_next_id Deprecated	core/includes/database.inc	Retrieves a unique id.
db_or Deprecated	core/includes/database.inc	Returns a new DatabaseCondition, set to "OR" all conditions together.
db_query Deprecated	core/includes/database.inc	Executes an arbitrary query string against the active database.
db_query_range Deprecated	core/includes/database.inc	Executes a query against the active database, restricted to a range.
db_query_temporary Deprecated	core/includes/database.inc	Executes a SELECT query string and saves the result set to a temporary table.
db_select Deprecated	core/includes/database.inc	Returns a new SelectQuery object for the active database.
db_set_active Deprecated	core/includes/database.inc	Sets a new active database.
db_transaction Deprecated	core/includes/database.inc	Returns a new transaction object for the active database.
db_truncate Deprecated	core/includes/database.inc	Returns a new TruncateQuery object for the active database.
db_update Deprecated	core/includes/database.inc	Returns a new UpdateQuery object for the active database.
db_xor Deprecated	core/includes/database.inc	Returns a new DatabaseCondition, set to "XOR" all conditions together.
hook_query_alter	core/lib/Drupal/Core/Database/database.api.php	Perform alterations to a structured query.
hook_query_TAG_alter	core/lib/Drupal/Core/Database/database.api.php	Perform alterations to a structured query for a given tag.

Classes

Name	Location	Description
Connection	core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php	PostgreSQL implementation of \Drupal\Core\Database\Connection.
Connection	core/lib/Drupal/Core/Database/Driver/mysql/Connection.php	MySQL implementation of \Drupal\Core\Database\Connection.
Delete	core/lib/Drupal/Core/Database/Query/Delete.php	General class for an abstracted DELETE operation.
Insert	core/lib/Drupal/Core/Database/Query/Insert.php	General class for an abstracted INSERT query.
Insert	core/lib/Drupal/Core/Database/Driver/pgsql/Insert.php	PostgreSQL implementation of \Drupal\Core\Database\Query\Insert.
Select	core/lib/Drupal/Core/Database/Query/Select.php	Query builder for SELECT statements.
Select	core/lib/Drupal/Core/Database/Driver/pgsql/Select.php	PostgreSQL implementation of \Drupal\Core\Database\Query\Select.
Update	core/lib/Drupal/Core/Database/Query/Update.php	General class for an abstracted UPDATE operation.

Interfaces

Name	Location	Description
QueryInterface	core/lib/Drupal/Core/Entity/Query/QueryInterface.php	Interface for entity queries.
SelectInterface	core/lib/Drupal/Core/Database/Query/SelectInterface.php	Interface definition for a Select Query object.
StatementInterface	core/lib/Drupal/Core/Database/StatementInterface.php	Represents a prepared statement.

Traits

Name	Location	Description
InsertTrait	core/lib/Drupal/Core/Database/Query/InsertTrait.php	Provides common functionality for INSERT and UPSERT queries.