Migrate & Seed Only Once?

Published 1 week ago by thc1967

I've finally bumped into a spot in my project that I can no longer use an in-memory sqlite database for my unit tests. It was cool while it lasted but, alas, I need to do some stuff that sqlite simply doesn't support.

And I want to keep using unit testing. I'm pretty stoked that I have hundreds of tests with thousands of assertions. I've found it helps tremendously with everything I do.

And I refuse to design my code around shortcomings in the testing setup I've elected to use. My code needs to be right and the tests should support and verify that; not the other way around.

So now I face the prospect of running my unit tests against a real, "on-disk" database like mysql (same thing I'm using for production).

With a sqlite memory database, I didn't have to worry or care about exactly how the tests would run the migration and the tiny little seeder I set up. If it ran them before each test class or even each function, that wouldn't be a big deal. It's all in memory.

That won't be the case any longer.

How, using Laravel 5.5, do I ensure that my PHPUnit tests run my migration and seed just once before running all the test classes & methods?

Thanks for the advice!

Best Answer (As Selected By thc1967)
ftrillo

I've been running my tests in an on disk mySQL database since I started, I'll tell you how I did it.

First, you create an alternate database called something like appName_test.

Then, you want to go to your config/database and add a test connection to the list, like this:

// Note the env() variables, they are specific to the test database.
'mysql_test' => [
    'driver' => 'mysql',
    'host' => env('DB_TEST_HOST', '127.0.0.1'),
    'port' => env('DB_TEST_PORT', '3306'),
    'database' => env('DB_TEST_DATABASE', 'forge'),
    'username' => env('DB_TEST_USERNAME', 'forge'),
    'password' => env('DB_TEST_PASSWORD', ''),
    'unix_socket' => env('DB_TEST_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Of course, you have to define those env variables in your .env file.

Then you need to setup your phpunit.xml to use the testing database during testing, like this:

<php>
    <env name="APP_ENV" value="testing"/>
    <env name="CACHE_DRIVER" value="array"/>
    <env name="SESSION_DRIVER" value="array"/>
    <env name="QUEUE_DRIVER" value="sync"/>
    <!-- Add this line, this is the connection name we defined before. -->
    <env name="DB_CONNECTION" value="mysql_test"/>
</php>

Your testing database is empty though, run migrate on it: php artisan migrate --database mysql_test

You can also run seed on it, if your tests need to have some data in the database. Mine inserted all the data during fixture setup.

Now, if your TestCases are using the DatabaseTransactions trait. Each test will run inside a different transaction that will be automatically rolled back at the end of the test, undoing any change to the database made during the test.

Because of that, I only need to run migrate again when I create new migrations.

Dont forget to clear your config cache before running phpunit!

Good luck, tell me how it goes.

ftrillo
ftrillo
1 week ago (12,140 XP)

I've been running my tests in an on disk mySQL database since I started, I'll tell you how I did it.

First, you create an alternate database called something like appName_test.

Then, you want to go to your config/database and add a test connection to the list, like this:

// Note the env() variables, they are specific to the test database.
'mysql_test' => [
    'driver' => 'mysql',
    'host' => env('DB_TEST_HOST', '127.0.0.1'),
    'port' => env('DB_TEST_PORT', '3306'),
    'database' => env('DB_TEST_DATABASE', 'forge'),
    'username' => env('DB_TEST_USERNAME', 'forge'),
    'password' => env('DB_TEST_PASSWORD', ''),
    'unix_socket' => env('DB_TEST_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Of course, you have to define those env variables in your .env file.

Then you need to setup your phpunit.xml to use the testing database during testing, like this:

<php>
    <env name="APP_ENV" value="testing"/>
    <env name="CACHE_DRIVER" value="array"/>
    <env name="SESSION_DRIVER" value="array"/>
    <env name="QUEUE_DRIVER" value="sync"/>
    <!-- Add this line, this is the connection name we defined before. -->
    <env name="DB_CONNECTION" value="mysql_test"/>
</php>

Your testing database is empty though, run migrate on it: php artisan migrate --database mysql_test

You can also run seed on it, if your tests need to have some data in the database. Mine inserted all the data during fixture setup.

Now, if your TestCases are using the DatabaseTransactions trait. Each test will run inside a different transaction that will be automatically rolled back at the end of the test, undoing any change to the database made during the test.

Because of that, I only need to run migrate again when I create new migrations.

Dont forget to clear your config cache before running phpunit!

Good luck, tell me how it goes.

thc1967
thc1967
1 week ago (27,160 XP)

That's a cool idea.

Unfortunately, transactions do not seem like they're going to work because of the context switch when testing controllers with objects that have relationships.

I'm creating the related objects in my test case, but those relations don't carry through into the controller when I call it with something like $this->post($route, $params). Therefore, I bomb out on referential integrity constraints in my controller.

Maybe if I do it without transactions. Do a migrate:fresh --seed before each run of my full test suite and just let the tests load up the database. Doesn't matter; it's a test database, right?

ftrillo
ftrillo
1 week ago (12,140 XP)

The problem with not cleaning the database after each test is that the data left behind by one test could affect the result of the next test.

I'm not sure I understand what context switch you mean, or what's happening to your related models. It'd be nice to see one of the tests that's not working with the transactions.

thc1967
thc1967
1 week ago (27,160 XP)
$item = make(Comment::class); // Creates a Post behind the scenes.

$this->post('/comments/store', $item->toArray()); // Touches the Post

The $this->post() results in a 500 error, which comes back as a referential integrity constraint violation because the Post that the Comment created doesn't exist within the context of the controller.

I think that's because I'm running the transaction within the Test object, which is a different memory space than the Controller, so the Controller isn't aware of the transaction...

Unfortunately, I'm distracted with other things so I've had to stop digging. Will be back to it later this weekend hopefully.

ftrillo
ftrillo
1 week ago (12,140 XP)

I think I get it. Your call to make(Comment::class) makes a Comment without persisting it to DB because you want to store it in the Controller logic. But, since Comment belongs to Post, you are also making a post.

Now if the post you're making isn't persisted to database, I can see why that would cause trouble. Is that the case? If so, try to actually persist the post to DB during the fixture setup and see what happens.

thc1967
thc1967
1 week ago (27,160 XP)

Actually, the make(Comment::class) calls create(Post::class) which does persist it to the database...

...but it doesn't because it's all transacted because I'm using the transaction trait in my test.

So my theory is that the the data created during the transaction that started in my test class isn't able to be seen in my controller, which I'm almost certain is running in a separate process from my test class.

zachleigh
zachleigh
1 week ago (200,130 XP)

I extract the DatabaseTransactions into my own trait to gain control over it:

<?php

namespace Tests;

trait DatabaseTransactions
{
    /**
     * Handle database transactions on the specified connections.
     */
    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();
            }
        });
    }

    /**
     * The database connections that should have transactions.
     *
     * @return array
     */
    protected function connectionsToTransact()
    {
        return property_exists($this, 'connectionsToTransact')
            ? $this->connectionsToTransact : [null];
    }
}

If you try to use the DatabaseTransactions trait that comes with Laravel, it will be registered with the test runner and this will not work. In my TestCase class, I do the following:

<?php

namespace Tests;

use Tests\DatabaseTransactions;
use Illuminate\Foundation\Testing\TestCase as BaseTestCase;

abstract class TestCase extends BaseTestCase
{
    use CreatesApplication, DatabaseTransactions;

    /**
     * If true, setup has run at least once.
     *
     * @var boolean
     */
    protected static $setUpRun = false;

    /**
     * Set up the test.
     */
    public function setUp()
    {
        parent::setUp();

        if (!static::$setUpRun) {
            \Artisan::call('migrate:refresh');

            \Artisan::call('db:seed', ['--class' => 'TestDatabaseSeeder']);

            static::$setUpRun = true;
        }

        $this->beginDatabaseTransaction();
    }
}

Basically, if its the first run, we're going to migrate and run a seeder to get everything set up. Otherwise, we'll just start database transactions.

ftrillo
ftrillo
1 week ago (12,140 XP)

@thc1967 If you really can't make it work with with transactions, try the DatabaseMigrations trait instead. It will migrate the database between tests, shoul work. But it'll probably make your tests run too slowly.

If you're sure that your tests don't care about any data left by a previous test then @zachleigh 's anwer is perfectly fine. It will avoid filling up the database too much by running the tests over and over. And it wont make the tests run too slowly.

As for the transactions...

If you're using the trait, your entire test runs inside the same transaction. Both the call to make() and the call to $this->post().

Try this, just to make sure.

$item = make(Comment::class); // Creates a Post behind the scenes.

// These should NOT fail. If make() is persisting the models.
Comment::findOrFail($item->id);
Post::finOrFail($item->post_id);

$this->post('/comments/store', $item->toArray()); // Touches the Post

There are three things that seem weird to me. I'm not saying they're wrong, because I don't know how your app works. But they jump at me.

First, your function is called make(), which to me implies the item isn't persisted to dabase. Factories make that distinction between make() and create().

Second, you're telling me you're sending a comment that's already in database to the store route (which is supposed to store in database).

Third, you're sending $item->toArray(). toArray will serialize the model and the related models that are loaded. If your route is only expecting data about the comment you could try $item->getAttributes().

thc1967
thc1967
1 week ago (27,160 XP)

I think I have it working with the Laravel DatabaseTransactions trait... mostly.

MySQL behaves differently than Sqlite when using transactions. The biggest change seems to be that when we consume an autonumber in MySQL, that autonumber stays consumed. This invalidates tests like ensuring I've redirected to a specific record ($this->assertRedirect(route('posts.show', 1))). The ID of the record would only be 1 for the very first test that I run that creates the object.

This is more proper database behavior, of course, but it means I need to rethink my test cases a little bit.

Please sign in or create an account to participate in this conversation.