How to log query execution time in Doctrine 3 using middlewares, now that "setSQLLogger()" is deprecated?

Doctrine has deprecated its ->setSQLLogger() in favour of Middleware.

I can't find any way to log start and end of query to get execution time.

Previously it could be done like in this question about Symfony2.

What kind of Middleware should I create to be able to log execution time of a query?

Right now, I only have enabled logging, but that just logs the query before executing it.

I have found that all queries are being gathered inside DebugDataHolder, so as alternative it would be to log all queries at the end of request:

use Doctrine\SqlFormatter\SqlFormatter;

class DatabaseFormatter implements FormatterInterface
{
    private string $id;
    private SqlFormatter $formatter;

    public function __construct(
        private DebugDataHolder $dataHolder,
    ) {
    }

    public function afterRequest() 
    {
        $queries = $this->dataHolder->getData();

        // ...
    }
}

Solution

I have analysed how Symfony is adding its middleware and compiled following code that can do whatever is needed with queries:

First create basic middleware as stated in this doc

namespace App\Doctrine;

use Doctrine\DBAL\Driver as DriverInterface;
use Doctrine\DBAL\Driver\Middleware as MiddlewareInterface;

class Middleware implements MiddlewareInterface
{
    public function __construct(
        /* ...dependencies from Symfony DI */
    ) {
    }

    public function wrap(DriverInterface $driver): Driver
    {
        // \App\Doctrine\Driver
        return new Driver($driver, /* ...dependencies */);
    }
}

Then add additional Middleware:

namespace App\Doctrine;

use Doctrine\DBAL\Driver as DriverInterface;
use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;

final class Driver extends AbstractDriverMiddleware
{
    public function __construct(
        DriverInterface $driver,
        /* ...dependencies */
    ) {
        parent::__construct($driver);
    }

    public function connect(array $params): Connection
    {
        // \App\Doctrine\Connection
        return new Connection(parent::connect($params), /* ...dependencies */);
    }
}

namespace App\Doctrine;

use App\Log\Formatter\DatabaseFormatter;
use Doctrine\DBAL\Driver\Connection as ConnectionInterface;
use Doctrine\DBAL\Driver\Middleware\AbstractConnectionMiddleware;
use Doctrine\DBAL\Driver\Result;
use Doctrine\DBAL\Driver\Statement as StatementInterface;

class Connection extends AbstractConnectionMiddleware
{
    public function __construct(
        ConnectionInterface $connection,
    ) {
        parent::__construct($connection);
    }

    public function prepare(string $sql): StatementInterface
    {
        // \App\Doctrine\Driver
        return new Statement(parent::prepare($sql), $sql);
    }

    /*
     * Overwrite any method from parent class with features
     */
}

namespace App\Doctrine;

use Doctrine\DBAL\Driver\Middleware\AbstractStatementMiddleware;
use Doctrine\DBAL\Driver\Statement as StatementInterface;

final class Statement extends AbstractStatementMiddleware
{
    private array $params = [];
    private array $types = [];

    public function __construct(
        StatementInterface $statement,
        private string $sql,
    ) {
        parent::__construct($statement);
    }

    /*
     * Overwrite any method from parent class with features.
     */
}

Connection class is used when making direct requests and Statement is used when making prepared statements.