Staying OO and Testable while working with a database

Question

What are some OOP strategies for working with a database but keeping things unit testable? Say I have a User class and my production environment works against MySQL. I see a couple possible approaches, shown here using PHP:

1. Pass in a $data_source with interfaces for load() and save(), to abstract the backend source of data. When testing, pass a different data store.

   $user = new User( $mysql_data_source );
   $user->load( 'bob' );
   $user->setNickname( 'Robby' );
   $user->save();
   

2. Use a factory that accesses the database and passes the result row to User's constructor. When testing, manually generate the $row parameter, or mock the object in UserFactory::$data_source. (How might I save changes to the record?)

   class UserFactory {
       static $data_source;
   
       public static function fetch( $username ) {
           $row = self::$data_source->get( [params] );
   
           $user = new User( $row );
           return $user;
       }
   }
   

I have Design Patterns and Clean Code here next to me, but I'm struggling to find applicable concepts.

Answer 1

So what you want to pick up is Martin Fowlers' Patterns of Enterprise Application Architecture (He also provides a catalog on his website here).

In it he describes several patterns for abstracting data access. The first approach you describe is Active Record. Your second approach is similar to Table Data Gateway.

An even better approach is to use an O/RM to remove the need for writing data access code by hand. I haven't used PHP since we were worried about Y2K, but wikipedia has a list of options for you. I don't know if they're any good though. I can tell you some things to look for in an O/RM however:

• Persistence Ignorance: the O/RM shouldn't force your business objects to derive from a specific interface/class in order to participate in the data access strategy.
• Relation Mapping: You should be able to map relationships between your objects (a customer has orders, orders have line items, line items have a product, etc.)
• Hierachical Mapping: You should be able to map class hierachies to the database.
• Query Syntax/Criteria Support: You should be able to create a query at run-time in terms of your Objects not in terms of the database, and the O/RM should translate and run the query on the database. Extra points if the query is a strongly typed chain instead of a string.

There are other factors to consider, but those are some of the most important. Hope this helps.

Answer 2

IMHO it depends on what you want to test, if you want to unit test your business logic you should stub/mock (Martin Fowler) you data access so your first suggestion is nice start. This stackoverflow question gives a nice C# example (I tried to find some PHP samples but could not find any).

If you want to test the data access in itself it isn't called unit testing any more but integration testing. Read here for some general guidance, this stackoverflow question also has some interesting links.

If you want to test stored procedure logic in your database look at xUnit TestPattern

Hope this helps

Answer 3

This isn't necessarily an immediately helpful answer, but if you are really concerned about database testability, you should look into how it is done in Ruby on Rails. As far as I know, no one has covered the topic better or more intuitively.

Answer 4

I recommned you check the Symfony Framework solution to this kind of problem. Symfony its a php OO framework with functional tests.

Here is a link, they used something like what you are thinking about.