Laravel seeding via SQL file

Question

I'm trying to read data from a .sql file in a seeder to fill 3-4 tables with some data and DatabaseSeeder.php looks like this

public function run() {
    $this->call([
        UsersTableSeeder::class,
        // Bunch of seeders using Eloquent
        SqlSeeder::class
    ]);
}

All other seeders execute and, actually, when trying to throw an exception in SqlSeeder.php I'm able to stop the seeding. However, SqlSeeder.php won't seed the database via php artisan migrate:fresh --seed, seems like it's bypassed. I always need to run php artisan db:seed --class SqlSeeder after, in order to make it seed the database. SqlSeeder.php looks like this

public function run() {
    $path = base_path().'/database/seeds/sql/data.sql';
    $sql = file_get_contents($path);
    DB::unprepared($sql);
}

Why's that?

check if all tables that seed from sql are empty after `php artisan migrate:fresh --seed` — Andriy Lozynskiy, Mar 26 '18 at 20:14
Solution: the .sql file was encapsulated into a transaction. Oddly enough, transactions and DB::unprepared() do not get along when calling php artisan migrate:refresh --seed but works when calling --class — Alain D'Ettorre, Mar 27 '18 at 11:19

score 1 · Accepted Answer · answered Mar 27 '18 at 12:45

I solved my own issue by removing transactions from the .sql file I was trying to execute via DB::unprepared(). Oddly enough, transactions completely fail when executing php artisan migrate:refresh --seed, but they work if I later call the SqlSeeder individually via php artisan db:seed --class SqlSeeder. There are no foreign key constraints for now and InnoDB was chosen as engine, just to be sure, but still transactions both fail and work depending on the command.

I guess it all depends on how Illuminate\Database\Seeder::call works and calls seeder classes internally, but I'm not sure.

Andriy Lozynskiy · Answer 2 · 2018-03-26T20:43:22.470

0

Check if seeding data in sql file is in right order.

For example if you have foreign key category_id that references to categories.id posts table will be empty without any errors when you use this sql file:

INSERT INTO posts (title, category_id) VALUES ('test', 1);
INSERT INTO categories (title) VALUES ('category');

You should seed categories first and only then posts.

edited Mar 26 '18 at 20:43

answered Mar 26 '18 at 20:36

Andriy Lozynskiy

2,444
2
17
35

I know about order of seeding and in fact I took care of that. After countless other tries, I've found out DB::unprepared() as well as DB::statement() cannot stand *transactions* when called from within DatabaseSeeder.php, but worked just fine, with transactions, if called via db:seed --class SqlSeeder.php. Don't know why, but it worked by removing the INSERT INTO statements from a transaction! – Alain D'Ettorre Mar 27 '18 at 11:18

score 0 · Answer 3 · answered Apr 25 '20 at 17:06

0

For Laravel 5 all you have to do inside of your main seed file is:

\DB::unprepared(\File::get(base_path('path/to/your/sql/file.sql')));

answered Apr 25 '20 at 17:06

Armin

2,021
2
19
17

Laravel seeding via SQL file

3 Answers3