Database abstraction layer

You are here

  1. 7 includes/database/database.inc database
  2. 4.6 includes/database.inc database
  3. 4.7 includes/database.inc database
  4. 5 includes/database.inc database
  5. 6 includes/database.inc database
  6. 8 core/includes/database.inc database

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

Drupal provides a database abstraction layer to provide developers with the ability to support multiple database servers easily. The intent of this layer is to preserve the syntax and power of SQL as much as possible, but also allow developers a way to leverage more complex functionality in a unified way. It also provides a structured interface for dynamically constructing queries when appropriate, and enforcing security checks and similar good practices.

The system is built atop PHP's PDO (PHP Data Objects) database API and inherits much of its syntax and semantics.

Most Drupal database SELECT queries are performed by a call to db_query() or db_query_range(). Module authors should also consider using the PagerDefault Extender for queries that return results that need to be presented on multiple pages (see https://drupal.org/node/508796), and the TableSort Extender for generating appropriate queries for sortable tables (see https://drupal.org/node/1848372).

For example, one might wish to return a list of the most recent 10 nodes authored by a given user. Instead of directly issuing the SQL query

SELECT n.nid, n.title, n.created FROM node n WHERE n.uid = $uid
  ORDER BY n.created DESC LIMIT 0, 10;

one would instead call the Drupal functions:

$result = db_query_range('SELECT n.nid, n.title, n.created
  FROM {node} n WHERE n.uid = :uid
  ORDER BY n.created DESC', 0, 10, array(':uid' => $uid));
foreach ($result as $record) {
  // Perform operations on $record->title, etc. here.
}

Curly braces are used around "node" to provide table prefixing via DatabaseConnection::prefixTables(). The explicit use of a user ID is pulled out into an argument passed to db_query() so that SQL injection attacks from user input can be caught and nullified. The LIMIT syntax varies between database servers, so that is abstracted into db_query_range() arguments. Finally, note the PDO-based ability to iterate over the result set using foreach ().

All queries are passed as a prepared statement string. A prepared statement is a "template" of a query that omits literal or variable values in favor of placeholders. The values to place into those placeholders are passed separately, and the database driver handles inserting the values into the query in a secure fashion. That means you should never quote or string-escape a value to be inserted into the query.

There are two formats for placeholders: named and unnamed. Named placeholders are strongly preferred in all cases as they are more flexible and self-documenting. Named placeholders should start with a colon ":" and can be followed by one or more letters, numbers or underscores.

Named placeholders begin with a colon followed by a unique string. Example:

SELECT nid, title FROM {node} WHERE uid=:uid;

":uid" is a placeholder that will be replaced with a literal value when the query is executed. A given placeholder label cannot be repeated in a given query, even if the value should be the same. When using named placeholders, the array of arguments to the query must be an associative array where keys are a placeholder label (e.g., :uid) and the value is the corresponding value to use. The array may be in any order.

Unnamed placeholders are simply a question mark. Example:

SELECT nid, title FROM {node} WHERE uid=?;

In this case, the array of arguments must be an indexed array of values to use in the exact same order as the placeholders in the query.

Note that placeholders should be a "complete" value. For example, when running a LIKE query the SQL wildcard character, %, should be part of the value, not the query itself. Thus, the following is incorrect:

SELECT nid, title FROM {node} WHERE title LIKE :title%;

It should instead read:

SELECT nid, title FROM {node} WHERE title LIKE :title;

and the value for :title should include a % as appropriate. Again, note the lack of quotation marks around :title. Because the value is not inserted into the query as one big string but as an explicitly separate value, the database server knows where the query ends and a value begins. That is considerably more secure against SQL injection than trying to remember which values need quotation marks and string escaping and which don't.

INSERT, UPDATE, and DELETE queries need special care in order to behave consistently across all different databases. Therefore, they use a special object-oriented API for defining a query structurally. For example, rather than:

INSERT INTO node (nid, title, body) VALUES (1, 'my title', 'my body');

one would instead write:

$fields = array('nid' => 1, 'title' => 'my title', 'body' => 'my body');
db_insert('node')->fields($fields)->execute();

This method allows databases that need special data type handling to do so, while also allowing optimizations such as multi-insert queries. UPDATE and DELETE queries have a similar pattern.

Drupal also supports transactions, including a transparent fallback for databases that do not support transactions. To start a new transaction, simply call $txn = db_transaction(); in your own code. 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, that is, all relevant queries 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();
  }
}

See also

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

Functions

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

Constants

Namesort descending Location Description
POSTGRESQL_NEXTID_LOCK includes/database/pgsql/database.inc The name by which to obtain a lock for retrive the next insert id.

Classes

Namesort descending Location Description
Database includes/database/database.inc Primary front-controller for the database system.
DatabaseCondition includes/database/query.inc Generic class for a series of conditions in a query.
DatabaseConnection includes/database/database.inc Base Database API class.
DatabaseConnectionNotDefinedException includes/database/database.inc Exception thrown if an undefined database connection is requested.
DatabaseConnection_mysql includes/database/mysql/database.inc
DatabaseConnection_pgsql includes/database/pgsql/database.inc
DatabaseConnection_sqlite includes/database/sqlite/database.inc Specific SQLite implementation of DatabaseConnection.
DatabaseDriverNotSpecifiedException includes/database/database.inc Exception thrown if no driver is specified for a database connection.
DatabaseStatementBase includes/database/database.inc Default implementation of DatabaseStatementInterface.
DatabaseStatementEmpty includes/database/database.inc Empty implementation of a database statement.
DatabaseStatementPrefetch includes/database/prefetch.inc An implementation of DatabaseStatementInterface that prefetches all data.
DatabaseStatement_sqlite includes/database/sqlite/database.inc Specific SQLite implementation of DatabaseConnection.
DatabaseTransaction includes/database/database.inc A wrapper class for creating and managing database transactions.
DatabaseTransactionCommitFailedException includes/database/database.inc Exception thrown when a commit() function fails.
DatabaseTransactionExplicitCommitNotAllowedException includes/database/database.inc Exception to deny attempts to explicitly manage transactions.
DatabaseTransactionNameNonUniqueException includes/database/database.inc Exception thrown when a savepoint or transaction name occurs twice.
DatabaseTransactionNoActiveException includes/database/database.inc Exception for when popTransaction() is called with no active transaction.
DatabaseTransactionOutOfOrderException includes/database/database.inc Exception thrown when a rollback() resulted in other active transactions being rolled-back.
DeleteQuery includes/database/query.inc General class for an abstracted DELETE operation.
DeleteQuery_sqlite includes/database/sqlite/query.inc SQLite specific implementation of DeleteQuery.
FieldsOverlapException includes/database/database.inc Exception thrown if an insert query specifies a field twice.
InsertQuery includes/database/query.inc General class for an abstracted INSERT query.
InsertQuery_mysql includes/database/mysql/query.inc
InsertQuery_pgsql includes/database/pgsql/query.inc
InsertQuery_sqlite includes/database/sqlite/query.inc SQLite specific implementation of InsertQuery.
InvalidMergeQueryException includes/database/database.inc Exception thrown for merge queries that do not make semantic sense.
MergeQuery includes/database/query.inc General class for an abstracted MERGE query operation.
NoFieldsException includes/database/database.inc Exception thrown if an insert query doesn't specify insert or default fields.
Query includes/database/query.inc Base class for query builders.
SelectQuery includes/database/select.inc Query builder for SELECT statements.
SelectQueryExtender includes/database/select.inc The base extender class for Select queries.
SelectQuery_pgsql includes/database/pgsql/select.inc
SelectQuery_sqlite includes/database/sqlite/select.inc SQLite specific query builder for SELECT statements.
TruncateQuery includes/database/query.inc General class for an abstracted TRUNCATE operation.
TruncateQuery_mysql includes/database/mysql/query.inc
TruncateQuery_sqlite includes/database/sqlite/query.inc SQLite specific implementation of TruncateQuery.
UpdateQuery includes/database/query.inc General class for an abstracted UPDATE operation.
UpdateQuery_pgsql includes/database/pgsql/query.inc
UpdateQuery_sqlite includes/database/sqlite/query.inc SQLite specific implementation of UpdateQuery.

Interfaces

Namesort descending Location Description
DatabaseStatementInterface includes/database/database.inc Represents a prepared statement.
QueryAlterableInterface includes/database/query.inc Interface for a query that can be manipulated via an alter hook.
QueryConditionInterface includes/database/query.inc Interface for a conditional clause in a query.
QueryExtendableInterface includes/database/select.inc Interface for extendable query objects.
QueryPlaceholderInterface includes/database/query.inc Interface for a query that accepts placeholders.
SelectQueryInterface includes/database/select.inc Interface definition for a Select Query object.

File

includes/database/database.inc, line 12
Core systems for the database layer.

Comments

I still find the best documentation to be at http://drupal.org/developing/api/database.

Note, that link is in the page above, it's just a bit hard to notice below the table.

thanks for this. I come to this page only to typically find that link ( -:

Does the new Drupal 7 database abstraction layer support different databases?

In 6 "This only works with two databases of the same type. For example the following code will not work.
<?php
// ... header of the settings.php file

$db_url = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);
"

As noted here http://drupal.org/node/18429

I'm not sure if this is the right place for this or not but I had a hard time finding how to upgrade db_placeholders() for D7.

What seems to work is

<?php
$tids
// An array of term IDs
$placeholders = implode(', ', array_fill(0, count($tids), "%d"));
$r = db_query("SELECT nid FROM {term_node} WHERE tid IN ($placeholders)", $tids);
?>

Which I found http://api.drupal.org/api/drupal/includes--database.pgsql.inc/function/d...

If anyone has a better way or the proper way to do this please let me know.

The query statement went from

<?php
  $result
= db_query("SELECT * FROM {sms_valid_rules} WHERE prefix IN (" . db_placeholders($potential_prefixes, 'int') . ")", $potential_prefixes);
?>

to

<?php
  $placeholders
= implode(', ', array_fill(0, count($potential_prefixes), "%d"));
 
$result = db_query("SELECT * FROM {sms_valid_rules} WHERE prefix IN ($placeholders)", $potential_prefixes);
?>

Can Mongodb use the Database abstraction layer?

Thank you!

Is that possible to check if the name of the table is correct before executing the query ?
Try catch allows to handle errors, but I want to prevent before trying anything.

I know that with php and mysql there was mysql_list_tables you allows to display the table and then check if the table is in there... but this function is discouraged and I don't know if we can make this?

Hello every one,

I have a query with a condition like this :

<?php
$query
->condition("date_begin","DATE_SUB(NOW(), INTERVAL 1 YEAR)", ">=");
?>

but this is not working cause by quote in DATE_SUB() function

How can i escape the quote inside query ?

Can anyone show me the right implementation of this SQL function inside drupal query?

Thanks in advance

how do i connect to different database system (specifically derby database).

please help

I am starting with drupal 7
I don't know how to select database with 3 tables
My queries in mySql is here:
SELECT db_sv.HoTen, db_cv.TenChucVu_Doan
FROM dv_sinhvien db_sv, dv_chucvu_doan db_cv, dv_sinhvien_chucvu db_sv_cv
WHERE db_sv.MaSinhVien = db_sv_cv.MaSinhVien
AND db_cv.MaChucVu_Doan = db_sv_cv.MaChucVu_Doan
LIMIT 0 , 30

How do I work with it in Drupal ???
Thanks everyone had look my question.