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?


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'));
"INSERT INTO `user` (`id`, `email`, `password`) VALUES (1,'','553ae7da92f5505a92bbb8c9d47be76ab9f65bc2');
INSERT INTO `user` (`id`, `email`, `password`) VALUES (2,'','f4542db9ba30f7958ae42c113dd87ad21fb2eddb');"

        $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=;dbname=' . self::DEV_DBNAME . ";charset=utf8",
                \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->db = new \kurapov\Database(new \PDO(
            'mysql:host=;dbname=' . self::TEST_DBNAME . ";charset=utf8",
                \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) {
            "INSERT INTO `" . self::TEST_DBNAME . "`.$table
            SELECT * FROM `" . self::TEST_DBNAME . "`.$table"


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


  • 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


  • 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

Testing your file system with vfsStream

If you take care after your project and code, then you write unit tests. But with them, there are «special cases». Filesystem and resources are one of them. Solving it straight on is done by making separate folder tree just for tests, hoping that they are stable enough to run after fails and that they will not touch and delete actual paths.

A more correct approach is to use in-memory virtual file system, vfs for short. And since resources are, by their nature, streams, so is the name of mock library for it - vfsStream. So lets install it..

composer install mikey179/vfsStream

Now we need to initialize it vfs. Note that all functions operate within the context of its root folder. If you start making virtual folders without providing it, they will not appear in the result tree.

public function setUp() {
    //vfsStreamWrapper::setRoot(vfsStream::newDirectory('root', 0777));
    //$this->rootDir = vfsStreamWrapper::getRoot();
    $this->rootDir = vfsStream::setup('root', 0777);


Now we need to setup initial tree state..

    'library' => [
        'bb2075d7d7023ebd5929f6a3f4c4d499' => [
            'size' => [

#PHPUnit_Framework_Error_Warning : unlink(vfs://root/library/bb2075d7d7023ebd5929f6a3f4c4d499/size/160.jpg): No such file or directory

If we would try to delete the file right away, we would see the error above. That happens because vfs emulates also file permissions and users. So you can't just delete file - the code doesn't know if its the same user that created files in the first place. And thats where second problem appears - there is no short syntax for providing permissions.

So simple and hacky solution would be to create folders and then add files one by one with permissions 777.

vfsStream::newFile('original.jpg', 0777)->setContent('test')->at(

Similarly, there is a method newDirectory for making folders. With rights, there is another problem - how do you see entire tree? Thats what *Visitor classes are for. For example they allow you to format tree as an array..

$result = vfsStream::inspect(new \org\bovigo\vfs\visitor\vfsStreamStructureVisitor())->getStructure();

    'root' => [
        'library' => [
            'f420b5caa94fb3ac74fe4fb602e38fe8' => []
], $result);

I've tried making my own version that would print out a tree as a string, maybe it will be merged into master..

\=root @777
.\=library @777
..\=f420b5caa94fb3ac74fe4fb602e38fe8 @755


Main problem with getting used to vsf is paths. vfsStream doesn't support chdir() or realpath()
But worse of all, you need to wrap all absolute paths, inside your code.. Meaning you need to refactor what you wrote the first and easy way

vfsStream::url('root/test.txt'); //will become vfs://root/test.txt

That means that you can't work with relative paths

You need to wrap paths with some function. And that means you can't concat path parts. If you do, once you inject path parts from your test, you'll get

PHPUnit_Framework_Error : Object of class orgbovigovfsStreamDirectory could not be converted to string

How do I overcome this? Well' I add wrapper function first. It's not pretty, but it adds some path abstraction. For regular code I return path from the input and for tests I can inject wrapper function that I want

public function removeSizeDir($path){
    if (is_dir($this->fullPath($path . '/size/'))) {
        rmdir($this->fullPath($path . '/size/'));

public $pathRewrite = false;

 * Wrap all filesystem access to change path in one place
 * Used actively with unit tests to wrap absolute paths to virtual file system
 * @param string $path
 * @return string
public function fullPath($path){
        $tmp = $this->pathRewrite;
        return $tmp($path);
        return $path;

This wrapper is injected on test setUp..

public function setUp() {
    $this->o              = new myObjectUnderTest($this->rootDir);
    $this->o->pathRewrite = function ($path) {
        return vfsStream::url('root/' . $path);

 * @test
public function removeSizeDir(){
            'library' => [
                'bb2075d7d7023ebd5929f6a3f4c4d499' => [
                    'size' => []
        ], $this->rootDir
    $result   = vfsStream::inspect(new \org\bovigo\vfs\visitor\vfsStreamAssertVisitor())->getStructure();

        $expected = <<<EOF
\=root @777
.\=library @777
..\=bb2075d7d7023ebd5929f6a3f4c4d499 @755
        $this->assertEquals($expected, $result, $result);

Good & quality code coverage to you :)

Connecting tests using @ticket annotations with Jira

Jira from Atlassian — is the most advanced task and bug tracker, which is agile enough to adapt to organization workflow. But if you are not using Bamboo yet, preferring PHPCI for continuous integration, then it might be beneficial to see test results grouped by feature.

This is quite a contraversial topic, because some test-enthusiasts don't understand why would you need to tie tests to features. These developers think simply that «all tests must pass», or just think its overcomplicates things without any benefit.

I think this method does help to increase transparency of feature coverage. If a developer is making a new feature, then it doesn't mean that he will cover it with tests immediately. And even code is 100% unit-test covered, it doesn't mean that there is no bug. You need to write integration tests and those don't usually generate coverage report. So seeing that at least some tests are created for feautre X, is useful for boosting confidence. This is useful if you are a developer who is not following TDD "test first" rule and if you are a manager and quality reporting is not transparent and granular enough.

My second point is that code, tests and features get old. You need to recycle old parts of application. Usually unit-test coverage helps to find code that never gets executed. With integration tests, again, there is no such flexibility — your code is covered, but it is never executed on the client/frontend side (deprecated API, service got migrated). In such cases, usually no one wants to risk removing enitre module and it lives on in your codebase as a zombie. Thats why high-level feature deprecation, requires deletion of tests and code. Annotation in this case is what helps you to tie everything together.

Finally, jira task annotation is an informative link, an extended way to comment your code, where you can post a screenshot, read full history of changes, see a big picture. Sometimes I practice linking complicated code areas and decisions with jira issue.

An interesting side effect which i noticed after using such technique - you are forced to organize your issues, which is a good thing. You don't feel comfortable making duplicate issues, distilling issues it too many subtasks or concentrate everything in one. You want to tie tasks among each other. Specification is thus based on tests, but without cucumbers.

Installing on PHPUnit

To tie tests, we'll need API client for Jira..

composer install chobie/jira-api-restclient

Now in tests/bootstrap.php, which is for bootstrapping phpunit, let's insert custom authentication for API and include installed library:

define('JIRA_LOGIN', '');
define('JIRA_PASS', '');
require __DIR__ . '/../vendor/composer/chobie/jira-api-restclient/src/Jira/Api.php';

In order for Jira to store data for every issue, we need to add a custom field. To do that, go to Settings and add new custom field, Tests..

Jira custom.png

Заметьте под каким номером поле сохранилось - он будет использоваться в коде. В моём случае это customfield_10402.



Next, we need to add extra functionality to each test class. But since we can't break singular inheritance from PHPUnit, we'll use traits. And in order for tests to refrain from pinging API after each test execution, we'll cache results in json file and make a network request in the end of test suite run. Substitute all constants and URLs.

trait JiraConnect {

    private $ticket;
    private $currentTest;
//    private $failed = false;

    static $testResults = [

    //Store test results in file for cross-class results
    function saveJira($status) {
        if (!$this->ticket) {

        $storage = PATH_APP . '/tests/ticket_status.json';
        if (!file_exists($storage)) {

        $s = json_decode(file_get_contents($storage), true);

        if (!isset($s['testResults'][$this->ticket])) {
            $s['testResults'][$this->ticket] = [];
        $s['testResults'][$this->ticket][$this->currentTest] = ($status ? 'ok' : 'fail');

        file_put_contents($storage, json_encode($s));

    static function updateJiraTestStatus() {
//        $name = $this->currentTest;

        if (!defined('JIRA_LOGIN') || JIRA_LOGIN == '') {
//            echo('Please define JIRA_LOGIN, JIRA_PASS in bootstrap.php to update JIRA issues');

        $api = new \chobie\Jira\Api(
            new chobie\Jira\Api\Authentication\Basic(JIRA_LOGIN, JIRA_PASS)

        $storage = PATH_APP . '/tests/ticket_status.json';
        $s       = json_decode(file_get_contents($storage), true);

        foreach ($s['testResults'] as $ticket => $testResults) {
            $sResult = "";
            foreach ($testResults as $class => $status) {
                $sResult .= $status . " — " . $class . "\n";

            $updObj                    = new stdClass();
            $updObj->customfield_10402 = [
                ['set' => $sResult]

            $r = $api->editIssue($ticket, [
                "update" => $updObj

    function usingMethod($class, $method) {
//        echo $class;
        $this->ticket      = $this->getTicket($class, $method);
        $this->currentTest = $class . '::' . $method;

        return $this;

    function getTicket($class, $method) {
        $r   = new ReflectionMethod($class, $method);
        $doc = $r->getDocComment();
        preg_match_all('#@ticket (.*?)\n#s', $doc, $annotations);

        if (isset($annotations[1][0]))
            return $annotations[1][0];

    function tearDown() {
        $className = explode('::', $this->toString())[0];
        $this->usingMethod($className, $this->getName());

    static function tearDownAfterClass() {

    function onNotSuccessfulTest(Exception $e) {

        if (method_exists($e, 'getComparisonFailure') && $e->getComparisonFailure()) {
            $trace = $e->getComparisonFailure()->getTrace();
        } elseif (method_exists($e, 'getSerializableTrace')) {
            $trace = $e->getSerializableTrace();

        if (isset($trace)) {
            $method = $trace[4]['function'];
            $class  = $trace[4]['class'];

            $this->usingMethod($class, $method)->saveJira(false);
        throw $e;

Now the integration test..

require_once 'JiraConnect.php';
class EndpointConnector extends \PHPUnit_Framework_TestCase {
    use JiraConnect;
     * @test
     * @depends login
     * @group security
     * @ticket MY-389
    function postAdd_SQLInjections() {…}

After each success or failure, JIRA issue will be filled with the results. Its better to have such configuration on staging server, to see latest state

Task view.png

Disadvantages — although you can search through «Tests» field, filtration is not as nice. There is no highlighting (like status has above). There is no view for all features and tests, no execution logs. Its not a full-blown CI server integration. Another problem - running tests fill «Activity stream», which mixes actual user changes. Finally, it runs only with phpunit so far and I haven't done anything to protractor e2e tests, which would be even more beneficial in Jira

SEO friendly redirect with PHP

If you are swithing domain for your resource, then its essential that all of your content is moved seamlessly, so that the end user doesn't notice any difference, but so should do the search engine. Here is how I do it..

if($SERVER['HTTP_HOST'] == '') {
    header("HTTP/1.1 301 Moved Permanently");
    header("Location:" . $_SERVER['REQUEST_URI']);

This will also have your query-part of the URL moved (?page=1 for example). Unfortunately, if you had hash (#) based navigation, you'll have to resort to javascript solution

Integration testing of web app for injections

If you've got a web app that you want to perfectly cover with tests, here is what you should have:

  • backend unit-tests — mostly models and isolated classes are covered - code gets isolated (also single responsibility principle is maintained)
  • frontend unit-tests — karma + phantomjs will check all of your angular-services or backbone-models
  • e2e (scenario, system) tests — most likely based on selenium (protractor, selenide). Slowly, entire functionality of working system with UI gets tested, so you tend to think about use cases
  • db/entity tests with migrations — your DB changes are added to base "from zero" dump and result state is compared to entity/record classes. This way your code and your DB schema is in sync
    • testing db-procedures I don't mention because i'm not into PL/SQL, but maybe you should
  • integration tests for external systems/apis - of any type (rest, ftp, soap) and source (social networks, accounting, warehouse, SMS-gateway) is tested for:
    • availability (like pingdom does)
    • structure compatibility (simple GET with json comparison is enough)
    • full write-interaction (usually partner-company sets up a testing machine with their developer)
  • integration tests of controllers/api — are executed without browserm through plain HTTP requests, that emulate  ajax or mobile devices
    • simple get - requests, checking for errors and stack traces
    • post/put requests that change data
    • in messed-up cases (with mobile devices), when e2e tests are impossible to run, but functions require testing, you get sequential scenario requests (not single get-post ones), that save entity and user states (in DB or session)

So lets take a look at last ones

Unit-testing controllers is inconvenient

Testing controllers with unit test is although fast in execution, is terrible at writing and maintenance. Yes, I've heard Uncle Bob saying that you need to cover code entirely, but controller is a place where multiple sources join:

  • configuration (from file includes, yaml, database, constants) — that means that we either need to execute & include all files, or that we need to mock all possibilities with constant-value replacements
  • new instances of models - you've got to mock those too, since you have separate unit tests for models
  • global IO variables and methods - you need to put them in some untestable models and mock them
  • global and/or static variables, factory-instantiation - same as with configuration, its complex
  • aspects - annotations, access, logging, reflection-based logic — you'll need to come up with some magic mocking there as well. For example - template names for methods can be in annotation, how do you unit-test that template engine gets called?

But worse of all ofcourse is not the mocking itself, but its amount. If your controller method has 5 models - thats the least amount of mocks you need to define. In addition, for every model method call, you need to write not one line of its emulation, but several — which and how many times was that method called, with which arguments and what got returned.

Sometimes mocks should return an object (just like PDO can return PDOStatement for example) and call its method. Now you have mocks depending on one another. Often, i get confused because of the order of their registration. Since calling testable method should be at the end of the test, registering mocks is supposed to be before it, so you get test method that follows testable code upside down. To put it shortly — writing unit-tests for controllers is dangerous. (Though some advice looking at phpspec)

How to test controllers

Integration tests are somewhat similar with end-to-end tests, but they don't include UI

  1. So we need a class with CURL functions for HTTP requests (get,post.. if you need delete and put for your API, put them in too)
  2. Write authentication call, if you have one (I use cookie file and thats enough for me)
  3. Add phpunit @depends login , so you don't rape your server if login fails and you've got tons of tests to execute
  4. Simple get-requests with existing DB ids that should tell you if some error got in, that unit tests missed

Now lets see POST/PUT requests. They usually have bigger security vulnerability frequency, because they have more parameters and logic during state change. Adding and changing entities should return some result. Lets say {result:1, id:3} in JSON will mean that object with certain ID got created. Apart from usual tests for saving, we need to push all possible parameters with SQL- and XSS-injections. 

SQL-injections shoule either give us error right away, or after we get/read entity, passed value (in our case 1' OR 1=1) will differ from stored in DB (in this case, possibly "1"). Sometimes, values get type-casted, and so string will become int value and we might need to treat as ok.

With XSS its a little harder. We need a browser to see if injected JS is executed. I solve this by running e2e tests after integration tests and since DB is not reset, system tests should encounter injected alerts that will stop e2e tests. A list of XSS attack tokens is on OWASP.

For automation, i wrote trait for PHPUnit-tests, because its easier to reuse same code in different places. 

trait SQLinjection {
    private $AttackTokens = [ 
        '1" OR 1=1'

    public function checkInfectedUpdate($saveURL,$readURL,$fields,callable $comparisonFn){
        foreach($this->attackTokens as $injection){
            $data = $fields;
            foreach($fields as $k=>$v){
                $data[$k]=($v=='*' ? $injection : $v);            
            $saveResult = $this->post($saveURL, $data);
            $getResult = $this->get($getURL);
            $comparisonFn($injection, $getResult, $saveResult);
    public function checkInfectedInsert(..){..}


Each integration test inherits IntegrationBaseTest -that includes CURL-wrappers and URL of the server. Test method should know how to compare injection with results from saving and getting, because in each entity of API get() calls and formats differ - somewhere its a mere array, while others include hierarchies that you need to iterate (and get a last version)

InvoiceControllerTest extends IntegrationBaseTest {
    private $phpErrorDetection = 'error';
    use SQLinjection;
     * @test
    function login() {
        $result = parent::login();

     * @test
     * @depends login
     * @group security
    function postSave_AddingInjection() {
        $self = $this;

            $this->baseURL . 'invoice/save',
            $this->baseURL . 'invoice/get?id=3',
                'company_id'  => '1',
                'title'        => '*',
                'description'  => '*'
            function ($injection, $getResponse, $insertResponse) use ($self) {
                $this->assertEquals($injection, $getResponse['result']['title']);
                $this->assertEquals($injection, json_decode($getResponse['result']['description']));

As you write controller tests, it seems that you also need to

  1. test for privileges (who should get response?)
  2. refactor controller code and move complex logic to models (because its hard to understand fat controllers)
  3. get rid of stacktrace printing - because client should see sensitive info
  4. fix cases with DB integrity violation, when you're trying to add an entity that refers to other entities, without checking in DB if they exist at all

My suggestion doesn't solve questions with CSRF, redirects, unsalted passwords, SSL, sessions, configuration errors, but it does improve application functional structure in security and logic, even if you use PDO with bindParam() everywhere.