Testing Code That Touches a Database
Point the tests at a throwaway database, wrap each test in a transaction and roll it back, so every test starts from the same rows.
$pdo->beginTransaction(); /* insert, read, assert */ $pdo->rollBack();A test that reads your live database is not a test. It passes today because of rows somebody happened to create, and fails next week when they are deleted. Worse, a test that writes to it will eventually write something a customer sees. A database test needs a database it is allowed to ruin.
A separate database
Create a second schema, shop_test beside shop, with the same tables and no data. Keep its credentials in the test configuration, never beside the production ones. In phpunit.xml a <php><env> element sets the environment variable your connection code already reads, so the application itself needs no knowledge that it is under test.
Roll back after every test
Writes from one test must not reach the next. A transaction is a group of statements the database will either keep or discard as a unit. Open one before the test and discard it afterwards:
protected function setUp(): void
{
$this->pdo = new PDO($dsn, $user, $password);
$this->pdo->beginTransaction();
}
protected function tearDown(): void
{
$this->pdo->rollBack();
}Everything the test inserted vanishes, and the next test sees the same empty tables. Queries inside the test still use prepared statements. A test is code, and that habit does not pause here.
What trips people up
On MySQL, a statement that changes the schema commits the open transaction silently, so a test that creates a table cannot be rolled back. Run migrations once before the suite, not inside a test.
Example
When to use it
- Check that a repository's SELECT actually returns the row the WHERE clause describes, against real SQL.
- Prove a unique-email constraint rejects a duplicate, which only the database can tell you.
- Verify a multi-table write leaves consistent data, then roll it back so the next test is unaffected.
- Run the suite against a disposable database in CI, created from migrations before the tests start.
More examples
The real thing: a PDO test with a rolled-back transaction
The lines you will actually write. The test creates the row it needs rather than assuming one exists, and both statements are prepared even though the values are literals in the test file, because a test is where the habit is set. The inTransaction() guard keeps tearDown from throwing when the test itself already committed or rolled back. Not runnable in the sandbox, which has no database server.
<?php
declare(strict_types=1);
use PHPUnit\Framework\TestCase;
final class UserRepositoryTest extends TestCase
{
private PDO $pdo;
protected function setUp(): void
{
$this->pdo = new PDO(
(string) getenv('DB_DSN'),
(string) getenv('DB_USER'),
(string) getenv('DB_PASSWORD'),
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$this->pdo->beginTransaction();
}
protected function tearDown(): void
{
if ($this->pdo->inTransaction()) {
$this->pdo->rollBack();
}
}
public function test_it_finds_a_user_by_email(): void
{
$insert = $this->pdo->prepare(
'INSERT INTO users (email, name) VALUES (:email, :name)'
);
$insert->execute(['email' => '[email protected]', 'name' => 'Ada']);
$select = $this->pdo->prepare('SELECT name FROM users WHERE email = :email');
$select->execute(['email' => '[email protected]']);
$row = $select->fetch(PDO::FETCH_ASSOC);
self::assertSame('Ada', $row['name']);
}
}Pointing the suite at the test database
Each <env> element sets a variable for the duration of the run, so getenv('DB_DSN') in setUp returns the test database and the same connection code serves both environments. Name the database so a mistake is obvious in an error message: nobody misreads shop_test for shop. The test credentials belong in version control only because the test database holds nothing worth stealing; production credentials never do.
<?php
// phpunit.xml
?>
<?xml version="1.0" encoding="UTF-8"?>
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="vendor/phpunit/phpunit/phpunit.xsd"
bootstrap="vendor/autoload.php"
cacheDirectory=".phpunit.cache"
colors="true"
failOnRisky="true">
<testsuites>
<testsuite name="unit">
<directory>tests</directory>
</testsuite>
</testsuites>
<php>
<env name="DB_DSN" value="mysql:host=127.0.0.1;dbname=shop_test;charset=utf8mb4"/>
<env name="DB_USER" value="shop_test"/>
<env name="DB_PASSWORD" value="shop_test"/>
</php>
</phpunit>Seeding and asserting through the repository
The fixture rows are inserted in setUp with one prepared statement reused in a loop, which is both the fast way and the safe way. Each test then reads through the class under test. Keep seeds this small: a test that needs forty rows to make its point is usually testing more than one thing.
<?php
declare(strict_types=1);
use PHPUnit\Framework\TestCase;
final class OrderTotalsTest extends TestCase
{
private PDO $pdo;
private OrderRepository $orders;
protected function setUp(): void
{
$this->pdo = TestConnection::open();
$this->pdo->beginTransaction();
$this->orders = new OrderRepository($this->pdo);
$seed = $this->pdo->prepare(
'INSERT INTO order_lines (order_id, sku, price, qty) VALUES (?, ?, ?, ?)'
);
foreach ([['pen', 250, 2], ['book', 1200, 1]] as [$sku, $price, $qty]) {
$seed->execute([77, $sku, $price, $qty]);
}
}
protected function tearDown(): void
{
$this->pdo->rollBack();
}
public function test_it_totals_every_line_of_an_order(): void
{
self::assertSame(1700, $this->orders->totalFor(77));
}
}
Discussion