# Database Queries

DatabaseInterface exposes query composition and execution separately. Use the SELECT builder for ordinary reads, the CRUD builder for common inserts and writes, and parameterized SQL when a query is too complex or dialect-specific for the builders.

# Simple SELECT with the builder

$qb = $this->database->createQueryBuilder();
$qb->select('id', 'email')
    ->from('dbm_user')
    ->where('verified = :verified')
    ->setParameter('verified', 1);

$users = $this->database->fetchAll(
    $qb->getSQL(),
    $qb->getParameters()
);

For one row, use fetch(); it returns an associative row or null.

# Joins, filters, and pagination

$qb = $this->database->createQueryBuilder();
$qb->select('p.id', 'p.name', 'a.name AS advertiser')
    ->from('product', 'p')
    ->join('p', 'advertiser', 'a', 'a.id = p.advertiser_id')
    ->where('p.status = :status')
    ->andWhereIn('p.product_type', $allowedTypes)
    ->setParameter('status', 'active')
    ->orderBy('p.id', 'ASC')
    ->setMaxResults(25)
    ->setFirstResult(0);

$offers = $this->database->fetchAll(
    $qb->getSQL(),
    $qb->getParameters()
);

The SELECT contract also includes leftJoin(), rightJoin(), addOrderBy(), whereIn(), and expr(). whereIn() creates bound placeholders; an empty list becomes a false condition (1 = 0). Validate limits and other business rules in application code.

# Complex SELECT with SQL

Use direct SQL for correlated subqueries, complex ordering, CTEs, or features that would become harder to read through the builder. Keep dynamic values in the parameter array:

$sql = <<<'SQL'
    SELECT p.id, p.name,
           (SELECT l.url
            FROM product_link l
            WHERE l.product_id = p.id AND l.status = 'active'
            ORDER BY l.priority ASC
            LIMIT 1) AS active_url
    FROM product p
    WHERE p.product_type = :type
      AND p.status = 'active'
    ORDER BY p.rating DESC, p.id ASC
    SQL;

$offers = $this->database->fetchAll($sql, ['type' => $productType]);

Direct SQL is appropriate for complex queries, provided the SQL structure is trusted and all external values are bound as parameters. Keep identifiers such as table names, column names, and sort directions on an application-controlled allowlist. See SQL Query Security for binding, validation, dynamic fragments, and code-review guidance.

# INSERT, UPDATE, and DELETE

The CRUD builder returns a QueryResult containing the statement and bound values. Execute those values through the adapter:

$insert = $this->database->builder()->buildInsertQuery(
    ['email' => $email, 'status' => 'pending'],
    'dbm_user'
);
$this->database->execute($insert->sql, $insert->params);
$userId = $this->database->getLastInsertId();

$update = $this->database->builder()->buildUpdateQuery(
    ['status' => 'active'],
    'dbm_user',
    'id = :id',
    ['id' => $userId]
);
$this->database->execute($update->sql, $update->params);

$delete = $this->database->builder()->buildDeleteQuery(
    'dbm_user',
    'id = :id',
    ['id' => $userId]
);
$this->database->execute($delete->sql, $delete->params);

The CRUD builder supports SqlExpression for trusted SQL expressions such as an increment. For example, SqlExpression::increment('view_count') adds one in the generated SET clause. Never put user input into an expression. The current CRUD builder omits array entries whose value is null; use parameterized SQL directly when you need to store SQL NULL. Its DELETE builder requires a non-empty WHERE; always make the condition narrow enough for the intended operation.

# Transactions and result metadata

For several writes that must succeed or fail together, use a transaction and roll back on failure:

$this->database->beginTransaction();

try {
    $this->database->execute($firstSql, $firstParams);
    $this->database->execute($secondSql, $secondParams);
    $this->database->commit();
} catch (\Throwable $exception) {
    if ($this->database->inTransaction()) {
        $this->database->rollback();
    }

    throw $exception;
}

query() returns a ResultInterface, whose fetch(), fetchAll(), and affectedRows() methods expose the result. The adapter's execute() returns a success boolean. Database errors are surfaced as query exceptions by the adapters.

The SQL syntax accepted by the builder and direct queries depends on the selected database driver. Use the Doctrine DBAL guide for platform-specific adapter behavior.