Be careful ordering columns in a migration

Over the life of an application, we often need to add columns to existing tables as requirements change.

It doesn't really matter to our code, but as a developer, it's nice to keep related columns near each other in the table definition.

Laravel migrations, at least with the mysql database, let us specify the desired position of an existing column:

Schema::table('users', function (Blueprint $table) {
    $table->string('address2')->nullable()->after('address');
});

Notice how my new address2 column is placed directly after the existing address column. Without this, it would just add address2 at the end of the table.

While this is great for keeping things organized, it can cause problems if you're doing this on a very large table. Depending on a few factors (indexes in use, version of MySQL, etc), this migration might result in rebuilding the whole table. If the table only has a few thousand rows, it will be almost instant. But if it has millions of rows, it could lock your table for a few minutes.

That isn't to say you shouldn't ever do it, but just be aware of the potential issues, and you may want to schedule a maintenance window for a migration like this.

Note: MySQL 8.0 introduced a new INSTANT algorithm that can help you avoid surprises like this. In version 8.0.12 it became the default algorithm, and in 8.0.29 it even works when inserting columns in the middle of a table. So your mileage will vary depending on the version of MySQL you're using.

Here to help,

Joel

P.S. Fellow Laravel dev Ash Allen has just released a new book Consuming APIs in Laravel. If you want to go deep on this topic, this is a great reference.