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.

  • 4
    If you’re talking to SQL, then those are integration tests by definition, not unit tests.
    – Telastyn
    Commented May 4, 2023 at 15:25

7 Answers 7


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.

  • This is an excellent point. At the end of the day, all that I need to know is that the hypothetical C# code has called the relevant SQL code with the correct arguments. As long as I can trust the SQL itself, then all is good. Have I understood you? If so, do you have any examples of this being done that you can link to?
    – J. Mini
    Commented May 4, 2023 at 14:30
  • While there are places where this can be useful, for example testing that code which translates a user defined query language to SQL works correctly, such testing becomes brittle when you have to update the SQL for any reason, and doesn't actually test that the SQL works. So whenever you find a bug or add a feature to your SQL, you need to update two lots of tests, one to test the SQL and one to test that the rep emits that SQL. Commented May 11, 2023 at 12:52
  • I'll accept this and award the bounty because you gave me the "Aha!" moment I needed. However, I'm not sure how far the "make a fake SQL server" idea can be taken. All of the procedures that I want to call live on a real server and there's no way that I'm copy and pasting their code in to my unit testing framework. All that I can imagine doing is logging what procedure my unit tests call and with what arguments, which appears to be what you suggest. If you have any links that cover a practical implementation of this idea, preferably in C#, then they would be a great addition to this answer.
    – J. Mini
    Commented May 12, 2023 at 15:10

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.


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.


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


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.


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.


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.

  • I think I'm completely missing your point. How do I know that my update has worked without reading what I've updated?
    – J. Mini
    Commented May 4, 2023 at 14:29
  • Yo do read. You do update then read the result. I'm just stressing that only the "updating" part is under test. The reading part is just a mean to access data
    – JayZ
    Commented May 5, 2023 at 8:16

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

  1. 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.

  • 1
    I think I'm completely missing your point. How does this change make the tests atomic? Furthermore, the code for updating lives on the server (it's in SQL), so avoiding the server might be impossible.
    – J. Mini
    Commented May 4, 2023 at 6:46
  • Steps {1, 2, 3, 5} are performed by the test infrastructure, independent of the SUT which is your app code. Step 4 is an RDBMS update issued by your app code, the thing we wish to test. You created the foo-updated.csv file when you originally authored the test. Now we're verifying the app produces exactly those bits. There is no separate DB server with its associated state, since middleware like sqlalchemy lets us use a throwaway sqlite file. That's what the step 1 rm was about. Feel free to combine initial 2 or 3 steps if you prefer to cp a frozen sqlite file into place.
    – J_H
    Commented May 4, 2023 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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