Utilize the PHP in-memory database for comprehensive unit testing

introduce

Unit testing is a fundamental practice in software development to ensure that individual components of code function correctly in isolation. Effectively managing test data is a key aspect of unit testing, and PHP in-memory databases can play a key role in achieving this. In this blog, we'll explore use cases and provide code samples for implementing a PHP in-memory database for unit testing.

What is a PHP in-memory database?

PHP in-memory databases for unit testing are database systems that run entirely in memory (RAM). It is specifically designed to facilitate the creation and management of test data during unit testing. Unlike traditional databases, in-memory databases used for testing do not require installation or teardown scripts, making test installation and cleanup more efficient.

Use cases for in-memory databases

• Isolation: An in-memory database allows you to completely isolate your tests from each other. Clean slate can be used for each test, eliminating interference between tests.
• Speed: In-memory operations are significantly faster than disk-based database operations, reducing the time it takes to perform tests.
• Data consistency: With an in-memory database, you can ensure data consistency by setting a specific state for each test, guaranteeing predictable results for your tests.
• Resource efficiency: In-memory databases are lightweight and do not require external server processes or disk storage, which makes them resource-efficient when running tests.
• Eliminate dependencies: You can eliminate dependencies on external databases, APIs, or services, making your tests more independent and portable.

Implement a PHP in-memory database for unit tests

In our example, we'll be using SQLite as an in-memory database, which is a popular choice for creating a lightweight in-memory database in PHP.

Set up

First, set up the SQLite in-memory database and create a table for testing. Here's an example setup code:

class MemoryDatabaseTest extends PHPUnit\Framework\TestCase
{
    protected $pdo;

    public function setUp(): void
    {
        $this->pdo = new PDO('sqlite::memory:');
        $this->pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT)');
    }
    // ... Rest of your setup logic
}           

Test data processing

You can now write tests that interact with the in-memory database, such as inserting, updating, or querying data:

public function testInsertData()
{
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => 'JohnDoe']);
    $this->assertEquals(1, $stmt->rowCount());
}
public function testQueryData()
{
    $stmt = $this->pdo->query('SELECT * FROM users');
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $this->assertEquals('JohnDoe', $users[0]['username']);
}
// ... Additional data manipulation tests           

clean

Don't forget to clean up after each test to ensure a fresh environment for the next test:

public function tearDown(): void
{
    $this->pdo = null; // Close the database connection
}           

Handle exceptions

You can write test cases to cover scenarios where exceptions are thrown, such as trying to insert duplicate data:

public function testInsertDuplicateData()
{
    $this->expectException(PDOException::class);
    // Insert data
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => 'JohnDoe']);
    // Attempt to insert duplicate data
    $stmt->execute([':username' => 'JohnDoe']);
}
// ... Other exception handling tests           

Test the database schema

You can also write tests to make sure the database schema is correct:

public function testTableSchema()
{
    $stmt = $this->pdo->query('PRAGMA table_info(users)');
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $this->assertCount(2, $columns); // Expecting two columns (id and username)
    $this->assertEquals('id', $columns[0]['name']);
    $this->assertEquals('username', $columns[1]['name']);
}
// ... Other schema-related tests           

Use a data provider

For more complex scenarios or when you have a predefined dataset, you can use a data provider to populate the database with test data before running tests. In this example, we'll test insert multiple users:

/**
 * @dataProvider userProvider
 */
public function testInsertMultipleUsers($username)
{
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => $username]);
    $stmt = $this->pdo->query('SELECT * FROM users WHERE username = :username');
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    $this->assertEquals($username, $user['username']);
}
public function userProvider()
{
    return [
        ['Alice'],
        ['Bob'],
        ['Charlie'],
    ];
}
// ... Other tests using data providers           

Test trades

You can test a database transaction by starting, rolling back, or committing it in a test. This example tests the rollback behavior:

public function testTransactionRollback()
{
    // Begin a transaction
    $this->pdo->beginTransaction();

    // Insert data
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => 'JohnDoe']);
    // Rollback the transaction
    $this->pdo->rollBack();
    // Verify that the data was not inserted
    $stmt = $this->pdo->query('SELECT * FROM users WHERE username = "JohnDoe"');
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    $this->assertFalse($user); // Expecting false as the user should not exist
}
// ... Other transaction-related tests           

Handle exceptions

Test cases can also override scenarios where exceptions are thrown. Here's an example of a test to make sure an exception is thrown when trying to insert duplicate data:

public function testInsertDuplicateData()
{
    $this->expectException(PDOException::class);
    // Insert data
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => 'JohnDoe']);
    // Attempt to insert duplicate data
    $stmt->execute([':username' => 'JohnDoe']);
}           

Test the database schema

You can also write tests to make sure the database schema is correct:

public function testTableSchema()
{
    $stmt = $this->pdo->query('PRAGMA table_info(users)');
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $this->assertCount(2, $columns); // Expecting two columns (id and username)
    $this->assertEquals('id', $columns[0]['name']);
    $this->assertEquals('username', $columns[1]['name']);
}           

Use data to mention

Data providers can be used to run the same tests with different input data. In this example, we'll test insert multiple users:

/**
 * @dataProvider userProvider
 */
public function testInsertMultipleUsers($username)
{
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => $username]);
    $stmt = $this->pdo->query('SELECT * FROM users WHERE username = :username');
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    $this->assertEquals($username, $user['username']);
}
public function userProvider()
{
    return [
        ['Alice'],
        ['Bob'],
        ['Charlie'],
    ];
}           

Test trades

You can also test database transactions by starting, rolling back, or committing them in a test. This example tests the rollback behavior:

public function testTransactionRollback()
{
    // Begin a transaction
    $this->pdo->beginTransaction();
  // Insert data
    $stmt = $this->pdo->prepare('INSERT INTO users (username) VALUES (:username)');
    $stmt->execute([':username' => 'JohnDoe']);
    // Rollback the transaction
    $this->pdo->rollBack();
    // Verify that the data was not inserted
    $stmt = $this->pdo->query('SELECT * FROM users WHERE username = "JohnDoe"');
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    $this->assertFalse($user); // Expecting false as the user should not exist
}           

conclusion

PHP in-memory databases, such as the SQLite in-memory database, provide an efficient and reliable way to manage test data for unit testing. By following the principles outlined in this blog, and incorporating these practices into your unit testing workflow, you can ensure that unit tests are fast, isolated, and self-contained, ultimately leading to more robust and reliable PHP code. Happy testing!