2

I want to ensure that my program code is correctly inserting, removing, and modifying data in my database. Full integration testing can be slow, and I have concerns about keeping mock objects and data current because my database is evolving rapidly.

Is it ever proper to test database interaction by setting a database record and then immediately retrieving the same record for comparison within the program code?

I imagine this doubles the overhead associated with modifying a record. However, my database is small and infrequently modified. Implementing a proper database integration test appears non-trivial (and slower) than simply retrieving the modified record and inspecting the change in the program code.

Thoughts?

Improve this question
7
  • Your database operation either works or gives an error/exception. That's how you know. Your questions seems to imply that your code works but still the database operation doesn't but also gives no error so you have to double check. It doesn't make sense. You are asking about a solution, but what is your problem? – Bogdan Dec 30 '18 at 17:10
  • 1
    My concern is almost exactly the opposite. I want to ensure that my code to modify the database will work round trip. For example, I am a new user of neo4j. I can update records in ways that are legal, but not what I intended. I could create a method that adds a value to the wrong list. Since I know the value I just added must be present, I can retrieve the list and verify it is present. Just because the database does not throw an error upon retrieval of the list does not mean that all list items are being properly updated (i.e., I may have broken the update method subsequently). – user2514157 Dec 30 '18 at 17:38
  • I could create a method that adds a value to the wrong list. Since I know the value I just added must be present, I can retrieve the list and verify it is present. And what guarantees that you don't make a mistake also when you retrieve the list, and you check the wrong list too where you just inserted a new value which is, of course, present. So the test works but you are still in the wrong list. I think you might be overthinking this. Maybe rephrase your question to add more details. As it stands it's not clear what you are asking – Bogdan Dec 30 '18 at 17:51
  • bug: >>>get_list() 1, 2 >>>add_to_list_in_databas(3) >>>get_list() 1, 2 If the add_to_list() method compares get_list() before adding 3 to get_list() after, and they match, then there is a bug. This would not detect that get_list() and add_to_list() are both pointing to the same wrong list, but it would detect at least some errors (e.g., they are adding and getting different lists). Quickly and easily detecting this error (without doing a full mock up or integration test) would have value to me. None of the testing books I read teach this as an option. Why? – user2514157 Dec 30 '18 at 18:29
  • Because, in most cases, it's a bad idea. What you are doing is an assert. You have code in your application which asserts that other code in the application is working correctly. That extra code hurts readability and also hurts performance. And extra code can also add extra bugs. And you said it yourself, it detects "some errors", not all errors. Tests should be separated from the final code. It's better to pay more attention to the code you write than to write even more code trying to catch moments where you were "asleep at the keyboard", so to speak. – Bogdan Dec 30 '18 at 19:38
4

Is it ever proper to test database interaction by setting a database record and then immediately retrieving the same record for comparison within the program code?

Yes, absolutely. I see software as a stack of layers. The lower the layer, the more pervasive the consequences of a bug. The persistence layer is usually the bottom-most layer.

There are all sorts of bugs which can happen at the persistence layer. Missing predicates in WHERE clauses, missing database constraints, improper handling of constraint violations. I've had many cases where, to optimize performance, I've needed to change the underlying schema (perhaps denormalizing), and changing queries to work with it. By encapsulating all data access into data access objects (DAO's) and testing all DAO's, I can refactor to my heart's content and trust that the persistence layer is solid.

To me, the holy trinity is:

  1. 100% test coverage of data access layer
  2. DAO tests run everything in a self-rolling-back transaction (some frameworks make this easier than others)
  3. CI pipeline drops and rebuilds a dedicated test database for every build

I had this on a couple of past projects and it was hyper productive and helped us achieve extremely good quality.

(No - I am not advocating for 100% test coverage in general. Just saying that if you can trust all data access, a lot of complex problems disappear and refactoring becomes easier)

Improve this answer
2

A test suite would not usually contain tests for database insertion and reads, unless you are implementing a database driver or similar.

However, tests of your actual code that involve the real database are not that uncommon. Such tests would be integration tests or end to end tests of your system. Some testing approaches like BDD even encourage end to end tests as the primary testing method.

The difference here is what specifically you are verifying through the tests. Avoid tests that verify that a third party component works correctly, or that your understanding of the component is correct. (For that, just use toy programs). Instead, use tests that verify that your software works as intended together with the external component (such as a specific database version).

Will such end to end tests be slow? Possibly, yes. But there are various strategies for speeding such tests up. You can configure the database with in-memory storage. You can arrange the tests into stories that follow the typical use of your software, rather than resetting the database after each small unit-level test. Finally, your test runner may be able to parallelize the tests, to exclude slow tests, or to prioritize more important tests.

Will this imply lots of extra effort? No, because you will just be testing your software (which may use the database as an implementation detail). If the database is used incorrectly your software will not be able to produce correct outputs or responses, and your tests should show this.

Improve this answer
2

Having your main code write, read back, and then compare, is self-validating code. Yet you should write some unit tests that such logic works correctly. If you have a unit test that round trips your data to a database, and confirms the validation code works, then you don't need to deploy the self-validating code. You would know the logic to round trip data is correct so you no longer have a need to deploy the self-validating code. You can avoid the computational overhead of deploying self-validating code. Better yet your unit tests would find any bugs before they went live and disappointed your users.

With the self-validating approach you should write both positive and a negative unit tests. The negative one would have to return bad data from the database. That might require you to mock the database to return bad data. So it not clear that properly testing the self-validating code would be less programing effort than writing some basic test that your database access logic works as expected.

There are extreme cases where self-validating code might be justified such as life support systems. Clearly they must also be fully unit tested and integration tested. The self-validating logic is more to protect against things like hardware failures.

What is likely the true problem here is that working with your database seems too hard. Yet there are lots of tools and frameworks that try to make it easier to write some data access tests. It's probably a good investment in time to look into some frameworks to make testing against a database easier.

With Java I have seen applications that use Oracle or DB2 have database access tests that run against in memory databases like H2 or Apache Derby. This was a valid approach as the tests were checking that objects could be queried and saved using an object mapping technology (e.g., Hibernate or JPA). It prevented human error of modifying application code or the database schema in a way that broke database persistence. In effect the tests trusted that the object mapping technology worked consistently with different database drivers and backend relational databases.

Such tests worked well as the majority of bugs were either column name bugs, or column type bugs, within in the database mapping configuration. Those are caught running against any database. The sort of bugs that were not properly tested using different databases in the unit tests are things like numerical precision that varies between database engines.

Improve this answer
2
  • You are absolutely correct - "What is likely the true problem here is that working with your database seems too hard." As an neophyte, I am trying to practice unit testing, and using my tests to improve my program, without getting sucked into a black hole. I am balking at mocking out my elasticsearch and neo4j databases because I have never mocked anything out, am still learning these databases, and my data structures are not yet stable. I appreciate your direction. – user2514157 Dec 31 '18 at 0:04
  • please up vote helpful or well considered answers. – simbo1905 Dec 31 '18 at 8:13
1

In our application we have DB integration tests, which run along the unit tests in every build.

Therefore we have a test database, which is recreated on every build (takes a couple of seconds).

For these DB accessor methods and the underlying stored procedures we intend a 100% coverage as bugs here are much easier to detect and if remain undetected would have a big impact to the overall application.

Recently we had a large bug in the application, which was very hard to investigate, but turned out to be a simple bug in a if/else condition in the stored procedure. Unfortunately the test was incomplete, as the data combination causing this bug was rare in live data.

Having all the DB accesses tested makes us much more confident in the overall application.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.