Database testing in PHP (PHPUnit, DbUnit)

Question

MySQL

I am engaged in service refactoring and decided to write tests for my classes. The class is the simplest, it uses PDO to connect to the database, no frameworks are involved. The choice fell on PHPUnit, which is probably not surprising.

I studied the manuals , looked at the slides .

But not everything is clear exactly according to the methodology of such testing. Judging by what I read and managed to code, you need to:

1) Create a separate test database
2) Draw fixtures (I use yaml format)
3) Run tests

Well, what about next? Let's say the structure of a table changes, for example, a lamb takes and drops the `id` column in the `users` table. It will run the tests - and everything is fine, the fixtures are the same. That is, it is a gamer in the form of constant manual control? - IMHO there is no gut.

And I also saw advice in the presentation, if possible, to avoid testing the database with the use of servers (the emphasis was on execution speed), and use mocks & stubs. This is what I would like to know in more detail - on the basis of what to generate them, how to track changes, and so on? Maybe someone faced a similar task, now everyone is testing, I feel it :)

Thank you in advance for any help.

Answer 1

At one time, the book Perl Testing: A Developer's Notebook helped me a lot to deal with testing . There is not much difference between Perl and PHP in the approach to testing, so you may find it useful too. Unfortunately, I didn’t find a quick translation, perhaps it is only in English.
When testing, one of the key points is that you need to constantly be very aware of what exactly we are testing now. And we usually test our code, not the environment (like a database) - in tests we assume that the database works correctly, the question is whether our code works correctly with the database. Therefore, yes, you can test working with the database even without the database itself - mock functions that send SQL queries to the database, and in the test checking that our code generates exactly those SQL queries, in the order and with the values ​​that it had to generate (and at the same time substituting the data we need under the guise of “response from the database to the SQL query” to the code under test). And yes, on a real database, this code may not work correctly, despite the fact that it passes the tests - simply because it generates the wrong SQL queries that need to be generated for this database server. But this has nothing to do with the tests - they did their job: they confirmed that the code works exactly as expected.
The base bug is not a code testing problem. If you want to write a test against bugs, compare the base schema with the reference one in the test, and correct the test after any change in the base schema. But it makes no sense.
It is very easy to check the compatibility of fixtures from tests with the current database schema: when starting the test, create a test database using the current schema of the main database, and store only data in fixtures, without the table schema.
Hemorrhoids with the need to constantly maintain and adjust tests for compatibility with new code - it exists, and it cannot be missing. But the effort that is spent on this is more than paid off by the benefits of having tests.

Answer 2

Thanks for the detailed answer.
Regarding the automatic generation and comparison of circuits - I also thought about this, and I think it still makes sense. You can also generate fixtures automatically based on the data of the working database, because you need to test a lot of cases, and it is necessary that various relationships between table data be observed. For example, there are users and their posts. It is necessary that the fixtures include users who have one post, several, none. Of course, during the initial formation of such data, the current, that is, not tested code, will be used, but in principle it should work.
But still, the main thing remained unclear to me :(( The task is to test not the code, but the operation of the service, and the fact that the service accesses the database just complicates the matter (“we assume that the database works correctly” - that’s how it works, mysql is debugged by other people, but what calls come there is already my responsibility). What are the ways to test working with the database without using the database? What data exactly needs to be mocked and where to check what?
for example there is such a class

<?php
// file app/User.php
  class User {
  ...
  public function create($params) {
    $this->pdo->prepare("INSERT INTO `users` ...");
    $this->pdo->execute($params);
  }
}

And test

<?php
// file tests/TestUser.php
 function testUserIsCreatedSuccessfully() {
    ...
   $user->create(array('firstname'= > 'John', 'lastname' => 'Doe'));
   ...
   $this->assertTablesEqual($expectedTable, $queryTable);
  }

How exactly and what should be mocked up in a function that tests adding a user? Does this mean that you need to edit the method under test so that it does not write to the database, but does something else? After all, one way or another, the tested method is called in the test code, writing to the database. How to find out that it worked correctly without accessing the database?

Answer 3

In addition to powerman,
I can highlight the following situations:
1) a test of functionality or classes that do not have a strong dependence on external services
2) the same thing, but with too extensive use of DAO
3) integration texts
In the first case, you can easily mock queries to the database or other external services.
In the second case, if possible, I use wrapping in a transaction and rolling back at the very end of the test (setUp and tearDown methods). If not, then the same as in paragraph 3.
In the third case, everything is done "live" in the development environment or use a test base and naturally have a script that generates the environment.

Answer 4

May come in handy:
phpunit documentation in Russian