How to convert Laravel migrations to raw SQL scripts?

Question

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.

Your customer’s database admin is a nugget. Can you not go around him and just run the Artisan CLI on the server via SSH? — Martin Bean, Jul 07 '15 at 08:44
Yeah, that's what I'd like, too. But "the customer is always right" etc. And I'm not even sure if they have PHP on the machine running their production database. Or maybe the DB admin is a control freak and wants to see raw SQL and kick our ass if he doesn't like something. — JustAMartin, Jul 07 '15 at 10:14
@Martin the customer is always a nugget, and is sometimes right. — Amelia, Jul 07 '15 at 12:09

Jocke Med Kniven · Accepted Answer · 2023-05-24T09:18:55.177

Update 2023-05-24 / Laravel 10

Lately I've been using this one-liner to get a list of all migrations as queries:

php artisan tinker --no-ansi --execute 'echo implode(PHP_EOL, array_reduce(glob("database/migrations/*.php"), fn($c, $i) => [...$c, ...array_column(app("db")->pretend(fn() => (include $i)->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 in vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php.

Thank you, your code will be really useful. I'll mark this as answer after a while, in case someone else has anything else to offer. — JustAMartin, Jul 07 '15 at 14:27
The example code is useful! Note: if your migrations contain any renameColumn, it will fail as it doesn't find the column with the old name. (L5.2) — gramgram, Jun 18 '17 at 18:40
I'm wondering how do you add the --pretend in light gray? I used the code markup in my answer, but I did not find the markup for colors or such? — andi79h, Feb 15 '18 at 15:08
The "--pretend" option does not exist in laravel 7.x or greater — Marcelo Fonseca, Nov 09 '20 at 17:27
What should I do if I already did all migrations? `artisan migrate --pretend` returns "Nothing to migrate.". I don't want to lose data rolling back migrations. — Sithell, May 02 '21 at 21:06
@jocke-med-kniven the added one-liner dies with error: Error Call to a member function up() on int. — Defkon1, May 31 '23 at 08:44

score 5 · Answer 2 · answered Feb 15 '18 at 15:05

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

score 1 · Answer 3 · answered Jun 15 '19 at 20:51

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'),
        ];
    }

score 0 · Answer 4 · answered Jan 20 '20 at 08:20

I'm using Laravel 6.X. For me, @user2479930's answer didn't work. I needed to read through Migrator's source code and had to add: $migrator->requireFiles($migrations); for it to work.

$migrator = app('migrator');
$db = $migrator->resolveConnection(null);
$migrations = $migrator->getMigrationFiles('database/migrations');
$migrator->requireFiles($migrations);
$queries = [];

foreach ($migrations as $migration) {
    $migration_name = $migration;
    $migration = $migrator->resolve($migrator->getMigrationName($migration_name));

    $queries[] = [
        'name' => $migration_name,
        'queries' => array_column($db->pretend(function () use ($migration) {
            $migration->up();
        }), 'query'),
    ];
}
dd($queries);

score 0 · Answer 5 · answered Nov 13 '20 at 15:05

I had to do it after --pretend, change this :

CreateTablenameTable: create table `tablename` (`id` bigint unsigned not null auto_increment primary key, `code` varchar(255) not null, `valeur` varchar(255) not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB
CreateTablenameTable: alter table `tablename` add unique `tablename_code_unique`(`code`)

To this :

create table tablename 
(
id bigint unsigned not null auto_increment primary key, 
code varchar(255) not null, 
valeur varchar(255) not null
) default character set utf8mb4 collate 'utf8mb4_unicode_ci' engine = InnoDB;
alter table tablename add unique tablename_code_unique(code);

How to convert Laravel migrations to raw SQL scripts?

5 Answers5

Update 2023-05-24 / Laravel 10

Use the migrate command

Hack the migrate command

Example code

Example output

Linked

Related