Laravel: Rename Table with Zero-Downtime Migrations

Disclaimer: this post will be based on a MySQL based application and will assume you have a CD pipeline in place that allows you to deploy seamlessly code and run migrations at deployment time.

Renaming a database table in production application can be a tricky operation. Of course Laravel offers a helper to do that and it's really easy to write the following migration:

Schema::rename($currentTableName, $newTableName);

With a small table it's not really a big deal, or if the table hasn't been used yet and it's behind a feature flag. This would work well for small impacting feature.

But what if your table hold many records and is a sensitive part of your application? Well you may want to schedule a downtime of your application to avoid impacting the users. Although scheduling a downtime requires a lot planning and maybe you can't really do this if you have users all across the world.

A solution might be to simply use MySQL views. Let's see how we can use them. Views are very convenient as in MySQL you can still use them to INSERT, UPDATE, or DELETE the original table. Be careful though as not every view is updatable.

To illustrate this blog post I have created a code sample application that can be found here. For the sake of the example here, the application contains non REST APIs that are just here to facilitate creation and deletion of records, you can always use Tinker for a more convenient way to test things. In a better world, you might want to chose a CRUD approach with Laravel Resource RESTful routing or you might have GraphQL mutations and queries.

Here I have a few migration and seed to have some data:

php artisan migrate
php artisan db:seed

We can verify we have successfully ran them:

php artisan tinker     
Psy Shell v0.10.4 (PHP 7.4.3 — cli) by Justin Hileman
>>> \App\Models\Item::count();
=> 500
>>> \App\Models\Item::all()->first()
=> App\Models\Item {#5175
     id: "1",
     title: "quia",
     description: "Quasi nostrum rerum omnis ut. Assumenda aut et suscipit porro ea assumenda. Quis sed eum eos.",
     weight: "299",
     user_id: "6",
     created_at: "2020-10-18 19:25:15",
     updated_at: "2020-10-18 19:25:15",
   }

Here our objective will be to rename the Item table to Product. Each of the following section can be a shippable PR.

Creating the view

The first thing is to create a view. The best way to do it so it can be reused in the project if necessary is to create a command that will create the view. That way if the code for the view needs to change you won't have to change all the places where you are creating the view.

php artisan make:command CreateProductView

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CreateProductView extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:view:product';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'This will create the products view';

    public function handle()
    {
        DB::statement("
            CREATE VIEW products
            AS
            SELECT * FROM items
            ;
        ");
    }
}

Then we can actually create the migration that will be a simple proxy to call this Artisan command:

php artisan make:migration create_products_view

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Database\Migrations\Migration;

class CreateProductsView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Artisan::call('db:view:product');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('DROP VIEW products');
    }
}

Laravel Model

Now that we have a view, Laravel doesn't care about the actual nature of what is querying whether it's an actual table or a view. We can now create a model for product. Or more accurately we can just rename our model.

Tip: if the table is a central piece of the application and it is too much work to actually rename everything you can always make a new model for the view that will hold the logic and the old model just a proxy for the new model.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class Product extends Model
{
    use HasFactory;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'title',
        'description',
        'weight',
    ];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }
}

Now you can access everything as you did before:

php artisan tinker 
Psy Shell v0.10.4 (PHP 7.4.3 — cli) by Justin Hileman
>>> \App\Models\Product::all()->first()
=> App\Models\Product {#4674
     id: 1,
     title: "vel",
     description: "Sunt quod et nesciunt. Perferendis repellat id cumque ipsum. Reiciendis facilis tenetur iste aut esse eos qui voluptas.",
     weight: 432,
     user_id: 1,
     created_at: "2020-10-18 20:37:49",
     updated_at: "2020-10-18 20:37:49",
   }

Tip: before jumping on the next section make sure you have replaced every references to the old model across code base.

Deleting the view and renaming the table

Now that we have everything working on the code level, we just have to finalize the database side.

php artisan make:migration rename_items_to_products

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Database\Migrations\Migration;

class RenameItemsToProducts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement(<<<EOT
            RENAME TABLE
                `products` TO `tmp_products`, -- Rename view to a temp view
                `items` TO `products` -- rename original real table
        EOT
        );

        DB::statement('DROP VIEW tmp_products');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement('RENAME TABLE `products` TO `items`');
        Artisan::call('db:view:product');
    }
}

Doing so in a single statement allow MySQL to treat it in a single process, so from the application point of view everything is transparent and there is no locking.

Tip: if you had the alias model, now that we have rename the view, you can have an additional PR that will remove it.

Conclusion

During each steps or deployment not once we do break the API or the code as the deployable code is at all time accessing the data without any trouble and using the view allow us to work on the table. This is a very simple way to work our way through zero down time migration.