Reputation: 553

Can I import a mysql dump to a laravel migration?

I have a complete database and need to create migration. I guess there must be a way to do it from a dump but not sure. Is there any way automatically or at least easier to do this task?

Upvotes: 52

Answers (14)

Lintank Wgbn

Reputation: 1

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Schema\MySqlSchemaState;
use Illuminate\Support\Facades\Schema;

return new class extends Migration 
{
    /**
     * Run the migrations.
     */
    public function up(): void 
    {
         $path = database_path('schema/wp_users.sql');
         $connection = Schema::getConnection();
         $mysqlstate = new MySqlSchemaState($connection);
         $mysqlstate->load($path);

         // Schema::create('wp_users', function (Blueprint $table) {
         //     $table->id();
         //     $table->timestamps();
         // });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
         Schema::dropIfExists('wp_users');
    }
};

Upvotes: 0

user26507586

Reputation: 1

I think the best way to solve this is to use mysql to import the file to avoid running out of memory.

$host = env('DB_HOST');
    $user = env('DB_USERNAME');
    $password = env('DB_PASSWORD');
    $database = env('DB_DATABASE');
    $fpath = Storage::path('database_dumps/dump.sql');
    $command = "mysql -h$host -u$user -p$password $database < $fpath";
    info("running: $command");
    $process = Process::run($command);
    if ($process->failed()) {
        Log::error($process->errorOutput());
    } else {
        Log::info($process->output());
    }

Upvotes: 0

JoBar

Reputation: 188

If you can dump to a CSV: An alternative for some generic data tables (Countries, States, Postal Codes), not via migrations but via seeders. Although you could do it the same way in a migration file.

In your seeder file:

    public function run()
    {
        $this->insertFromCsvFile('countries', 'path/to/countries.csv');
        $this->insertFromCsvFile('states', 'path/to/states.csv');
        $this->insertFromCsvFile('postal_codes', 'path/to/postal_codes.csv');
    }

    private function insertFromCsvFile($tableName, $filePath)
    {
        if( !file_exists($filePath) ){
            echo 'File Not Found: '.$filePath."\r\n";
            return;
        }
        $headers = $rows = [];
        $file = fopen( $filePath, 'r' );
        while( ( $line = fgetcsv( $file ) ) !== false ){

            // The first row should be header values that match column names.
            if( empty( $headers ) ){
                $headers = explode( ',', implode( ',', $line ) );
                continue;
            }

            $row = array_combine( $headers, $line );
            foreach( $row as &$val ) if( $val === 'NULL' ) $val = null;
            $rows[] = $row;

            // Adjust based on memory constraints.
            if( count($rows) === 500 ){
                DB::table( $tableName )->insert($rows);
                $rows = [];
            }
        }
        fclose( $filePath );

        if( count($rows) ) DB::table( $tableName )->insert($rows);
    }

Run the seeder: php artisan db:seed --class=GenericTableSeeder

Upvotes: 4

Pragnesh Chauhan

Reputation: 8476

I am writing my answer as this might help to someone who is using new laravel 8.

In laravel 8, dumping SQL and run migration using SQL(.dump) file is possible. Please refer below link for more detail.

https://laravel.com/docs/8.x/migrations#squashing-migrations

php artisan schema:dump

// Dump the current database schema and prune all existing migrations...
php artisan schema:dump --prune

schema:dump will create new directory under database > schema and SQL dump will stored there. After that when you try to migrate first it will run dump file from schema and then any pending migration.

Upvotes: 10

zmonteca

Reputation: 2354

Another alternative is using the PDO directly:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

$sql_dump = File::get('/path/to/file.sql');
DB::connection()->getPdo()->exec($sql_dump);

Upvotes: 13

Akash khan

Reputation: 979

Simple solution provided by Laravel Article for generating migration file from an existing database table.

Try: https://laravelarticle.com/laravel-migration-generator-online

Upvotes: 2

JohnTaa

Reputation: 2824

You can use Raahul/Larryfour Package, A model and migration generator for Laravel 4

Raahul/Larryfour Package

After insallation you can use a command line to create a migration from existed database like this:

php artisan raahul:fromdb --only yourdatabase

And you will find the migration in app/migrations/ folder

Upvotes: 1

Choirul MA

Reputation: 61

another solution work for me in Laravel 5.2:

DB::unprepared(File::get('full/path/to/dump.sql'));

Upvotes: 6

mastercheef85

Reputation: 2269

i recently standing in front of the same problem. i didn't want to install a package specially for that, so i decided to write a little tool to help me and others ;)

Here is the link: http://laravel.stonelab.ch/sql-seeder-converter/

And here you can comment it, if you have any improvement proposals or questions: http://www.stonelab.ch/en/sql-to-laravel-seeder-converter/

Upvotes: 2

shock_gone_wild

Reputation: 6740

This question is answered already, but recently in a project, the provided answers did not satisfy my needs any longer. It also does not import a whole database dump, but one (large) table. I felt I should share that with you.

The problem was, I wanted to import a quite large table (list of zipcodes) during my artisan:migrate operation. The solution with DB::unprepared($dump) took way to long and I found an alternative which is MUCH faster.

Just export your table as CSV and use the following Code in your migration's up() function.

    // i had to str_replace the backslash on windows dev system... but works on linux, too
    $filename = str_replace("\\", "/", storage_path('path/in/storage/to/your/file.csv'));

    $query = "LOAD DATA LOCAL INFILE '".$filename."' INTO TABLE yourtable
        FIELDS TERMINATED BY '\t'
        ENCLOSED BY ''
        LINES TERMINATED BY '\n'
        IGNORE 0 LINES
        (col1,col2,...);";

    DB::unprepared($query);

Just update the query as you need. And of course, you should make sure, that the table with the cols 'col1', 'col2' etc... exists. I created it just before the importing of the file. with Schema::create()...

If you run into following error message:

PDO::exec(): LOAD DATA LOCAL INFILE forbidden

There is a way you can get rid of this message: Although it's not really documented you can just add an 'options' key to your config/database.php file. For example mine looks like that:

        'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
        'options'   => array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
        )

Note: i'm currently using laravel 5 but it should work with laravel 4, too.

Upvotes: 14

ceejayoz

Reputation: 180024

I have a complete database and need to create migration. I guess there must be a way to do it from a dump but not sure. Is there any way automatically or at least easier to do this task?

Not automatically, but we run dumps in a migration using DB::unprepared(). You could use file_get_contents to import from a .sql file and thus not have to worry about escaping the entire dump's " marks...

<?php

use Illuminate\Database\Migrations\Migration;

class ImportDump extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::unprepared("YOUR SQL DUMP HERE");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {

    }

}

Upvotes: 12

Tomas Buteler

Reputation: 4117

You can import dumps in Laravel like this:

DB::unprepared(file_get_contents('full/path/to/dump.sql'));

If I were to refactor an existing app, though, I'd take the time to write migrations from scratch, import the dump into different tables (or a different db, if table names are the same) then import the content to the new structure via seeds.

Upvotes: 71