Reduce spin up and teardown of databases by using transactions

[efagerberg]

What action do you want to perform

I imagine spinning up and tearing down a database each test function is more expensive than spinning up one for the session (or n when using multiprocessing through something like pytest-xdist). I believe pytest-django does something like this, and uses transactional function scoped fixtures which are rolledback as cleanup. Although I have also heard of using snapshots to do something similar/

This would lead to cases where setting up the initial database state for a test file is simpler and can be module scoped.

What are the results

Currently the library needs to spin up and tear down databases per each test function as the fixtures are function scoped.

[fizyk]

@efagerberg have you tried this approach: https://github.com/ClearcodeHQ/pytest-postgresql#using-a-common-database-initialisation-between-tests ?

Maybe I should specifically state, that it also ought to speed up the test initialisation 🤔

[efagerberg]

Thanks for the link that is helpful but the common set up use case. However I think in general if I had n tests using the db fixture with distinct set up states that would mean n test databases would need to be spun up correct? Whereas one db that is set up at the session start and torn down at the end seems like it would reduce the time spent in all those n tests.

What I really like about the library is how easy it is to use. But sometimes I get into situations where there are module or session scoped fixtures that I can't have reference the db fixtures because they are function scoped and sometimes I see some tests I have that need a test db that take some time and it would be awesome to get that time spent reduced.

I can see the argument that if I'm testing a lot of database things I'm probably missing some service module to abstract that database operations, but sometimes I like to wait until I get 2 or 3 similar use cases to abstract the db fetching to a module.

[fizyk]

@efagerberg that's true, or you'd need at least several schemas within a database.

I can see that the noproc fixture factory could be used to create more templates databases for specific cases, although that is neither intuitive nor simple.

Maybe a dictionary argument for load (or a 2nd dict_load to the fixture factory to be used instead of dbname and load) that would look something like that:

{
    "db_name": ["load.sql", ... ],
    "other_db_name": ["other_load.sql", ...]
}

Maybe this would do the trick, then for each test you'd use separate client factories each with appropriate dbaname defined 🤔

That something worth thinking about (and maybe pursuing if one would find time)

[fizyk]

Another question, what will the subtransactions be good for if the code you're testing creates its own connections. This would be the best way to use PostgreSQL, you've got a tests connection you use to upload data fixtures and then check if the changes are what is expected and you've got the connection used exclusively by your code (even in a separate process) that operates on the database.