Mutl databases (multi db) support

[bluetech]

This issue replaces some historical issues: #76, #342, #423, #461, #828, #838, #839 (probably a partial list).

Background

Django supports multi databases. This means defining multiple entries in the DATABASE setting, which then allows directly certain queries to certain databases.

One case is when an extra database is entirely independent, has its own migrations, setups etc.

Second case is when an extra database is readonly, only used for read queries, not managed by Django.

Third case is a readonly replica, for this Django provides the MIRROR setting

Django allows configuring the order in which test databases are set up.

Django's multi-db testing support

pytest-django mostly relies on Django's underlying TransactionTestCase and TestCase classes for dealing with DB setups and such. Each pytest-django test gets run in a dynamically-generated TestCase/TransactionTestCase.

The main setting for mutli-db support is TransactionTestCase.databases. This tells Django which databases to consider for the test case. By default it's only default. It's possible to specify __all__ to include all databases.

Historical note: The TransactionTestCase.databases attribute was added in Django 2.2. Before that a multi_db attribute was used. pytest-django only supports Django>=2.2 so we happily don't need to concern ourselves with that.

Previous attempts

#397 - Adds multi_db=True argument to pytest.mark.django_db(), adds django_multi_db fixture. Problem: uses the old multi_db attribute instead of the databases attribute.

#416 - Very similar to #397.

#431 - Adds django_db_testcase fixture which allows the user to completely customize the test case class, including setting databases. Rejected for being too flexible, I'd prefer direct support for multi-db.

#896 - Adds a global per-database setting for whether to add to the databases value or not. Rejected because I think it should be possible to customize per-test.

Proposed solution

IMO we want something like #397/#416, but modernized to use databases instead of multi_db. The fixture part would be a bit problematic because it's no longer just a boolean (fixture enabled/not enabled), but a list of database aliases. So some solution would be needed for that, or maybe only the mark would be supported.

I'll try to work on it myself, but if for some reason I don't, PRs are definitely welcome!

[jgb]

Following this! It's a showstopper for us, preventing updates beyond Django 3.0. We currently have an internal monkey patching workaround that works with Django <= 3.0 but I can't get it to work when they remove the multi_db parameter.

[bluetech]

Initial PR in #930.

[pmourlanne]

@jgb #397 was developed for your exact use case back in the day :) It is outdated now :zoidberg:

[jgb]

@bluetech I'm testing 4.3.0 specifically for the multi db support. It seems to work, kind of. At least under django 3.0 it works. However as soon as I upgrade to django 3.1 or 3.2, it goes wrong, because somehow my data isn't getting flushed anymore after each test?
Actually it goes wrong with all versions of django: 3.0, 3.1 and 3.2.
Like I run a test which creates an object in the database, and when I run the test again the object of the previous run still exists.
Any idea what might be going wrong here?

It seems like this old issue describes what I'm seeing: #76
4.3.0 does allow me to access multiple db's, but it doesn't properly clean / flush them in between test runs.

[jcushman]

Awesome! Thanks for working on this, it looks great.

I converted a multidb project over to the experimental API and it seems to be working, including flushing data between test runs. (I was previously using the workaround of TransactionTestCase.databases = TestCase.databases = set(settings.DATABASES.keys()) in a session-scoped fixture.)

It's also working correctly with pytest-xdist, which is very cool.

The fixture part would be a bit problematic because it's no longer just a boolean (fixture enabled/not enabled), but a list of database aliases. So some solution would be needed for that, or maybe only the mark would be supported.

FWIW I definitely struggled with the lack of a fixture version. Just to spell out the issue you're talking about, with a single-db project I would do something like this:

@pytest.fixture
def person(db):
    return Person.objects.create(...)

# no need for pytest.mark.django_db:
def test_person(person):
    ...

With multi-db I would imagine wanting to do something like this:

@pytest.fixture
def db_people(add_django_db):
    add_django_db('people')

@pytest.fixture
def db_books(add_django_db):
    add_django_db('books')

@pytest.fixture
def person(db_people):
    return Person.objects.create(...)

@pytest.fixture
def book(db_books):
    return Book.objects.create(...)

# no need for @pytest.mark.django_db(databases=['people', 'books'])
def test_reader(person, book):
    ...

(Not sure if that's possible to implement, but just as an example of how an API could work.)

This would be convenient for fixtures in general, because otherwise it's easy to forget to remove 'books' from databases when you edit which fixtures are used by test_reader later, and it's also nice just for removing a line of noise from each test. But it becomes particularly useful when using doctests:

def reader_stuff():
    """
    # no way to use pytest.mark here?
    >>> person = getfixture("person")
    >>> book = getfixture("book")
    ...
    """

The workaround I found to get my doctests working was to add an autouse fixture so db and transactional_db fixtures would by default load all databases, unless there's an explicit pytest.mark.django_db:

@pytest.fixture(autouse=True)
def database_defaults(request):
    # set default databases for `db` and `transactional_db` fixtures
    if not hasattr(request.node, '_pytest_django_databases'):
        request.node._pytest_django_databases = list(settings.DATABASES.keys())

That's a pretty handy thing to be able to opt into, so it might be nice to have a more official way to do it? But it still leaves the doctests less efficient than they could be otherwise, since they don't actually need access to all the databases, so I think a fixture version would still be useful.

One other idea I had while doing the conversion -- it would be cool if there was some flag I could use to be warned about unused databases, so if I removed the book fixture from test_reader I'd be warned that 'books' was no longer needed in the annotation. Not sure if that's possible to track, just a random brainstorm in case there's some handy way to implement it.

Thanks again for pushing this forward!

[jgb]

@bluetech so why does the flushing between each test work for @jcushman but not for me? ❓

[bluetech]

@jgb @jcushman Thanks for the reports! I'll be looking at this again this weekend and I'll reply then.

[jcushman]

@jgb here's what I'm using successfully in case it helps.

Packages:

Python 3.7.10
Postgres 11.11
Django==3.2.3
pytest==6.0.1
pytest-django==4.3.0
pytest-xdist==1.32.0

Databases:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        ...
    },
    'capdb': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        ...
    }

I started with adding just a simple test file before converting my actual tests:

@pytest.fixture
def court():
    return Court.objects.create(name='foo')  # comes from capdb

@pytest.fixture
def mailing_list():
    return MailingList.objects.create(email='foo@example.com')  # comes from default

@pytest.mark.parametrize("x", range(10))
@pytest.mark.django_db(databases=['default', 'capdb'])
def test_multi(x, court, mailing_list):
    assert Court.objects.count() == 1
    assert MailingList.objects.count() == 1

Results:

# pytest capdb/tests/test_multi.py
========================================= test session starts =========================================
platform linux -- Python 3.7.10, pytest-6.0.1, py-1.10.0, pluggy-0.13.1
django: settings: config.settings.settings_pytest (from ini)
rootdir: /app, configfile: setup.cfg
plugins: django-4.3.0, forked-1.0.1, flaky-3.6.0, celery-4.3.0, cov-2.9.0, redis-2.0.0, xdist-1.32.0
collected 10 items

capdb/tests/test_multi.py ..........                                                            [100%]

========================================= 10 passed in 4.28s ==========================================

I imagine if you can try an isolated test like that and narrow down the issue it might help bluetech when they get back to working on this. You might also look closely at your fixtures, maybe entirely disable them, and see if the isolated test starts working again -- it wouldn't be that surprising if an old multidb workaround in your fixtures is messing with this.

[gonzaloamadio]

Does this mean that if I have a multi-db project, I can not use pytest for tests?

I have a legacy DB and I created an app with some models that correlates with tables in that legacy DB, and also I have created some endpoints with Django DRF (managed=False), so no migrations are done.
So basically would be case 1 of first comment by bluetech.

[bluetech]

@jcushman

FWIW I definitely struggled with the lack of a fixture version

Yes, I'm pretty sure we need some integration with fixtures here.

Your suggestion should be doable; all we really need is to know the list of databases once the test is to be executed.

I think the add_django_db API is not too great, but maybe I need to get used to it a bit.

I'll definitely mull it over. Of course if someone wants to submit a PR with a proposal that would be possible as well.

# no way to use pytest.mark here?

Right, currently there is no way to add marks directly to doctests. This is pytest-dev/pytest#5794. I can't think of any clear way to support it either.

One other idea I had while doing the conversion -- it would be cool if there was some flag I could use to be warned about unused databases

For the multi-db support, pytest-django depends almost entirely on the django.test code, so such a feature would probably have to go through Django. It might be possible to somehow track whether a connection for a database was used during a test, but I'm not sure. There are also bound to be a lot of false-positives (or rather, cases where you want to keep a DB anyway), so would definitely need to be off by default.

[bluetech]

@jgb

My answer is pretty much what @jcushman said (thanks!) -- it works here, so we'll need more details to help us narrow down the cause.

[bluetech]

@gonzaloamadio

Does this mean that if I have a multi-db project, I can not use pytest for tests?

It's supposed to be the other way around - previously you couldn't, now you can.

If you configured the legacy database in your DATABASES then it should work. If you tried it and it didn't work, we'd need to know how it failed.