With Annotated Container verging on a 2.0 release I have been dogfooding it in 3 or 4 personal applications. Using it has helped confirm some of my design ideas in practice. I'm confident that Annotated Container is a powerful dependency injection container that can offer a lot of interesting capabilities.

In each of the applications I'm creating with Annotated Container I've reached a point where I want to start testing database interactions. I'm a firm believer in testing everything that's possible to be tested and the vast majority of my code, and any that I actually believe is quality, was written with Test Driven Development (TDD). Database interactions shouldn't be any different. Unfortunately, the story for PHP database testing is kind of lackluster. Specific frameworks may have ways to assist with these kind of tests, but if you're looking for something framework-agnostic you're kinda out of luck.

My projects that have reached the point of database testing have all re-implemented slightly-different variations of the same database test scaffolding. I was tired of this unmaintainable approach and wanted something that could work in any of my projects. The rest of this article introduces cspray/database-test-case with how to get started by creating an example test suite with database interactions.

Writing tests with DatabaseTestCase

In our example tests we're going to look at interacting with a table designed to store blog posts. This table is not meant to be representative of a normalized schema you'd want to use in a production app. However, it will show how we can use cspray/database-test-case to ensure we work with a clean database state, easily load fixture data, and make assertions on the state of a table's records. I'm going to use a PDO instance and PostgreSQL for our tests. This is the database and connection type that's supported out-of-the-box with additional connection instances and databases supported in the future.

First, let's take a look at the table schema we're gonna be testing against.

CREATE TABLE blog_posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255),
    author VARCHAR(255),
    contents TEXT,
    created_at TIMESTAMP DEFAULT now()
);

Like I mentioned, a simple schema that'll be easy to demo some tests, but not something you'd want in a production-level app! Now, let's take a look at a test case that interacts with this table. There's no code under test here, I want to show you the functionality available from the test case itself.

<?php declare(strict_types=1);

namespace Cspray\DatabaseTestCase\BlogPostDemo;

use Cspray\DatabaseTestCase\DatabaseTestCase;
use Cspray\DatabaseTestCase\LoadFixture;
use Cspray\DatabaseTestCase\SingleRecordFixture;
use Cspray\DatabaseTestCase\DatabaseRepresentation\Row;
use PDO;

final class BlogPostsTestCase extends DatabaseTestCase {

    protected static function getConnectionAdapter() : ConnectionAdapter {
        return new PdoConnectionAdapter(
            new ConnectionAdapterConfig(
                database: 'postgres',
                host: 'localhost',
                port: 5432,
                user: 'postgres',
                password: 'password'
            ),
            PdoDriver::Postgresql
        );
    }
    
    public function testUnderlyingConnectionInstanceOfPdo() : void {
        // Pass this to your own code under test that expects a PDO instance!
        self::assertInstanceOf(PDO::class, self::getUnderlyingConnection());
    }

    public function testGettingTableRepresentationForAssertingOn() : void {
        // An immutable representation of every row in the given table at a given point
        $table = $this->getTable('blog_posts');
        
        // The $table is countable and will return the number of rows it currently has
        self::assertCount(0, $table);
    }
    
    // Load data into the database before your test starts
    // Pass as many Fixture instances as you want!
    #[LoadFixture(
        new SingleRecordFixture(
            'blog_posts',
            ['title' => 'My blog post', 'author' => 'cspray', 'contents' => 'Test all the things']
        ),
        new SingleRecordFixture(
            'blog_posts', 
            ['title' => 'Another post', 'author' => 'cspray', 'contents' => 'Something, something testing databases']
        )
    )]
    public function testLoadingFixtureDataForSingleTest() : void {
        $table = $this->getTable('blog_posts');
        
        self::assertCount(2, $table);
        
        // You can iterate over $table to get access to each Row present
        $expectedTitles = ['My blog post', 'Another post'];
        $actualTitles = array_map(
            static fn(Row $row) => $row->get('title'),
            iterator_to_array($table)
        );
        self::assertSame($expectedTitles, $actualTitles);
        
        // Or you could get a specific row and assert on that
        $anotherPost = $table->getRow(1);
        self::assertSame('Something, something testing databases', $anotherPost->get('contents'));
    }
    
    public function testRefetchingTableAfterPersistence() : void {
        // In database tests it is common to assert that your table is empty...
        $beforePersist = $this->getTable('blog_posts');
        self::assertCount(0, $beforePersist);
        
        // perform some code that persists something ...
        yourCodeThatTakesPdoInstanceAndPersistsOneRecord(
            self::getUnderlyingConnection()
        );
        
        // and assert on post conditions. Remember to refetch a new table!
        $afterPersist = $this->getTable('blog_posts');
        
        self::assertCount(1, $afterPersist);
        self::assertCount(0, $beforePersist);
    }

}

And there you have it! A basic example of how to write tests with database interactions using cspray/database-test-case. The repo has integration tests that confirms all the functionality described works when actually interacting with a database.

Next Steps

Next up is to start replacing the adhoc database test scaffolding in my projects with cspray/database-test-case. I plan on continuing to provide more connection adapters and better utilities for asserting on the state of a database. I've listed some immediate plans in the repo's issues. Happy testing!