Native SQL and the DBAL
Running SQL through the DBAL connection, binding parameters and lists safely, and mapping rows back to entities.
$connection->executeQuery($sql, ['ids' => $ids], ['ids' => ArrayParameterType::INTEGER])->fetchAllAssociative()The DBAL is Doctrine's Database Abstraction Layer: the connection, the drivers and the type conversion that sit beneath the ORM. When a query cannot be expressed in DQL — a window function, a recursive CTE, a vendor operator such as PostgreSQL full-text search — you drop to this layer and write SQL.
Get a connection by type-hinting Doctrine\DBAL\Connection in a constructor; autowiring supplies it. $entityManager->getConnection() returns the same object, which is the form you will see inside repositories.
Running and reading
executeQuery(string $sql, array $params = [], array $types = []): Result is for reads. From the Result you take fetchAllAssociative() for every row as an associative array, fetchAssociative() for the first row or false, fetchOne() for the first column of the first row, fetchFirstColumn() for one column across all rows, and iterateAssociative() to stream. executeStatement(string $sql, array $params = [], array $types = []): int is for writes and returns the number of rows affected.
Parameters
Placeholders are named, :status, or positional, ? — the same two forms as DQL, and you may not mix them in one statement. An array cannot be bound to a single placeholder on its own, so pass its type: ArrayParameterType::INTEGER makes Doctrine rewrite the SQL into one placeholder per value. Giving a parameter a DBAL type such as Types::DATETIME_IMMUTABLE converts the PHP value on the way in.
Getting objects back
$entityManager->createNativeQuery($sql, $rsm) maps raw rows onto entities using a ResultSetMapping. ResultSetMappingBuilder reads the mapping from the entity itself and will even generate the select clause.
What trips people up: a DBAL result is arrays with database column names, not properties.
Example
<?php
// src/Report/ArticleStatsQuery.php
declare(strict_types=1);
namespace App\Report;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Connection;
final class ArticleStatsQuery
{
// The connection is autowired by type, not fetched from a container.
public function __construct(
private readonly Connection $connection,
) {
}
/**
* @param int[] $authorIds
*
* @return array<int, array{author_id: int, published: int}>
*/
public function countPublishedPerAuthor(array $authorIds): array
{
// Column names here are database names, not entity properties.
$sql = <<<'SQL'
SELECT author_id, COUNT(*) AS published
FROM article
WHERE status = :status
AND author_id IN (:authorIds)
GROUP BY author_id
ORDER BY published DESC
SQL;
return $this->connection->executeQuery(
$sql,
['status' => 'published', 'authorIds' => $authorIds],
// The list needs its type so the SQL is rewritten into
// one placeholder per value.
['authorIds' => ArrayParameterType::INTEGER],
)->fetchAllAssociative();
}
}When to use it
- Run a reporting query using a window function or a CTE that DQL cannot express.
- Use a database-specific feature such as PostgreSQL full-text search and still get entities back.
- Update or delete a large batch of rows in one statement without loading anything.
More examples
Mapping native SQL rows back to entities
generateSelectClause() keeps the SQL in step with the mapping, so adding a property to Article does not break the query. The entities that come back are managed exactly like entities from DQL: change one and the next flush() writes it. Writing the mapping by hand instead means ResultSetMapping with addEntityResult(Article::class, 'a') and one addFieldResult('a', 'published_at', 'publishedAt') per column.
// src/Repository/ArticleRepository.php (method on the generated class)
use App\Entity\Article;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
/**
* PostgreSQL full-text search has no DQL equivalent.
*
* @return Article[]
*/
public function search(string $term): array
{
$em = $this->getEntityManager();
$rsm = new ResultSetMappingBuilder($em);
// Reads the column-to-property mapping from Article itself.
$rsm->addRootEntityFromClassMetadata(Article::class, 'a');
// Generates the column list, keyed by result alias => SQL table alias.
$sql = 'SELECT '.$rsm->generateSelectClause(['a' => 'a']).'
FROM article a
WHERE a.search_vector @@ plainto_tsquery(:term)
ORDER BY a.published_at DESC
LIMIT 20';
return $em->createNativeQuery($sql, $rsm)
->setParameter('term', $term)
->getResult();
}A bulk write with a typed parameter
Reach for this when the update touches many rows and no lifecycle behaviour needs to run. It never loads an entity, so it is fast, and equally it tells the entity manager nothing: objects already in memory keep their old status. Passing the type is what lets you hand over a DateTimeImmutable instead of formatting a string yourself.
// src/Service/ArticleArchiver.php (method on a service)
use Doctrine\DBAL\Types\Types;
public function archiveOlderThan(\DateTimeImmutable $cutoff): int
{
// executeStatement() is for writes and returns the affected row count.
return $this->connection->executeStatement(
'UPDATE article SET status = :new WHERE status = :old AND published_at < :cutoff',
[
'new' => 'archived',
'old' => 'published',
'cutoff' => $cutoff,
],
// The DBAL type converts the DateTimeImmutable for the database.
['cutoff' => Types::DATETIME_IMMUTABLE],
);
}Picking the right fetch method
Choosing the narrowest fetch method saves a loop and a layer of arrays. fetchAssociative() returning false rather than null is the detail that catches people, because a strict comparison against null passes an empty result straight through. For a result too large to hold in memory, iterateAssociative() yields the rows one at a time.
// src/Report/ArticleStatsQuery.php (method on the same service)
/**
* @return array{total: int, newest: array{id: int, title: string}|false, ids: list<int>}
*/
public function overview(): array
{
$c = $this->connection;
// One value from the first column of the first row.
$total = (int) $c->fetchOne('SELECT COUNT(*) FROM article');
// One row, or false when the table is empty.
$newest = $c->fetchAssociative(
'SELECT id, title FROM article ORDER BY published_at DESC LIMIT 1'
);
// One column from every row. Positional placeholder this time.
$ids = $c->fetchFirstColumn(
'SELECT id FROM article WHERE status = ?',
['published'],
);
return ['total' => $total, 'newest' => $newest, 'ids' => $ids];
}
Discussion