Django connections object does not see the tables

Bottom Line: My Django connections object does not see the table relations of a second database during testing with pytest-django.

Overview: I have a problem where my Django connections object seems to get the wrong database information. I stumbled upon this issue when I was querying on a table in the 'customers' DB and Django told me the relation does not exist. With the settings.py database section was set up like below:

DATABASES = {
    'default': {
        'NAME': 'user_data',
        'ENGINE': 'django.db.backends.postgres',
        'USER': 'postgres_1',
        'PASSWORD': 'superS3cret'
    },
    'customers': {
        'NAME': 'customer_data',
        'ENGINE': 'django.db.backends.postgres',
        'USER': 'postgres_1',
        'PASSWORD': 'superS3cret'
    }
}

Both cursors below get the information from the 'default' database when I run 'pytest' on the directory with:

sql = """SELECT table_name FROM information_schema.tables WHERE table_nameschema='public'"""

default = connections["default"].cursor()
default.execute(sql)
raw_data = default.fetchall()
sql_columns = [col[0] for col in default.description]
df1 = pd.DataFrame(raw_data, columns=sql_columns)

customers = connections["customers"].cursor()
customers.execute(sql)
raw_data = customers.fetchall()
sql_columns = [col[0] for col in customers.description]
df2 = pd.DataFrame(raw_data, columns=sql_columns)

The results of df1 and df2 are exactly the same: Only the table names in the 'default' database.

This happens with pytest-django and using a second Postgres database, but only sometimes.

In the query above I would expect df1 and df2 to be different, so far as the 'default' and 'customers' databases are different. However, on occasion, the connections cursor does not properly 'see' all the info in the second database.

The strange thing is that the connection settings show up differently when I print:

print(connections.databases)

the 'connections' object contains two different DBs, but one is a "test" DB. The print statement yields a dictionary, but note the "test_customers":

(pdb) { 'default': { <conn info>}, 'test_customers': { <conn info> } }

It seems as though Django is trying to set up a test database, failing, and not passing tests because the tables in 'test_customers' do not exist in the same way as in production.

How do I fix this so that pytest-django ALWAYS sees the tables in the second database (customers) during testing? Am I doing something wrong with setup and teardown of the DB?

UPDATE: Reading the pytest-django docs on DB creation/re-use has pointed me in the right direction. However, I am a little perturbed by this section of the docs:

Currently pytest-django does not specifically support Django’s multi-database support. You can, however, use normal Django TestCase instances to use its multi_db support.

If you have any ideas about the best API to support multiple databases directly in pytest-django please get in touch, we are interested in eventually supporting this but unsure about simply following Django’s approach.

pytest-django does not support multiple databases. When I was experimenting with multiple databases and parameters --reuse-db/--create-db the outcome is, that sometimes it works (all databases are created and can be used correctly) and sometimes it does not work (either the database is not created or Django is complaining that the database already exists).

IMHO there are two options: 1) do not use pytest with Django; 2) simplify your tests so you do not need multiple databases. For option 2) I'm using this setup:

normal settings:

DATABASES = {
    'default': ...,
    'secondary': ...,
}

pytest.ini:

[pytest]
...
DJANGO_SETTINGS_MODULE=my_app.settings.test
...

test.py:

# Import all from normal settings
from .base import *

DATABASES.pop('secondary')
# This will route all queries and migrations to the default DB
DATABASE_ROUTERS = []