Database Abstraction Layer

  
Last updated March 23, 2023
Module DevelopmentDrupal 10Drupal 9Drupal 8Drupal 7

Drupal's vendor-agnostic database abstraction layer provides a unified database query API that can query different underlying databases. It is built upon the PHP Data Objects (PDO) database API and inherits much of its syntax and semantics.

The Database API is designed to preserve the syntax and power of SQL as much as possible. It also:

  • Supports multiple database servers
  • Allows developers to leverage more complex functionality (i.e., transactions)
  • Provides a structured interface to dynamically construct queries
  • Enforces security checks
  • Provides modules with an interface for modifying other queries in the system

The Database API may not always be the best option for interacting with data. API use in Drupal is usually situational, e.g. using the Node API for Node CRUD operations, or the more generic Entity API for Entity for CRUD, the Configuration API for accessing configuration data, etc. We generally recommend directly querying the database as a last resort.

Example tasks

  • Create a new database table for storing custom data
  • Insert and retrieve data from a custom table or set of tables
  • Query the watchdog table for log messages

Confidence

Drupal's database abstraction layer did not change significantly between Drupal 7 and Drupal 8+ and has been quite stable for some time. The primary difference in the current version of Drupal is that access to the database should be done via a database connection service instead of the now deprecated db_* functions. Query syntax and use of result sets remain similar.

Drupalize.Me resources

In Drupal the preferred method in most cases for storing and retrieving data is via the Entity API.

Topic

Entities

Site Building, Module Development, Drupal 10, Drupal 9, Drupal 8, Drupal 7
More information

Entities are the fundamental building blocks that make up any Drupal site. Having a good understanding of the Entity system is an important part of ensuring the data model of your Drupal site is set up properly.

Tutorial

Working with Entity CRUD

Module Development, Drupal 8, Drupal 9, Drupal 10
More information

Entity CRUD (Create, Read, Update, and Delete) operations are handled via the EntityTypeManager service.

In this tutorial we'll:

Tutorial

Expose a Custom Database Table to Views

Module Development, Drupal 9, Drupal 8, Drupal 10
More information

Any Drupal module that provides custom database tables should implement hook_views_data() to describe the schema of those tables to Views. This hook is used to provide information to Views about the fields in a table, their human-readable names, and the types of data (string, integer, date, etc.) stored in each column. You can also tell Views how it should handle sorting, filtering, and formatting the data. Implementations of hook_views_data() can also be used to describe relationships between tables.

Note: Use the service container to get a query object. After which queries work almost identically to Drupal 7.

$connection = \Drupal::database();
$query = $connection->select('node', 'n');
VideoHDCC

An Overview of the Database Layer in Drupal 7

15m42s  //  Module Development, Drupal 7
VideoHDCC

Querying the Database with db_select()

16m10s  //  Module Development, Drupal 7
VideoHDCC

Adding a Table to the Database

11m23s  //  Module Development, Drupal 7
VideoHDCC

Querying the Database with Query Methods

23m26s  //  Module Development, Drupal 7

More Guides

We have guides on many Drupal skills and topics.

Explore guides

External resources

  • Database API (Drupal.org)
    • Detailed documentation on how to query a database using Drupal's API.
  • Database abstraction layer (api.drupal.org)
    • Technical documentation for the database abstraction layer. Use this if you just need a quick reminder about how a particular feature works.