Database abstraction layer

  1. drupal
    1. 5
    2. 6
    3. 7 database.inc
    4. 8 database.inc

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

Drupal provides a slim 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, while letting Drupal control the pieces of queries that need to be written differently for different servers and provide basic security checks.

Most Drupal database queries are performed by a call to db_query() or db_query_range(). Module authors should also consider using pager_query() for queries that return results that need to be presented on multiple pages, and tablesort_sql() for generating appropriate queries for sortable tables.

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

<?php
  SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
?>

one would instead call the Drupal functions:

<?php
  $result = db_query_range('SELECT n.title, n.body, n.created
    FROM {node} n WHERE n.uid = %d', $uid, 0, 10);
  while ($node = db_fetch_object($result)) {
    // Perform operations on $node->body, etc. here.
  }
?>

Curly braces are used around "node" to provide table prefixing via db_prefix_tables(). 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 common pattern of iterating over the result set using db_fetch_object().

Functions & methods

NameDescription
db_affected_rowsDetermine the number of rows changed by the preceding query.
db_affected_rowsDetermine the number of rows changed by the preceding query.
db_affected_rowsDetermine the number of rows changed by the preceding query.
db_check_setupVerify if the database is set up correctly.
db_connectInitialize a database connection.
db_connectInitialize a database connection.
db_connectInitialise a database connection.
db_decode_blobReturns text from a Binary Large OBject value. In case of PostgreSQL decodes data after select from bytea field.
db_decode_blobReturns text from a Binary Large Object value.
db_decode_blobReturns text from a Binary Large OBject value.
db_distinct_fieldWraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query.
db_distinct_fieldWraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query.
db_distinct_fieldWraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query.
db_encode_blobReturns a properly formatted Binary Large OBject value. In case of PostgreSQL encodes data for insert into bytea field.
db_encode_blobReturns a properly formatted Binary Large Object value.
db_encode_blobReturns a properly formatted Binary Large OBject value.
db_errorDetermine whether the previous query caused an error.
db_errorDetermine whether the previous query caused an error.
db_errorDetermine whether the previous query caused an error.
db_escape_stringPrepare user input for use in a database query, preventing SQL injection attacks. Note: This function requires PostgreSQL 7.2 or later.
db_escape_stringPrepare user input for use in a database query, preventing SQL injection attacks.
db_escape_stringPrepare user input for use in a database query, preventing SQL injection attacks.
db_escape_tableRestrict a dynamic tablename to safe characters.
db_fetch_arrayFetch one result row from the previous query as an array.
db_fetch_arrayFetch one result row from the previous query as an array.
db_fetch_arrayFetch one result row from the previous query as an array.
db_fetch_objectFetch one result row from the previous query as an object.
db_fetch_objectFetch one result row from the previous query as an object.
db_fetch_objectFetch one result row from the previous query as an object.
db_lock_tableLock a table.
db_lock_tableLock a table.
db_lock_tableLock a table. This function automatically starts a transaction.
db_next_idReturn a new unique ID in the given sequence.
db_next_idReturn a new unique ID in the given sequence.
db_next_idReturn a new unique ID in the given sequence.
db_num_rowsDetermine how many result rows were found by the preceding query.
db_num_rowsDetermine how many result rows were found by the preceding query.
db_num_rowsDetermine how many result rows were found by the preceding query.
db_prefix_tablesAppend a database prefix to all tables in a query.
db_queryRuns a basic query in the active database.
db_query_rangeRuns a limited-range query in the active database.
db_query_rangeRuns a limited-range query in the active database.
db_query_rangeRuns a limited-range query in the active database.
db_query_temporaryRuns a SELECT query and stores its results in a temporary table.
db_query_temporaryRuns a SELECT query and stores its results in a temporary table.
db_query_temporaryRuns a SELECT query and stores its results in a temporary table.
db_resultReturn an individual result field from the previous query.
db_resultReturn an individual result field from the previous query.
db_resultReturn an individual result field from the previous query.
db_rewrite_sqlRewrites node, taxonomy and comment queries. Use it for listing queries. Do not use FROM table1, table2 syntax, use JOIN instead.
db_set_activeActivate a database for future queries.
db_status_reportReport database status.
db_status_reportReport database status.
db_status_reportReport database status.
db_table_existsCheck if a table exists.
db_table_existsCheck if a table exists.
db_table_existsCheck if a table exists.
db_unlock_tablesUnlock all locked tables. This function automatically commits a transaction.
db_unlock_tablesUnlock all locked tables.
db_unlock_tablesUnlock all locked tables.
db_versionReturns the version of the database server currently in use.
db_versionReturns the version of the database server currently in use.
db_versionReturns the version of the database server currently in use.
pager_queryPerform a paged database query.
tablesort_sqlCreate an SQL sort clause.
_db_queryHelper function for db_query().
_db_queryHelper function for db_query().
_db_queryHelper function for db_query().
_db_query_callbackHelper function for db_query().
_db_rewrite_sqlHelper function for db_rewrite_sql.

Constants

NameDescription
DB_QUERY_REGEXPIndicates the place holders that should be replaced in _db_query_callback().

includes/database.inc, line 8