Unable to run unit tests using tortoise-orm initializer() with a postgres database

[anu-kailash]

Describe the bug
We are unable to test using the initializer() when using a postgres db.

When initializer is called
initializer(['src.models'], db_url="asyncpg://your_postgres_user:your_postgres_password@localhost:5432/your_postgres_db")

the config generated by the tortoise code is

{
  "connections": {
    "models": {
      "engine": "tortoise.backends.asyncpg",
      "credentials": {
        "port": 5432,
        "database": "your_postgres_db",
        "host": "localhost",
        "user": "your_postgres_user",
        "password": "your_postgres_password"
      }
    }
  },
  "apps": {
    "models": {
      "models": [
        "src.models"
      ],
      "default_connection": "models"
    }
  }
}

Here the connection name is also "models" (same as app_name which is "models" by default)
This results in an exception:

>       initializer(['src.models'], db_url="asyncpg://your_postgres_user:your_postgres_password@localhost:5432/your_postgres_db")

tests/test_auth.py:36: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/contrib/test/__init__.py:113: in initializer
    loop.run_until_complete(_init_db(_CONFIG))
/usr/lib/python3.10/asyncio/base_events.py:649: in run_until_complete
    return future.result()
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/contrib/test/__init__.py:72: in _init_db
    await Tortoise.init(config, _create_db=True)
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/__init__.py:513: in init
    await connections._init(connections_config, _create_db)
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/connection.py:33: in _init
    await self._init_connections()
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/connection.py:90: in _init_connections
    await connection.db_create()
../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/backends/base_postgres/client.py:112: in db_create
    await self.create_connection(with_db=False)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <tortoise.backends.asyncpg.client.AsyncpgDBClient object at 0x7fe71acbdc60>, with_db = False

    async def create_connection(self, with_db: bool) -> None:
        if self.schema:
            self.server_settings["search_path"] = self.schema
    
        if self.application_name:
            self.server_settings["application_name"] = self.application_name
    
        self._template = {
            "host": self.host,
            "port": self.port,
            "user": self.user,
            "database": self.database if with_db else None,
            "min_size": self.pool_minsize,
            "max_size": self.pool_maxsize,
            "connection_class": self.connection_class,
            "loop": self.loop,
            "server_settings": self.server_settings,
            **self.extra,
        }
        try:
            self._pool = await self.create_pool(password=self.password, **self._template)
            self.log.debug("Created connection pool %s with params: %s", self._pool, self._template)
        except asyncpg.InvalidCatalogNameError as ex:
            msg = "Can't establish connection to "
            if with_db:
                msg += f"database {self.database}"
            else:
                msg += f"default database. Verify environment PGDATABASE. Exception: {ex}"
>           raise DBConnectionError(msg)
E           tortoise.exceptions.DBConnectionError: Can't establish connection to default database. Verify environment PGDATABASE. Exception: database "your_postgres_user" does not exist

../../.cache/pypoetry/virtualenvs/brand-api-8GqwAfLl-py3.10/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py:67: DBConnectionError

To Reproduce
Just run this code in python prompt:

import asyncio
from tortoise.contrib.test import _init_db
loop = asyncio.get_event_loop()
loop.run_until_complete(_init_db({'connections': {'models': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'port': 5432, 'database': 'your_postgres_db', 'host': 'db', 'user': 'your_postgres_user', 'password': 'your_postgres_password'}}}, 'apps': {'models': {'models': ['src.models'], 'default_connection': 'models'}}}))

Expected behavior
No exception. I have specified a complete db_url so I do not expect any environment variable to be used for this.

Additional context
This is the result of the reproduction along with complete stack trace.

>>> import asyncio
>>> from tortoise.contrib.test import _init_db
>>> loop = asyncio.get_event_loop()
>>> loop.run_until_complete(_init_db({'connections': {'models': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'port': 5432, 'database': 'your_postgres_db', 'host': 'localhost', 'user': 'your_postgres_user', 'password': 'your_postgres_password'}}}, 'apps': {'models': {'models': ['src.models'], 'default_connection': 'models'}}}))
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 59, in create_connection
    self._pool = await self.create_pool(password=self.password, **self._template)
  File "/usr/local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 70, in create_pool
    return await asyncpg.create_pool(None, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/asyncpg/pool.py", line 403, in _async__init__
    await self._initialize()
  File "/usr/local/lib/python3.10/site-packages/asyncpg/pool.py", line 430, in _initialize
    await first_ch.connect()
  File "/usr/local/lib/python3.10/site-packages/asyncpg/pool.py", line 128, in connect
    self._con = await self._pool._get_new_connection()
  File "/usr/local/lib/python3.10/site-packages/asyncpg/pool.py", line 502, in _get_new_connection
    con = await connection.connect(
  File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 2329, in connect
    return await connect_utils._connect(
  File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 991, in _connect
    conn = await _connect_addr(
  File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 828, in _connect_addr
    return await __connect_addr(params, True, *args)
  File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 876, in __connect_addr
    await connected
asyncpg.exceptions.InvalidCatalogNameError: database "your_postgres_user" does not exist

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.10/asyncio/base_events.py", line 649, in run_until_complete
    return future.result()
  File "/usr/local/lib/python3.10/site-packages/tortoise/contrib/test/__init__.py", line 72, in _init_db
    await Tortoise.init(config, _create_db=True)
  File "/usr/local/lib/python3.10/site-packages/tortoise/__init__.py", line 513, in init
    await connections._init(connections_config, _create_db)
  File "/usr/local/lib/python3.10/site-packages/tortoise/connection.py", line 33, in _init
    await self._init_connections()
  File "/usr/local/lib/python3.10/site-packages/tortoise/connection.py", line 90, in _init_connections
    await connection.db_create()
  File "/usr/local/lib/python3.10/site-packages/tortoise/backends/base_postgres/client.py", line 112, in db_create
    await self.create_connection(with_db=False)
  File "/usr/local/lib/python3.10/site-packages/tortoise/backends/asyncpg/client.py", line 67, in create_connection
    raise DBConnectionError(msg)
tortoise.exceptions.DBConnectionError: Can't establish connection to default database. Verify environment PGDATABASE. Exception: database "your_postgres_user" does not exist
>>> 

Here if you see with_db=False is hard-coded.

  File "/usr/local/lib/python3.10/site-packages/tortoise/backends/base_postgres/client.py", line 112, in db_create
    await self.create_connection(with_db=False)

So in create_connection the value of self._template["database"] is always None. That seems to be the reason for the exception inside create_pool. Please check.

[abondar]

tortoise.exceptions.DBConnectionError: Can't establish connection to default database. Verify environment PGDATABASE. Exception: database "your_postgres_user" does not exist

Does your environment has PGDATABASE variable?

[anu-kailash]

no

[abondar]

I think to understand error you first need to understand what exactly initialiser doing:

1. It connects to default database
2. Runs create database for given database name (if there is {} in database name - it will format it with random uuid)
3. Populate this DB with schemas generated from your models
4. Connects to newly created db

Your error occurs because it's connection to default db failed, as it not exists. For most of postgres clients default db equals to user name. If your user was postgres it would connect to postgres db that usually exist, but in your case there no db with same name as user

So you have few options to workaround:

1. create database your_postgres_user
2. use user postgres
3. override PGDATABASE with another database name
4. do not use initializer/finalizer at all, and manage database yourself

Note that default database can't be equal to database you want to run your tests against, as you can't create/delete database you are already connected to

[anu-kailash]

Thanks for the explanation @abondar . I have changed the user to postgres but now I get asyncpg.exceptions.ObjectInUseError: cannot drop the currently open database even though nothing else is using the database.

Can you explain the 4th option? "do not use initializer/finalizer at all, and manage database yourself"

My fastapi application is set up using register_tortoise() function and I create a TestClient for it to run unit tests. But when any of the routes being tested read/write using a tortoise Model, I get this error:

/usr/local/lib/python3.10/site-packages/tortoise/models.py:1273: in get
    db = using_db or cls._choose_db()
/usr/local/lib/python3.10/site-packages/tortoise/models.py:1010: in _choose_db
    db = router.db_for_read(cls)
/usr/local/lib/python3.10/site-packages/tortoise/router.py:36: in db_for_read
    return self._db_route(model, "db_for_read")
/usr/local/lib/python3.10/site-packages/tortoise/router.py:31: in _db_route
    return connections.get(self._router_func(model, action))
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <tortoise.router.ConnectionRouter object at 0x7f4c3d82a380>, model = <class 'src.models.Account'>, action = 'db_for_read'

    def _router_func(self, model: Type["Model"], action: str):
>       for r in self._routers:
E       TypeError: 'NoneType' object is not iterable

/usr/local/lib/python3.10/site-packages/tortoise/router.py:18: TypeError

This is the config dict I am passing to register_tortoise:

{'connections': {'master': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'host': 'localhost', 'port': '5432', 'user': 'postgres', 'password': 'your_postgres_password', 'database': 'your_postgres_db'}}, 'slave': {'engine': 'tortoise.backends.asyncpg', 'credentials': {'host': 'localhost', 'port': '5432', 'user': 'postgres', 'password': 'your_postgres_password', 'database': 'your_postgres_db'}}}, 'apps': {'models': {'models': ['src.models', 'aerich.models'], 'default_connection': 'master'}}, 'routers': ['src.models.Router'], 'use_tz': False, 'timezone': 'UTC'}

Router definition:

from tortoise.models import Model
from typing import Type

class Router:
    def db_for_read(self, model: Type[Model]):
        print(model, "db_for_read")
        return "slave"

    def db_for_write(self, model: Type[Model]):
        print(model, "db_for_write")
        return "master"

These problems are all faced during unit test runs only. When I run the application and test each endpoint via swagger manually everything works perfectly with no changes in code or configuration. However, I cannot run manual tests every time so it is imperative for me to get unit tests running or else I will have to switch back to other ORM.

[abondar]

I have changed the user to postgres but now I get asyncpg.exceptions.ObjectInUseError: cannot drop the currently open database even though nothing else is using the database.

Can you try changing your database in to something like
asyncpg://postgres:your_postgres_password@localhost:5432/test_{}?
To be sure, that db is always freshly created and not reused by anyone

That how we run tests internally and it works, at least here

My fastapi application is set up using register_tortoise()

In 0.21 version we introduced new way to register Tortoise with fastapi -
tortoise.contrib.fastapi.RegisterTortoise
You can try it - may be it will change something

Here how we use it in example
https://github.com/tortoise/tortoise-orm/blob/develop/examples/fastapi/main.py#L25

There is also simple test configuration for fast api in example
https://github.com/tortoise/tortoise-orm/blob/develop/examples/fastapi/_tests.py

But overall error you are getting indicates that for some reason tortoise wasn't properly initialised, because if it was - tortoise.router.ConnectionRouter.init_routers should have been called and you wouldn't be having None here.

For diagnostics - you can check in debugger if Tortoise._init is set to true before your failing .get() call

Can you explain the 4th option? "do not use initializer/finalizer at all, and manage database yourself"

It just means that you create database and migrate it to needed state beforehand, using some external tools or manually, and then you run tests
tortoise.contrib.fastapi.RegisterTortoise also provides option generate_schemas which may help preparing test env

[anu-kailash]

Thanks for the pointers! The fix I made is different though so I will try to explain here (maybe it will help someone else using Fastapi with tortoise-orm and trying to write unit tests like me)

Finally got it to work changing the way I used the FastApi TestClient.
Earlier I was using it as described here: https://fastapi.tiangolo.com/tutorial/testing/#using-testclient

client = TestClient(app)

This gave me the exception:

self = <tortoise.router.ConnectionRouter object at 0x7f4c3d82a380>, model = <class 'src.models.Account'>, action = 'db_for_read'

    def _router_func(self, model: Type["Model"], action: str):
>       for r in self._routers:
E       TypeError: 'NoneType' object is not iterable

Now I switched to using it so that startup and shutdown events get triggered as described in https://fastapi.tiangolo.com/advanced/testing-events/

with TestClient(app) as client:

This helped and my tests are running now. They run fine whether I use RegisterTortoise with lifespan or register_tortoise but with the deprecation at FastApi, the better approach is to use RegisterTortoise with lifespan as mentioned by @abondar

In 0.21 version we introduced new way to register Tortoise with fastapi -
tortoise.contrib.fastapi.RegisterTortoise
You can try it - may be it will change something