Laravel 5.5 Consolidate migrations w/ production database

Question

Hopefully, I can explain this well.

I have a Laravel application that has been in production for a minute. So, I have a bunch of migration files with a lot of changes. I would like to consolidate these migration files without losing the database.

The way I think this would work:

1. Get all production tables migrated to the desired state.
2. Consolidate all the migration files into the minimum number of files needed.
3. Clear the migrations table.
4. Either run migrations or populate the migrations table.

Part of why I would like to do this is because I would like to make some of the service providers public with the cleanest migration set possible.

The difficult version might be to:

1. Backup or duplicate tables.
2. Run migrations.
3. Write and run script to populate the "clean" tables.

Just hoping there's an easier way than that.

Edit (from comments): I have a production database that has about 50+ migration files - some minor changes, some large changes. If I consolidated, the number of migrations needed would be about 12 or so. I would like to consolidate the migration files, but still be able to perform migrate:rollback on production - not that I would.

Answer 1

I found this question, while looking for the information on combining (laravel calls it squashing) migrations. I figured I'd drop an answer, for anyone looking for this information in the future.

Laravel 8.0 allows this now:

./artisan schema:dump while use your database cli client to export a database schema dump into the database/schema folder of laravel.

future calls to ./artisan migrate will execute the schema file first if no migrations have been run on the target database, then it will pick up and run all migrations after the last one that was stored in the schema dump file.

You can read more here Laravel 8.0 Squash Database Migrations

Answer 2

You could use the library "xethron/migrations-generator" . Here's the repo.

After installation, basic usage: php artisan migrate:generate

Some Discussions about this are found at laracast.

Answer 3

After a couple of over-engineered and overly clever solution attempts, I think the following is a workable solution to the problem.

tl;dr:

• Bookend migrations on either side of migration(s) that build the schema from nothing.
• Update project.
• Migrate.
• Delete bookends and all previous migrations.
• Delete records from migrations table.

The first bookend renames the affected tables. The second bookend copies the data from the renamed tables to the fresh tables, then deletes the renamed tables.

Note: You can do whatever you like inside the bookends, this is just a minimum.

So, let's say you something like the following for migrations:

• 2017_09_05_000000_create_some_table.php
• 2017_09_05_000001_add_field_x_to_some_table.php
• 2017_09_05_000002_add_field_y_to_some_table.php
• 2017_09_05_000003_add_field_z_to_some_table.php

We would create another migration:

• 2017_09_05_000004_pre_refresh.php

We would create another migration based on the knowledge we have now:

• 2017_09_05_000005_create_some_table.php

We would create the last bookend, where data migration will occur:

• 2017_09_05_000006_post_refresh.php

The first four migrations will not be run because they already have been.

/** 2017_09_05_000004_pre_refresh.php */
class PreRefresh extends Migration
{
    public function up()
    {
        $prefix = 'zz_';
        $tablesToRename = [
            'foos',
            'bars'
        ];

        foreach($tablesToRename as $table) {
            Schema::rename($table, $prefix . $table);
        }
    }
}

No need for a down, because this is a one shot deal. This will run first, which should result in all the tables listed in the array being renamed. Then the consolidated (optimized) migration(s) will run.

/** 2017_09_05_000006_post_refresh.php */
class PostRefresh extends Migration
{
    public function up()
    {
        // Do what you need to do.
        // If you cannot use your models, just use DB::table() commands.

        $foos = DB::table('zz_foos')->get();
        foreach ($foos as $foo) {
            DB::table('foo')->insert([
                    'id'         => $foo->id,
                    'created_at' => $foo->created_at,
                    'updated_at' => $foo->updated_at
                ]);
        }

        $bars = DB::table('zz_bars')->get();
        foreach ($bars as $bar) {
            DB::table('bar')->insert([
                    'id'         => $bar->id,
                    'created_at' => $bar->created_at,
                    'updated_at' => $bar->updated_at,
                    'foo_id'     => $bar->foo_id
                ]);
        }

        // Tear down.
        $prefix = 'zz_';
        $tablesToRename = [
            'foo',
            'bar'
        ];

        foreach ($tablesToRename as $table) {
            DB::statement('SET FOREIGN_KEY_CHECKS=0');
            Schema::dropIfExists($prefix . $table);
            DB::statement('SET FOREIGN_KEY_CHECKS=1');
        }
    }
}

After running this, you can delete all your migrations from the pre_refresh and prior. As well as the post_refresh. Then you can head into the migrations table and delete the entries for those migrations.

Deleting the entries isn't entirely necessary, but if you migrate:rollback you will get error messages stating that the migration can't be found.

Caveats

1. If the architecture isn't modular by design, it can be quite cumbersome. However, if you have separated your code into services it does appear to be a little easier.
2. Laravel error handling and messages during migrations are very limited; so, debugging could be difficult.
3. Highly recommend starting with the most stable tables in your app/service. Further, starting with those that are foundational to your app might also prove beneficial.

Note: When I actually do this in production, not just my local (over and over again), and if there is not a better answer, then I will accept this.

Considerations

If you are breaking your application into service providers with discreet migrations, then you can comment out the service provider in /config/app when you run the migrations. This way you create a batch for the now baselined service. So, let's say you have the following migrations where each letter represents a migration, and each duplicate letter represents the same service:

• A
• B
• C
• A
• C
• B
• A

After consolidating service A:

• B
• C
• C
• B
• A

After consolidating B:

• C
• C
• A
• B

After consolidating C:

• A
• B
• C

update

54 migrations down to 27 so far. I even pulled out some Schema changes from large up() and down() methods and make them separate migrations. The nice side-effect here is the batches. I migrated starting with the base tables upon which everything else is supported; therefore, rolling back is more service by service.

Answer 4

It seems you are doing it in wrong way. Normally you shouldn't touch your migrations so there's no point to merge them. Especially if you pushed your code in production server you shouldn't touch at all.

So to sum up - migrations are to help you keeping your database schema as it should be but if you want to play with them, merge them or rebuild, no one will stop you, but if you make mistake, you can break something easily.

There's nothing wrong to have multiple migrations with small changes - this is how it works, you make changes for your PHP files, you make changes in your schema.