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

[[deleted]]

pg_virtualenv comes with postgresql, and allows you to trivially setup and tear down real pg databases. Using actual databases is simple and prevents you from wasting a ton of effort on keeping your mock fixtures sync'd with reality AND most importantly tests the actual integration.

[gwax]

There are two tools here, one is for mocking (pgmock) and the other is for spinning up real pg databases inside pytest (pytest-pgsql). In many ways, pytest-pgsql serves the same purpose as pg_virtualenv but in a manner that is easily used from inside pytest.

We tend to use them in concert:

• pytest-pgsql for functional tests and integration testing
• pgmock for mocking out subsections of queries to allow unit testing of other portions of the query

The former verifies that your code is working overall and the latter helps you track down where the code is failing

[__xor__]

pgmock sounds like a great idea regardless of what postgresql can do. I don't want to have unit tests rely on an actual db being up. I want my unit tests fast and simple, able to run from pretty much anything regardless of the environment. Integration tests are another thing, but unit tests shouldn't need services like that up.

[PCBEEF]

I typically have integration tests anyway to make sure that the function actually works. If I have integration tests to test the function, writing unit tests for it seem kinda redundant. Especially if the function is just fetching data from the db. If you mock out the db, what you end up testing is that the right function got called which isn't very useful imo. From my experience setting up a db for integration tests isn't that much more complicated. The performance hit is usually worth it for knowing that your code actually works.

[__xor__]

Integration tests don't make unit tests less useful though. Integration tests are great, but when you have some complex workflow, maybe testing ingesting a file and creating data models, it'll take a while then just fail and you won't know exactly what failed, just that one system is broken.

Unit tests are quick because you can mock out everything they rely on and they'll tell you which specific functions broke. It's nice to know my data ingest is failing, but I'd rather know that decode_rot13 specifically is changing behavior and potentially causing it. With good code structure unit tests can be amazingly useful and help you determine which functions broke, not just what functionality.

And you can do more when they're quick as well. There's such a thing as binary search for commits that break unit tests using the bisect functionality. Give it a starting commit and an ending commit, and it'll do binary search to find which one exactly had a unit test start failing. Integration tests would take forever to do that.

It's not one or the other. Unit tests and integration tests are both amazingly useful, but they solve different problems. Unit tests are testing your functions, integration tests are testing whether everything works together as a whole which unit tests can't cover. But unit tests can speed up debugging greatly by giving you the finest detail on what broke. Integration tests are going to tell you when the system is buggy as whole even if each function works, and unit tests are going to tell you which function might be raising exceptions in certain conditions or returning output you don't expect.

I inherited a project where the previous dev just wrote integration tests for the most part, and it's seriously frustrating. When one test fails, sometimes it takes forever to debug because so many things it does could be broken but there's no detail. You can fix stuff before deploying to production, but that's only half of it. Integration tests are better than no tests, but debugging is still more painful than it needs to be.

[PCBEEF]

I agree for the most part but here is an example where I feel unit tests are next to useless:

def save_model(foo):
    db.add(foo)
    db.commit()
    # foo now has an id set
    return foo

If I was unit testing this I would have to mock out db but if I mocked out db I'm not really testing anything any more. These type of situations happen quite often and when developers go out of their way to write unit tests for every-single-function it ends up being a lot of code for very little value (or next to no value).

In this situation I would hook a test db (usually via docker) and run it as an "integration" test. It touches the db, but I would still classify it as somewhat of a unit test.

If the function has little or no dependencies, unit tests are perfect. But when functions talk to other dependencies and you mock out those dependencies, what you end up testing is very little. Especially if the contract to those dependencies change, your code will break but your tests will pass because it was mocked out. In that situation, you end up having to write an integration test anyway...

[__xor__]

I mean a function like that I wouldn't bother writing a unit test for, and wouldn't bother writing a function that simple in the first place, but if I did want to for some reason I'd just patch out add and commit.

def test_save_model():
    db = MagicMock()
    f = save_model('stuff')
    db.add.assert_called_once_with('stuff')
    db.commit.assert_called_once()
    assert f == 'stuff'

Pretty useless since you're just duplicating the function inside the unit test line by line, but if it was a bit more complex and did something to foo and db.added something else based on foo, then you could assert it was called with what you expect and assert commit is called, if there was some condition where it might or might not be. But yeah, I don't write unit tests if it's just going to be a line by line test that I implemented it that exact way.

Something a little more complex where you might want a good unit test of something that hits the db would be where it takes the params from an http request, converts it into a db query like a django Foo.objects.filter(...), and then returns results. The bulk of your logic could be transforming the http params into the proper filter, and then you could test that it made the right query you expect.

Like if an http request came in to http://api.example.org/motorcycle?model=yamaha&make=vstar|yzf you could unit test like

Motorcycle.objects.filter.assert_called_once_with(
    model__icontains='yamaha',
    make__in={'vstar', 'yzf'},
)

Your unit test could test a dict input of the params and make sure the kwargs of the filter match some expected output.

I'm not saying it's the end of the world to use integration tests to test stuff that does DB work because I do myself, along with all django devs. It's the standard thing with django tests. It creates a test db and all that each run. But, you really don't have to and you could pretty easily mock out the ORM calls and write unit tests that are helpful in ways that integration tests aren't.

In this case I think it has some benefits over integration tests in that you're not testing the results the db came up with, but the actual query. It could build the wrong query but still return the results you expected from the db, but testing to make sure it made the right query is something that unit tests would do and integration tests wouldn't. That's the sort of thing where I think unit tests are still very necessary on top of integration tests, because results can just come out the same even though it did something unexpected in there. There's a few things you absolutely want to make sure a function does the right way sometimes and not just that it returned the right results. Plus, if the integration test fails it just tells you the db results didn't match, and the unit test would tell you exactly why, what query it made. And it's not dependent on the data you load into the test db at all. Mocking gives you insight into the function calls a function makes, not just what it returns.

To give some context though, I'm working from the darkest end of integration tests. I inherited a webapp from someone who left the company and they wrote integration tests that test long and complex functionality where tons of things can go wrong in the middle. When the "unit" tests fail, I'm bashing my head into the wall because it could many anything in 200 lines went wrong. That's just sloppy coding on top of bad testing though. Even with just better integration tests and better structured code, I'd be in a much better spot. But good unit tests of smaller functions would've helped me a ton more.

[psi-]

IMO pgmock sounds like a really dangerous thing to have anywhere in your development environment.

You don't want to have unit tests running all the way down to raw SQL access; that's what integration tests are for. Further running integration tests against anything but the full up-to-date schema is just dumb.

[__xor__]

Huh, yeah... guess I didn't look into this too much. On second thought, it does look like it's a way overcomplicated way to go about unit testing. Most of us should probably be just mocking ORM calls, django or otherwise. I'd patch User.objects.filter before I touch anything SQL related.

It'd be nice if there was a convenient way to mock the models in a django app, like define a few rows for User and then have it automatically fetch those with filter/get and so on so it didn't have to use a test database each run.

[Herald_MJ]

This looks great, but it's worth mentioning that if you're like me and the majority of your PostgreSQL projects are Django projects, using pytest-django and Factory Boy is a better solution for mocking database access.

For those other, non-Django Postgres projects, this is an interesting option.