How to convert Laravel migrations to raw SQL scripts?
user2479930's code is great, but I was getting:
Class 'LocalItemsSchema.php' not found
I debugged the issue and fixed it with the following:
foreach($migrations as $migration) {
$migration_name = $migration;
$migration_name = str_replace('.php', '', $migration_name);
$migration = $migrator->resolve($migration_name);
$queries[] = [
'name' => $migration_name,
'queries' => array_column($db->pretend(function() use ($migration) { $migration->up(); }), 'query'),
];
}
Use the migrate command
You can add the --pretend
flag when you run php artisan migrate
to output the queries to the terminal:
php artisan migrate --pretend
This will look something like this:
Migration table created successfully.
CreateUsersTable: create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)
CreateUsersTable: create unique index users_email_unique on "users" ("email")
CreatePasswordResetsTable: create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)
CreatePasswordResetsTable: create index password_resets_email_index on "password_resets" ("email")
CreatePasswordResetsTable: create index password_resets_token_index on "password_resets" ("token")
To save this to a file, just redirect the output without ansi:
php artisan migrate --pretend --no-ansi > migrate.sql
This command only include the migrations that hasn't been migrated yet.
Hack the migrate command
To further customize how to get the queries, consider hacking the source and make your own custom command or something like that. To get you started, here is some quick code to get all the migrations.
Example code
$migrator = app('migrator');
$db = $migrator->resolveConnection(null);
$migrations = $migrator->getMigrationFiles('database/migrations');
$queries = [];
foreach($migrations as $migration) {
$migration_name = $migration;
$migration = $migrator->resolve($migration);
$queries[] = [
'name' => $migration_name,
'queries' => array_column($db->pretend(function() use ($migration) { $migration->up(); }), 'query'),
];
}
dd($queries);
Example output
array:2 [
0 => array:2 [
"name" => "2014_10_12_000000_create_users_table"
"queries" => array:2 [
0 => "create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)"
1 => "create unique index users_email_unique on "users" ("email")"
]
]
1 => array:2 [
"name" => "2014_10_12_100000_create_password_resets_table"
"queries" => array:3 [
0 => "create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)"
1 => "create index password_resets_email_index on "password_resets" ("email")"
2 => "create index password_resets_token_index on "password_resets" ("token")"
]
]
]
This code will include all the migrations. To see how to only get what isn't already migrated take a look at the
run()
method invendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php
.
Just in case you are facing the same problem as I did:
php artisan migrate --pretend
did not output anything, yet runs the SQLs without adding the record to migrations. In other words,
- it does the SQL job, which was not intended
- returned nothing, which was the reason I did the call and
- did not add the entry to migrations, which sort of destroys the situations as I was not able to re-run the migration without manually remove tables
The reason for it was my setup with several databases, which are addressed with
Schema::connection('master')->create('...
More on that issue you may find here: https://github.com/laravel/framework/issues/13431
Sadly, a Laravel developer closed the issue, quote "Closing since the issue seems to be a rare edge case that can be solved with a workaround.", so there is not much hope, it will be fixed anytime soon. For my maybe rare case, I'll use a third party SQL diff checker.
Cheers