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?

gre_gor · Accepted Answer · 2017-10-29 19:38:33Z

102

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

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

edited Oct 29, 2017 at 19:38

gre_gor

6,65712 gold badges104 silver badges105 bronze badges

answered May 23, 2016 at 2:54

Andrew Koper

7,3418 gold badges45 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

giovannipds Over a year ago

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.

Kamil Kiełczewski Over a year ago

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

Mazzy Over a year ago

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

Mycodingproject Over a year ago

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.

Arda · Accepted Answer · 2023-04-20 09:47:49Z

15

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

edited Apr 20, 2023 at 9:47

answered Apr 4, 2017 at 14:36

Arda

6,9633 gold badges53 silver badges70 bronze badges

Comments

emotality · Accepted Answer · 2023-07-06 12:31:37Z

8

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.

edited Jul 6, 2023 at 12:31

answered May 20, 2022 at 18:01

emotality

13.1k4 gold badges43 silver badges60 bronze badges

Comments

raveren · Accepted Answer · 2021-04-20 14:37:48Z

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

edited Apr 20, 2021 at 14:37

raveren

18.8k12 gold badges74 silver badges89 bronze badges

answered Jul 3, 2020 at 13:33

Mazzy

1,9642 gold badges18 silver badges36 bronze badges

1 Comment

Zini Over a year ago

The parameter "-e" is wrong used in the example, it expect a SQL command and in this example it will try to run "source path/to/schema.sql" what is not a valid SQL command and will result in a SQL error ERROR 1064 (42000).

Robby Alvian Jaya Mulia · Accepted Answer · 2020-05-19 02:02:39Z

@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

Chika Nwazuo Evuka · Accepted Answer · 2024-05-26 15:34:33Z

0

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

answered May 26, 2024 at 15:34

Chika Nwazuo Evuka

512 bronze badges

Collectives™ on Stack Overflow

Use Laravel seed and sql files to populate database

6 Answers 6

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related