Symfony2 & Doctrine: Create custom SQL-Query

Symfony2 & Doctrine: Create custom SQL-Query

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Creating Custom SQL Queries in Symfony2 and Doctrine

Using raw SQL within a Symfony application often stems from a need for specific performance optimizations or complex data manipulations that the ORM cannot easily express. However, the standard pattern of calling $em->createQuery($sql) without parameters is fundamentally flawed. As shown in common pitfalls, executing a query string directly inside createQuery bypasses Doctrine's Query Builder mechanisms, preventing parameter binding and leaving your application vulnerable to SQL injection attacks.

The core issue with the snippet $em->createQLQuery($sql)->execute() lies in how the result set is retrieved. When you pass a raw string like 'SELECT * FROM users', Doctrine treats it as a DQL (Doctrine Query Language) statement. If that string contains actual SQL syntax or relies on specific column names that differ between database drivers, it will fail immediately. Furthermore, without placeholders (? or named parameters), any dynamic input you inject into that string becomes a security risk.

To execute raw SQL safely and effectively in Symfony2, you should not use the Query object returned by createQuery. Instead, utilize the QueryBuilder's ability to execute raw SQL through the execute() method on an EntityManager, or more commonly, leverage the DBAL connection directly.

Executing Raw SQL via EntityManager

The most idiomatic way to run a custom SQL query while maintaining access to Doctrine's entity manager is by using the createNativeQuery method. This tells Doctrine explicitly that you are providing raw SQL instead of DQL.

use Doctrine\ORM\EntityManagerInterface;

class UserRepository {
    public function getUsersByCountry($country, $entityManager): array
    {
        // Use createNativeQuery to indicate raw SQL
        $query = $entityManager->createNativeQuery(
            'SELECT u.id, u.username FROM users u WHERE u.country = :country',
            [
                'country' => $country,
            ]
        );

        return $query->getResult();
    }
}

In this example, we pass the SQL string as the first argument and an associative array of parameters as the second. This ensures that $country is treated as a literal value and safely escaped by the database driver. You can then map the result to your entities or plain arrays depending on whether you want to hydrate them into User objects or keep them as simple data rows.

Direct DBAL Connection Access

Sometimes, the overhead of mapping raw results back to entities is unnecessary, or the SQL logic requires a level of control that DQL cannot provide. In these cases, accessing the database connection directly via the Doctrine ORM's getConnection() method is a valid approach. This pattern is often preferred when working with complex reporting queries where you do not need object hydration at all.

use Doctrine\DBAL\Connection;

public function getRawData(Connection $connection): array
{
    $sql = 'SELECT * FROM analytics WHERE date > :date LIMIT 100';
    
    return $connection->executeQuery($sql, [
        'date' => new \DateTime(),
    ], ['fetchAllAssociative']);
}

Here, we explicitly pass the fetch mode. Using 'fetchAllAssociative' returns an array of associative arrays rather than objects, which is often more efficient for large datasets. This approach decouples your logic from the entity mapping layer entirely.

Trade-offs and Considerations

While raw SQL offers granular control, it introduces significant maintenance costs. One major pitfall is portability; a query written for PostgreSQL might fail on MySQL due to syntax differences or function availability. If you write custom SQL, ensure that your codebase documents these dependencies clearly.

Additionally, debugging raw queries can be tricky because stack traces often point to the executeQuery call rather than the specific logic inside the SQL string. Profiling tools like Doctrine's profiler will show the raw query time but won't offer the same introspection capabilities as debugging DQL.

If you are migrating from a legacy system or dealing with highly specific database optimizations, custom SQL is justified. However, for standard data access patterns, sticking to DQL ensures better type safety, easier caching strategies, and reduced risk of accidental data exposure. For teams looking to manage complex migrations between Symfony and other frameworks like Laravel, understanding these underlying patterns helps in architecting portable data layers.

Our network