For most of us in the PHP community, writing our first integrated test usually means interacting with a database. For too long I considered this a difficult and frustrating thing to do, so I avoided it, leaving code either uncovered, or covered with overly specified tests using way too many test doubles. Most of the modern frameworks do this kind of thing for you, but here's how I do it.
It's worth noting that I tend to work on products, rather than software products. My software doesn't get distributed, I don't have to support multiple database vendors etc.
The stages of the test
When we think about how a database is involved in a test, we can think of the usual Arrange, Act, Assert steps, but with set up and tear down either side of them.
- Set the database up to a known state for all tests
- Arrange the database records for this specific test
- Act, exercising the system under test
- Assert against the state of the database
- Tear the database back down to it's known state for the next test
This article is purely going to focus on the first and last stages, setting the database up and tearing it down. I have a test support class that manages this for me, it takes a Doctrine DBAL connection, which is already configured with access to a test database server.
<?php
namespace tests\support\Database;
use Doctrine\DBAL\Connection;
class Fixture
{
private $conn;
public function __construct(Connection $conn)
{
$this->conn = $conn;
}
public function setup() { }
}
Set up
Given we have a database server available to us, the set up stage is going to
include getting our schema loaded. While it's tempting to run your database
migrations to set the schema up, I feel like this is a waste of time. I rarely
write database migrations and once they've hit all development, staging and
production environments, they're not really useful to me any more and don't need
to be tested. I like to keep a copy of the current schema alongside the tests in
version control, tests/support/Database/fixture.sql. I then import this fixture during
the set up stage.
public function setup()
{
$this->load();
}
private function load()
{
$file = $file ?: __DIR__ . '/fixture.sql';
$params = $this->conn->getParams();
system("MYSQL_PWD={$params['password']} mysql -h{$params['host']} -P {$params['port']} -u{$params['user']} {$params['dbname']} < $file");
}
I shell out to the mysql command line client as I've found it to be a shade faster, but your mileage will vary and you could try going through the DBAL instance as well.
In order to keep the fixture up to date when creating migrations, I have a short script that will load the fixture, run the migrations and then dump the database back in to that fixture file, ready to be committed to version control alongside the migration.
public function update()
{
$this->load();
# It's not quite like this, but you get the idea
system("APPLICATION_ENV=testing php atstisan migrate");
$this->dump();
}
public function dump()
{
$file = $file ?: __DIR__ . '/fixture.sql';
$params = $this->conn->getParams();
system("MYSQL_PWD={$params['password']} mysqldump --set-gtid-purged=OFF -h{$params['host']} -P {$params['port']} -u{$params['user']} --opt {$params['dbname']} > $file");
}
If I have the need for some standard data to be available to every test, I don't
bother writing code to seed the database at the start of every test. I load the
fixture, do whatever it takes to get the data in there, be it scripting or by
hand, and then dump the fixture again, ready to be stored in version control.
This keeps my start up time quick and deterministic, what's in the fixture.sql
file is what every test starts with.
Tear down
You could tear the database down by deleting the entire fixture, allowing the setup method to reload the entire fixture again, but this can be very slow. To speed things up, we can load the fixture once at the start of the test suite, and then have every test tear the database back down to this initial state.
public function setup()
{
if ($this->fixtureLoaded) {
return;
}
$this->load();
$this->fixtureLoaded = true;
}
A popular way of tearing a database down is to run each test in a transaction and then roll back the transaction after the test has completed. This works quite well and is really fast, but has a couple of drawbacks.
The first is that all database operations need to run through that same connection that holds the transaction. This isn't so easy when you're wanting to do headless browser tests with tools like selenium, or if you need to do any out of bounds processing like queue workers. To get around this, you can use a truncation strategy. The tear down stage truncates all the necessary tables to return the database to known state (except for auto increment counts, but I can live with that).
public function tearDown()
{
$this->truncate();
}
public function truncate()
{
foreach ($this->tablesToTruncate as $table) {
$this->conn->delete($table, array(1 => 1));
}
}
Again, here I use the equivalent of DELETE FROM $table WHERE 1=1, which I've
found to be a hair quicker than TRUNCATE $table, but you should benchmark for
yourself. I manually keep a list of tables to truncate, but you could easily
make a list of tables not to truncate, or if you don't have any data in the
fixture.sql file, truncate all tables.
The second drawback to using transactions and the most important for me, is that the state of the database is always torn back down at the end of the test. If you're trying to debug a particular problem, it can be very useful to be able to examine the database after the test has finished. Surely this is the same using a truncation strategy, I hear you say. You would be right, but with truncation, we can move the truncation to the setup method, not do anything on tear down and don't call me Shirley.
public function setup()
{
if ($this->fixtureLoaded) {
$this->truncate();
return;
}
$this->load();
$this->fixtureLoaded = true;
}
public function tearDown() {}
This way, each class gets a clean state, but after a test has run, you get the chance to inspect the state of the database. It's pretty fast too, at least fast enough for me.
Usage
I use this code in a setup method of a base class in my day job, but you can also do the same
thing with a trait. This example uses a singleton to get hold of a Fixture
instance, but I'll write about other ways of doing that in another article.
<?php
namespace tests\support;
use tests\support\Database\Fixture;
trait UsesDatabase
{
/**
* @before
*/
public function setupDatabase()
{
Fixture::instance()->setup();
}
}
<?php
namespace tests\integration;
use tests\support\UsesDatabase;
class SomeTest extends \PHPUnit_Framework_TestCase
{
use UsesDatabase;
// ...
}
Happy testing!