Published under Laravel.
Laravel defaults to using an in-memory SQLite database for testing. Using this type of database has some advantages over a traditional MySQL database such as less configuration, your tests "just work", they run faster than a traditional database, etc. However, there are also some disadvantages as well:
Some schema changes are not supported in SQLite but are supported in MySQL and Postgres, which means you need to work around them in your migrations so that they run properly when testing and when in production.
Some database features are not supported in SQLite such as full-text searching
You can never fully trust SQLite will work exactly how a MySQL/Postgres database works in production
When using the
RefreshDatabase trait that comes out of the box with Laravel, a SQLite test suite can actually be much slower than MySQL
I'm working on a project where we just transitioned from an in-memory SQLite test suite to a MySQL test suite. At the time of the transition, we had 52 migrations in the project. A full test suite (426 tests) with the in-memory driver took between 1.5 minutes to 3 minutes, depending on the machine running the tests.
After the transition, the full test suite (with more tests too, up to 495 at the time of writing this post), now runs between ~14 seconds to ~20 seconds, depending on the machine running the tests.
The key is in the
RefreshDatabase trait. When using the trait with an in-memory database, Laravel will re-migrate the database with each test run. So as an example, if your migrations take 1.5 seconds to run, each test using the
RefreshDatabase trait in your suite will take at least 1.5 seconds. Some basic math for a test suite with 400 tests:
400 * 1.5 = 600 seconds = 10 minutes!!
As fast as in-memory is, the root problem is that you're running migrations over and over again. This is where
Instead of running your migrations from scratch for each test, the trait keeps track once your migrations have been run for the first time. From that point forward it uses database transactions to rollback the database after each test, which resets the database to the state it was in at the beginning of the test (migrated but with no data).
There is a small caveat that you need to be aware of. When the database rolls back the transaction, any auto-incremented keys are not rolled back. Databases do this to prevent primary key collisions when using transactions.
So if you're asserting that a database includes a hard coded ID, you'll need to update your assertions because you can no longer guarantee that auto-increment fields will start at 1.
We made a simple helper class to use for our tests:
Usage of this class looks like this:
There's two steps to switching to using MySQL for your tests:
Create a new database to use for tests. You probably don't want to use the same database for local development as you do for testing because each time you run a test, the database will be reset.
phpunit.xml to use the MySQL connection and new database:
Apart from those two changes, you'll have to update any tests that assert a hard coded auto-incrementing ID as described above!