Foreign Key Support für SQLite mit Doctrine und Symfony

SQLite macht gerade für lokale Entwicklungen viel Spaß. Um es jedoch im Produktionsbetrieb zu nutzen, gibt es einige Fallstricke zu beachten die es z.B. bei MariaDB nicht gibt. Ein ganz wesentlicher Faktor sind Foreign Keys.

Nehmen wir an, wir setzen in einem Symfony Projekt als ORM Doctrine ein und wollen per Default SQLite verwenden. In der .env setzen wir die DATABASE_URL auf eine SQLite Datenbank:

DATABASE_URL=sqlite:///%kernel.project_dir%/var/data/database.sqlite

Und schon läufts. Was aber wenn wir in unseren Entities Foreign Keys u.ä. mit Cascade Operationen nutzen wollen? Klassisches Beispiel, Objekte die einem Benutzer zugeordnet sind:

/**
 * @var User
 *
 * @ORM\ManyToOne(targetEntity="App\Entity\User")
 * @ORM\JoinColumn(name="`user`", referencedColumnName="id", onDelete="CASCADE", nullable=false)
 * @Assert\NotNull()
 */
private $user;

Die Datenbank wird ohne Klage erstellt, allerdings bleiben diese Einträge einfach zurück, nachdem das User Objekte gelöscht wurde. Wieso?

Tja, einfach gesagt: Foreign Keys werden in SQLIte zwar leidlich gut unterstützt, sie sind jedoch deutlich problematischer zu nutzen als in MySQL und müssen zudem auch noch explizit aktiviert werden.

Zum Hintergrund sei zunächst die offizielle Dokumentation zu Foreign Keys empfohlen. Darüberhinaus ist auch ein ausführlicher Blick in die Pragma Anweisungen für SQLite sinnvoll. Das hier sagt die Dokumentation zu PRAGMA foreign_keys:

As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. […] To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting.

Demnach sind Foreign Keys standardmäßig deaktiviert und wir müssen Sie explizit anschalten. Am einfachsten ist dies mit einem Doctrine EventSubscriber zu lösen. Hierzu erstellen wir eine Klasse, die auf das PostConnect Event lauscht und welche direkt die Pragma Anweisung zur Aktivierung von Foreign Keys ausführt:

namespace App\Doctrine;

use Doctrine\Common\EventSubscriber;
use Doctrine\DBAL\Event\ConnectionEventArgs;
use Doctrine\DBAL\Events;

class SqliteForeignKeyActivationSubscriber implements EventSubscriber
{
    /**
     * {@inheritdoc}
     */
    public function getSubscribedEvents()
    {
        return [
            Events::postConnect,
        ];
    }

    /**
     * @param ConnectionEventArgs $args
     * @throws \Doctrine\DBAL\DBALException
     */
    public function postConnect(ConnectionEventArgs $args)
    {
        if ('sqlite' !== strtolower($args->getDatabasePlatform()->getName())) {
            return;
        }

        $args->getConnection()->exec('PRAGMA foreign_keys = ON;');
    }
}

Das ganze noch in der services.yaml registrieren:

App\Doctrine\SqliteForeignKeyActivationSubscriber:
    class: App\Doctrine\SqliteForeignKeyActivationSubscriber
    tags:
        - { name: doctrine.event_listener, event: postConnect }

Das war es auch schon: Foreign Keys sind dauerhaft in Eurem Projekt aktiviert und ihr könnt darüberhinaus gefahrlos andere Datenbanken einsetzen, da wir den Typ ja zuerst via getDatabasePlatform() sicherstellen, bevor wir „PRAGMA foreign_keys“ aktivieren.

Spannend wird es jetzt, wenn wir Migrations nutzen, die Tabellen verändern (ALTER TABLE) sollen welche Foreign Keys nutzen … dazu folgt in Kürze ein weiterer Artikel.