Testing what the database actually does

The bug was that two customers could register with the same email address if one of them capitalised it. There was a test for exactly this, it passed, and it had passed for two years — because it ran against a mocked connection that returned whatever the test told it to.

The symptom

public function testEmailIsUnique(): void
{
    $pdo = $this->createMock(PDO::class);
    $pdo->method('prepare')->willReturn($this->statementReturning(null));

    $repo = new CustomerRepository($pdo);

    $this->assertNull($repo->byEmail('[email protected]'));
}

// this asserts that the mock returns what the mock was told to return.

$ vendor/bin/phpunit --filter EmailIsUnique
OK (1 test, 1 assertion)

$ mysql -e "SELECT id, email FROM customers WHERE email LIKE 'ada@%'"
4471  [email protected]
8812  [email protected]          ← the collation is case-insensitive
                              ← and the unique index agreed

The database’s collation is case-insensitive, so the unique index treats the two as the same string and the second insert should have failed. It did not, because there was no unique index — the migration adding it had been written, reviewed and never run on production. Nothing in the test suite could have caught either fact.

Why it happens

A mock asserts what you believe the collaborator does. That is exactly right for a collaborator you wrote, whose behaviour is defined by your own code, and exactly wrong for a database — where the interesting behaviour is the part you did not write and may not know about.

Collation, unique constraints, foreign key actions, decimal rounding, timezone conversion, maximum index key length, what happens to a string too long for its column: every one of those is a property of the database and its configuration, and no mock will ever tell you about any of them.

The fix

A real database, made fast enough to keep

<testsuites>
  <testsuite name="unit"><directory>tests/Unit</directory></testsuite>
  <testsuite name="integration">
    <directory>tests/Integration</directory>
  </testsuite>
</testsuites>

<php>
  <env name="DB_CONNECTION" value="mysql"/>
  <env name="DB_DATABASE" value="app_test"/>
</php>

Splitting the suites is what keeps the fast feedback loop fast: the unit suite runs in two seconds on every save, and the integration suite runs before a push and in CI. Merging them produces a suite that is too slow to run often, which is how a test suite stops being used.

Running against SQLite instead is the tempting shortcut and it defeats the purpose entirely. SQLite has different collation, different type affinity, no strict mode, and it enforces foreign keys only if asked — so the tests pass and the production behaviour is untested. If the point is to test what the database does, it has to be the database.

Transactions per test, and where that breaks down

// each test in a transaction, rolled back afterwards
use IlluminateFoundationTestingRefreshDatabase;

class CustomerRepositoryTest extends TestCase
{
    use RefreshDatabase;
}

// where it does not work:
//   code under test that commits explicitly
//   tests of transaction behaviour itself
//   anything on a SECOND connection — a spawned worker,
//     a raw PDO handle, a browser test

Truncating every table between tests costs a statement per table per test, which on forty tables and four hundred tests is sixteen thousand statements. A transaction and a rollback is one of each. On that project it took the integration suite from four minutes to fifty seconds, which is the difference between running it before every push and not.

The second-connection case produces a genuinely confusing failure: the data exists inside the uncommitted transaction and is invisible to anything connecting separately, so a test that spawns a process sees an empty database. Those tests need the truncating variant and are worth isolating rather than fighting.

The tests that only a real database can pass

public function testEmailUniquenessIsCaseInsensitive(): void
{
    $this->repo->save(new Customer('[email protected]'));

    $this->expectException(DuplicateEmail::class);
    $this->repo->save(new Customer('[email protected]'));
}

public function testTotalIsNotAFloat(): void
{
    $this->repo->save($this->orderWorth(1999));
    $this->repo->save($this->orderWorth(1999));

    // 19.99 + 19.99 in a DECIMAL column, read back
    $this->assertSame(3998, $this->repo->totalCents());
}

public function testDeletingACustomerKeepsTheirOrders(): void
{
    $id = $this->repo->save($this->customerWithOrders(3));
    $this->repo->delete($id);

    $this->assertCount(3, $this->orders->ofCustomer($id));
}

Every one of those asserts something about the schema rather than about the code, and every one of them would pass against a mock regardless of whether the schema was right. The third is the one worth generalising: a test that asserts a foreign key action is the only defence against somebody adding ON DELETE CASCADE to be helpful.

Fixtures that describe intent

// what the fixture usually looks like
$this->insert('orders', [
    'id' => 1, 'customer_id' => 1, 'status' => 3,
    'total' => 4900, 'placed_at' => '2018-10-01 00:00:00',
]);

// what it should look like
$order = $this->anOrder()
    ->placedBy($this->aCustomer())
    ->awaitingPayment()
    ->worth(Money::gbp(4900))
    ->save();

The first version breaks when a column is added with a NOT NULL constraint, in every test, and the fix is a mechanical edit across two hundred files. The builder version breaks in one place. More importantly, status => 3 tells the next reader nothing and awaitingPayment() tells them everything.

Verifying it worked

$ time vendor/bin/phpunit --testsuite unit
OK (284 tests)          real 0m2.104s

$ time vendor/bin/phpunit --testsuite integration
OK (96 tests)           real 0m51.882s

# and the run that found things nobody had written:
$ DB_HOST=mariadb vendor/bin/phpunit --testsuite integration
FAILURES!
Tests: 96, Failures: 2

1) OrderRepositoryTest::testInsertOrderIsAtomic
   MariaDB 10.3 returns a different SQLSTATE for a deadlock
2) ReportTest::testGroupByOrdering
   GROUP BY implies a sort here and does not on MySQL 8.0

Running the same suite against both engines found two behavioural differences, neither of them in any code we had written. The deadlock one mattered: the retry logic matched on a MySQL-specific error code and would have silently failed to retry on MariaDB, which is exactly the class of bug that only appears under production load on the one host that is different.

What this costs

A test suite that needs a container, and a slower feedback loop for the part of it that touches the database. Fifty seconds is tolerable and it is fifty seconds; on a larger schema it will be three minutes, and at that point the suite gets run less often and the tests stop paying. Keeping the integration suite small and deliberate — testing what the database does rather than testing the application through the database — is what keeps it from growing into the thing nobody runs.

There is also a real objection that these are not unit tests and should not be confused with them, and it is correct. They are slower, they need infrastructure, and they fail for reasons unrelated to the code under test. The answer is not to avoid them but to keep them in a separate suite with a separate name, so that a red build says which kind of thing broke — and so that nobody is tempted to mock a database again in order to make the number go up.