Laravel migration MySQL/SQLite gotcha.

If you're running your integration tests with a fresh in-RAM database, watch out for the small things

Recently we were bringing an old Laravel app up to date, and we ran into a weird case where the database schema didn't match what we were expecting. Prod was working, staging was working, local was working, and the tests were all failing. What in the world was happening?

We were going through and adding tests to verify that existing functionality would continue working, and there was a migration file that looked similar to this:

Schema::table('tools', function (Blueprint $table) {
    $table->renameColumn('attachment', 'screenshot');
    $table->after('name', function (Blueprint $table) {
        $table->string('file_name')->nullable();
        $table->string('extension')->nullable();
        $table->unsignedBigInteger('size')->nullable();
    });
});

This looked nice and clean, and worked fine in MySQL, which is the DB flavor that this system was running. Since there weren't any tests on this portion of the codebase, we glossed right over the fact that this migration wasn't actually doing everything it was supposed to in SQLite. Let's break it down:

1. First, rename the column "attachment" to "screenshot".
2. Next, add three new columns to the tools table after the existing column "name"

Laravel comes with this super handy block in the phpunit configuration:

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

This means that whenever we run the tests, we'll use SQLite instead of MySQL (or MariaDB), and we'll write that database file to RAM. Spin it up, run all migrations, get a clean fresh database that only lives as long as the tests are running. Love it!

The only issue, we were not able to use the built in DB test methods ($this->assertDatabaseHas(), $this->assertDatabaseCount(), etc.) correctly. For some reason, we weren't seeing the columns file_name, extension, or size in the SQLite database.

Turns out, you can't do more than column renaming in a single command in the Laravel migration. All we needed to do was modify the old migration file into two steps. Something like this:

// rename column
Schema::table('tools', function (Blueprint $table) {
    $table->renameColumn('attachment', 'screenshot');
});

// add new columns
Schema::table('tools', function (Blueprint $table) {
    $table->after('name', function (Blueprint $table) {
        $table->string('file_name')->nullable();
        $table->string('extension')->nullable();
        $table->unsignedBigInteger('size')->nullable();
    });
});

Since this migration already ran successfully in all the environments that matter, nothing is really changing where the data is important. However, now SQLite was seeing two different instructions for the tools table, and like magic, the tests were working as expected.