如何将 Laravel 迁移转换为原始 SQL 脚本？

How to convert Laravel migrations to raw SQL scripts?

提问by JustAMartin

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.

我团队的开发人员真的习惯了 Laravel 迁移的强大功能，他们在本地机器和我们的开发服务器上工作得很好。但是客户的数据库管理员不会接受 Laravel 迁移。他要求为我们应用程序的每个新版本提供原始 SQL 脚本。

Is there any tool or programming technique to capture the output from Laravel migrations to up/down SQL scripts?

是否有任何工具或编程技术可以捕获从 Laravel 迁移到上/下 SQL 脚本的输出？

It would be perfect if we could integrate SQL script generation in our CI system (TeamCity) when creating production builds.

如果我们可以在创建生产构建时将 SQL 脚本生成集成到我们的 CI 系统（TeamCity）中，那就太完美了。

By the way, we will be using Laravel 5 and PostgreSQL for this project.

顺便说一下，我们将在这个项目中使用 Laravel 5 和 PostgreSQL。

回答by user2479930

Use the migrate command

使用迁移命令

You can add the --pretendflag when you run php artisan migrateto output the queries to the terminal:

您可以--pretend在运行时添加标志php artisan migrate以将查询输出到终端：

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:

要将其保存到文件中，只需在没有 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 allthe 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.

此代码将包括所有迁移。要了解如何唯一得到的是不是已经迁移走看看run()在方法vendor/laravel/framework/src/Illuminate/Database/Migrations/Migrator.php。

回答by andi79h

Just in case you are facing the same problem as I did:

以防万一你遇到和我一样的问题：

php artisan migrate --pretend

php artisan migrate --pretend

did not output anything, yet runs the SQLs without adding the record to migrations. In other words,

没有输出任何内容，但运行 SQL 而不将记录添加到迁移。换句话说，

• 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

• 它执行 SQL 工作，这是不打算的
• 什么也没回，这就是我打电话的原因
• 没有将条目添加到迁移，这会破坏情况，因为我无法在不手动删除表的情况下重新运行迁移

The reason for it was my setup with several databases, which are addressed with

原因是我设置了几个数据库，这些数据库是用

Schema::connection('master')->create('...

Schema::connection('master')->create('...

More on that issue you may find here: https://github.com/laravel/framework/issues/13431

您可以在此处找到有关该问题的更多信息：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.

可悲的是，Laravel 开发人员关闭了该问题，引用“关闭，因为该问题似乎是一个可以通过变通方法解决的罕见边缘情况。”，所以希望不大，很快就会修复。对于我可能很少见的情况，我将使用第三方 SQL 差异检查器。

Cheers

干杯

回答by Eugene Gekhter

user2479930's code is great, but I was getting:

user2479930 的代码很棒，但我得到了：

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

回答by Daniel Cheung

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.

我正在使用 Laravel 6.X。对我来说，@ user2479930 的回答没有用。我需要通读 Migrator 的源代码并添加：$migrator->requireFiles($migrations);让它工作。

$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);