Automated Testing with Databases

1
Automated Testing with Databases

• Philip Nelson
• Chief Scientist
• Plan Administrators Inc.

2
Contact

• The final version of slides and demo code will be
  available at my blog site, http//tinyurl.com/78zb
  2
• This presentation began as an article in Applied
  Domain Driven Design and Patterns by Jimmy
  Nilsson
• XUnit patterns by Gerard Meszaros

3
Why?
4
What we'll cover

• What do I mean by automated tests?

• When should you include database access in tests?

• What alternatives are there?

• How do you maintain test data?

• How do test with changing database schemas?

5
What do I mean by automated tests?

• Tests are run by a process and the results are
  tallied automatically
• Setup/cleanup for the tests do not require human
  intervention
• Tool support for verification of results
• Most commonly done with testing tools and
  frameworks, for example junit, nunit, TestNG,
  AnyUnit etc.

6
When should you include database access in tests?

• Integration tests of course you are integrating
  various parts of your system and the DB is
  important
• What about unit tests?
• What about acceptance tests, particularly the
  part of a plan where regression testing is
  executed?

7
When should you avoid databases in tests?

• Testing logic and design?
• Running hundreds to thousands of tests?
• Logic in SQL or Stored Procedures?
• Database isolated by layers or tiers?
• Data shared by many people?

8
How should you decide?

• There is no perfect answer
• The basic trade off is test speed vs of
  database accesses and resets
• The more subtle trade off is between unit testing
  and integration testing
• How do I know I'm done?

9
Test flow

• Establish preconditions
• Execute code under test
• Verify
• Clean up

10
Test preconditions

• After 5 unsuccessful logins, the user will be
  locked out
• When inventory has been depleted to the critical
  level, send a message to the order system to
  replenish
• After assets have increased over 100,000 begin
  the process to have the account type changed to
  plan Y
• Manual testing is really hard because the
  required conditions often happen only once

11
public void TestLoginFailCheck()
doLoginSetup() //stuff to test .....
Assert.AreEqual(what I expected,
theTestedThing, not good)
doLoginCleanup() public void
TestDepletedInventory() doInventorySetup()
..... Assert.AreEqual(what I expected,
theTestedThing, not good)
doInventoryCleanup()
12
public void resetEnvironment() ....
//database and other shared setup public void
TestLoginFailCheck() doLoginSetup()
//stuff to test ..... Assert.AreEqual(what
I expected, theTestedThing, not
good) public void TestDepletedInventory()
doInventorySetup() .....
Assert.AreEqual(what I expected,
theTestedThing, not good)
13
Tests should only setup what is unique about the
test

• Yes previous number of failed logins
• Yes current inventory
• Yes current asset total values
• No test logins to work with
• No part numbers needed to fill out inventory
• No names of assets needed to make Asset class
  load correctly

14
Recap

• Automated testing allows you to think about setup
  for groups of tests with a shared setup
• Groups of tests involving a database and a deep
  class hierarchy are too hard to guess exactly
  what setup is required the permutations of all
  the tables, fields and classes that affect the
  outcome are too numerous to manage test by test
• Separate database accessing tests from other
  tests from the very start!

15
Decision point

• Can you reset the whole database or not?
• The whole database should be set/reset to a
  known state before each test.
• Each developer/tester needs their own sandbox,
  and it should ideally be run locally
• If you can't reset the whole database, you will
  have to choose maintaining state techniques or
  mock techniques.

16
Know your reset techniques

• Truncate database (unlogged) and insert test data
• Reset proprietary database files during setup
• Build database and test data from scratch before
  each test
• Run tests in a transaction that you roll back
  when complete (maintain state technique)

17
Other ideas

• Fast, in memory databases (HSQL)
• Ramdisks
• File based databases (Access, dBase, SleepyCat)
  can just be copied if you can disconnect and
  close the file
• Xml/data files on the file system
• Anything where you can easily copy original
  setups can work.

18
Do your tests really need to hit a database?

• To many using a database in unit tests is a code
  smell.
• Use Mock Objects
• Use Dynamic Mock Frameworks
• Use alternate test based repository classes for
  tests
• These techniques make for much faster test speed,
  a major factor when the number of tests gets
  larger
• These techniques can make it easier to get
  consistent data environments for your tests

19
On the other hand

• These techniques can mean a parallel hierarchy of
  mock/stub/fake objects for the real data access
  objects
• If your tests require much data, you will be hand
  coding lots of data values
• As your project changes, your test data will have
  to be changed in your code
• Even if you avoid the database in unit tests, you
  still need integration tests with the database

20
Decision Point

• Test speed vs database connected tests
• DB tests with resets are hard to get done in less
  than 250 ms. and longer is easy to achieve
• Raw data is easier to manage in databases
• Putting your test data in your test code makes it
  easy to find and trace and share
• Keeping the database out of the tests allows your
  code to migrate along paths that make sense for
  code

21
Demonstration of reset techniques

• DbUnit
• Rebuild database from test data

22
Conclusions

• DbUnit is a JUnit extension
• The schema is maintained outside of the tool but
  there are places to execute a create script if
  you prefer
• Also allows tests to compare raw data rather than
  by doing asserts against object model
• Xml, Excel formats for data, though schema is
  opaque to meaning making editing challenging

23
Conclusions continued

• Best practices from DbUnit documentation
• Use one database instance per developer
• Good setup don't need cleanup!
• Use multiple small datasets
• Perform setup of stale data once for entire
  test class or test suite

24
Verification help

• DbUnit also has a set based Assert to compare a
  real data set with a canned data set.
• Set based compares are rare
• With an object oriented program it's often pretty
  easy to compare to object graphs
• Serialize known good state to xml
• Reconstitute the graph
• Write an iterator to compare the real vs expected

25
There are alternate ways to keep test data

• Xml that makes sense to your application
• YAML

26
Sample YAML from Ruby active record

• Read about fixtures at
• http//ar.rubyonrails.org/classes/Fixtures.html
• first_training_log
• id 1
• notes "real easy"
• another_training_log
• id 2
• intensity 4
• notes "a bit harder today"

27
Demonstration of reset techniques

• Reset Sql Server data files

28
Reset conclusion

• Very clean way to reset a whole database
• Can be very fast, though hardware is a big factor
• Test data can be maintained in the DB data files
  and managed with normal database techniques
• The larger the DB files get, the longer the setup
  time will be
• Not all database systems have programmatic access
  to resets in this way
• Not practical unless you have a local database

29
Demonstration of reset techniques

• XtUnit
• Transaction rollback cleanup

30
XtUnit conclusions

• Very simple to do
• Sql Server/MS windows specific
• Your test cannot manipulate transactions
  themselves
• Setup speed issues are replaced by test speed
  issues, it all depends on the size of the
  transactions
• Can be used on a shared database across a
  network, making a very useful choice for some
  situations
• It's possible to use the transaction technique in
  other environments

31
Demonstration of reset techniques

• Reset with domain model and NHibernate

32
NHibernate conclusions

• Setup data is encoded in your actual language
  using your domain model.
• The compiler can catch many of your schema
  evolution issues and the test setup will catch
  many more
• Depending on log issues, the test speed may be
  slower than some other techniques
• Use of Hibernate/NHibernate does make it possible
  to use other DB systems for tests

33
Decision point

• Mock object, or state based testing

34
Demonstration of Mock Techniques

• Domain Layer Repository

35
Demonstration of Mock Techniques

• Record and Playback

36
Mock Object conclusions

• Mock approaches require some degree of parallel
  systems
• Dynamic mock approaches can be less code but have
  limitations you may not be able to live with
• Hand coded mock approaches can do exactly what
  you want, but increase the work
• Mock approaches offer a consistent and very
  performant way to test
• You will be maintaining test data in code or files

37
Mock conclusions....

• You may be able to avoid test data to a large
  degree with behavioral techniques
• Test that a stored procedure is called correctly
• Use a Mock Object for the data access
• Just Verify() that the data access object was
  called correctly and...
• Assume the stored procedure is a different unit
  and you don't need to test the result
• Testing the results is known as State based
  testing
• Testing that the call was done is known as
  behavioral based testing

38
Different decisions for different tests

• Unit Test
• Integration Test
• Acceptance Test

39
Unit Tests the case for no DB

• These are repeated massively so speed really
  counts
• While focusing on logic, dealing with data access
  can be distracting
• A database is not part of your Unit, so don't
  introduce it in the test
• By not using the DB, you can cross check the
  functionality of the unit without the assumptions
  of preconditions that the database might offer
  nulls, default data etc.

40
Unit Tests the case for using a DB

• Some logic requires lots of data for example
  financial calculations and it's easier to manage
  lots of data with tools made for data
• If you can afford the execution time, you are
  exercising your actual system much more, not a
  mocked or stubbed system
• You don't have to fully architect for
  substitution of your data access classes. In
  legacy code, this may be the deal breaker
• It's easy to use live data as examples for test
  cases

41
Unit Tests - conclusion

• Unit test have natural preference to avoid the
  use of databases. However, you can make it work
  up to a point. On new code, I would suggest
  learning to live without, but on code that
  doesn't easily support the architecture, learn
  the tools needed and live with the database.

42
Integration Tests the case for no DB

• There really isn't a case for no DB, these are
  integration tests where parts of the system come
  together.

43
Integration Tests conclusion

• Integration tests are most often run as part of a
  continuous or daily build process. The speed is
  less a factor and exercising real code is
  paramount.

44
Acceptance tests

• There hasn't been much said about databases with
  acceptance tests. I have many stories to tell
  about QA testers that have to go through great
  agony to rerun tests, but generally systems are
  rebuilt daily at most. This is an interesting
  area for further research in the quest of better
  testing productivity.

45
Conclusions

• Can you work with a local database?
• Can you reset a local database with any of the
  techniques presented here?
• Would your architecture or programming culture
  accept mock techniques for testing?
• Separate database connected tests from the others
• What is a reasonable time for your test run to
  take?

46
Schema evolution

• Release day capture current schema from live
• Current live alter scripts to test databases
• Alter scripts as part of daily build
• Depending on the number of changes, you may need
  to run alter scripts against test databases
• Alternatively, automate the daily test schema
• After QA cycle, run alter scripts during release
  to live

47
Questions?