Developers of my team are really used to the power of Laravel migrations, they are working great on local machines and our dev servers. But customer's database admin will not accept Laravel migrations. He asks for raw SQL scripts for each new version of our application.
Is there any tool or programming technique to capture the output from Laravel migrations to up/down SQL scripts?
It would be perfect if we could integrate SQL script generation in our CI system (TeamCity) when creating production builds.
By the way, we will be using Laravel 5 and PostgreSQL for this project.
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.
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.
$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);
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
.