CODEWITHSUNDEEP
laravel-4
Andrew Koper
Andrew Koper

Reputation: 7191

Use Laravel seed and sql files to populate database

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?

Upvotes: 40

Views: 48193

Answers (6)

Chika Nwazuo Evuka
Chika Nwazuo Evuka

Reputation: 41

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");
    }
}

Upvotes: 0

emotality
emotality

Reputation: 13025

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.

Upvotes: 8

Arda
Arda

Reputation: 6926

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

Upvotes: 15

Mazzy
Mazzy

Reputation: 1944

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();

Upvotes: 4

Robby Alvian Jaya Mulia
Robby Alvian Jaya Mulia

Reputation: 1397

@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

Upvotes: 2

Andrew Koper
Andrew Koper

Reputation: 7191

  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!');
    }
}

Upvotes: 102

Related Questions