'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?
Solution 1:[1]
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.
Solution 2:[2]
Laravel can't do that, but I think this will help: Laravel migration generator
It generate migrations based on existing tables.
Solution 3:[3]
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.
Solution 4:[4]
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()
{
}
}
Solution 5:[5]
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);
Solution 6:[6]
another solution work for me in Laravel 5.2:
DB::unprepared(File::get('full/path/to/dump.sql'));
Solution 7:[7]
Simple solution provided by Laravel Article for generating migration file from an existing database table.
Try: https://laravelarticle.com/laravel-migration-generator-online
Solution 8:[8]
You can create laravel migration and models directly from database using https://github.com/XCMer/larry-four-generator
Execute the following code after installing the package
php artisan larry:fromdb
Solution 9:[9]
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/
Solution 10:[10]
You can use Raahul/Larryfour Package, A model and migration generator for Laravel 4
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
Solution 11:[11]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow