Howto write doctrine sql log to a seperate file in symfony (2.0)

Category: Symfony Tags: doctrine, monolog

Do you want to write the doctrine sql log to a separate file/handler. Well it seems not that easy in the first but I looked into it and it is not as complicated as you might think...

In symfony standard setup doctrine writes its messages to the default monolog service.

So at first you need to set up your own logging service and attach a handler to it. I found a good explanation here.

# Acme/MyBundle/Resources/config/services.yml

services:
    my_logger:
        class: Symfony\Bridge\Monolog\Logger
        arguments: [@doctrine]
        calls:
            - [pushHandler, [@my_handler]]
    my_handler:
        class: Monolog\Handler\StreamHandler
        # 200 = INFO, see Monolog::Logger for the values of log levels
        arguments: [%kernel.root_dir%/%kernel.environment%.doctrine.log, 200]

Next you need to inject your service into doctrine.

If you look into doctrine extension class, you'll find the following code:

// Symfony/Bundle/DoctrineBundle/DependencyInjection/DoctrineExtension.php

if (isset($connection['logging']) && $connection['logging']) {
    $configuration->addMethodCall('setSQLLogger', array(new Reference('doctrine.dbal.logger')));
    unset ($connection['logging']);
}

So doctrine uses the service 'doctrine.dbal.logger' as logging service. Lets look at the service definition.

<!-- Symfony/Bundle/DoctrineBundle/Resources/config/dbal.xml -->

<service id="doctrine.dbal.logger" public="false">
    <tag name="monolog.logger" channel="doctrine" />
    <argument type="service" id="logger" on-invalid="null" />
</service>

doctrine.dbal.logger uses the default monolog.logger. That is where you need to inject your own service.

There is a description of howto override another bundles service configuration with a compiler pass. Add a compiler pass to your bundle and change the service definition of doctrine.dbal.logger.

<?php

// Acme/MyBundle/DependencyInjection/Compiler/MyLoggerCompilerPass.php

namespace Acme\MyBundle\DependencyInjection\Compiler;

use Symfony\Component\DependencyInjection\Compiler\CompilerPassInterface;
use Symfony\Component\DependencyInjection\ContainerBuilder;
use Symfony\Component\DependencyInjection\Reference;

class MyLoggerCompilerPass implements CompilerPassInterface
{
    public function process(ContainerBuilder $container)
    {
        $definition = $container->getDefinition('doctrine.dbal.logger');
        $definition
            ->setArguments(array(new Reference('my_logger')))
            ->clearTags();
    }
}

Finally add the compiler pass to the container in your bundle class.

<?php

// Acme/MyBundle/AcmeMyBundle.php

namespace Acme\MyBundle;

use Symfony\Component\HttpKernel\Bundle\Bundle;
use Symfony\Component\DependencyInjection\ContainerBuilder;

use Acme\MyBundle\DependencyInjection\Compiler\MyLoggerCompilerPass;

class AcmeMyBundle extends Bundle
{
    public function build(ContainerBuilder $container)
    {
        parent::build($container);
        $container->addCompilerPass(new MyLoggerCompilerPass());
    }
}

Et voila. Now doctrine logs everything to my_logger service.