What's the recommended way to test a large database change?

So imagine you're trying to make a large change to the structure and data of your database. The existing data needs to be migrated into the new database structure.

I presume this means you need to make a new database migration class that will simultaneously read existing data, delete rows or tables, create new columns or tables, and then put the data back in a slightly different way. But since you're a good developer, this can all be done, but perhaps with some hard work. And you really want to test it well.

But now imagine this: You want to test this big migration on live data, so you download your live database to your development machine to test the migration.

But now what?

I think what you want to do is start with a fresh database, then run all the migrations except the last one, then manually insert the live data yourself, then run the final migration. Right?

If so, how can you run all migrations except the last one? Is there an option for this? Or how else can you do it?

I look forward to the discussion.

gych

2 years ago

Level 29

What you could do is put the new migrations in a separate and different folder then the already existing migrations, for example /database/migrations-v2

In your laravel app.php config file you can then add

'run_v2_migrations' => (bool) env('RUN_V2_MIGRATIONS', false),

Then in AppServiceProvider you add this inside of the boot method

if (config('app.run_v2_migrations')) $this->loadMigrationsFrom(base_path("database/migrations-v2"));

This will run the v2 migrations when the config value is set to true. Not sure if there are other solutions but this is easy to implement.

martinbean

2 years ago

Level 80

But now imagine this: You want to test this big migration on live data, so you download your live database to your development machine to test the migration.

@magmatic Nope. Production data should never leave production environments.

To reduce risk, you should be committing smaller changes more often, instead of trying to roll out a huge change all in one go. You will catch problems faster, and if any issues do arise then they’ll be smaller in scope than if you were to try and roll out a massive change to your database schema and data got mangled during the migration or import stage.

Please or to participate in this conversation.