Laravel: Mass Inserts Using DB Query Builder

by Mike Classic on October 6, 2016

NOTE: THIS ARTICLE WAS WRITTEN FOR THE OLD SITE WHEN LARAVEL 4 WAS OUT, BUT STILL APPLIES TO LARAVEL 5 TODAY

When passing arrays for mass insert into the DB QueryBuilder, make sure each array entry has the same fields to populate, no extras. This may be intuitive for you, but it was not for me.

Symptoms

The problem manifested itself during database seeding. Here is a sample schema for the type of table I was building and seeding.

Schema::create('services', function(Blueprint $table)
{
    $table->primary('id');
    $table->boolean('auxiliary')->default(false);
    $table->decimal('rate');
});

I had a seeder class which had an array of entries to insert. This is basically an array of arrays, each nested array represents one table row. This is demonstrated below:

class ServicesTableSeeder extends Seeder {
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('services')->delete();

        $entries = [
            [
                'rate' => 50.00,
                'description' => 'Cheaper Service'
            ],
            [
                'rate' => 100.00,
                'description' => 'Some Service',
                'auxiliary' => true
            ],
            /* ... */
        ];

        DB::table('services')->insert($entries);
    }
}

As you can see, one row had more fields to populate than the previous. I had thought that the query builder would accomodate for this, but it doesn't. I'd run this seeder, and the table would simply not populate at all, with no errors thrown.

The solution I came up with was to run array inserts where the rows/entries all populate the same fields. So if you have rows that populate more/less/different fields, group them together in a separate array of entries, as such:

class ServicesTableSeeder extends Seeder {
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('services')->delete();

        $entries = [
            [
                'rate' => 50.00,
                'description' => 'Cheaper Service'
            ],
            [
                'rate' => 60.00,
                'description' => 'Medium Service'
            ]
        ];

        $entries2 = [
            [
                'rate' => 100.00,
                'description' => 'Some auxiliary service',
                'auxiliary' => true
            ],
            [
                'rate' => 90.00,
                'description' => 'Explicitly non-auxiliary service',
                'auxiliary' => false
            ]
        ];

        DB::table('services')->insert($entries);
        DB::table('services')->insert($entries2);
    }
}

Notice how the two arrays of entries have the same fields to populate.

Tags: Laravel PHP