Unit testing Magento with the database

This year I want to dive more into unit testing in PHP. Writing good, test-driven code is important for the stability and guarantee of your project, but it also helps you – as a developer – sleep a little better at night. You might have seen earlier posts regarding unit testing in PHP in the past, like how to write unit testable code and some simple basics on how to write unit tests for Magento. More recently, I also wrote a smaller post on how you can manipulate the Magento configuration on the fly so you can write unit tests that address Magento’s database. In this article I’ll go more in-depth on this subject, explaining how I wrote some very nifty unit tests for Magento, that needed to access the database.

The case I was working on

A big client of ours had a very specific wish on how to calculate discounts for their customers. The discount was calculated according to the organization a customer was assigned to. So the price of product X could differ between customer A and B.
As if that was not enough: the product catalog was also populated by external suppliers, and some suppliers could sell the same product. If that was the case, the supplier that had the cheapest product would get the sale, but customers could also get different discounts for different suppliers. Therefore it could happen that even though supplier A was the cheapest kid in town, the discount applied to you, as a customer on  supplier B could cause supplier B to become the cheapest, and therefore get the deal!
Are you confused? Well don’t worry, It’s just a little background story, but it shows you that when the wish of the client becomes complex and there are many parameters to consider, it’s easy to slip in an error or two.

Unit testing and databases

Right from the start it became apparent that we had to write unit tests for this module. But how can you test something like this? All the prices of products, suppliers, organizations and the discount rules that combine them all together are stored in the database, and most of the calculations were – for speed reasons – done in huge SQL-queries that would join multiple tables and had several cases in them.
For me, when I’m writing a unit test, I want to keep the test so simple and self-contained as possible. I want to be able to say: “when I execute this function with these parameters I expect this result”. But when you take databases in the equation things get pretty complex – pretty fast. A lot of numbers and relations that influence the calculation are stored in the database and if there are some mutations in your database (some record changes or some relation gets removed) chances are that your test will no longer turn green. It’s also important that when you work in a team (like I do), your co-workers can also run the tests. And chances are that their local database differs from yours. So how do we deal with that?

Create test tables in the database

The first thing you have to do to overcome this problem is create dedicated tables in your database with testdata. I just created new tables according to the schemes of the tables used by our module, prefixed them with “test_”, and filled them with dummy data. With this data it’s possible for you to calculate the expected result. You can add a SQL-file of these tables to your repository so your co-workers can also add these tables to their local development environment (or even better as you will read further on: have your unit test create these tables for you!).
That’s it: you now have data that can be used by the code that you want to test.

Create your testsuite

Now you have the testdata in your database, you can create your testsuite. In my situation I created a folder called Tests  in my module folder, with the file phpunit.xml :

It’s a very basic setup as you can see. As I said before, I like to keep things as basic as possible. What I do is the following:

• I call a file called bootstrap.php . This file is executed prior before the entire testsuite.
• I define the constant MAGENTO_ROOT , which is used by Magento (usually this constant is defined in index.php , but we’re not using that entry point in our test so we have to declare it manually).
• I set the entry point of my testsuite to the current directory.

The bootstrap

The bootstrap.php  file is executed prior before the entire testsuite, and it does the following things:

• Initialize Magento.
• Reset the test tables for each testrun (in case we’re testing some CRUD operations for example).

It looks like follows:

The test

The beauty with this bootstrap is, that each time the test runs, it (re-)creates the test tables in the database. Therefore we have exact knowledge of the data that we’re testing against, and therefore we can calculate the expected results that our test should return.
But first a little background information: like I said before, the discount calculation is done in huge SQL-queries within Magento. If you want to write unit testable SQL-code in Magento you have to fetch the table name from the configuration of the module. Even more so: you always have to do it this way, because it’s the Magento way:

This is because the getTableName() -function in the above method will map discount  and rule  to the following XPath in your configuration:  global/models/discount_resource/entities/rule/table  to fetch the correct table name. But you probably already knew this. The reason I’m pointing this out is because like I said before, you can change the configuration of Magento on the fly. Doing this allows the getTableName() -function to return a different table name for your unit test then it would do normally.
Let’s say for example the the XPath global/models/discount_resource/entities/rule/table  would return as table name “discount_rules” and I created a (test-) table called “test_discount_rules” then I could make Magento use my test table by executing the following line in my script:

This only affects the current script execution and the configuration value is not saved to the disk. This makes it perfect for using in a unit test. Consider setting up a unit test like this:

Putting the above code in the setUp() -part of your unit test makes sure Magento uses your test table on any occasion where the getTableName() -method is used (which usually is in the SQL queries). And that’s actually the only preparation you have to do for your test. You can now just test your Magento methods as you would use them in your code, and test if the results returned are what you expect. Take a look at these 2 tests for example, which check if the discount is calculated correctly:

And there you have it, perfectly unit testable code in Magento and a unit test that queries the database, just like it would happen on the production site, but with calculated test data that can be added to your repository and shared with your co-workers. Amazing!

In conclusion

In this article I showed you a case I ran into and that I had to unit test. I showed you how I handled the testing of complex SQL-queries against test data in the database, and still keep the test self-contained and simple.
In my defense I have to admit that I’m still relatively new to unit testing. I started in 2014 with the subject and it’s one of my goals of 2015 to write as much test-driven code as possible (or at least for the most critical code that should be unit tested). Therefore it could be that my approach might seem quirky or you have a much better solution or insight on how to deal with these kind of subjects. If you have any comments on this article, don’t hesitate to share them with me in the comment section below this article. Because I’m always eager to learn and improve my programming skills.