Add a second local database for testing with DDEV
The use of an in-memory SQLite database for PHPUnit and Pest tests is common practice. However, SQLite reaches its limits for larger or long-term projects, especially when it comes to certain database operations such as dropColumn() or sorting columns. SQLite does not support these specific operations.
I use SQLite as long as possible, but at a certain point it becomes limiting. If you use DDEV, you can avoid this by adding a second MySQL database to the container.
Create an additional database
In the .ddev/commands/web/ directory, place the file create_testing_database.sh
echo "Create testing mysql database and grant all permissions to db user"
mysql -hdb -uroot -proot -e "CREATE DATABASE IF NOT EXISTS testing;"
mysql -hdb -uroot -proot -e "GRANT ALL ON *.* TO 'db'@'%';"Language:shellTo execute this script every time the container is started, we add a hook in the DDEV configuration file .ddev/config.yaml. Add the following section there:
hooks:
post-start:
- exec: "/var/www/html/.ddev/commands/web/create_testing_database.sh"Language:yaml-frontmatterIntegration of the new database in PHPUnit or Pest
Once the second database is set up in DDEV, the next step is to use it in your PHPUnit or Pest tests. To do this, you need to customize the configuration files of these test frameworks or create a testing environment.
PHPUnit and Pest configuration
Change the database settings in the phpunit.xml file. Make sure that the connection data refers to your new test database and the specific port. An example section could look like this:
<php>
<env name="DB_CONNECTION" value="mysql" />
<env name="DB_HOST" value="db" />
<env name="DB_PORT" value="3307" />
<env name="DB_DATABASE" value="testing" />
<env name="DB_USERNAME" value="db" />
<env name="DB_PASSWORD" value="db" />
</php>Language:xmlThe new testing database can now also be accessed via the DB client of choice:
