Migrate large amounts of data in Laravel with Artisan Console and Chunk

Want to learn something new? Check out my most recent Egghead course:



Build a Twelve-Factor Node.js App with Docker - WATCH NOW on Egghead.io!

Cheers -Mark

Wed, 10/25/2017 - 13:35

Submitted by markoshust Wed, 10/25/2017 - 13:35

I've recently had to create a script to migrate a large amount of data post-deployment. This presented a couple issues; one being that the script needed to be performant, another being that since it took at least a few minutes to run on a couple hundred thousand rows, I needed to display the status of the script for devops so it didn't appear to be hungup or failed.

I resolved on using a combination of chunk and each, and passing through the total number of iterable records by reference. This way the migration script didn't run out of memory, as queries were chunked down to 100 rows at a time. It also allows the use of a simple helper function to output the status to the console.

Without further ado, here you go!

namespace App\Console\Commands;

use App\Foo;
use App\Bar;
use Illuminate\Console\Command;

class MigrateFoo extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'migrate:foo';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Migrate the foo table data to the new format';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $query = Foo::where('bar_id', '!=', 0);
        $queryCount = $query->count();
        $i = 0;

        $query->chunk(100, function ($foos) use ($queryCount, &$i) {
            $foos->each(function ($foo) {
                $query = Bar::where('baz_id', $foo->baz_id)
                    ->where('qux_id', $foo->qux_id);

                if ($bar = $query->get()->first()) {
                    $foo->bar_id = $bar->id;
                    $foo->save();
                }
            });

            $i += $foos->count();
            $this->showStatus($i, $queryCount);
        });

        $this->showStatus($numAnswers, $queryCount, true);
    }

    /**
     * Console output of status every progression of XX records.
     *
     * @param int $i
     * @param int $total
     * @param bool $force
     */
    public function showStatus(int $i, int $total, bool $force)
    {
        $outputEvery = 5000;

        if ($i % outputEvery === 0 || $force) {
            $percentage = $total > 0
                ? round(($i / $total) * 100)
                : 0;

            echo "Foo table $i of $total records updated ($percentage% Complete)" . PHP_EOL;
        }
    }
}

Comments

Nice code,  Thanks for sharing. Did you know you can use a progress bar to display the status.. looks much better :) public function handle(){ $query = Foo::query(); $queryCount = $query->count(); $i = 0; $this->comment('Updating ' . $queryCount . ' Foos'); $bar = $this->output->createProgressBar($queryCount); $bar->setFormat('%current%/%max% [%bar%] %percent:3s%% | Remaining: %remaining% | Memory Usage: %memory:6s% | %message%'); $bar->display(); $query->chunk(100, function ($foos) use ($queryCount, &$i, $bar) { $foos->each(function (Foo $foo) use ($bar, $i) { $bar->advance(); $bar->setMessage('Current Foo ' . $foo->id); sleep(1); }); $i += $foos->count(); }); $bar->finish();}

Add new comment