Laravel tests database migration from schema

Jan Glaser 0
Viewed 42

The problem

  • I am using Laravel 8.83.23
  • I have schema dump from squashed migrations in database\schema\mysql-schema.dump
  • tests are running above test database, as in database.php

    'testing' => [
                    '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', ''),
                ],
  • Before I squashed migrations, my test cases only used DatabaseMigrations trait, and the test database was recreated every time and all worked, example of test class:

    class SystemControllerTest extends TestCase
    {
        use WithFaker;
        use DatabaseMigrations;
        /**
         * @var User
         */
        private $user;
    
        public function setUp(): void
        {
            parent::setUp();
    
            //create roles and data
            $this->seed(RoleAndPermissionSeeder::class);
        ... etc
  • the migrations were found and executed, recreating the database
  • then, I squashed the migrations, so all migrations got deleted, and I got database\schema\mysql-schema.dump
  • php artisan migrate works as expected through command line, creating full database schemas from the dump (it finds it)
  • tests however no longer work, as there is an error

    SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cinema_test.roles' doesn't exist (SQL: delete from `roles`)
  • when I check the sql test database after the test runs, it is empty (only table migrations gets created there, and it is empty)
  • this error persists even when I call artisan migrate in the test's setup:

    public function setUp(): void
        {
            parent::setUp();
            Artisan::call('migrate', array(
       '--database' => 'testing',
       '--force' => true));
            //it crashes here
            $this->seed(RoleAndPermissionSeeder::class);
  • RoleAndPermissionSeeder just operates with the sql tables, which do not exist, hence the error
  • I even tried DatabaseMigrations and DatabaseTransactions and RefreshDatabase traits, without any success
  • how do I populate the database data? There is no way for me to read the output of the Artisan::call('migrate') command, so I do not know what is happening there
  • return code of Artisan::call('migrate') is 0
  • is there maybe some setup I am missing?
laravel-8 mysql testing php laravel
2 Answers

I have finally figured this out.

The reason for the problem

The problem was in incorrect setup of the testing environment. I have not discovered the exact reason, but I figured out how to setup the testing environment so that the dump would be found and loaded.

How I hunt down the bug

This describes my steps on how I found a way to fix this.

In database.php I have copied testing database instead of normal one
  • in database.php I had the main database connection:

    'mysql' => [
                'driver' => 'mysql',
                'url' => env('DATABASE_URL'),
                'host' => env('DB_HOST', '127.0.0.1'),
                'port' => env('DB_PORT', '3306'),
                'database' => env('DB_DATABASE', 'forge'),
                'username' => env('DB_USERNAME', 'forge'),
                'password' => env('DB_PASSWORD', ''),
                'unix_socket' => env('DB_SOCKET', ''),
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'prefix_indexes' => true,
                'strict' => false,
                'engine' => null,
                'options' => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                ]) : [],
            ],

and the testing connection


    'testing' => [
                    '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', ''),
                ],
  • I copied the testing connection data into a new mysql connection, just to see, whether on a command line I get same results
  • so, the file then looked like this

    'mysql' => [
                'url' => env('DATABASE_URL'),
                '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_SOCKET', ''),
                'charset' => 'utf8mb4',
                'collation' => 'utf8mb4_unicode_ci',
                'prefix' => '',
                'prefix_indexes' => true,
                'strict' => false,
                'engine' => null,
                'options' => extension_loaded('pdo_mysql') ? array_filter([
                    PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                ]) : [],
            ],
    
    /*'testing' => [
                    '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', ''),
                ],*/
  • on the console, I ran php artisan:migrate
  • the database dump was found and loaded
  • therefore, the dump was found in normal cases, but was not found, in testing cases
  • after some research, I changed the testing environment setup in phpunit.xml, I will explain it now
The file phpunit.xml

The phpunit.xml was as follows (not full file shown here):


       <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>
        <env name="DB_CONNECTION" value="testing"/>     
    </php>
</phpunit>
  • so, we can see that the testing database connection is defined for unit tests
  • on web I found advice to set the database table only, instead of changing entire connection for tests, because it is easier
  • I tried such an approach, so the phpunit.xml became
       <server name="QUEUE_CONNECTION" value="sync"/>
        <server name="SESSION_DRIVER" value="array"/>
        <server name="TELESCOPE_ENABLED" value="false"/>
        <env name="DB_DATABASE" value="cinema_test"/>   
    </php>
</phpunit>
  • I deleted the testing connection from database.php and related obsolete variables from .env file
  • this fixed the issue, and the dump file got loaded even in tests now
Conclusion

Although I have not figured out the real cause for the lavavel not loading the dump file, I have found a workaround which was to only change the database name for tests, instead of defining entirely new sql connection for testing pursposes. This solved the issue, and the database dump file gets loaded during tests now.

Jan Glaser0

Seems like schema dumps can't be used for the in-memory database when testing

https://laravel.com/docs/9.x/migrations#squashing-migrations

May be able to do something like this

DB::unprepared(file_get_contents("path/file.sql"));

Would only try as a last resort, personally would want a test environment migration, also you should add a check for a test environment migration if you take this approach

WesleyNZ0
Related