How to Seed SQL dumps in Laravel

Laravel Sep 22, 2021

Importing SQL dumps into a Laravel database can be a crucial step in setting up your project. In this post, I will guide you through two different approaches to importing SQL dumps and share my recommendation for the best method.

1. Creating the Seeder

Assuming you have already set up your Laravel project, let us start by creating a new seeder called DumpSeeder. Simply execute the following command: php artisan make:seeder DumpSeeder. This will generate a new file in your database/seeders folder.

laravel_folder_structure_for_dumpseeder

When you open the newly created DumpSeeder, you will see the following code that Laravel already generated for us:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;

class DumpSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        //
    }
}

2. Creating the dump directory

Next, we need to create a directory to store our SQL dumps. I suggest creating a dumps directory within the database directory and placing your SQL dump file inside it, as shown in the example below.

laravel_folder_structure_for_our_sql_dumps

3. Modifying the seeder to seed SQL dumps

There are multiple ways to seed SQL dumps, but let us start with the method that has worked best for me.

3.1 First Method

In the DumpSeeder, place the following code in the run() function:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class DumpSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $file = database_path('dumps/codeonwards.sql');
        $sql = File::get($file);
        DB::connection()->getPdo()->exec($sql);
    }
}

In this function, we locate the SQL dump file on line 18, retrieve its contents on line 19, and finally seed the SQL dump into the database on line 20.

To test the DumpSeeder, execute the following command: php artisan db:seed --class=DumpSeeder.

If everything goes smoothly, you will see the message "Database seeding completed successfully."

If you encounter any issues, you can try the alternative code provided below. The only difference is in line 20, where we use the DB::unprepared() method to execute the SQL queries. All SQL queries are prepared in Laravel by default so running this unprepared statement might solve your error.

3.2 Second method

Replace the code inside the run function with the following code:

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class DumpSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $file = database_path('dumps/codeonwards_users.sql');
        $sql = File::get($file);
        DB::unprepared(file_get_contents($sql));
    }
}

Based on my experience, I have encountered more errors with the second method, which is why I recommend the first method.

4. Conclusion

After extensive research, I found the first method to be the best for importing SQL dumps in Laravel. While there are other options, they either involve more code or have a higher chance of errors. I did not include them in this post, but you can always try the second method if the first one does not do the trick.

Tags

Tijn van den Eijnde

Recommended for you