Faster Laravel Migration for testing projects with a lot of tables

user_3681102 0
Viewed 1971

I just begin to work on this big project that has zero tests. The idea is to TDD every new feature and/or bug, and with time we will increase the test coverage.

I don't make tests using SQLite in-memory DB. I do prefer to use MySql because it's the same DB that I use in production. Normally, in small projects, it's no problem, but with a big project, it is!

The problem that I faced is related to performance, a normal MySql instance running in-disk (M.2 SSD) takes around 90 secs to run all the migrations of this big project. There are over 200 tables to migrate, with a lot of relationships.

The solution for this problem was to set up MySql in memory too, using tmpfs with docker. This trick allowed me to decrease the migration time to only 10 secs, not bad, but really annoying if you just want to run 1 test! 10 secs to migrate, few milliseconds to test.

Laravel 8 just brought a new feature called Schema Dump: https://github.com/laravel/framework/pull/32275

I just saw this new feature and it really amused me, very nice! It will help a lot of people and save a lot of time. If you have a lot of migrations you can significantly reduce the time to migrate them all. This otherwise, does not resolve my problem. The number of migrations of the project is pretty close to 1 migration per table. No need to optimize anything here.

For curiosity sake, I took a Schema snapshot of the database and try to restore it with the MySql command line. It took around 3 secs to run the schema restore and set up everything:

 mysql -h 127.0.0.1 -u root -P 3331 -p default < database/migrations.sql

For the time being, the test database stays migrated all the time, this way my test flow (one test one run) stays super fast!

I like to think that a single test should be like a button that you push and it lights green or red instantly.

My question: - It is possible to decrease even more the migration time for projects with a large number of tables? (only for testing)

I don't have inside out knowledge about MySql, perhaps I'm missing something...

laravel migration phpunit mysql performance
3 Answers

If the goal is to load a database at a certain point in time, and you need to use that same snapshot repeatedly, then I would suggest trying an LVM snapshot, not a "migration".

It involves an OS-level snapshot of the disk. You would arrange to have just the MySQL dataset on the disk, and use LVM something like this:

One time setup: Stop mysqld, take an LVM snapshot

When ready to reload that snapshot, do different LVM magic to use the snapshot instead of the current state of the disk.

Sorry, I can't predict how few seconds it will take, but it does not involve the mysqldump at all.

user_17668310

Thanks to saddam kamal and shock_gone_wild to trigger a thought that clear up my mind for this problem. I do not need to migrate the database every single time that I run a test. In my current workflow, I migrate everything manually once per day. This can be automated!

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication;
    use DatabaseTransactions;

    protected function setUp(): void
    {
        parent::setUp();

        // first run of the day,
        // the database will be migrated to tmpfs
        $result = DB::select(DB::raw("SHOW TABLES LIKE 'users';"));
        if (!count($result))
        {
            $this->artisan('migrate:fresh');
        }
    }
}

I don't know what was I thinking! This is really a simple piece of code, and it makes it all happened. As the database is in memory (tmpfs), it needs to be migrated only once, on the first test of the day. This will take around 10 secs to run the first time, and the next time the test will run in milliseconds.

user_36811020

I use an approach which may feel unwieldy when first setting up the tests, but it makes a big difference to the speed of running the test suite. It's merely to only run those migrations which are needed for that test, instead of running all migrations or doing a full schema import. Eg:

This in a test:

$this->migrate([
    '2016_04_29_132815_create_authors_table',
    '2016_04_29_132815_create_categories_table'
]);
$this->seed(CategoriesTableSeeder::class);

And this in TestCase:

use Artisan;

/**
 * Runs migrations for individual tests
 *
 * @params array $migrations
 * @return void
 */
public function migrate(array $migrations = []): void
{
    $path = database_path('migrations');
    $migrator = app()->make('migrator');
    $migrator->getRepository()->createRepository();
    $files = $migrator->getMigrationFiles($path);

    if (!empty($migrations)) {
        $files = collect($files)->filter(
            function ($value, $key) use ($migrations) {
                if (in_array($key, $migrations)) {
                    return [$key => $value];
                }
            }
        )->all();
    }

    $migrator->requireFiles($files);
    $migrator->runPending($files);
}

/**
 * Runs some or all seeds
 *
 * @params string $seeds
 * @return void
 */
public function seed($seeds = ''): void
{
    $command = "db:seed";

    if (empty($seeds)) {
        Artisan::call($command);
    } else {
        Artisan::call($command, ['--class' => $seeds]);
    }
}
user_8178340
Related