Properly revert database changes after unit-tests

[sjanssen2]

While coding a unit test for the auth_handler, I stumbled upon the problem that some database operations are not easily reverted (maybe intentionally). However, for a proper unit test we want the database to be in the same state after testing as it was prior to it. How can we achieve this?

Here is the conversation with Jose, Daniel and me:
Jose: if you are modifying the database, you should add a tearDown method in the test class that reverts those changes. If you do that, this error will never be raised, so you can remove this code.

Stefan: Honestly, I don't know how I can undo the changed. I have the feeling that there is no python method to undo this specific operation, thus I need to operate on the database myself or revert the complete database?! Any suggestions how I should proceed here?

Daniel:, in the CI environment, the database is reset on every build, and on a local system, this would require probably thousands of runs before its an issue, right?

Jose: Not sure - but I'm more concerned if a change in the order of the tests is going to fire up a stochastic failure. If we don't foresee this issue, what we can do is open an issue, put a TODO comment on the code with the link to the issue and try to fix later, so we don't loose track of that. I don't know from the top of my head how to revert this changes in the database, as that is specific to the code being executed, but I was told that in this repository, instead of reseting the full database in each test, the local changes are reverted

Stefan:I dived into the SQL code. When a kit is registered to an user, the kit is inserted into the table ag_kit and removed from the table ag_handout_kits. During this process, we loose the information in column "created_on". Thus, I cannot revert this function.
To have the DB in the same state as before the test, I would need to write code which anticipates what is going to happen, store the information which is going to be lost somewhere else, perform the SQL operation which I want to test, and create a new function which takes the otherwise stored information to revert the tested SQL operation. This sounds not only quite complicated, but surely introduces potential mistakes + the need to keep code in sync.
What shall I do??

Jose: Thanks for taking a look @sjanssen2 !! I think for the time being you can create a new issue, and put a comment in the code: #TODO: see issue #XXX so we can keep track of it.

I think at some point we can do something like what we are doing on Qiita ATM, reset the DB on each class (rather than test), but I think this will need a longer discussion.

[wasade]

It isn't necessarily the case that the database must be reset after each unit test. In many cases, it is possible to design the unit test to be dynamic. For instance, in the case of an auth test, the specific kit could remain static, and the test can toggle whether that kit is or is not authorized and subsequently validate whether the expected change happened.

Testing gets very weird with databases as in the ideal case, we're unit testing pure functions. That notion is completely thrown out when databases are involved.