tortoise-orm: Cannot have Unittest + Postgres + Fastapi combination. It just doesn't work.

My frustration described here https://github.com/tortoise/tortoise-orm/issues/1611 continues.

While I got the unittest work with sqlite (this is where I stopped last time), I can’t get it to work with postgres. And I need postgres because I just introduced ArrayField.

The combination is this:

  • unittest (I can’t work with pytest, sorry, I already have 1500 unit tests and I need this to work with unittest)
  • postgres
  • FastAPI version 0.111.0 (newest stable)
  • tortoiseorm[psycopg]==0.21.4
  • python 3.12

I have created a minified example which clearly proves it’s not possible to successfully write even the simplest unit test. It is 100% reproducible on any system, I have tried this on Linux and now on Windows.

Create new conda/virtualenv and install the 2 packages above (tortoiseorm[psycopg]==0.21.4 and fastapi==0.111.0). Then create this simple project with db/models.py, main.py and tests.py

db/models.py:

from tortoise import Model, fields
from tortoise.contrib.postgres.fields import ArrayField


class MyModel(Model):
    id = fields.IntField(primary_key=True)
    some_int = fields.IntField(null=True)
    some_array_field = ArrayField(element_type="int", null=True)

main.py:

from contextlib import asynccontextmanager

from fastapi import FastAPI, WebSocket
from tortoise.contrib.fastapi import RegisterTortoise

from db.models import MyModel


@asynccontextmanager
async def lifespan(app: FastAPI):
    async with RegisterTortoise(
        app=app,
        db_url="psycopg://postgres:postgres@127.0.0.1:5432/tortoise_test",
        modules={"models": ["db.models"]},
    ):
        yield


app = FastAPI(lifespan=lifespan)


@app.websocket("/ws")
async def websocket_endpoint(
    websocket: WebSocket,
):
    await MyModel.filter(id=123)  # make some query to DB
    await websocket.accept()

tests.py:

import unittest

from tortoise.contrib.test import TestCase, initializer, finalizer
from fastapi.testclient import TestClient
from main import app

class TortoiseUnittest(TestCase):

    @classmethod
    def setUpClass(cls):
        initializer(
            modules=["db.models"],
            app_label="models",
            db_url="postgres://postgres:postgres@127.0.0.1:5432/tortoise_test_{}",
        )

    @classmethod
    def tearDownClass(cls):
        finalizer()

    def test_do_nothing(self):
        pass

    def test_connet_to_websocket(self):
        client = TestClient(app)
        with client.websocket_connect(
            f"/ws",
        ) as websocket:
            pass

Try running test_do_nothing. You’ll get this error:

ModuleNotFoundError: No module named 'asyncpg'

Wait, what? How come a fresh installation of tortoiseorm[psycopg] cannot run even an empty unit test?

But OK, let’s pip install tortoiseorm[asyncpg], and let’s try again.

This time the test_do_nothing passes. Yay!

So let’s get to the final boss. Let’s run test_connect_to_websocket which also does pretty much nothing other than connecting to the websocket, and the websocket function in main.py makes a single query to DB.

When you run this test you’ll see this error:

  File "asyncpg\protocol\protocol.pyx", line 166, in prepare
RuntimeError: Task <Task pending name='starlette.testclient.WebSocketTestSession._run.<locals>.run_app' coro=<WebSocketTestSession._run.<locals>.run_app() running at C:\Users\PC\miniconda3\envs\tortoise-test\Lib\site-packages\starlette\testclient.py:147> cb=[TaskGroup._spawn.<locals>.task_done() at C:\Users\XXXXXXX\envs\tortoise-test\Lib\site-packages\anyio\_backends\_asyncio.py:701]> got Future <Future pending cb=[Protocol._on_waiter_completed()] created at C:\Users\XXXXXXX\envs\tortoise-test\Lib\asyncio\base_events.py:449> attached to a different loop

I hearby declare I will donate some money to this project if someone provides me with working example of a unittest code which is using postgres, makes a websocket connection and the code makes a successful query to the database. Because even the simplest example is not working. The unittest+fastapi+postgres seems like a pretty common combo, and not sure if other people had seen this issue before? At any rate, googling it is not returning any meaningful results.

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 15 (6 by maintainers)

Most upvoted comments

I got everything working, with 0.21.5 there’s no need to write a custom wrapper on RegisterTortoise. Thanks @abondar !

If someone’s interested, here’s the full example on how we can have tortoise, fastapi and unittest working together using postgres DB.

  • The example is using a websocket endpoint, but it should work for regular REST endpoints as well.
  • The example is using starlette’s TestClient (AsyncClient does not surface websocket_connect method, and as such would not allow for testing websockets)
  • It is a full isolation, meaning each individual unit test creates and destroys the whole DB - it should cover all scenarios, including tests which use transactions and locks.
  • Tests are unittest.IsolatedAsyncioTestCase, so we’re not using anything from tortoise.contrib.test, we’re not using initializer/finalizer either. It’s as simple as it gets.
  • We even have a test for 2 concurrent users connecting to websocket. We employ a simple synchronization: user 1 is waiting for the user 2 to connect.

requirements.txt:

tortoise[psycopg]==0.21.5
fastapi==0.111.1

db/models.py:

from tortoise import Model, fields
from tortoise.contrib.postgres.fields import ArrayField


class MyModel(Model):
    id = fields.IntField(primary_key=True)
    some_int = fields.IntField(null=True)
    some_array_field = ArrayField(element_type="int", null=True)

main.py:

import logging
from contextlib import asynccontextmanager

from fastapi import FastAPI, WebSocket
from tortoise import Tortoise
from tortoise.contrib.fastapi import RegisterTortoise
from db.models import MyModel


@asynccontextmanager
async def lifespan(app: FastAPI):
    if getattr(app.state, "testing", False):
        # If we're in unit tests, create a DB with a dynamic name (the {} placeholder), create schemas and drop
        # the database when the app's lifespan ends
        logging.info("Initializing test db")
        async with RegisterTortoise(
            app=app,
            db_url="psycopg://tortoise_test:tortoise_test@127.0.0.1:5432/tortoise_test_{}",
            modules={"models": ["db.models"]},
            _create_db=True,
            generate_schemas=True,
        ):
            yield
        await Tortoise._drop_databases()
    else:
        logging.info("Initializing main db")
        # Otherwise, we just use the regular DB for our regular work
        async with RegisterTortoise(
            app=app,
            db_url="psycopg://tortoise_test:tortoise_test@127.0.0.1:5432/tortoise_test",
            modules={"models": ["db.models"]},
        ):
            yield


app = FastAPI(lifespan=lifespan)


@app.websocket("/ws")
async def websocket_endpoint(
    websocket: WebSocket,
):
    await MyModel.create(some_int=321, some_array_field=[1, 2, 3])  # run some query on DB
    await websocket.accept()

tests.py:

import asyncio
import unittest
from asyncio import Event

from fastapi.testclient import TestClient

from db.models import MyModel
from main import app

app.state.testing = True


class PureUnittestTests(unittest.IsolatedAsyncioTestCase):

    async def test_connect_to_websocket(self):
        with TestClient(app) as client:
            with client.websocket_connect(
                f"/ws",
            ) as websocket:
                self.assertEqual(1, await MyModel.all().count())

    async def test_connect_to_websocket_concurrent_users(self):
        async def user_1_script(client: TestClient, user_2_connected: Event):
            with client.websocket_connect(
                f"/ws",
            ) as websocket:
                await user_2_connected.wait()

        async def user_2_script(client: TestClient, user_2_connected: Event):
            with client.websocket_connect(
                f"/ws",
            ) as websocket:
                user_2_connected.set()

        with TestClient(app) as client:
            async with asyncio.TaskGroup() as tg:
                user_2_connected = Event()
                tg.create_task(user_1_script(client, user_2_connected))
                tg.create_task(user_2_script(client, user_2_connected))

            self.assertEqual(2, await MyModel.all().count())

OMG, I’m slowly realizing what a hell of a mess this whole FastAPI ecosystem is. Why are we doing this to ourselves? Each of the libs (FastAPI, Pydantic, Tortoise, starlette) is an excellent library on its own but putting it all together and making it work in a civilized way is a continuous world of frustration for someone who’s used to working with django. I won’t event talk about how websocket examples are completely wrong in the FastAPI docs, and one needs to write their own mini-framework to actually use it correctly. There’s so many missing parts everyone needs to figure out on their own, and so many things that so many people WILL do wrong and so many bugs they will have in their code, because there is no framework, just an ocean of individual libs. Again, don’t get me wrong, a lot of those libs, including Tortoise are GREAT libs, but instead of being in the business of writing apps, I’m suddenly in the business of writing a framework gluing it all together in a useful, meaningful way and making sure it’s bulletproof.

Anyway, I think I’m slowly figuring it out. I’m going to give up on having the auto-created test db (as this just doesn’t seem physically possible, unless someone gives me even the simplest example how to do that exactly) and will use a persistent test db. This solution is far from perfect, for many reasons:

First, is managing schema evolution. After any schema change, the test DB is out of sync with the ORM, and we need to remember to migrate it every time. So we effectively need to maintain TWO local databases: the regular one for manual tests, and the test one for unit tests. Whereas in django the framework itself takes care of create->migrate->drop the test DB automatically out of the box.

Second, if the test db is not ephemeral, it may keep some data accidentally in case something goes wrong with the truncate. Drop database is the ultimate truncate, isn’t it? 😃 And “create database from ORM” guarantees 100% consistency between the code and the db schema without even caring about migrations.

Third, think about a CI/CD setup with one-click deployment pipeline which run tests as one of the steps, you’d like this step to be as bulletproof as possible, and what’s more bulletproof than drop DB if exist -> create DB? Otherwise you’ll just have another persistent remote database in the dev environment that exists only for tests and all sorts of things can go wrong with it.

Fourth, what even is truncate_all? I’ve been working in the REST API back-end business with postgres for over almost 15 years now and would never need to manually truncate all tables on the db. I don’t even know how to do that. Googling “how to truncate all tables in postgres” points to articles where people write a fairly complex custom functions to do that. There are constraints, non-null foreign keys, and so on. It’s not trivial just to clear the entire db in one call. But maybe I’m wrong, maybe there is some kind of a one-line wrapper which I can put in tearDown()? I could not find anything on this topic in the tortoise docs.

The simplest working “hello world” example that I was looking for may look like this. Note that it is STILL incomplete, as it does not have the truncate part.

class PureUnittestTests(unittest.IsolatedAsyncioTestCase):
    """
    We're using pure unittest's IsolatedAsyncioTestCase (not inheriting from any of the tortoise.contrib.test
    base classes)
    """

    async def test_connect_to_websocket_NOT_WORKING(self):
        """
        This is how FastAPI tells you to use the test client.
        It won't work with Tortoise because it won't initialize tortoise!
        """
        client = TestClient(app)
        with client.websocket_connect(
            f"/ws",
        ) as websocket:
            pass

    async def test_connect_to_websocket(self):
        """
        The corret way of doing it. TestClient will call app's lifespan, which will call RegisterTortoise.
        We still need to take care of the following:
        1) We need to point RegisterTortoise to a test db on our own e.g. depending on a app.state.testing flag
           or some environment var.
        2) We need to remember to always maintain the test DB in sync with the code!
        3) We need to somehow truncate all tables after each test on our own. Not yet sure how?
        """
        with TestClient(app) as client:
            with client.websocket_connect(
                f"/ws",
            ) as websocket:
                pass

As you can see, even this simple example is not yet 100% complete. I still need to figure out how to truncate all tables after each test.

Finally, let me stress that again, I could NOT work out even a simplest working example of a test which would use startlette’s TestClient with tortoise.contrib.test classes such as TruncationTestCase, IsolatedTestCase or TestCase. None of this is working, all working examples are pydantic examples.