How to write a PHP unit test for a method that uses live database data?

How exactly do I write a test for a method that uses live database?

Consider this code:

class PricingRepository extends GenericRepository
{
    public function getOptionPrice(int $productId, int $quantity, float $productPrice = 0.0): float
    {
        //retrieves option record for a given product
        $row = $this->getMySql()->paramQuery("
            select * from pricing
            where product_id = ?", array(
            $productId
        ))->getSingleArray();

        //based on pricing type computes appropriate value
        if ($row['pricing_type'] === 'Quantity-based')
            return $row['base'] + $row['amount_per_quantity'] * $quantity;
        if ($row['pricing_type'] === 'Percentage-based')
            return $productPrice * $row['percentage'];

        throw new InvalidArgumentException("invalid pricing type detected");
    }
}

I have A LOT of methods like the one above, and so I want to ensure that my tests are solid and will not change when the database data changes. I am looking for advice/solution towards best-in-class unit testing method and one that perhaps does not rely on changes of data in the database.

The way I could write a naive unit test now could be something like this:

use PHPUnitFrameworkTestCase;
class OptionPricingTest extends TestCase
{    
    function setUp()
    {
        $this->pricingRepository = new PricingRepository();
    }

    function testOptionPricing()
    { 
        $actual_option_price = $this->pricingRepository->getOptionPrice(111, 1, 100);
        $this->assertEquals(10.0, $actual_option_price);
    }
}

But if the data or pricing type changes, my test will also have to change.

Answer

The design of your repository makes it hard to test.

Use Dependency Injection

Consider not to create the database connection within the repository class, but to inject it through the constructor instead.

interface DBInterface
{
    public function paramQuery($query, array $params = []): DBInterface;
    public function getSingleArray(): array;
    // ...
}

class GenericRepository
{
    /** @var DBInterface */
    private $mysql;

    public function __construct(DBInterface $mysql)
    {
        $this->mysql = $mysql;
    }

    protected function getMySql(): DBInterface
    {
        return $this->mysql;
    }

    // ...
}

Then it gets easy to inject a mock object.

Mock Dependency

For the testcase above, the mock could look like this:

class MysqlMock implements DBInterface
{
    private $resultSet = [];
    private $currentQuery;
    private $currentParams;

    public function paramQuery($query, array $params = []): DBInterface
    {
        $this->currentId = array_shift($params);
    }

    public function getSingleArray(): array
    {
        return $this->resultSet[$this->currentId];
    }

    public function setResultSet($array records)
    {
        $this->resultSet = $records;
    }

    // ...
}

This way, you’re independent from actual changes of prices and removal of products. You just have to change the test, if the structure of your data changes.

use PHPUnitFrameworkTestCase;
class OptionPricingTest extends TestCase
{    
    private $pricingRepository;
    private $mysqlMock;

    public function setUp()
    {
        $this->mysqlMock         = new MysqlMock;
        $this->pricingRepository = new PricingRepository($this->mysqlMock);
    }

    public function testOptionPricing()
    { 
        $this->mysqlMock->setResultSet([
            111 => [
                'pricing_type'        => 'Quantity-based',
                'base'                => 6,
                'amount_per_quantity' => 4,
            ]
        ]);

        $actual_option_price = $this->pricingRepository->getOptionPrice(111, 1, 100);
        $this->assertEquals(10.0, $actual_option_price);
    }
}