Laravel 9: how to insert data using raw sql while doing a migration?

Question

I've a "static" table I need to import while doing initial migrations on my project.

I've a .sql file. It's a lot of data, > 35k records

My idea was to create table in a standard migration and then, with "something" execute the insert sql I already have in the separate file.

Is it possible? How?

Answer 1

You can add this in another migration file.

use Illuminate\Support\Facades\DB;

public function up()
{
    // ini_set('memory_limit', '-1'); <-- you can add this in case the file is big
    DB::unprepared(file_get_contents('path/to/dump.sql'));
}

Answer 2

You're not limited to migration or migration related procedures when writing a migration. So yes you can run insert queries etc. Safer would be to separate table creation migration and data insert migration. But you may also do it in same migration too.

Schema::create('your_tables', function (Blueprint $table) {
         //table properties here
$table->id();
});

//then you can do inserts/creates etc. Make sure this table's model is defined !

YourTable::create(...);

//if you want to run SQL file:

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

Process is a symfony component. So we are running the SQL file directly from MySQL console. In other words, this won't go through Laravel ORM.

I suggest you use process otherwise it might fail with big and complex SQL files. In your case 35k might be big depending on row size.

Remember, it is safer to do table creation and data insertion in separate files. This is the mentality of migration, one step at a time so it's easier to rollback later on.