I'm building out a Shedquarters in my backyard! Check out the ever-evolving post + pictures here →

Using Laravel's Schema Builder Outside of Migrations

November 18, 2021

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);
}
}
Code highlighting powered by torchlight.dev (A service I created!)

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!

Thanks for reading! My name is Aaron and I'm currently working at small property tax firm in Texas, where I serve in dual roles as COO & CTO.

My main project focus is Hammerstone, where we build components for your Laravel and Rails applications. I currently do a podcast.

If you ever have any questions or want to chat, I'm always on Twitter
Copyright 2013 - 2021, Aaron Francis.