Database Tests

Database Tests

Testing Code That Touches the Database

A fake proves your logic; only MySQL 524 proves your SQL. Point integration tests at a separate shop_test database loaded from the shop schema, with a least-privilege account, and wrap each test in a transaction that tearDown() rolls back, so every test starts from the same rows. The repository is that of Subsection 4.16.15.

tests/Database/ProductRepositoryTest.php, one rolled-back transaction per testPHP

<?php
use PHPUnit\Framework\TestCase;
use Shop\ProductRepository;
final class ProductRepositoryTest extends TestCase {
  private static ?PDO $pdo = null;              // one connection for the whole class
  private ProductRepository $repo;
  protected function setUp(): void {
    self::$pdo ??= PDO::connect(getenv('DB_DSN'), getenv('DB_USER'), getenv('DB_PASS'));
    self::$pdo->beginTransaction();
    $this->repo = new ProductRepository(self::$pdo);
  }
  protected function tearDown(): void {
    self::$pdo->rollBack();                      // undo whatever the test wrote
  }
  public function testRestockAddsToTheStock(): void {
    $this->assertTrue($this->repo->restock('BK-SQL-02', 5));
    $this->assertSame(5, $this->repo->findBySku('BK-SQL-02')?->stock);
  }
}

Running the database suite, then checking that nothing was left behindPHP

vendor/bin/phpunit --testsuite database --testdox
sudo mysql -t shop_test -e "SELECT sku, stock FROM products WHERE sku = 'BK-SQL-02'"

Output

...
Product Repository
 ✔ Restock adds to the stock
OK (1 test, 2 assertions)
+-----------+-------+
| sku       | stock |
+-----------+-------+
| BK-SQL-02 |     0 |
+-----------+-------+

The test saw stock 5 inside its transaction; the table still says 0. Beware: DDL such as CREATE TABLE or TRUNCATE commits implicitly and ends the transaction.

MENU