This is something that is puzzling me a bit.
I am seeing some people who like to implement DB-related integration tests (in the case of BLOBAs) to test the insertion with a Legacy DB in a CI pipeline, which I think bring very little value (while they require a fair amount of time for implementation) and bring a fair amount of risks, here is below my rationale:
- The premise of the folks implementing those tests: I wanna test that my data are properly inserted into a certain DB, which often translates to: I want to check that the DB set of tables or whatever constraints are ok when I’m executing that piece of DB-related code or containing the aforementioned piece of DB-related code.
- Against which DB (ie. which env) are you running your int. tests?
- Only dev: you’re testing the testing DB constraints, any relevance to the actual prod. operations? Clear benefits?
- Prod: existing data might mess up with your test, also, you test might mess up with the business (risky, especially if the only safeguard, aka your non0completing transaction happens to complete in prod env)
- Everything in between (eg. staging): then it really depends if the staging schema is up-to-date… it’s not always the case…
- When are you running your tests: before or after DB migration? More often than not, it seems that they’re run before the migration in a given env, risky too. So now you have to differentiate integration tests that must be run before or after DB migrations (adding a bit more complexity which is not taken of atm). In the case that these integration tests are failing while being run after the DB migration is done, do you want to revert the DB migration?
I’m wondering if I am missing anything, or if I am wrong about something in particular? Basically since the premise is those tests are actually relevant because they’re testing the DB schema, and maybe not even the one that is gonna be used in prod.
Put it otherwise, when are DB-related integration tests relevant when being run in a CI pipeline?
Note, my post differs from:
- Are (database) integration tests bad? and the best upvoted answer: https://softwareengineering.stackexchange.com/a/301508/171752
- Should integration test be included in continuous integration (CI)?
It’s more a combination of both.
2
First off. Wow!!! your staging environment does not have the correct schema? Stuff tests, work that out first. Your process has bigger issues than testing whether or not a migration works.
The best database tests basically do it all.
- build a pre-migrated dev database, test and record results, migrate, test and record results, compare results unexpected differences = 0.
- build a post-migrated dev db directly, compare to the migrated dev db above -> differences = 0.
- Obtain a copy of production/masked copy compare schema against a pre-migrated dev db. differences = 0
- test prod/masked copy and record result, migrate, test and record results, compare results, unexpected differences == 0.
- compare prod/masked copy schema to post-migrated dev db schema. differences = 0
It ensures that
- the devs can build a version of the db directly.
- That migrate works schematically
- That migration works from a data perspective
- That the devs are working from the same starting point as production.
- That any stored logic behaves consistently/as expected post-migration
Rationale
If your product can integrate with several DB implementations then yes, it makes a lot of sense. What works in one DB engine requires a different accent, or a two step process in another DB engine.
It would make sense to not only have integration tests, but parity tests. Ensuring that relevant behaviour behaves consistently.
If your product needs to work with different versions of the same schema, then yes this makes a lot of sense. Many products need to be able to open older versions to at least migrate them. It makes sense to ensure that this is possible and correct. A brilliant way of doing this is running and recording the results of various operations against A, and contrasting them with the result on migrated B.
If your application is exercising complicated queries that synthesise extracts, or manipulate data across several tables, or has interesting behaviour in its triggers, then yes this makes a lot of sense. That smells of business logic to me. Testing that it works as expected is a very sound rationale.
If your product is only exercising plain CRUD operations then probably not. Its not very valuable, except when it isn’t. I mean you are right in that this is exactly what you expect when you read the label. The problem is that label is affixed to software, and there is one universal in software: Its probably broken.
What that means is one of two things:
- The engine is currently broken and you’ve written your query to make use of the reliably broken implementation. When they fix that implementation your code breaks.
- The engine is currently working correctly and you’ve written your query to make use of the correct implementation. Except something in the hardware changes, or something in the OS changes, or something in the db engine changes; and now your code no longer works.
3