Phpunit testing with database

Question

I am trying to focus a bit on unit testing using PHPunit.

I have found a very good tutorial over here http://blog.nickbelhomme.com/php/phpunit-training-course-for-free_282

But there is something I'm missing and don't yet understand how to do.

I have a user module which maintains all information about users. And there is a function save which saves the user in the database. So I have a testFunction

public function testCanCreateUser()
{
    $userData = array(
        'userName'  =>  'User1',
        'firstName' =>  'Joey',
        'lastName'  =>  'Hendricks',
        'email'     =>  'Joey@hendricks.com',
        'password'  =>  'f$tfe8F'
    
    ); 
    $user = new Model_User($userData);
    $user->save();
     
}

The first time when I will run my test this will work. Since the database is empty. But When I run my tests for the second time it won't work since my system doesn't allow the same user twice in the db. So In order to do this I have to recreate my testdatabase every time before I run my tests. What is the best way to do this?

Or is this problem to be solved on a different way?

score 27 · Accepted Answer · edited May 09 '20 at 21:22

If you want to test your business logic: Mock away the Database class and return fake data

If you want to test the class that fires the SQL statements (and imho you could test that too since I kinda wanna know if my code works fine with a real db in the backend) it gets a little complicated but there are ways to do it:

Using setUp() and tearDown() to get a consistent state for your data before running your tests is (imho) a fine way to write db-driven unittests. It can get annoying to write lots of custom sql by hand though.
To make your life a little easier you can look into the DbUnit extension and see if that works for your Application.
If you really want to dive into Unittesting database interactions the best read on the subject is (imho) the chapter on db-unittesting in Sebastian Bergmanns phpqa book.
Could your application allow for a custom database name and automated setup of all tables it may also be possible to set the db up once with a lot of testdata and use that data in all your tests. You could be carefull so though that one test doesn't rely on data written by another one.

Just wondering how you know about there is a good article in Sebastion Bergmanns book while it is not out yet. But it stays a complicated thing. — sanders, Jan 20 '11 at 17:41

score 14 · Answer 2 · answered Jan 03 '11 at 14:59

Run tests with other copy of the database that is empty and/or cleared in setUp() or tearDown() methods, but be careful not to do what github did.

If you're using a good database (i.e. not MySQL with MyISAM tables) you can wrap test in a transaction and roll it back after the test:

 function setUp() { $this->db->exec("BEGIN"); }
 function tearDown() { $this->db->exec("ROLLBACK"); }

The downside is that you can't test code that uses transactions (unless you abstract that and emulate with savepoints, but that's iffy).

Ideally you should use dependency injection and run tests on fake database class:

$fakedb = new DatabaseThatDoesntReallySaveThings();
$user = new Model_User($fakedb, $userData);
$user->save();
$this->assertTrue($fakedb->wasAskedToSaveUser());

`(new PDO())->beginTransaction()` should work too, even with Mariadb/mysql databases. — sridesmet, Mar 14 '18 at 13:12

score 3 · Answer 3 · answered Jan 03 '11 at 15:03

I think you can use tearDown() method to clean your saved data.

protected $_user;

public function testCanCreateUser()
{
    ...

    $this->_user = new Model_User($userData);
    $this->_user->save();
}

public function tearDown()
{
    $this->_user->delete();
}

Phpunit testing with database

3 Answers3

Linked