13.07.2022・TechStuff
13.07.2022・TechStuff

Shopware: Debug SQL Queries on the CLI

Fabian Blechschmidt

You are trying to debug a Command, ScheduledTask or Message on the queue?

Symfony Profiler and toolbar

For the frontend it is easy, you can just use the Symfony Profiler – easily activated by turning on the dev mode.

This can be done, by changing APP_ENV="prod" to APP_ENV="dev" in your .env file.

And then you have a toolbar in the footer which opens this:

Screenshot of the symfony profiler, Doctrine/SQL section

SQL Queries on the CLI

But for the CLI it is not so easy. It took me a while to figure it out, but with this post from Alex on stackoverflow I got the right hint.

I have the problem, that @Alex doesn’t work for me, because on dev, Shopware is interfering at two positions:

src/Kernel.php:49
vendor/shopware/core/HttpKernel.php:179

So I just hacked the core:

\Shopware\Core\Profiling\Doctrine\DebugStack::startQuery

And added the few lines directly into DebugStack:

public function startQuery($sql, ?array $params = null, ?array $types = null): void
{
    $this->ensureMasterSlaveCompatibility($sql);

// ADDED
    $doctrineExtension = new \Shopware\Core\Profiling\Twig\DoctrineExtension();
    echo $doctrineExtension->replaceQueryParameters(
            $sql,
            array_merge($params ?? array())
        ) . ';' . PHP_EOL;
// ADDED END

    parent::startQuery($sql, $params, $types);
}

But don’t try this with the frontend ? Or you end up with this:

Screenshot of a webpage filled with SQL queries and the symfony error screen.