Drupal Study Guide

Appearance

Database API ​

Drupal's database API provides abstraction using PDO to provide support for multiple types of database servers, and allow for secure query assembly. Core supports MySql, PostgreSQL and SQLite implementations. Any query on Drupal entities or fields should use the Entity Query API.

Instantiating the Database ​

To access (and open if necessary) a connection object, use:

php
$database = \Drupal::database();
// Or
$database = \Drupal::service('database');

$database will be of type \Drupal\Core\Database\Connection.

If services are not yet available, \Drupal\Core\Database\Database::getConnection() can get a database connection.

Table Prefixing & Placeholder Arrays ​

Table Name Prefixing: Wrap table names in curly braces {} to allow Drupal to manage table name prefixing, which is essential for scenarios like running multiple sites from the same database.

Placeholder Arrays: If a placeholder value is an array, Drupal automatically expands it into a comma-separated list, simplifying IN clause constructions. If a placeholder value is an array, Drupal automatically expands it into a comma-separated list, simplifying IN clause constructions.

php
$result = $database->query(
  "SELECT * FROM {mytable} WHERE id IN (:ids[])",
  [':ids[]' => [13, 42, 144]]
);

Best Practices: ​

  • Use Cases: Reserve static queries for simple SELECT statements. For INSERT, UPDATE, or DELETE operations, or when dealing with complex queries involving joins, prefer dynamic queries using Drupal's query builder to ensure safety and maintainability.
  • Security: Always use placeholders for user-supplied data to prevent SQL injection. Avoid concatenating variables directly into SQL strings.
  • Complex Operations: For complex UPDATE or DELETE queries that involve joins, static queries might be necessary, but handle them with caution and ensure proper sanitization.

Example Queries ​

Example: Static Queries ​

php
<?php
// Static queries are passed almost verbatim to the database.
$database = \Drupal::database();
$query = $database->query("SELECT id, example FROM {mytable}");
$result = $query->fetchAll();

Example: Single Value ​

php
<?php
$query = \Drupal::database()->select('node_field_data', 'nfd');
$query->addField('nfd', 'nid');
$query->condition('nfd.title', 'Developer');
$query->range(0, 1);
$nid = $query->execute()->fetchField();

Example: Single Row Example ​

php
$query = \Drupal::database()->select('node_field_data', 'nfd');
$query->fields('nfd', ['nid', 'title']);
$query->condition('nfd.type', 'intern');
$query->range(0, 1);
$vegetable = $query->execute()->fetchAssoc();

Example: Using DB LIKE ​

php
$query = \Drupal::database()->select('node_field_data', 'nfd');
$query->fields('nfd', ['nid', 'title']);
$query->condition('nfd.type', 'intern');
$query->condition('nfd.title', $query->escapeLike('ca') . '%', 'LIKE');
$vegetable = $query->execute()->fetchAllKeyed();

Example: Get several rows with JOIN ​

php
$query = \Drupal::database()->select('node_field_data', 'nfd');
$query->fields('nfd', ['nid', 'title']);
$query->addField('ufd', 'name');
$query->join('users_field_data', 'ufd', 'ufd.uid = nfd.uid');
$query->condition('nfd.type', 'intern');
$vegetable = $query->execute()->fetchAllAssoc('nid');

Example: Insert row into database ​

php
$query = \Drupal::database()->insert('flood');
$query->fields([
  'event',
  'identifier'
]);
$query->values([
  'My event',
  'My indentifier'
]);
$query->execute();

Example: Update row in database ​

php
$query = \Drupal::database()->update('flood');
$query->fields([
  'identifier' => 'My new identifier'
]);
$query->condition('event', 'My event');
$query->execute();

Example: Upsert (Update or insert if not exists) ​

php
$query = \Drupal::database()->upsert('flood');
$query->fields([
  'fid',
  'identifier',
]);
$query->values([
  1,
  'My indentifier for upsert'
]);
$query->key('fid');
$query->execute();

Example: Deletion ​

php
$query = \Drupal::database()->delete('flood');
$query->condition('event', 'My event');
$query->execute();
?>
  1. hook_config_import_steps_alter()
  2. hook_config_schema_info_alter()

Questions ​

General ​

How should Enitity or Field data be accessed?

Any query on Drupal entities or fields should use the Entity Query API

What are placeholders and why should they be used?

Placeholders are named tokens (e.g., :title, :event, :identifier) used in dynamic database queries instead of inserting raw values directly. These placeholders get automatically replaced with actual values when the query is executed. Using placeholders allows for value sanitation and is considered a best practice.

Backend Developer (Advanced) ​

How do you instantiate a database connection?
php
$database = \Drupal::database();
// Or
$database = \Drupal::service('database');

Alternatively for OOP:

  • In some cases a database connection object may already be available as a member on the current class; for example, many plugins and services have $this->database (or $this->connection) - the database connection object as a member.
  • If it is possible, use DI (dependency injection) to use @database service or $container->get('database'); to inject the database connection.

Resources ​