Testing with SQLite: the foreign key issue

Guided by the wonderful Test Driven Laravel, I'm now trying very hard to write tests before writing any code. Duh! TDD.

Okay, yes. And I assume that most of us use the easiest, simplest, laziest of defaults when writing tests that require a database. Meaning:

<env name="DB_CONNECTION" value="sqlite"/>
<env name="DB_DATABASE" value=":memory:"/>

And I also assume that we want to use the easiest, simplest, laziest method for handling relationship coherence in our models. Meaning something like:


Schema::create('books', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
});

Schema::create('pages', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('book_id')->unsigned();
    $table->integer('position');

    $table->foreign('book_id')
        ->references('id')->on('books')
        ->onDelete('cascade');
});

As it turns out, that won't work.

Not in testing at least, and not by default.

What happens is that SQLite doesn't handle foreign key restrictions by default. So if in your tests you expect, for example, that when deleting a Book, all the related pages should be removed, they won't.

Now, I don't want to have to build Observers to handle these kinda situations. And I also don't want to change my testing database and workflow.

Well, I didn't have to:

Have fun!