Database abstraction layer

  1. 7.x drupal/includes/database/database.inc database
  2. 5.x drupal/includes/database.inc database
  3. 6.x drupal/includes/database.inc database
  4. 8.x drupal/core/includes/database.inc database

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

@sec sec_intro 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://drupal.org/developing/api/database

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

@sec sec_simple 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 @ref sec_dynamic 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 @ref sec_connection 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') ...

@sec sec_dynamic Dynamic SELECT queries For SELECT queries where the simple query API described in @ref sec_simple 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 @ref sec_connection below for details.

The dynamic query API lets you build up a query dynamically using method calls. As an illustration, the query example from @ref sec_simple 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 @code condition, etc. See
https://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.

@sec_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 @ref sec_dynamic 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 @ref sec_connection
below for details.

For example, if your query is:
@code
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();

@sec sec_transaction Tranactions 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();
  }
}

@sec sec_connection 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

http://drupal.org/developing/api/database

Entity API

Schema API

Functions

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

Classes

Namesort descending Location Description
Connection drupal/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
Connection drupal/core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php
Delete drupal/core/lib/Drupal/Core/Database/Query/Delete.php General class for an abstracted DELETE operation.
Insert drupal/core/lib/Drupal/Core/Database/Query/Insert.php General class for an abstracted INSERT query.
Insert drupal/core/lib/Drupal/Core/Database/Driver/pgsql/Insert.php
Select drupal/core/lib/Drupal/Core/Database/Driver/pgsql/Select.php
Select drupal/core/lib/Drupal/Core/Database/Query/Select.php Query builder for SELECT statements.
Update drupal/core/lib/Drupal/Core/Database/Query/Update.php General class for an abstracted UPDATE operation.

Interfaces

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

File

drupal/core/includes/database.inc, line 16
Core systems for the database layer.