Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert Laravel migrations to raw SQL scripts?

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.

like image 210
JustAMartin Avatar asked Jul 07 '15 08:07

JustAMartin


People also ask

What is php artisan migrate rollback?

By default, php artisan migrate:rollback will rollback all of your database migrations. By specifying --step=1 , you're saying that you only want to rollback the latest database migration. Plus, if you change the number, e.g. into --step=2 , you're telling Laravel to only rollback the last two migrations.

What is migration squashing in Laravel?

Migration SquashingLaravel will write the new schema file to database/schema . Then when you run your migrations, Laravel will run the SQL from the schema file first before moving on to anything created later in the migrations folder. Note: Migration squashing is currently only supported for MySQL and Postgres.


1 Answers

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 in vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php.

like image 190
user2479930 Avatar answered Sep 17 '22 15:09

user2479930