Non-standard session handling

[seallard]

Description

Sessions are currently handled in a non-standard and non-explicit way in cg, which risks causing issues with incomplete transactions etc.

The main issues consists of:

• Sqlalchemy tied into store class.
• Commits are made in random places in the code base. I think we want to either commit everything or nothing if something goes wrong, but I'm not entirely certain.
• The current design makes it difficult to rollback our database fixtures between tests, so we instead create the entire database and populate all tables for all tests which is really inefficient.

Suggested solution

Implement a standard database module and explicitly provide a session argument to each method interacting with the database, alternatively retrieve a globally managed session. For example, see CG:s Genotyping API or a Facebook project for a more standard approach to database setup and session management.

In general, a session should be created and torn down for each request and CLI invocation. Once the request or command has been processed successfully, the session should be committed. Otherwise it should be rolled back.

This can be closed when

• Sessions are handled in a explicit and standardised way in CG

Blocked by

[islean]

Linking #2580 since a potential cause could be that we commit each file, rather than all files at once in finish-all, coupled with bad timing.

[seallard]

The current way of commits being spread through out the code base is necessary since we have long running cli commands (10 hours +). The teardown always does a rollback on any open session in the request and cli command teardown.