Using Laravel's schema builder outside of migrations

Laravel's schema build is typically only used for migrations, but it can be used anywhere in your application!

I don't do it very often, but there are a few cases in our application where we need to process tens or hundreds of thousands of records in one process. We could pull everything into PHP and do the processing there, but it usually makes more sense to delegate that hard work to the database.

Using Laravel's Schema class, you can quickly create a temporary table using all the methods you're used to. You can add columns, indexes, anything you want.

In our application we have one table that has something like 30 million rows. It holds property data across many counties in Texas. There's one place where we need to find the unique property ID (PID) based on a secondary value ( the lookup).

The lookup could be one of three different columns, so we don't want to maintain expensive, infrequently used indexes across 3 columns * 30 million rows.

Instead, we create a tiny temporary table and index the one column we're using!

use App\County;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

class FindPropertyIds extends Command
{
    public function handle()
    {
        // ...
    }
    
    public function populateLookupTable(County $county)
    {
        // Specific lookup columns per county.
        $column = Arr::get([
            'DAL' => 'parcel_number_alt1',
            'FOR' => 'parcel_number_alt1',
            'KAU' => 'parcel_number_alt3',
            'TAR' => 'parcel_number_alt2',
            'TRV' => 'parcel_number_alt2',
        ], $county->abbreviation);
    
        // Drop the temp table if it already exists.
        Schema::dropIfExists('tmp_lookup');
    
        Schema::create('tmp_lookup', function (Blueprint $table) {
            // Table only lasts as long as the connection persists.
            $table->temporary();
            // The lookup column, with an index.
            $table->string('lookup')->index();
            // The Property ID we're looking for.
            $table->string('pid');
        });
        
        // Insert into the small, fast table from our
        // massive, slower table. 
        $populate = <<<SQL
    INSERT into tmp_lookup 
        SELECT pid, {$column} as lookup FROM properties
        WHERE {$column} IS NOT NULL 
            AND is_latest = 1 
            AND county_id = {$county->id}
    SQL;
    
        DB::statement($populate);
    }
}

It's not a commonly used technique, but it is a powerful tool to reach for when you need it!

There are some good comments over on Twitter that highlight a few more use cases!

Aaron Francis @aarondfrancis

I don't do it very often, but you can use Laravel's Schema builder anywhere you want, not just in migrations!

If you ever need to make a temp table to help you mass process data more quickly, it's a great solution. pic.twitter.com/I7eAHbgFoo