Use Laravel seed and sql files to populate database

Question

I got several .sql files of countries, states and cities of the world from github. How can I run them with Laravel's seed files to populate those tables in my database?

Ruben

Answer 1

1. Add DB::unprepared() to the run method of DatabaseSeeder.

2. Run php artisan db:seed at the command line.

   class DatabaseSeeder extends Seeder {
   
       public function run()
       {
           Eloquent::unguard();
   
           $this->call('UserTableSeeder');
           $this->command->info('User table seeded!');
   
           $path = 'app/developer_docs/countries.sql';
           DB::unprepared(file_get_contents($path));
           $this->command->info('Country table seeded!');
       }
   }
   

Ruben

Another thing you can use is this converter: laravel.stonelab.ch/sql-seeder-converter - though it didn't handle well large SQL files. You can also export and use your IDE's search and replace to prepare it for you. Otherwise, follow what @AndrewKoper suggested. – Guadalupe

phpMyAdmin add some stuff to sql file like SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET AUTOCOMMIT = 0; START TRANSACTION; SET time_zone = "+00:00"; - get rid of it if doesn't work – Stylo

This solution does not work with more complex .SQL files and your migration will exit without errorhaving only half of the file completed. A better solution is to use MySQL cli command – Periotic

In my case, this solution only works if you leave the insert statement alone. You should get rid of all the code that has been dumped other than INSERT statement. It doesn't work if you try to run mysql dump as it is. – Muezzin

Answer 2

I found a package that creates seed files from database tables and rows. It currently supports Laravel 4 through 10:

https://github.com/orangehill/iseed

In the end, it's basically as easy as this:

php artisan iseed my_table

or for multiple occasions:

php artisan iseed my_table,another_table

Mettah

Answer 3

2022 Simplified answer from Andrew Koper :

class WhateverSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $file_path = resource_path('sql/whatever.sql');

        \DB::unprepared(
            file_get_contents($file_path)
        );
    }
}

Fun fact: 60,000 rows took me 50s to import from JSON file where this took only 400ms.

Proficient

Answer 4

As used by other answers, DB::unprepared does not work with more complex SQL files.

Another better solution would be to use the MySQL cli directly inside a process:

$process = new Process([
            'mysql',
            '-h',
            DB::getConfig('host'),
            '-u',
            DB::getConfig('username'),
            '-p' . DB::getConfig('password'),
            DB::getConfig('database'),
            '-e',
            "source path/to/schema.sql"
        ]);
        $process->mustRun();

Periotic

Answer 5

@Andre Koper solutions is understandable, but sadly it doesn't work for me. This one is a bit confusing but atleast works for me.

So instead of using DB::unprepared, I use this:

// DatabaseSeeder.php
class DatabaseSeeder extends Seeder {

    public function run()
    {
       // Set the path of your .sql file
       $sql = storage_path('a_id_territory.sql');

       // You must change this one, its depend on your mysql bin.
       $db_bin = "C:\wamp64\bin\mariadb\mariadb10.3.14\bin";

       // PDO Credentials
       $db = [
           'username' => env('DB_USERNAME'),
           'password' => env('DB_PASSWORD'),
           'host' => env('DB_HOST'),
           'database' => env('DB_DATABASE')
       ];
       exec("{$db_bin}\mysql --user={$db['username']} --password={$db['password']} --host={$db['host']} --database {$db['database']} < $sql");
    }
}

Then while migrating database just add --seed

php artisan migrate:refresh --seed

or

php artisan migrate:fresh --seed

Tested on Laravel 7.0.x

Deletion

Answer 6

If you have a raw SQL file and you want to automatically insert them into the database, create a folder named SQL in your laravel database directory, and include the SQL file,Eg: database/sql/sqlifile.sql, create a table using PHP artisan and include the file, below is an example of it.

 public function up(): void
{


    $path=database_path('sql/sqlfile.sql');
    echo "Looking for SQL file at: $path\n";

    if (File::exists($path)) {
        DB::unprepared(File::get($path));
    } else {
        throw new \Exception("SQL file not found: $path");
    }
}

Duumvir