Laravel migrations tips

When developing a Laravel application, it’s quite common to run the development (and test) version with a SQLite database, and the production version with a MySQL database. Normally, the migrations will take care of creating the correct database schema. However, there are a few caveats.

Indeed, SQLite is much more permissive than MySQL, and will allow some schemas that MySQL won’t. So, to have a headache when you will deploy your app, here are a few tips to define your migrations…

1. Avoid text fields with a default value

MySQL does not allow text fields with a default value, so avoid, and use nullable instead…

public function up()
{
    Schema::table('objects', function (Blueprint $table) {
        // Avoid! Will work with SQLite, but not with MySQL
        $table->text('name')->default('');
        // Works with SQLite and MySQL
        $table->text('email_note')->nullable();
    });
}

2. Indexed text fields must have a maximum length

Internally, MySQL does not handle string fields with a maximum length the same way as text fields with unlimited length. And only the former can be indexed.

public function up()
{
    Schema::table('objects', function (Blueprint $table) {
        // Avoid! Will not work with MySQL
        $table->text('name');
        // Works...
        $table->string('name', 255);
        
        $table->unique('name');
    });
}

This apply to all index types:

• $table->primary('id');
• $table->primary(['id', 'parent_id']);
• $table->unique('email');
• $table->index('state');

3. Foreign key identifiers must be of type unsigned big integer

Again, this would work with SQLite, but not with MySQL.

public function up()
{
    Schema::table('objects', function (Blueprint $table) {
        // Avoid! Will work with SQLite, but not with MySQL
        $table->integer('user_id');
        // Works with SQLite and MySQL
        $table->unsignedBigInteger('user_id');

        $table->foreign('user_id')->references('id')->on('users');
    });
}

This blog post is licensed under CC BY-SA 4.0