When writing tests against the database, should the test to create a resource also retrieve the resource to check the values were correctly inserted?

Question

Where I work, we have some integration tests which spin up a new SQL database instance in Docker, so that we can test the repository logic for write actions as well as read actions. We have a test for the "Create" method, which returns the ID of the newly created resource. Should my test assert just be that the ID of the newly created resource is greater than 0? Or should I also retrieve the resource using the "Get" method, to check that the retrieved data is the same as the written data?

Answer 1

How would you know if your thing was created, if not by looking at it and seeing if it was created?

That is sort of the answer, really.

Feels like testing more than just the method under test, but I suppose there's no other simple way around it.

The underlying basis for your point is valid, but it's one of those considerations that usually cost more effort than it saves, making it moot.

Yes, in theory it would be better if your assertion (i.e. fetching the object to confirm it's correct) wouldn't rely on your codebase. However, that creates an additional dependency as you'd have to rely on the correctness of that external data-fetching tool. And this tool would also need to adapt to changed to the data structure, so there's a fairly tight coupling between your codebase, database and external tool in regards to the schema.

But really, your codebase does contain getter logic already. Could we not just rely on that? Well, if it's tested and proven to work, then we can rely on it.

So your creation test can use your getter logic, provided the getter logic itself is tested. The correctness of the creation test depends on the correctness of the getter test. Since we usually consider any test failure as a failure of the test suite, the gray area here is mostly bypassed.

Answer 2

Testing insert, selects, and deletes is probably OK as well as testing to see whether the ID was created, but in that case the tests are just re-asserting those database functions are working correctly. So, I would consider the value of those tests to be low.

The tests that your application performs should be specific to your application.

For example, if you have a SELECT with a WHERE clause, set up the data to test the WHERE clause to make sure it is filtering data correctly.

If the application is inserting data, not only check to see if the ID was created, but also check the data that was inserted is correct. Verify the data is correct for each column, etc. Those should be more valuable. Also, if you add a column to your database, the insert test would be updated as well. Now there is more value to those test(s) as opposed to checking whether the ID is created.