SELECT Queries in Drupal: Reading Data with DBTNGfor Drupal 11 , and 10

Up to now, every lesson has dealt with data that Drupal already knows how to store for you — form values, block configuration, hook state. But sooner or later, every real module needs its own custom table: a log of user actions, a list of imported records, a queue of pending jobs. In this lesson you'll learn how Drupal reads that kind of custom data back out of the database — safely, and without writing a single line of raw SQL.

What you'll learn in this lesson

• What DBTNG (Drupal's database abstraction layer) is, and why you almost never write SQL strings by hand in Drupal
• How to build a dynamic SELECT query with $connection->select()
• How to filter rows safely with ->condition(), without ever risking SQL injection
• How to JOIN two tables together and control how many rows come back
• Why real Drupal modules put database code in its own dedicated "repository" class instead of scattering it everywhere

Why not just write SQL?

You could, technically, write "SELECT * FROM users WHERE name = '" . $name . "'" and run it directly. Don't. If $name ever comes from user input — a search box, a URL, a form field — that string concatenation is a direct invitation for SQL injection, one of the most damaging and common web vulnerabilities. Drupal's database layer exists specifically so you never have to think about escaping values yourself: every value you pass through its query builder is automatically parameterized and safely escaped, on every database engine Drupal supports (MySQL, MariaDB, PostgreSQL, SQLite).

The source file

Path: modules/dbtng_example/src/DbtngExampleRepository.php

<?php

namespace Drupal\dbtng_example;

use Drupal\Core\Database\Connection;
use Drupal\Core\Messenger\MessengerInterface;
use Drupal\Core\Messenger\MessengerTrait;
use Drupal\Core\StringTranslation\StringTranslationTrait;
use Drupal\Core\StringTranslation\TranslationInterface;

/**
 * Repository for database-related helper methods for our example.
 *
 * This repository is a service named 'dbtng_example.repository'. You can see
 * how the service is defined in dbtng_example/dbtng_example.services.yml.
 *
 * For projects where there are many specialized queries, it can be useful to
 * group them into 'repositories' of queries. We can also architect this
 * repository to be a service, so that it gathers the database connections it
 * needs. This way other classes which use the repository don't need to concern
 * themselves with database connections, only with business logic.
 *
 * This repository demonstrates basic CRUD behaviors, and also has an advanced
 * query which performs a join with the user table.
 *
 * @ingroup dbtng_example
 */
class DbtngExampleRepository {

  use MessengerTrait;
  use StringTranslationTrait;

  /**
   * The database connection.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Construct a repository object.
   *
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection.
   * @param \Drupal\Core\StringTranslation\TranslationInterface $translation
   *   The translation service.
   * @param \Drupal\Core\Messenger\MessengerInterface $messenger
   *   The messenger service.
   */
  public function __construct(Connection $connection, TranslationInterface $translation, MessengerInterface $messenger) {
    $this->connection = $connection;
    $this->setStringTranslation($translation);
    $this->setMessenger($messenger);
  }

  /**
   * Save an entry in the database.
   *
   * Exception handling is shown in this example. It could be simplified
   * without the try/catch blocks, but since an insert will throw an exception
   * and terminate your application if the exception is not handled, it is best
   * to employ try/catch.
   *
   * @param array $entry
   *   An array containing all the fields of the database record.
   *
   * @return int
   *   The number of updated rows.
   *
   * @throws \Exception
   *   When the database insert fails.
   */
  public function insert(array $entry) {
    try {
      $return_value = $this->connection->insert('dbtng_example')
        ->fields($entry)
        ->execute();
    }
    catch (\Exception $e) {
      $this->messenger()->addMessage($this->t('Insert failed. Message = %message', [
        '%message' => $e->getMessage(),
      ]), 'error');
    }
    return $return_value ?? NULL;
  }

  /**
   * Update an entry in the database.
   *
   * @param array $entry
   *   An array containing all the fields of the item to be updated.
   *
   * @return int
   *   The number of updated rows.
   */
  public function update(array $entry) {
    try {
      // Connection->update()...->execute() returns the number of rows updated.
      $count = $this->connection->update('dbtng_example')
        ->fields($entry)
        ->condition('pid', $entry['pid'])
        ->execute();
    }
    catch (\Exception $e) {
      $this->messenger()->addMessage($this->t('Update failed. Message = %message, query= %query', [
        '%message' => $e->getMessage(),
        '%query' => $e->query_string,
      ]
      ), 'error');
    }
    return $count ?? 0;
  }

  /**
   * Delete an entry from the database.
   *
   * @param array $entry
   *   An array containing at least the person identifier 'pid' element of the
   *   entry to delete.
   *
   * @see Drupal\Core\Database\Connection::delete()
   */
  public function delete(array $entry) {
    $this->connection->delete('dbtng_example')
      ->condition('pid', $entry['pid'])
      ->execute();
  }

  /**
   * Read from the database using a filter array.
   *
   * The standard function to perform reads for static queries is
   * Connection::query().
   *
   * Connection::query() uses an SQL query with placeholders and arguments as
   * parameters.
   *
   * Drupal DBTNG provides an abstracted interface that will work with a wide
   * variety of database engines.
   *
   * The following is a query which uses a string literal SQL query. The
   * placeholders will be substituted with the values in the array. Placeholders
   * are marked with a colon ':'. Table names are marked with braces, so that
   * Drupal's' multisite feature can add prefixes as needed.
   *
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   \Drupal::database()->query(
   *     "SELECT * FROM {dbtng_example} WHERE uid = :uid and name = :name",
   *     [':uid' => 0, ':name' => 'John']
   *   )->execute();
   * @endcode
   *
   * For more dynamic queries, Drupal provides Connection::select() API method,
   * so there are several ways to perform the same SQL query. See the
   * @link http://drupal.org/node/310075 handbook page on dynamic queries. @endlink
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->condition('uid', 0)
   *     ->condition('name', 'John')
   *     ->execute();
   * @endcode
   *
   * Here is select() with named placeholders:
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE uid = 0 AND name = 'John'
   *   $arguments = array(':name' => 'John', ':uid' => 0);
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->where('uid = :uid AND name = :name', $arguments)
   *     ->execute();
   * @endcode
   *
   * Conditions are stacked and evaluated as AND and OR depending on the type of
   * query. For more information, read the conditional queries handbook page at:
   * http://drupal.org/node/310086
   *
   * The condition argument is an 'equal' evaluation by default, but this can be
   * altered:
   * @code
   *   // SELECT * FROM {dbtng_example} WHERE age > 18
   *   \Drupal::database()->select('dbtng_example')
   *     ->fields('dbtng_example')
   *     ->condition('age', 18, '>')
   *     ->execute();
   * @endcode
   *
   * @param array $entry
   *   An array containing all the fields used to search the entries in the
   *   table.
   *
   * @return object
   *   An object containing the loaded entries if found.
   *
   * @see Drupal\Core\Database\Connection::select()
   */
  public function load(array $entry = []) {
    // Read all the fields from the dbtng_example table.
    $select = $this->connection
      ->select('dbtng_example')
      // Add all the fields into our select query.
      ->fields('dbtng_example');

    // Add each field and value as a condition to this query.
    foreach ($entry as $field => $value) {
      $select->condition($field, $value);
    }
    // Return the result in object format.
    return $select->execute()->fetchAll();
  }

  /**
   * Load dbtng_example records joined with user records.
   *
   * DBTNG also helps processing queries that return several rows, providing the
   * found objects in the same query execution call.
   *
   * This function queries the database using a JOIN between users table and the
   * example entries, to provide the username that created the entry, and
   * creates a table with the results, processing each row.
   *
   * SELECT
   *  e.pid as pid, e.name as name, e.surname as surname, e.age as age
   *  u.name as username
   * FROM
   *  {dbtng_example} e
   * JOIN
   *  users u ON e.uid = u.uid
   * WHERE
   *  e.name = 'John' AND e.age > 18
   *
   * @see Drupal\Core\Database\Connection::select()
   * @see http://drupal.org/node/310075
   */
  public function advancedLoad() {
    // Get a select query for our dbtng_example table. We supply an alias of e
    // (for 'example').
    $select = $this->connection->select('dbtng_example', 'e');
    // Join the users table, so we can get the entry creator's username.
    $select->join('users_field_data', 'u', 'e.uid = u.uid');
    // Select these specific fields for the output.
    $select->addField('e', 'pid');
    $select->addField('u', 'name', 'username');
    $select->addField('e', 'name');
    $select->addField('e', 'surname');
    $select->addField('e', 'age');
    // Filter only persons named "John".
    $select->condition('e.name', 'John');
    // Filter only persons older than 18 years.
    $select->condition('e.age', 18, '>');
    // Make sure we only get items 0-49, for scalability reasons.
    $select->range(0, 50);

    $entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

    return $entries;
  }

}

How it works

Why this lives in its own "repository" class

DbtngExampleRepository isn't a controller or a form — it's a plain PHP class registered as a Drupal service (you can see it wired up under the ID dbtng_example.repository in dbtng_example.services.yml). This is called the repository pattern: instead of scattering database queries across every controller and form that needs data, you centralize them in one class. Anything that needs data — a controller, a form, another service — injects this repository and calls a method on it, without ever seeing a query object itself. That separation makes queries easy to reuse, easy to test in isolation, and easy to change later without hunting through the whole codebase.

The constructor: three injected services

public function __construct(Connection $connection, TranslationInterface $translation, MessengerInterface $messenger)

Drupal's service container automatically supplies three dependencies when this class is instantiated: the database Connection itself, a translation service, and the messenger service (for showing status/error messages). Storing $connection as a property means every method below can call $this->connection->select(...) directly, instead of reaching for the global \Drupal::database() shortcut. That distinction matters more than it looks: a class that receives its dependencies through the constructor can be unit-tested with a fake/mock connection, while a class that calls \Drupal::database() internally cannot easily be tested in isolation.

The load() method — a dynamic SELECT

public function load(array $entry = []) {
    $select = $this->connection
      ->select('dbtng_example')
      ->fields('dbtng_example');

    foreach ($entry as $field => $value) {
      $select->condition($field, $value);
    }
    return $select->execute()->fetchAll();
}

This is the core SELECT pattern you'll use constantly:

• $this->connection->select('dbtng_example') starts a query builder targeting the {dbtng_example} table. (Drupal automatically wraps table names in curly braces internally, so it can prefix them for multisite installs — you don't need to worry about that part.)
• ->fields('dbtng_example') with no second argument selects every column — the equivalent of SELECT *.
• ->condition($field, $value) appends a WHERE field = value clause. Call it more than once and the conditions combine with AND by default.
• ->execute() actually runs the query against the database and returns a result set object.
• ->fetchAll() pulls every matching row back as an array of PHP objects.

Because the WHERE conditions are built inside a foreach loop over whatever $entry array is passed in, this one method can answer completely different questions depending on its caller: load([]) returns every row, while load(['name' => 'Ada']) returns only rows where the name column equals "Ada". That's the "dynamic" in Drupal's dynamic query API — the shape of the query is decided at runtime, not hardcoded in advance.

Filtering safely with condition()

condition() takes up to three arguments: the field, the value, and an optional comparison operator (it assumes = if you leave it out).

// Equality (the default)
->condition('name', 'John')

// Greater-than comparison
->condition('age', 18, '>')

You can also use <, >=, <=, !=, LIKE, IN, NOT IN, and BETWEEN — whatever your database supports for a WHERE comparison, DBTNG almost certainly exposes it as an operator string here. Every value you hand to condition() is automatically parameterized behind the scenes — this is the mechanism that makes DBTNG immune to SQL injection even when the values come straight from user input.

The advancedLoad() method — JOIN, aliasing, and limits

advancedLoad() packs several more advanced techniques into one method. Let's take it piece by piece.

$select = $this->connection->select('dbtng_example', 'e');

The second argument, 'e', is a table alias. Every later reference to this table in the query uses the short alias e instead of repeating dbtng_example — this becomes essential once a second table enters the picture, because two tables might both have a column called name.

$select->join('users_field_data', 'u', 'e.uid = u.uid');

join() performs an INNER JOIN — only rows that have a match in both tables are returned. The three arguments are: the table to join, its alias (u), and the ON condition connecting the two tables. DBTNG also provides leftJoin(), rightJoin(), and addJoin() for other join types.

$select->addField('e', 'pid');
$select->addField('u', 'name', 'username');
$select->addField('e', 'name');

addField() selects one specific column from an aliased table. The optional third argument renames the column in the result — here, u.name (the joined user's name) comes back as username, so it doesn't collide with e.name (the example table's own name column) in the returned row.

$select->condition('e.name', 'John');
$select->condition('e.age', 18, '>');

Conditions on an aliased table use dot notation — 'e.name' instead of just 'name' — so Drupal (and the underlying database) knows exactly which table's column you mean once more than one table is involved.

$select->range(0, 50);

range() adds a SQL LIMIT/OFFSET clause — here, "give me at most 50 rows, starting from the first one." This one line matters more than it looks: an unbounded query against a table that grows to millions of rows can exhaust your server's memory just building the result set. Get in the habit of using range() on any query that isn't guaranteed to return a small, fixed number of rows.

$entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC);

Passing \PDO::FETCH_ASSOC to fetchAll() returns each row as a plain associative array (['pid' => 1, 'username' => 'admin', ...]) instead of the default stdClass object. Both are fine — pick whichever shape is more convenient for whatever you're about to do with the data next.

See it for yourself

Visit /examples/dbtng-example/list on your DDEV site. This page calls exactly the two methods you just read — load() and advancedLoad() — and renders the results as HTML tables.

Every row you see traces directly back to a method on DbtngExampleRepository. The controller behind this page never writes a query itself — it injects the repository, calls load(), and hands the resulting array to a render array of type table. All the actual database logic — connecting, filtering, fetching — is fully contained in the class you just read.

Quick check: if you wanted this page to show only people older than 21, which single line inside load() or advancedLoad() would you change, and what would you change it to? (Hint: it's a condition() call, and the answer looks a lot like the age example above.)

Key takeaways

• Use $connection->select('table_name') to start a dynamic SELECT query; chain ->fields(), ->condition(), ->execute(), and a fetch method (fetchAll(), fetchAssoc(), fetchCol()) to complete it.
• ->fields('table_alias') with no second argument selects every column; pass an array of column names as the second argument to select only specific fields.
• ->condition($field, $value, $operator) safely parameterizes WHERE clauses — the default operator is =, and supported operators include >, <, IN, LIKE, and more. Never build a WHERE clause by concatenating strings yourself.
• Use ->addField('alias', 'column', 'result_alias') together with ->join() when querying across multiple tables; table aliases prevent column-name collisions and are required once more than one table is involved.
• Always add ->range($start, $count) to any query that could plausibly return a large result set — it prevents memory exhaustion and keeps pages responsive as your data grows.
• Put your database queries in a dedicated repository service class rather than inline in controllers or forms — it keeps presentation code clean, makes queries reusable across multiple routes, and lets you unit-test with a mocked connection.

Coming up next

Reading data is only half the story. In the next lesson we'll look at the other three methods on this same repository class — insert(), update(), and delete() — and see how a Drupal form safely writes new rows into the database, complete with the exception handling you need for production-quality code.