Migrating to Big Integer

Basecamp’s outage in November 2018 made me realize that it might be a good idea to start using big integers instead of regular integers for unique identifiers in MySQL (auto-increments and relationship references).

Here is a migration to automatically change all your IDs from regular integer size to big integer:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class IntToBigintMigration extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $tables = \DB::connection()->getDoctrineSchemaManager()->listTableNames();

        $ignore = ['migrations']; // Tables to ignore.

        foreach ($tables as $table) {
            if (in_array($table, $ignore)) {
                continue;
            }

            Schema::table($table, function (Blueprint $blueprint) use ($table) {
            	$columns = \DB::getSchemaBuilder()->getColumnListing($table);

                foreach ($columns as $column) {
                    if ($column == 'id') {
                        $blueprint->bigIncrements($column)->change();
                    }

                    if (ends_with($column, '_id')) {
                        $blueprint->unsignedBigInteger($column)->change();
                    }
                }
            });
        }
    }
}

Don’t forget to add any table you wish to ignore in the $ignore array.

Last but not least, please try it on a development instance before you push it to your production server (sorry for stating the obvious).

I’m grateful for Basecamp’s team to be transparent about this outage; not only they helped younger companies avoid that issue, they also demonstrated the infinite amount of care they put into serving their customers well.

What a company to look up to.

Published by Ben Villiere

Software Developer since 2007. I enjoy solving complex problems with efficient code. My mission is to help people improve their professional life by automating mundane tasks. This blog is my technical journal where I write about my work.

Follow me on: | |