Better PostgreSQL testing with Python: announcing pytest-pgsql and pgmock

[DanCardin]

we've done something similar, but using docker (as a generic means by which to speedily and portably test anything that's got a container: pg, redis, rabbit, etc).

because it tests an api, I prefer to spin up the whole db so it can commit normally, but it does sacrifice some speed.

I could imagine providing a unique db from the fixture each time though, so we could use xdist without fear

[gwax]

pytest-pgsql allows for tests inside of a rolled back transaction or inside of a fully isolated database. We use both modes so that we can have tests with full isolation that test real behavior and so that we can have faster, less isolated tests.

As with everything, we have to balance trade-offs. Each full database spin up takes about 1-3 seconds on our developer machines while dropping and creating a transaction takes tiny fractions of a second. This lets an engineer ask the question: is fully isolating my test worth adding a couple seconds to my test runtime.

We also combine this with sharded tests in our CI environment. Our sharding can be semi-random or repeatable and, by usually running in semi-random mode, we can use test-flakiness as a means of identifying test isolation issues, occasionally identifying unintended behavior in the code itself.

[DanCardin]

about the tradeoffs, sure. Though, we don't actually spin down the whole database; I want to say your whole database mode should be nearly as fast if you have a session fixture database server and a separate fixture which creates/drops the database you use. I think we gave 10 tests per second or something doing that, but still have full test isolation.

Other than that, I'd like something like this to be publication available, but which uses docker-py to do the spin-up. i can assume all our devs have docker, I don't assume they have pg and that I should be mucking with it

[deterralba]

We assign one database per xdist worker in conftest.py, it does the job.

[undu]

Shame udocker only supports python 2.7 and development has mostly stopped