Doctrine Doctor (ahmed-bhs/doctrine-doctor)

Doctrine Doctor

https://github.com/ahmed-bhs/doctrine-doctor
Admin
Doctrine Doctor is a runtime analysis tool for Doctrine ORM that detects N+1 queries, performance...

Tokens:48,266
Snippets:459
Trust Score:8.1
Update:2 months ago
Show doc for...
Context Summary (auto-generated)
Raw
# Doctrine Doctor

Doctrine Doctor is a runtime analysis tool for Doctrine ORM, integrated directly into Symfony's Web Profiler. Unlike static analysis tools (PHPStan, Psalm) that analyze code without execution, Doctrine Doctor detects issues during actual query execution, providing insights into N+1 queries, missing database indexes, slow queries, security vulnerabilities, and architectural violations. The tool analyzes real execution context including actual parameter values, data volumes, and execution plans.

The bundle provides 66 specialized analyzers organized into four categories: Performance (N+1 detection, missing indexes, slow queries, hydration issues), Security (DQL/SQL injection, sensitive data exposure), Integrity (cascade configuration, bidirectional consistency, type mismatches), and Configuration (charset, collation, timezone handling). Results appear directly in the Symfony Web Profiler during development with backtraces pointing to exact code locations and actionable suggestions for fixes.

## Installation

```bash
composer require --dev ahmed-bhs/doctrine-doctor
```

Auto-configured via Symfony Flex. No additional configuration required.

## Basic Configuration

Configure analyzers in `config/packages/dev/doctrine_doctor.yaml`:

```yaml
doctrine_doctor:
    enabled: true

    profiler:
        show_in_toolbar: true
        show_debug_info: false

    analyzers:
        n_plus_one:
            enabled: true
            threshold: 5  # Minimum duplicate queries to trigger detection

        slow_query:
            enabled: true
            threshold: 100  # Milliseconds

        missing_index:
            enabled: true
            slow_query_threshold: 50
            min_rows_scanned: 1000
            explain_queries: true

        hydration:
            enabled: true
            row_threshold: 99
            critical_threshold: 999
```

## Enable Query Backtraces

Enable backtraces to see exact code locations in issues:

```yaml
# config/packages/dev/doctrine.yaml
doctrine:
    dbal:
        profiling_collect_backtrace: true
```

## AnalyzerInterface

The core interface that all analyzers implement. Analyzers receive a collection of query data from the current request and return a collection of detected issues.

```php
<?php

namespace AhmedBhs\DoctrineDoctor\Analyzer;

use AhmedBhs\DoctrineDoctor\Collection\IssueCollection;
use AhmedBhs\DoctrineDoctor\Collection\QueryDataCollection;

interface AnalyzerInterface
{
    public function analyze(QueryDataCollection $queryDataCollection): IssueCollection;
}
```

## Creating a Custom Analyzer

Implement `AnalyzerInterface` to create custom detection rules for your domain-specific requirements.

```php
<?php

namespace App\Analyzer;

use AhmedBhs\DoctrineDoctor\Analyzer\AnalyzerInterface;
use AhmedBhs\DoctrineDoctor\Collection\IssueCollection;
use AhmedBhs\DoctrineDoctor\Collection\QueryDataCollection;
use AhmedBhs\DoctrineDoctor\DTO\IssueData;
use AhmedBhs\DoctrineDoctor\Factory\IssueFactoryInterface;
use AhmedBhs\DoctrineDoctor\ValueObject\Severity;

final class LargeResultSetAnalyzer implements AnalyzerInterface
{
    public function __construct(
        private readonly IssueFactoryInterface $issueFactory,
        private readonly int $threshold = 1000,
    ) {}

    public function analyze(QueryDataCollection $queryDataCollection): IssueCollection
    {
        return IssueCollection::fromGenerator(
            function () use ($queryDataCollection) {
                foreach ($queryDataCollection as $queryData) {
                    if ($queryData->rowCount !== null && $queryData->rowCount > $this->threshold) {
                        $issueData = new IssueData(
                            type: 'large_result_set',
                            title: sprintf('Large Result Set: %d rows returned', $queryData->rowCount),
                            description: sprintf(
                                'Query returned %d rows (threshold: %d). Consider pagination.',
                                $queryData->rowCount,
                                $this->threshold
                            ),
                            severity: Severity::warning(),
                            suggestion: null,
                            queries: [$queryData],
                            backtrace: $queryData->backtrace,
                        );

                        yield $this->issueFactory->create($issueData);
                    }
                }
            }
        );
    }
}
```

Register the custom analyzer:

```yaml
# config/services.yaml
services:
    App\Analyzer\LargeResultSetAnalyzer:
        arguments:
            $threshold: 500
        tags:
            - { name: 'doctrine_doctor.analyzer' }
```

## QueryDataCollection API

Type-safe collection for analyzing query data with filtering, grouping, and aggregation methods.

```php
<?php

use AhmedBhs\DoctrineDoctor\Collection\QueryDataCollection;
use AhmedBhs\DoctrineDoctor\DTO\QueryData;

// Filter queries by type
$selectQueries = $queryDataCollection->onlySelects();
$insertQueries = $queryDataCollection->onlyInserts();
$updateQueries = $queryDataCollection->onlyUpdates();
$deleteQueries = $queryDataCollection->onlyDeletes();

// Filter slow queries (above 100ms threshold)
$slowQueries = $queryDataCollection->filterSlow(100.0);

// Filter queries with backtrace information
$tracedQueries = $queryDataCollection->withBacktrace();

// Filter queries matching SQL pattern
$userQueries = $queryDataCollection->matchingSql('user');

// Group queries by normalized SQL pattern (for N+1 detection)
$queryGroups = $queryDataCollection->groupByPattern(
    fn (string $sql): string => $this->normalizeQuery($sql)
);

// Calculate execution time statistics
$totalTime = $queryDataCollection->totalExecutionTime();     // Total ms
$avgTime = $queryDataCollection->averageExecutionTime();     // Average ms
$slowest = $queryDataCollection->slowest();                  // QueryData|null
$fastest = $queryDataCollection->fastest();                  // QueryData|null

// Sort by execution time
$sortedDesc = $queryDataCollection->sortByExecutionTimeDescending();
$sortedAsc = $queryDataCollection->sortByExecutionTimeAscending();

// Count queries by type
$counts = $queryDataCollection->countByType();
// Returns: ['SELECT' => 15, 'INSERT' => 3, 'UPDATE' => 2, 'DELETE' => 0, 'OTHER' => 0]

// Exclude vendor paths from analysis
$appQueries = $queryDataCollection->excludePaths(['vendor/', 'var/cache/']);

// Get queries with high row counts
$largeResults = $queryDataCollection->withRowCountAbove(1000);
```

## QueryData DTO

Immutable data transfer object representing a database query with execution metadata.

```php
<?php

use AhmedBhs\DoctrineDoctor\DTO\QueryData;
use AhmedBhs\DoctrineDoctor\ValueObject\QueryExecutionTime;

// Create from profiler data
$queryData = QueryData::fromArray([
    'sql' => 'SELECT * FROM users WHERE id = ?',
    'executionMS' => 15.5,
    'params' => [1],
    'backtrace' => [...],
    'rowCount' => 1,
]);

// Or construct directly
$queryData = new QueryData(
    sql: 'SELECT * FROM users WHERE status = ?',
    executionTime: QueryExecutionTime::fromMilliseconds(25.5),
    params: ['active'],
    backtrace: null,
    rowCount: 150,
);

// Query type detection
$queryData->isSelect();    // true
$queryData->isInsert();    // false
$queryData->isUpdate();    // false
$queryData->isDelete();    // false
$queryData->getQueryType(); // 'SELECT'

// Performance checks
$queryData->isSlow(100.0);  // true if > 100ms
$queryData->executionTime->inMilliseconds(); // 25.5

// Convert to array for serialization
$array = $queryData->toArray();
// Returns: ['sql' => '...', 'executionMS' => 25.5, 'params' => [...], ...]
```

## SuggestionFactory

Factory for creating actionable suggestions attached to detected issues. Provides type-safe methods for common issue patterns.

```php
<?php

use AhmedBhs\DoctrineDoctor\Factory\SuggestionFactory;
use AhmedBhs\DoctrineDoctor\ValueObject\SuggestionMetadata;
use AhmedBhs\DoctrineDoctor\ValueObject\SuggestionType;
use AhmedBhs\DoctrineDoctor\ValueObject\Severity;

// N+1 Query - Eager Loading suggestion
$suggestion = $suggestionFactory->createEagerLoading(
    entity: 'User',
    relation: 'profile',
    queryCount: 100,
    triggerLocation: 'UserController::index() in UserController.php:45',
);

// N+1 Query - Batch Fetch suggestion (for ManyToOne/OneToOne)
$suggestion = $suggestionFactory->createBatchFetch(
    entity: 'Order',
    relation: 'customer',
    queryCount: 50,
    triggerLocation: 'OrderService::process()',
);

// N+1 Query - Extra Lazy suggestion (for collections with partial access)
$suggestion = $suggestionFactory->createExtraLazy(
    entity: 'Category',
    relation: 'products',
    queryCount: 25,
    hasLimit: true,
);

// Collection N+1 - Eager Loading with parent-child context
$suggestion = $suggestionFactory->createCollectionEagerLoading(
    parentEntity: 'Author',
    collectionField: 'books',
    childEntity: 'Book',
    queryCount: 100,
);

// Flush in Loop detection
$suggestion = $suggestionFactory->createFlushInLoop(
    flushCount: 50,
    operationsBetweenFlush: 1.5,
);

// Missing Index suggestion
$suggestion = $suggestionFactory->createIndex(
    table: 'orders',
    columns: ['customer_id', 'created_at'],
    migrationCode: 'CREATE INDEX IDX_ORDERS_CUSTOMER_DATE ON orders (customer_id, created_at);',
);

// DQL Injection security issue
$suggestion = $suggestionFactory->createDQLInjection(
    query: "SELECT u FROM User u WHERE u.name = '" . $userInput . "'",
    vulnerableParameters: ['userInput'],
    riskLevel: 'critical',
);

// Create from template (recommended approach)
$suggestion = $suggestionFactory->createFromTemplate(
    'Performance/query_optimization',
    [
        'code' => $badCode,
        'optimization' => $goodCode,
        'execution_time' => 150.5,
        'threshold' => 100,
    ],
    new SuggestionMetadata(
        type: SuggestionType::performance(),
        severity: Severity::warning(),
        title: 'Slow Query Optimization',
        tags: ['performance', 'query', 'optimization'],
    ),
);
```

## N+1 Query Detection Example

Doctrine Doctor automatically detects N+1 queries during page requests:

```php
<?php
// Controller - BAD: Triggers N+1
class ArticleController extends AbstractController
{
    #[Route('/articles')]
    public function index(ArticleRepository $repository): Response
    {
        // This loads all articles
        $articles = $repository->findAll();

        // Template access to $article->getAuthor() triggers N+1
        return $this->render('articles/index.html.twig', [
            'articles' => $articles,
        ]);
    }
}

// Template (Twig) - triggers lazy loading
// {% for article in articles %}
//     {{ article.author.name }}  <- N+1 query per article!
// {% endfor %}
```

```php
<?php
// Controller - GOOD: Eager loading prevents N+1
class ArticleController extends AbstractController
{
    #[Route('/articles')]
    public function index(ArticleRepository $repository): Response
    {
        // Use QueryBuilder with JOIN FETCH
        $articles = $repository->createQueryBuilder('a')
            ->leftJoin('a.author', 'author')
            ->addSelect('author')  // Eager load authors
            ->getQuery()
            ->getResult();

        return $this->render('articles/index.html.twig', [
            'articles' => $articles,
        ]);
    }
}
```

## Configuring Analyzer Thresholds

Customize detection sensitivity per environment:

```yaml
# config/packages/dev/doctrine_doctor.yaml - Local Development (strict)
doctrine_doctor:
    analyzers:
        n_plus_one:
            threshold: 2      # Catch even minor N+1 issues
        slow_query:
            threshold: 20     # Fast local DB
        missing_index:
            slow_query_threshold: 10
            min_rows_scanned: 100

---
# config/packages/staging/doctrine_doctor.yaml - Staging (production-like)
doctrine_doctor:
    analyzers:
        n_plus_one:
            threshold: 5      # Balanced detection
        slow_query:
            threshold: 100    # Production-like hardware
        missing_index:
            slow_query_threshold: 50
            min_rows_scanned: 5000

---
# config/packages/prod/doctrine_doctor.yaml - Production (disabled)
doctrine_doctor:
    enabled: false  # MUST be disabled in production
```

## Disabling Specific Analyzers

Disable analyzers that aren't relevant to your project:

```yaml
doctrine_doctor:
    analyzers:
        # Disable if using non-Doctrine money handling
        float_for_money:
            enabled: false

        # Disable if intentionally using non-standard naming
        naming_convention:
            enabled: false

        # Keep all security analyzers enabled (recommended)
        dql_injection:
            enabled: true
        sql_injection:
            enabled: true
        sensitive_data_exposure:
            enabled: true
```

## Environment Variable Configuration

Use environment variables for dynamic configuration:

```yaml
# config/packages/doctrine_doctor.yaml
doctrine_doctor:
    enabled: '%env(bool:DOCTRINE_DOCTOR_ENABLED)%'

    analyzers:
        slow_query:
            threshold: '%env(int:SLOW_QUERY_THRESHOLD)%'
        n_plus_one:
            threshold: '%env(int:NPLUS_ONE_THRESHOLD)%'
```

```bash
# .env.local
DOCTRINE_DOCTOR_ENABLED=true
SLOW_QUERY_THRESHOLD=50
NPLUS_ONE_THRESHOLD=3
```

## Excluding Third-Party Entities

Filter out vendor entity analysis for cleaner reports:

```yaml
doctrine_doctor:
    analysis:
        exclude_third_party_entities: true  # Default: true
        exclude_paths:
            - 'vendor/'
            - 'var/cache/'
```

## Validating Configuration

Debug and validate your configuration:

```bash
# Check YAML syntax
php bin/console lint:yaml config/packages/doctrine_doctor.yaml

# View merged configuration
php bin/console debug:config doctrine_doctor

# View with resolved environment variables
php bin/console debug:config doctrine_doctor --resolve-env
```

## Summary

Doctrine Doctor provides comprehensive runtime analysis for Doctrine ORM applications, automatically detecting performance bottlenecks like N+1 queries and missing indexes, security vulnerabilities such as SQL injection, and code quality issues including cascade misconfigurations and type mismatches. The tool integrates seamlessly with Symfony's Web Profiler, requiring minimal configuration while providing immediate value through actionable suggestions with code examples.

The bundle is designed for development environments and should always be disabled in production. By analyzing actual query execution patterns rather than static code, Doctrine Doctor catches issues that static analysis tools miss, such as lazy loading triggered by templates, query performance with real data volumes, and database execution plan analysis. Custom analyzers can be created by implementing `AnalyzerInterface` and registering them with the `doctrine_doctor.analyzer` tag, enabling teams to enforce domain-specific best practices.