A Better Database Testing Workflow in Laravel

DatabaseSetup.php

<?php

use Illuminate\Contracts\Console\Kernel;

trait DatabaseSetup
{
    protected static $migrated = false;

    public function setupDatabase()
    {
        if ($this->isInMemory()) {
            $this->setupInMemoryDatabase();
        } else {
            $this->setupTestDatabase();
        }
    }

    protected function isInMemory()
    {
        return config('database.connections')[config('database.default')]['database'] == ':memory:';
    }

    protected function setupInMemoryDatabase()
    {
        $this->artisan('migrate');
        $this->app[Kernel::class]->setArtisan(null);
    }

    protected function setupTestDatabase()
    {
        if (! static::$migrated) {
            $this->artisan('migrate:refresh');
            $this->app[Kernel::class]->setArtisan(null);
            static::$migrated = true;
        }
        $this->beginDatabaseTransaction();
    }

    public function beginDatabaseTransaction()
    {
        $database = $this->app->make('db');

        foreach ($this->connectionsToTransact() as $name) {
            $database->connection($name)->beginTransaction();
        }

        $this->beforeApplicationDestroyed(function () use ($database) {
            foreach ($this->connectionsToTransact() as $name) {
                $database->connection($name)->rollBack();
            }
        });
    }

    protected function connectionsToTransact()
    {
        return property_exists($this, 'connectionsToTransact')
                            ? $this->connectionsToTransact : [null];
    }
}

TestCase.php

<?php

abstract class TestCase extends Illuminate\Foundation\Testing\TestCase
{
    use DatabaseSetup;

    /**
     * The base URL to use while testing the application.
     *
     * @var string
     */
    protected $baseUrl = 'http://localhost';

    protected function setUp()
    {
        parent::setUp();
        $this->setupDatabase();
    }

    /**
     * Creates the application.
     *
     * @return \Illuminate\Foundation\Application
     */
    public function createApplication()
    {
        $app = require __DIR__.'/../bootstrap/app.php';

        $app->make(Illuminate\Contracts\Console\Kernel::class)->bootstrap();

        return $app;
    }
}

nice

awesome!

Great work Adam !

Hey Adam, I really love this idea but something I have been bitten by before is with a CI machine that works with multiple branches which may have different migrations, if you run migrate:refresh on a different branch and it tries to roll back something that doesn't exist as a migration it will fail. It might be useful to have a migrate:rollback running at the end. Do destructors work on traits?

@adamwathan this is so rad. If I am running windows, I need mysql on it running, correct?

Great! I love it

Adam, great thanks, that's very very helpful!

@adamwathan great idea and trait!

1. Some people talk about about how the "right way" is to re-migrate your DB after every test to make sure there are no bugs/interdependence. Thoughts? It seems that a transaction will be fine, unless there is a bug with the MySQL Transaction itself (obviously outside of our control)

2. Just to verify, this will be only be helpful for an actual testing connection; an in-memory db will be the same whether using DatabaseMigration or DatabaseSetup, correct?

3. You mention in the video https://adamwathan.me/2016/11/14/a-better-database-testing-workflow-in-laravel that using transactions are faster than an in memory db as your times show. But mine are opposite, transactions are slightly slower than in-memory. Any idea as to why?

Some people talk about about how the "right way" is to re-migrate your DB after every test to make sure there are no bugs/interdependence. Thoughts? It seems that a transaction will be fine, unless there is a bug with the MySQL Transaction itself (obviously outside of our control)

Transactions keep the database clean by not persisting any modified data in between test runs. Remigrating is useless, since you're just dumping the empty tables and setting them all back up again. You're just taking extra time to remigrate for no gain in the tests. Which becomes a major problem once you have a long chain of migrations to run through over time.

Remigrating fully is a hack to not use transactions to keep things stable. If you wanted to be purist on "remigrating after every run" then you should do a dump of the clean tables and restore from a dump file and not even run the migration system for tests at all. That is the best speed without involving transactions.

Just to verify, this will be only be helpful for an actual testing connection; an in-memory db will be the same whether using DatabaseMigration or DatabaseSetup, correct?

This isn't making much sense to me. You aren't ever testing connections (since if they fail the tests will fail), you're testing whether the application running hits the given DB as needed.

You mention in the video that using transactions are faster than an in memory db as your times show. But mine are opposite, transactions are slightly slower than in-memory. Any idea as to why?

As with anything towards performance... It depends™. Your number of tables, migrations, HDD/SSD access speed, RAM access speed, CPU capacity and current workload, etc. can all affect what is going on. In his video with his machine running as it was, all combined to make his memory version slower. Your results may be different between any set of given machines with the exact same test setup. We're talking a few hundred milliseconds at worst here in most normal environments, that isn't too much to worry about in either direction.

You should PR this into Laravel core somehow!

@adamwathan Would it be better to only run refresh if migrations actually change?

Something like this?
https://gist.github.com/slava-vishnyakov/2ae1d1e030204ad10c1898b20aeba4cb#file-databasesetup-php-L33-L36
https://gist.github.com/slava-vishnyakov/2ae1d1e030204ad10c1898b20aeba4cb#file-databasesetup-php-L61-L77

https://gist.github.com/slava-vishnyakov/2ae1d1e030204ad10c1898b20aeba4cb/revisions?diff=split

For my app refresh takes 3.5 seconds :( So this really speeds up the process.

@adamwathan For learning purposes, can I ask what the technical reason is for $this->app[Kernel::class]->setArtisan(null);?

Note that Laravel has a build in trait \Illuminate\Foundation\Testing\RefreshDatabase (formerly added as FreshDatabase) that has the same logic

Yeah, looks like it's not needed anymore in recent Laravel versions.

awesome