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 https://github.com/pytest-dev/pytest-django/pull/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: https://github.com/pytest-dev/pytest-django/issues/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? :question:

[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='[email protected]')  # 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 https://github.com/pytest-dev/pytest/issues/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.

[gonzaloamadio]

Hi, @bluetech I have made a reusable app (let's call it API) that has this models unmanaged models. Then (in same repo) another "testapp" that install this API

In this testapp/settings.py I have 2 databases. One is a legacy db, the one that will be queried by models unmanaged models in API app.

DATABASES = {
    'default': {
        'ATOMIC_REQUESTS': True,
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(str(BASE_DIR), 'db.sqlite3'),
         . . . 
    },
    'legacy_db': {
        'ENGINE': django.db.backends.postgresql,
        . . . 
    }
}
DATABASE_ROUTERS = ['my-project.testapp.database_routers.DatabaseRouter']

And then I have also a testapp/settings_test.py. In this file, I disable migrations, define sqlite databases and set managed=True for models.

from .settings import *
from django.test.runner import DiscoverRunner

class DisableMigrations(object):

    def __contains__(self, item):
        return True
    def __getitem__(self, item):
        return

MIGRATION_MODULES = DisableMigrations()

class UnManagedModelTestRunner(DiscoverRunner):
    """
    Test runner that automatically makes all unmanaged models in your Django
    project managed for the duration of the test run.
    """

    def setup_test_environment(self, *args, **kwargs):
        from django.apps import apps

        self.unmanaged_models = [
            m for m in apps.get_models() if not m._meta.managed
        ]
        for m in self.unmanaged_models:
            m._meta.managed = True
        super(UnManagedModelTestRunner, self).setup_test_environment(
            *args, **kwargs
        )

    def teardown_test_environment(self, *args, **kwargs):
        super(UnManagedModelTestRunner, self).teardown_test_environment(
            *args, **kwargs
        )
        # reset unmanaged models
        for m in self.unmanaged_models:
            m._meta.managed = False


DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": os.path.join(BASE_DIR, "db_test.sqlite3"),
        "TEST": {
            "ENGINE": "django.db.backends.sqlite3",
            "NAME": os.path.join(BASE_DIR, "db_test.sqlite3"),
        },
    },
    'legacy_db': {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": os.path.join(BASE_DIR, "db_legacy_test.sqlite3"),
        "TEST": {
            "ENGINE": "django.db.backends.sqlite3",
            "NAME": os.path.join(BASE_DIR, "db_legacy_test.sqlite3"),
        },
    },
}

# Set Django's test runner to the custom class defined above
TEST_RUNNER = "testapp.settings_test.UnManagedModelTestRunner"

If I run normal unit tests with "production" settings, it fails as expected failing because relations for unmanaged models does not exists. ./manage.py test --settings=testapp.settings

If I run using test settings , it work as expected. ./manage.py test --settings=testapp.settings_test

BUT, if I run using pytest.

    def execute(self, query, params=None):
        if params is None:
            return Database.Cursor.execute(self, query)
        query = self.convert_query(query)
>       return Database.Cursor.execute(self, query, params)
E       django.db.utils.OperationalError: no such table: ingredient

Here is a gist with more code (models, factory, test, settings): https://gist.github.com/gonzaloamadio/14f935d96809299b7f1e9fb88a6e8e94

I put a breakpoint and have inspected DB. And also as expected, when I run with unittest suite, the ingredient table was there?

But when run with pytest.. no ingredient table there

[gonzaloamadio]

One more comment. I have run unittest with verbose option. This is the output

❯ ./manage.py test --settings=testapp.settings_test --verbosity=2

Using existing test database for alias 'default' ('db_test.sqlite3')...
Operations to perform:
  Synchronize unmigrated apps: account, admin, core_api, auth, contenttypes, corsheaders, django_extensions, django_filters, messages, rest_framework, runserver_nostatic, sessions, softdelete, staticfiles, test_without_migrations
  Apply all migrations: (none)

Synchronizing apps without migrations:
  Creating tables...
    Creating table auth_permission
    Creating table auth_group
     . . .  more of django core stuff tables
Running migrations:
  No migrations to apply.

Using existing test database for alias 'legacy_db' ('db_legacy_test.sqlite3')...
Operations to perform:
  Synchronize unmigrated apps: account, admin, core_api, auth, contenttypes, corsheaders, django_extensions, django_filters, messages, rest_framework, runserver_nostatic, sessions, softdelete, staticfiles, test_without_migrations
  Apply all migrations: (none)
Synchronizing apps without migrations:
  Creating tables...
    Creating table ingredient                   <--- This is the key.  
    Running deferred SQL...
Running migrations:
  No migrations to apply.

So I found this solution : https://stackoverflow.com/questions/30973481/django-test-tables-are-not-being-created/50849037#50849037

Basically do in conftest what UnManagedModelTestRunner is doing.

This solution worked for me @bluetech

$ cat conftest.py

import pytest
# Set managed=True for unmanaged models. !! Without this, tests will fail because tables won't be created in test db !!
@pytest.fixture(autouse=True, scope="session")
def __django_test_environment(django_test_environment):
    from django.apps import apps

    get_models = apps.get_models

    for m in [m for m in get_models() if not m._meta.managed]:
        m._meta.managed = True

[bluetech]

@gonzaloamadio Right, pytest doesn't consider TEST_RUNNER (it is itself the test runner), so your tweaks are not affecting it. I'm not sure if you were eventually able to do the modification in the conftest.py or not. If not, let me know and I'll try to help.

[bepuca]

So I just stumbled upon this section on the docs. We are currently upgrading a multi DB Django project from Django 1.11 to Django 3.2 and also upgrading the pytest and pytest-django packages. I was not aware of all these changes, but for us it worked out of the box without any issues. The tests are passing without problems. So thank you for that!

[AnderUstarroz]

Multi DB is supported then? I am facing a strange issue when trying to execute queries using cursor within multiple DBs:

• https://stackoverflow.com/questions/71109520/how-to-query-additional-databases-using-cursor-in-django-pytests

Am I doing a bad use of the fixtures?

[peterschwarzdev]

Same problem here! Django DB fixtures don't work with the cursors generated by:

from django.db import connections

???

[mschoettle]

We ran into a problem when we added a second database to the Django settings. When running pytest it tried to create a database with a second test_ prefix (test_test_<dbname>) for the default database. It did only happen to a few other developers so I could not reproduce it.

We were able to fix it by specifying a specific database name for the test database in the settings (in DATABASES['default']['TEST']['NAME']).

[AnderUstarroz]

What about accessing a non-default DB within a fixture, how can we do that?

I know about the db fixture, which allows access just to the default DB:

@pytest.fixture()
def db_access(db):
    # Here you can access just the "default" db.

[raianul]

What I did so far - in confest.py

TestCase.databases = {"default", "replica"}

However its creating both test database but all of my fixture is executing for default, not in the replica. Ultimately my tests failed.

Here is my fixture -

@pytest.fixture(name="test_factory")
def fixture_test_factory():

    def _test(**kwargs):
        return TestModel(name="test", **kwargs)

    return _test

Do I need to make any other changes?

[bobflorian]

solid multi db support would a huge win for us. we have multi tenant Django app touching a bunch of databases and ancillary databases as well

[erkandmp]

Hey guys, thanks to all for all of your work in this project!

I found my way to this thread while I was upgrading some packages and running the test suit:

AssertionError: Database connections to 'xxxx_db' are not allowed in this test. 
Add 'xxxx_db' to pytest_django.fixtures._django_db_fixture_helper.<locals>.PytestDjangoTestCase.databases 
to ensure proper test isolation and silence this failure.

suggestion If there could be a way to configure globally that access to the non-default database is ok, it would help us a lot. All of the tests are marked as "talks to the database"(ie. db fed as an argument fixture or @pytest.mark.django_db) and trying to get to pytest-django>=4.4.3 would require to refactor multiple thousands of tests for us. 😅

For now I pinned pytest-django==4.2.0.

Thanks again and have a great time!

[dannyzhong]

my case is even more complicated, I have two databases, one in MySQL, one in Postgresql, not sure how to pytest them

[christianbundy]

Is enable_db_access_for_all_tests meant to support multiple databases? Here's what I'm using:

@pytest.fixture(autouse=True)
def enable_db_access_for_all_tests(db):
    pass

[christianbundy]

I'm unsure how useful this will be for others, but if you're transitioning from a codebase where all of your tests inherit from a standard test class where you set `databases = "all", or if you use that pattern often, you should know:

• You don't need to enable_db_access_for_all_tests.
• This pattern causes problems pytest-xdist. You may see django.utils.connection.ConnectionDoesNotExist: The connection '_' doesn't exist., which comes from _databases_support_transactions splitting cls.databases assuming it's a set of aliases, when in reality it's the string "__all__".
  • You can solve this by setting databases = ["default", "other", "etc"].
  • If you just override _databases_support_transactions to return True, then your pytest-xdist workers will apparently use the same database connection.
  • ~I can't imagine how/why it would be related to the above, but previously I was experiencing #527 and haven't seen issues fixing "__all__" and removing everything from my conftest.py.~

[lachlancannon]

Is the current support meant to include support for the MIRROR setting? Everything except that was working for me, and I also couldn't write the test data directly to the second database without permission errors, e.g. User.objects.using('reporting').create(email='[email protected]')

[SHxKM]

Is there any way where I can apply:

@pytest.mark.django_db(databases=['default'])

To all tests? I have hundreds of tests that are using the db using the "magic" db argument to the test:

def test_profile(db):
 ...

Edit: Even after removing the 2nd DB from my dev.py settings file and even removing references to using(...) pytest is failing. Where is it getting it's idea about the 2nd DB from now?

[smohsenmohseni]

Is the current support meant to include support for the MIRROR setting? Everything except that was working for me, and I also couldn't write the test data directly to the second database without permission errors, e.g. User.objects.using('reporting').create(email='[email protected]')

i have same issue

[pmaresca]

Is it possible to know which db a test that specifies multiple db's is intended to be running against? i.e. I've got two db's and the test runs twice, does the test know which db this run is for?