Tom Newby

Enable foreign keys for SQLite-based Symfony tests

14 Oct 2018

We use Symfony Functional Tests extensively to test the APIs we build for our application. By being able to replicate an entire HTTP request and run actual database queries against fixtures in SQLite generated with Faker, we get fast, repeatable tests that give us confidence our application works as expected.

However, a few months back, we ran into a bug in production that wasn’t caught by our tests. In a nutshell, foreign key constraints weren’t being enforced and our data was attempting to enter an invalid state! In production, this resulted in MySQL rejecting the update query, but in test it was not!

Running a different database in test is largely a non-issue as Doctrine abstracts away most of the differences between different database platforms like nuances around connecting, preparing queries, handling results etc.

However, SQLite does not have foreign keys enabled by default, and Doctrine doesn’t enable them either. Our workaround was applying the idea from a StackOverflow answer on the issue: using a Symfony EventSubscriber to execute the command to enable foreign keys in the preFlush event of Doctrine.

<?php

// Tests/EventSubscriber/ForeignKeyEnabler.php

declare(strict_types=1);

namespace App\Tests\EventSubscriber;

use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Event\PreFlushEventArgs;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * We enable foreign keys manually/specifically in the test environment as SQLite does not have them enabled by default.
 * @see https://tomnewby.net/
 */
class ForeignKeyEnabler implements EventSubscriber
{
    /** @var EntityManagerInterface */
    private $manager;

    public function __construct(EntityManagerInterface $manager)
    {
        $this->manager = $manager;
    }

    public function getSubscribedEvents()
    {
        return [
            'preFlush',
        ];
    }

    public function preFlush(PreFlushEventArgs $args): void
    {
        $this->manager
            ->createNativeQuery('PRAGMA foreign_keys = ON;', new ResultSetMapping())
            ->execute()
        ;
    }
}

We must also then register the event subscriber in our services_test.yaml file:

services:
    # ...
    Hyra\Tests\Helper\EventSubscriber\ForeignKeyEnabler:
        tags:
            - { name: doctrine.event_subscriber }

Now, before your test executes a flush, it will enable the foreign keys first, thereby replicating the same behaviour as MySQL! Happy testing!

Follow me on Twitter: @tomnewbyau