What’s the best strategy for unit-testing database-driven applications?

by

I’ve actually used your first approach with quite some success, but in a slightly different ways that I think would solve some of your problems:

  1. Keep the entire schema and scripts for creating it in source control so that anyone can create the current database schema after a check out. In addition, keep sample data in data files that get loaded by part of the build process. As you discover data that causes errors, add it to your sample data to check that errors don’t re-emerge.

  2. Use a continuous integration server to build the database schema, load the sample data, and run tests. This is how we keep our test database in sync (rebuilding it at every test run). Though this requires that the CI server have access and ownership of its own dedicated database instance, I say that having our db schema built 3 times a day has dramatically helped find errors that probably would not have been found till just before delivery (if not later). I can’t say that I rebuild the schema before every commit. Does anybody? With this approach you won’t have to (well maybe we should, but its not a big deal if someone forgets).

  3. For my group, user input is done at the application level (not db) so this is tested via standard unit tests.

Loading Production Database Copy:
This was the approach that was used at my last job. It was a huge pain cause of a couple of issues:

  1. The copy would get out of date from the production version
  2. Changes would be made to the copy’s schema and wouldn’t get propagated to the production systems. At this point we’d have diverging schemas. Not fun.

Mocking Database Server:
We also do this at my current job. After every commit we execute unit tests against the application code that have mock db accessors injected. Then three times a day we execute the full db build described above. I definitely recommend both approaches.

More Related Contents:

  1. What is an ORM, how does it work, and how should I use one? [closed]
  2. Why should you use an ORM? [closed]
  3. Best way to test a MS Access application?
  4. Microservices with shared database? using multiple ORM’s? [closed]
  5. Is it possible to generate django models from the database?
  6. How to unit test an object with database queries
  7. Mocking database in node.js?
  8. “Cannot drop database because it is currently in use”. How to fix?
  9. Storing Images in DB – Yea or Nay?
  10. What’s the best mock framework for Java? [closed]
  11. Would you store binary data in database or in file system? [closed]
  12. Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old)
  13. Fabric.js – how to save canvas on server with custom attributes
  14. How to disable Django query cache?
  15. Elastic search, multiple indexes vs one index and types for different data sets?
  16. Import SQL dump into PostgreSQL database
  17. How to simulate a DB for testing (Java)?
  18. Multivalued Fields a Good Idea?
  19. Elasticsearch query to return all records
  20. Mocking Java enum to add a value to test fail case
  21. How can I mock user input (from “input” in 3.x, or “raw_input” in 2.x) for a unit test?
  22. What is the best way to unit-test SLF4J log messages?
  23. How to test or mock “if __name__ == ‘__main__'” contents
  24. Versioning Database Persisted Objects, How would you? [closed]
  25. What is the default Precision and Scale for a Number in Oracle?
  26. How to mock Controller.User using moq
  27. Count array elements that matches condition
  28. SQL DataType – How to store a year?
  29. Any example of a necessary nullable foreign key?
  30. Mysql service won’t start, InnoDB init function error

Leave a Comment