Sometimes with many different web programs which are driven by databases of different complexity around the after sales. Typically, there's an ORM layer outside of the company and presentation logic. This will make unit-testing the company logic fairly straightforward things could be implemented in discrete modules and then any data required for the exam could be photoshopped through object mocking.

But testing the ORM and database itself happens to be fraught with problems and compromises.

Through the years, I've attempted a couple of methods, none which completely satisfied me.

  • Load an evaluation database with known data. Run tests from the ORM and ensure the right data returns. The disadvantage here is your test DB needs to maintain any schema alterations in the applying database, and can get free from sync. Additionally, it depends on artificial data, and could not expose bugs that occur because of stupid user input. Finally, when the test database is small, it will not reveal issues just like a missing index. (OK, that 4g iphone is not really what unit testing ought to be employed for, however it does not hurt.)

  • Load a duplicate from the production database and test against that. The issue here's you will probably have no clue what's within the production DB at any time your results might need to be rewritten if data changes with time.

Many people have stated that these two methods depend on specific data, along with a unit test should test only functionality. To that particular finish, I have seen recommended:

  • Make use of a mock database server, and appearance that the ORM is delivering the right queries in reaction to some given method call.

What methods perhaps you have employed for testing database-driven programs, if any? What's labored the good for you?

I have really used the first approach with a relatively good success, however in a rather various ways which i think would solve a number of your problems:

  1. Keep your entire schema and scripts for creating it in source control to ensure that anybody can make the present database schema following a take a look at. Additionally, keep sample data in documents that will get loaded by area of the build process. While you uncover data that triggers errors, combine it with your sample data to check on that errors don't re-emerge.

  2. Make use of a continuous integration server to construct the database schema, load the sample data, and run tests. This is the way we keep our test database synchronized (repairing it at each test run). Though this involves the CI server connect and possession of their own devoted database instance, I only say that getting our db schema built 3 occasions each day has significantly assisted find errors that most likely will not have been found till right before delivery (otherwise later). I can not state that I rebuild the schema before every commit. Does anybody? With this particular approach you will not need to (well maybe we ought to, nevertheless its not really a large deal if a person does not remember).

  3. For my group, user input is performed in the application level (not db) making this examined via standard unit tests.

Loading Production Database Copy:
It was the approach which was used inside my last job. It had been an enormous discomfort reason for a few issues:

  1. The copy would get free from date in the production version
  2. Changes could be designed to the copy's schema and wouldn't get propagated towards the production systems. At this time we'd have diverging schemas. Difficult.

Mocking Database Server:
We do that inside my current job. After every commit we execute unit tests from the application code which have mock db accessors injected. Then three occasions each day we execute the entire db build referred to above. I certainly recommend both approaches.