Mock MySQL DB for PHPUnit

Question

I'm trying to build unit tests for my Yii project.

Problem: MySQL database. I don't want to have to run a MySQL database every time I run the tests as it is slow, unreliable, maybe some team members don't have it set up, etc.

There seems to be a way to do a SQLite DB in memory and use that, but the SQL produced by Yii doesn't seem to work on SQLite the same it does on MySQL. I get loads of errors.

In short: I want to mock a MySQL database in memory.

How can I do this?

Answer 1

Encapsulate your MySQL operations in a Data Access Object. Not only do you hide the SQL from your business logic which has other benefits, you can use mock DAOs when testing the rest of the application. Mocks won't require a database and allow you to validate that the business logic is making the correct calls to the data layer.

This is similar to Mchl's answer but moves the mocking up one layer. I find it much easier to mock findUserByEmail() rather than the various mysqli methods. You can leave SQL verification to the DAO tests and run against the database in your integration tests.

Answer 2

One way to do is is to mock your connection object and check if it's query() method is called with expected SQL. You need another way to validate that the SQL you're expecting is correct though. This can be done as a separate group of tests (or even moved outside your testing suite) so that it is not run with all other tests.