How can unit tests be atomic in server-dependent CRUD apps?

Question

Suppose that the following are true:

1. You believe that unit tests should be atomic. That is, tests should always test exactly one thing.
2. You have written a CRUD app in a general-purpose language such as C#. In particular, the app can Read and Update.
3. Your app stores all data on a SQL server. Your language therefore needs a way of talking to SQL such as methods that nicely wrap and call SQL procedures.

Under these conditions, how can you write atomic tests? For example, I cannot imagine a way to test your app's Update functionality without also testing the Read functionality. You can't test if your Update has worked without Reading the result.

Answer 1

If you are doing unit tests, as distinct from integration tests, then you should only be testing one module of code. Anything outside that module can be mocked.

So if your code sends messages to an SQL server, there's nothing to stop you creating a fake SQL server that logs any messages sent to it, and which makes up data packets whenever it's queried. That fake server becomes part of the unit test framework.

As for testing "exactly one thing", that depends on what you call one thing. One function, one class, one app? I prefer doing things that deliver value more than following dogma.

Answer 2

Your definition of "atomic" is problematic since it is unclear what the "one thing" is. Is "works according to specifications" one thing? Is a single instruction one thing?

Unit testing is one type of automated tests, the other common type is integration testing. But in my opinion the important part is that tests are automated. I would argue that "unit test" is sometimes used where "automated test" would be more appropriate.

Unit tests

Tests some type of "unit". This might be a small method, or something much larger. The important thing is that it is fairly self contained, with few dependencies to the outside and a well defined behavior. These dependencies are then mocked, so that only the unit is tested.

Testing smaller units have the benefit of making a bug easier to fix once it has been found. While this is useful, it is in my experience a larger problem to find the bugs in the first place. I can stand a little bit of debugging if I understand the expected behavior of the unit well.

You should write tests the interface of the unit, and avoid testing internal implementation details. Naturally this might involve multiple methods, and that is not really a problem. If you often need to update existing tests when making changes to code it may indicate that the tests and/or interface is poorly designed.

Integration tests

Tests how units talk to each other and interact.

In practice there will be disagreements of what is a unit test or integration test. In my opinion the difference is mostly irrelevant. The end goal is to find bugs faster and cheaper. Any automated test that does this is a good one. Call it whatever you want. A better division might be according to runtime. Group tests that are slow to run and run these less frequently.

Testing of databases

When using a database it is often a good practice to separate the database parts from the rest of the application. This means you should be able to test most of the application with a mock of the database.

To test the database parts you can just use an actual database for your testing. Have the tests create a new database, populate it with some fake data, and run some queries to verify that everything works. This will be much easier if you have an automated way to create databases from scratch. This is often useful since database abstractions are leaky. Something that works with one database vendor might not work with another. It can also be very useful when developing to have a easy and fast way to test any changes. Just make sure to have some cleanup strategy to avoid accumulating obsolete test data.

Conclusion

Don't focus to much on terminology, and focus more on writing tests that add value for you and your particular application.

Answer 3

The idea of tests being 'atomic' refers specifically just to the code under test, and not to the test itself - meaning that only one behaviour should be tested.

That isn't the same as requiring tests to only "do one thing" or even for tests to only have one behaviour; indeed, the premise of automated testing is that the verification of those tests is also automated, therefore it is logically impossible for any test to only do one thing or have a single behaviour.

Atomic unit tests nearly always have at least 3 different categories of behaviour, typically grouped as Arrange, Act, Assert.

• Arrange all necessary preconditions and inputs.
• Act on the object or method under test.
• Assert that the expected results have occurred.

Atomicity of a unit test refers just to the Act behaviour, as that would typically be nothing more than a single action such as a raising an event/trigger, sending a message, or calling a function/procedure.

Just to be clear, any code and behaviours which fall into the arrange/assert categories are not under test. A test may perform a significant number of different operations and behaviours as part of the arrange/assert steps, and still be atomic because those behaviours serve only to set the preconditions of the test and then to verify the outcome.

In the given example, reading data from a database to verify a successful update operation would mean that you have an atomic test which is only testing 'update'. Any database commands or queries used to either set up the database or verify test outcomes are not included in the definition of code under test.

Answer 4

As for unit testing concerns, whatever is going on and happens beyond your data access layer is irrelevant, and you should not spend time verifying.

[code under test] -- update --> [DAL:Concrete] ---> [Datasource] ---> [Datastore]

I cannot imagine a way to test your app's Update functionality without also testing the Read functionality

Well, it depends entirely on the code you are testing. If the code being tested ...

• Is black-boxed (w/o inputs and outputs), then you need to verify the update at the DAL. In other words, you assert that certain DAL functions have been accessed with very concrete arguments. E.g. a statement (e.g. SQL) or an object.

  [code_under_test()] -- update --> [DAL:Mock]   
  //assert what DAL methods have been accessed and their arguments
  //or write the result in a file and assert do assert the content
  

• Has output arguments, then you inspect the output argument after the update.

  [code_under_test(outputArg)] -- update --> [DAL:Mock]
  //assert outputArg
  

• Returns a value, then you validate the correctness of the update by inspecting the result.

  [obj code_under_test()] -- update --> [DAL:Mock]
  //assert obj
  [bool code_under_test()] -- update --> [DAL:Mock]
  //assert bool
  [number code_under_test()] -- update --> [DAL:Mock]
  //assert number
  

• Has output arguments and returns a value, then you validate the correctness of the update by inspecting both.

  [obj code_under_test(outputArg)] -- update --> [DAL:Mock]
  //assert obj & outputArg
  
  [bool code_under_test(outputArg)] -- update --> [DAL:Mock]
  //assert bool & outputArg
  
  [number code_under_test(outputArg)] -- update --> [DAL:Mock]
  //assert number & outputArg
  

In any case, unit tests should not depend on a real DB. Think about it. The db may or may not be reachable from all the workstations/build servers, one test can poison the results for future tests (a larger or smaller row count you don't know what causes it), or you may feel tempted to make tests order-dependant so before test A is required before test B and C, causing temporal coupling.

Before going too serious about You believe that unit tests should be atomic. better work on

• Unit tests don't depend on the infrastructure (external folders, db, remote services, network, etc.)
• Unit test should be deterministic
  • Unit tests should be executable anytime, anywhere and in any order

I find'em to be a better "dogma" to drive your designs and tests.

Answer 5

I cannot imagine a way to test your app's Update functionality without also testing the Read functionality. You can't test if your Update has worked without Reading the result.

I can. Come with me as I conduct a thought experiment.

Imagine databases don't exit. No ones made one yet. But someone published a paper detailing how you'd talk to one if it did. One of the ways to talk to it is this update command. The paper details what it should look like.

You can write code that issues that update command. And you can write tests that show it was issued correctly. All without an actual database. All you need is code that accepts the command (a mock) and tells your test about what it got. There isn't any read functionality. The mock wouldn't know what to do with a read command if it got one. It just knows how to tell the test, right away, exactly what it got. That's not a database. It's not a read. It's an echo.

Answer 6

For example, I cannot imagine a way to test your app's Update functionality without also testing the Read functionality. You can't test if your Update has worked without Reading the result.

You are only testing the "update" part. In your "update" test, "reading" is not under test, it's just a way to retrieve results as you would plainly call a method.

Of course this assume that "read" can be trusted. In your case it means you have tested it already in another test. For the "reading" test you won't need "update", you just need existing data, so no cyclical logic here.

Answer 7

Simplify your datastore. Use a local DB, and .CSV files.

2. You have written a CRUD app in a general-purpose language ...

... such as C# or python.

I have written a bunch of Flask apps that hit postgres / mysql databases via SqlAlchemy. It's a very convenient ecosystem.

One of the nicest things it offers is vendor portability, as long as you stick to conservative phrasing like SQL92 and don't go crazy with vendor extensions. Change the connect string, and now you're hitting a sqlite backend.

Back to unit tests. You're going to need a way to put a database into a predictable state, repeatedly. There's more than one way to do that: code, cp, .sql files, .csv files, even mocks though those take time to author. Assume we have a tiny amount of infrastructure that can establish zero-length table(s) with proper schema. And that we have one-liners which can populate table from .CSV and also dump from table to .CSV. And that a utility like /usr/bin/diff is available.

Now testing dozens of Update methods is trivial:

1. rm database.sqlite
2. create empty table(s)
3. populate table foo from foo-original.csv
4. run your atomic Update unit test
5. dump-and-diff against foo-updated.csv

And repeat (some of) those steps for the next unit test. The .CSV files will definitely be checked into source control.

If the update affects timestamps or new GUIDs, you will need a diff that understands which fields to ignore. For example the dump utility might project down to just a subset of a table's columns.