Laravel 5.1: Enable SQLite foreign key constraints

Question

In SQLite, foreign key constraints are disabled by default.

What's the best way to configure Laravel 5.1's SQLite database connection to enable foreign key constraints? I don't see a way of doing this in ['connections']['sqlite'] in /config/database.php.

Answer 1

Here's one solution. In the boot() method of App\Providers\AppServiceProvider, add:

if (DB::connection() instanceof \Illuminate\Database\SQLiteConnection) {
    DB::statement(DB::raw('PRAGMA foreign_keys=1'));
}

Thanks to @RobertTrzebinski for this blog post regarding Laravel 4.

Answer 2

You could also activate foreign keys on a per test (file) basis, when the tests actually depend on tables with foreign keys.

Here's a trait: (e.g. tests/ForeignKeys.php)

<?php

namespace Tests;

trait ForeignKeys
{
    /**
     * Enables foreign keys.
     *
     * @return void
     */
    public function enableForeignKeys()
    {
        $db = app()->make('db');
        $db->getSchemaBuilder()->enableForeignKeyConstraints();
    }
}

don't forget to run the method somewhere in your test set-up chain. I added mine as an override to my TestCase: (tests/TestCase.php)

<?php

namespace Tests;

/**
 * Class TestCase
 * @package Tests
 * @mixin \PHPUnit\Framework\TestCase
 */
abstract class TestCase extends \Illuminate\Foundation\Testing\TestCase
{
    use CreatesApplication;

    ...

    /**
     * Boot the testing helper traits.
     *
     * @return array
     */
    protected function setUpTraits()
    {
        $uses = parent::setUpTraits();

        if (isset($uses[ForeignKeys::class])) {
            /* @var $this TestCase|ForeignKeys */
            $this->enableForeignKeys();
        }
    }

    ...

after that you can add it to your tests like so:

<?php

namespace Tests\Feature;

use Tests\ForeignKeys;
use Tests\TestCase;
use Illuminate\Foundation\Testing\DatabaseMigrations;

class ExampleFeatureTest extends TestCase
{
    use DatabaseMigrations;
    use ForeignKeys;

    ...

Answer 3

For me using a the facade DB within App\Providers\AppServiceProvider in Laravel 5.2 produced error. Here is my solution:

if(config('database.default') == 'sqlite'){
    $db = app()->make('db');
    $db->connection()->getPdo()->exec("pragma foreign_keys=1");
}

Answer 4

Since I only want to use this in my tests, but in all tests, I ended up with a simple implementation in the Tests\TestCase class like this:

 abstract class TestCase extends BaseTestCase
 {
        use CreatesApplication;

        protected function setUp()
        {
            parent::setUp();

            $this->enableForeignKeys();
        }

        /**
         * Enables foreign keys.
         *
         * @return void
         */
        public function enableForeignKeys()
        {
            $db = app()->make('db');
            $db->getSchemaBuilder()->enableForeignKeyConstraints();
        }
}

This works like a charm :-)