When writing unit tests, you sometimes have to test if function did proper operations to the database. Simple CRUD-operations for example: you want to test if your code is affecting the database like you intended. Or for more complex situations, like imports or mass mutations you want to be able to test everything thoroughly.
Now, to make an adjustment to the database and validate the result is one, but if you want to do the same test over and over again, you must have a database that also resets itself to a specific state before each test run. So how can we do this in an easy way?
Shell scripts to the rescue!
Now what we want to do prior before each testrun is the following:
- Drop the whole existing (test-) database
- Create a new (test-) database
- Import the SQL-file for the new (test-) database
- Run PHPUnit
This can be easily done with a small shell script:
# Drop existing database:
echo "Drop existing database ... "
echo "DROP DATABASE IF EXISTS $dbname" | mysql -u $dbuser -p$dbpassword
# Create test database:
echo "Creating new database ... "
echo "CREATE DATABASE $dbname; GRANT ALL PRIVILEGES ON $dbname.* TO $dbuser@localhost IDENTIFIED BY '$dbpassword'" | mysql -u $dbuser -p$dbpassword
# Import test database:
echo "Importing test database ... "
mysql -u $dbuser -p$dbpassword $dbname < ./testdatabase.sql
echo "Starting PHPUnit ... "
Disclaimer: Please note that this script is purely intended to test operations on the database according to a test-database. As you might notice, the first command this script executes is dropping your whole existing database. This means that if you were developing your site and thought: “Hey! Let’s run this neat little test right now!”, it means that all mutations you might have done on the database are deleted, never to be restored. It’s important that you know this before someone gets hurt!
It’s not the holy grail
Please note that running unit tests against a database is a complex subject on itself and the ‘best’ solution depends on the type of application/situation you would like to test. For example: I am currently writing a massive importer for Magento and for me it’s important that the product catalog is completely empty at the beginning of the test. But I can also imagine that you might have a situation where you want to test your database-manipulating methods at any given time. If that’s the case, dropping and re-importing an entire database might not be the best solution for you, unless you know what you’re doing.
I hope that this article showed you some insight in how to handle databases with PHPUnit. But then again: if you have better ideas on how to approach this subject, or if you have tips, please feel free to share them with the rest of us in the comments.
Visitors give this article an average rating of 5.0 out of 5.
How would you rate this article?
★ ★ ★ ★ ★