Laravel migration, how to deal with enum & SQLite in tests context

Laravel migration, how to deal with enum & SQLite in tests context

For any application, automated tests are a must-have to ensure operation and stability. Sometimes it could be complicated to realise tests because to maximize the tests running we have to use tools that are not directly used by the application stack.

The simplest example to illustrate that context is when you use MySQL in your development or production environment and you run your tests with SQLite database. Has you certainly now, SQLite does not support all SQL features (omitted SQL features in SQLite) and especially enum type.

Following, I will show you a migration strategy I use with Laravel to be able to use enum column in both MySQL and SQLite database tables.


Ok let's start, first of all, I create an interface to record enum values as constants and I create one more constant, an array, that I fill with all constants. If my application roles have to change, I will update the code consequently (the interface and all use of the constant).

<?php

namespace abenevaut\Infrastructure\Interfaces\Domain\Users\Users;

interface UserRolesInterface
{
   const ROLE_ADMINISTRATOR = 'administrator';
   const ROLE_CUSTOMER = 'customer';
   const ROLE_ACCOUNTANT = 'accountant';

   const ROLES = [
      self::ROLE_ADMINISTRATOR,
      self::ROLE_CUSTOMER,
      self::ROLE_ACCOUNTANT,
   ];
}


Then, I create a Laravel migration that the UserRolesInterface will use to create an enum column in a database table. Nothing hard so far. 

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use abenevaut\Infrastructure\Interfaces\Domain\Users\Users\UserRolesInterface;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            
         
           $table
              ->enum('role', UserRolesInterface::ROLES)
              ->default(UserRolesInterface::ROLE_CUSTOMER)
              ->index();
         
           // ...
         
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}


At this step, we can run tests with SQLite without any trouble. Migration will be executed because Fluent (used in Eloquent) will transform enum column as a string column in SQLite context.

 

Now we need to update roles in database (add or remove a role), numbers of Laravel topics advice to use a raw query to update enum column in MySQL.


DB::statement('ALTER TABLE users CHANGE COLUMN role role ENUM("'.UserRolesInterface::ROLE_ADMINISTRATOR.'", "'.UserRolesInterface::ROLE_CUSTOMER.'") NOT NULL DEFAULT "'.UserRolesInterface::ROLE_CUSTOMER.'"');


At this point we got a problem because of the none implemented alter table CHANGE instruction in SQLite, so we have to protect the raw query to be executed only in a different context than SQLite database driver. Also do not forget to update the previously created interface, especially the array constant !

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
use abenevaut\Infrastructure\Interfaces\Domain\Users\Users\UserRolesInterface;

class AlterUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
		if ('sqlite' !== config('database.default'))
		{
			DB::statement('ALTER TABLE users CHANGE COLUMN role provider ENUM("'.UserRolesInterface::ROLE_ADMINISTRATOR.'", "'.UserRolesInterface::ROLE_CUSTOMER.'")');
		}
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}


And Tada! The magic operates! What will happen when we will run migration ?
  • MySQL will run not already ran migration to update the table
  • SQLite will permanently run all migrations when starting to execute a new test, it will run the first migration with the updated interface constant that allows to be in the right context to use `Blueprint::enum()` method and it will never run instruction in the second migration