Supporting delete cascade with SQLite and Laravel

If using SQLite, it is useful to be able to cascade a delete to related models. For instance, if a user is deleted, all their posts should also be deleted rather than being orphaned.

In the schema for the pivot table, you specify;

    $table->integer('post_id')->unsigned();
    $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade'); 

    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); 

This works out of the box for mysql, but with sqllite it is not supported without turning it on.

I had this issue, and created a workaround, but I’m not comfortable with the solution because it required me to change the Laravel source. I’m only a newbie so could not really see an ‘app’ way of doing it.

In config/database.php

    'sqlite' => [
        'driver'   => 'sqlite',
        'database' => storage_path().'/database2.sqlite',
        'prefix'   => '',
        'exec'	   => 'PRAGMA foreign_keys = ON;',  //enable delete cascade
    ],

I added a new element ‘exec’

Then in /vendor/laravel/framework/src/Illuminate/Database/Connectors/SQLiteConnector.php, replace;

    return $this->createConnection("sqlite:{$path}", $config, $options);

with

    $pdo=$this->createConnection("sqlite:{$path}", $config, $options);

    //any exec statement?
    $exec = array_get($config, 'exec');
    if(isset($exec))
    {
        $pdo->exec($exec);
    }
    return $pdo;

This allows the foreign_keys property to be set each time the connection is opened, and also any additional exec statements that might be needed.