Database testing — transaction rollback per test
Concept
Database testing with transaction rollback is the fast, safe way to test code that writes to the database. Each test method runs inside a database transaction. After the test, the transaction is rolled back — database returns to its previous state. No cleanup code needed.
RefreshDatabase trait: Provided by the testing framework. When a test class uses this trait, the TestCase's setUp() opens a transaction before the test and tearDown() rolls it back after.
Why this is fast: On the first test run, migrations execute once (creating the schema). All subsequent tests just use the existing schema inside a transaction. Rolling back a transaction is near-instant compared to truncating/re-migrating tables.
assertDatabaseHas($table, $conditions): Assert a row exists in the database. Runs SELECT COUNT(*) FROM $table WHERE <conditions>. Passes if count ≥ 1.
assertDatabaseMissing($table, $conditions): Inverse of assertDatabaseHas. Passes if count = 0.
assertDatabaseCount($table, $expected): Assert the exact number of rows in the table.
assertSoftDeleted($table, $conditions): Assert deleted_at IS NOT NULL for matching rows.
Transactions and nested transactions: If your code under test uses a database transaction, the test's outer transaction becomes a savepoint. Rollback of the inner transaction doesn't affect the outer. Some edge cases (DDL in transactions, MySQL not supporting nested transactions) require special handling.
Factory usage: User::factory()->create() in a test inserts via the outer transaction. When the test ends and the transaction rolls back, the user disappears — no manual cleanup.
Code Example
<?php
namespace Framework\Testing;
trait RefreshDatabase
{
// setUp() and tearDown() are called by TestCase
// This trait just marks the class as needing DB transaction management
}
// assertDatabaseHas implementation in TestCase
abstract class TestCase extends \PHPUnit\Framework\TestCase
{
// ... (from fw-11-001/002)
protected function assertDatabaseHas(string $table, array $data): void
{
$conditions = array_map(fn($col) => "{$col} = ?", array_keys($data));
$whereClause = implode(' AND ', $conditions);
$sql = "SELECT COUNT(*) as count FROM {$table} WHERE {$whereClause}";
$result = $this->getConnection()->selectOne($sql, array_values($data));
\PHPUnit\Framework\Assert::assertGreaterThan(
0,
(int) $result['count'],
"Failed asserting that table [{$table}] contains record matching: " . json_encode($data)
);
}
protected function assertDatabaseMissing(string $table, array $data): void
{
$conditions = array_map(fn($col) => "{$col} = ?", array_keys($data));
$whereClause = implode(' AND ', $conditions);
$sql = "SELECT COUNT(*) as count FROM {$table} WHERE {$whereClause}";
$result = $this->getConnection()->selectOne($sql, array_values($data));
\PHPUnit\Framework\Assert::assertEquals(
0,
(int) $result['count'],
"Failed asserting that table [{$table}] does not contain record matching: " . json_encode($data)
);
}
protected function assertDatabaseCount(string $table, int $expected): void
{
$result = $this->getConnection()->selectOne("SELECT COUNT(*) as count FROM {$table}");
\PHPUnit\Framework\Assert::assertEquals(
$expected,
(int) $result['count'],
"Expected {$expected} rows in [{$table}], found {$result['count']}."
);
}
protected function assertSoftDeleted(string $table, array $data): void
{
$dataWithDeleted = array_merge($data, []); // base conditions
$conditions = array_map(fn($col) => "{$col} = ?", array_keys($dataWithDeleted));
$whereClause = implode(' AND ', $conditions) . ' AND deleted_at IS NOT NULL';
$sql = "SELECT COUNT(*) as count FROM {$table} WHERE {$whereClause}";
$result = $this->getConnection()->selectOne($sql, array_values($dataWithDeleted));
\PHPUnit\Framework\Assert::assertGreaterThan(
0,
(int) $result['count'],
"Failed asserting [{$table}] has soft deleted record matching: " . json_encode($data)
);
}
}
// Usage in tests
class UserTest extends TestCase
{
use RefreshDatabase;
public function test_creating_user(): void
{
$user = User::create(['name' => 'Alice', 'email' => 'alice@example.com']);
$this->assertDatabaseHas('users', ['email' => 'alice@example.com']);
$this->assertDatabaseCount('users', 1);
}
// After this test: transaction rolls back, users table is empty for next test
}