Nothing found, try being more general or more specific

Integration tests with prepared DB

Lately I have been working on integration (API) tests and I like it. I run half of the application, but I'm not tied to UI that might change so often. Its a golden ratio between slow end-to-end tests and very quick but isolated unit tests. Lets see a special type of such tests, which use prepared data for each one of them. 

Tests like these are required when the project grows so big that running tests with one DB can result in unstable tests and conflicts. Somewhere you might get gradual increase in list length, somewhere you want a fixed ID but you have autoincrement in place, or you might want to delete something. 

Its especially noticeable in e2e tests where you are forced to handle entire data lifecycle for tests to remain in working condition. Controlling lifecycle of creation-changes-deletion, forces tests to be dependent on each other, which means you can't run test separately. I had an experience with testing when I wanted to login with custom user role, but there was no prepared test users in DB and there was no user creation in UI either (from adminpanel side). How do you test that, but with generated test data from different users not breaking tests?

Example

Here is my solution..

use kurapov\tests\database\IsolatedDataIntegrationTestBase;

class UserIsolatedDataTest extends IsolatedDataIntegrationTestBase {
    /**
     * @test
     */
    function postRemove_UserByManager() {
        //$this->db->execute(file_get_contents(dirname(realpath(__FILE__)) . '/' . __CLASS__ . '/' . __FUNCTION__ . '.sql'));
        
        $this->db->execute(
"INSERT INTO `user` (`id`, `email`, `password`) VALUES (1,'manager@kurapov.ee','553ae7da92f5505a92bbb8c9d47be76ab9f65bc2');
INSERT INTO `user` (`id`, `email`, `password`) VALUES (2,'user@kurapov.ee','f4542db9ba30f7958ae42c113dd87ad21fb2eddb');"
        );

        $this->loginAs('manager@kurapov.ee');
        $result = $this->curlPOST($this->baseURL . 'User/remove', ['id' => 2]);
        $this->assertNotContains('error', $result);
        $this->assertEquals("{'status':'ok'}", $result, $result);
    }
}

In this case when I run test, database schema already exists — its isolated and clean. I only add data that I will need for my tests and make a curl request. I don't check for result state of DB in this case. If my SQL is too long, I can extract it in a separate file.

Since this test deals simultaneously with network requests (curlPOST function) and preparing DB state, then test class inherits written by me IntegrationTestBase and IsolatedDataIntegrationTestBase respectively. If I would have been working with DB function directly without network requests, I could have used DBUnit.

I prefer to write in raw SQL for mysql, without any abstraction (like doctrine) and without any in-memory DB. Instead, my process looks like this:

  • Before running tests, I run all migrations to have up-to-date schema
  • In test setUp phase, I delete my old test database
  • I copy schema from project's DB into new test database without any data
  • For each test, an SQL is executed that adds specific data (like users or FK-connected entities)
  • I run network request that executes specific API function
  • In network request parameters I pass special value that affects backend configuration, switching it to test database (available only for development mode)
  • If my test fails, I still have last DB state that I can debug

Here is how my inherited file looks like that deals with copying database..

namespace kurapov\tests\database;
use IntegrationTestBase;
use PDO;

class IsolatedDataIntegrationTestBase extends IntegrationTestBase {
    const DEV_DBNAME  = "myproject";
    const TEST_DBNAME = "myproject-test";
    
    public function setUp() {
        $this->db = new \kurapov\Database(new \PDO(
            'mysql:host=127.0.0.1;dbname=' . self::DEV_DBNAME . ";charset=utf8",
            'root','',
            [
                \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                \PDO::ATTR_PERSISTENT         => true,
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION
            ]
        ));

        $this->db->execute("DROP DATABASE IF EXISTS `" . self::TEST_DBNAME . "`;");
        $this->duplicateDB();

        $this->db = new \kurapov\Database(new \PDO(
            'mysql:host=127.0.0.1;dbname=' . self::TEST_DBNAME . ";charset=utf8",
            'root','',
            [
                \PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
                \PDO::ATTR_PERSISTENT         => true,
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION
            ]
        ));
    }

    public function tearDown() {
//        $this->db->execute("DROP DATABASE `" . self::TEST_DBNAME . "`;");
    }

    protected function curlPost($url, $data, $useCookie = true) {
        $data['isolated_db'] = self::TEST_DBNAME;
        return parent::curlPost($url, $data, $useCookie);
    }

    protected function curlGET($url, $useCookie = true) {
        return parent::curlGET($url . '&isolated_db=' . self::TEST_DBNAME, $useCookie);
    }

    private function duplicateDB() {
        $tables = $this->db->execute("SHOW TABLES;");
        $this->db->execute("CREATE DATABASE `" . self::TEST_DBNAME . "`;");

        foreach ($tables as $table) {
            $tableName = $table['Tables_in_' . self::DEV_DBNAME];
            $this->db->execute("CREATE TABLE `" . self::TEST_DBNAME . "`.`$tableName` LIKE `" . self::DEV_DBNAME . "`.`$tableName`;");
        }
    }

    public function copyTable($table) {
        $this->db->execute(
            "INSERT INTO `" . self::TEST_DBNAME . "`.$table
            SELECT * FROM `" . self::TEST_DBNAME . "`.$table"
        );
    }
}

Results

I don't force all of my integration tests to have separate database - only where its needed. Most tests still run on a single DB

Advantages

  • More stable tests, less dependencies
  • Faster execution of isolated tests, compared to chains of dependent ones
  • Writing SQLs in tests affects how you design your schema
  • In case of test failure, state is debuggable
  • Its possible to extend this usage for e2e tests and/or have it run in parallel per test or per test thread

Disadvantages

  • You have to know DB schema and what data is required for particular function. That might be slow, but is something that will affect your application design
  • You have to maintain tests and change SQLs if schema is altered