Switch to test database using FastAPI and ormar

I am trying ormar with FastAPI and struggling with testing. Because I use Django in the majority of my projects, I am trying to separate my databases between development and testing. But I am struggling to do this.

The goal is basically, use PostgreSQL for development/production, but switch to SQLite when running pytest. And remove all data from the SQLite database when tests are done.(Imagine Django+Docker dev environment)

I've tried the following from the ormar documentation, but no luck.

TEST_DB_URL = "sqlite:///../db.sqlite"

@pytest.fixture(autouse=True, scope="module")
def create_test_database():
    engine = sqlalchemy.create_engine(TEST_DB_URL)
    metadata.create_all(engine)
    yield
    metadata.drop_all(engine)

After the above and when I try to do something with the database in tests(using pytest), the following error occurs.

====================================================================================== FAILURES =======================================================================================
_______________________________________________________________________________ test_user_registration ________________________________________________________________________________

db = <function db.<locals>.wrapper at 0x7fb7d30c9820>

    @pytest.mark.asyncio
    async def test_user_registration(db):
>       users = await db(User.objects.all)

app/users/test_users.py:51: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
app/conftest.py:39: in wrapper
    return await func()
/usr/local/lib/python3.9/site-packages/ormar/queryset/queryset.py:1016: in all
    rows = await self.database.fetch_all(expr)
/usr/local/lib/python3.9/site-packages/databases/core.py:147: in fetch_all
    async with self.connection() as connection:
/usr/local/lib/python3.9/site-packages/databases/core.py:251: in __aenter__
    raise e
/usr/local/lib/python3.9/site-packages/databases/core.py:248: in __aenter__
    await self._connection.acquire()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <databases.backends.postgres.PostgresConnection object at 0x7fb7d319e910>

    async def acquire(self) -> None:
        assert self._connection is None, "Connection is already acquired"
>       assert self._database._pool is not None, "DatabaseBackend is not running"
E       AssertionError: DatabaseBackend is not running

/usr/local/lib/python3.9/site-packages/databases/backends/postgres.py:162: AssertionError
=============================================================================== short test summary info ===============================================================================
FAILED app/users/test_users.py::test_user_registration - AssertionError: DatabaseBackend is not running

My database setup looks like this and this works just fine saving data etc via regular API calls.

import os

import databases
from fastapi import FastAPI
import sqlalchemy

from app.resources.utils import get_models_path_list

models_path_list = get_models_path_list()


def get_db_uri(*, user, password, host, db):
    return f'postgresql://{user}:{password}@{host}:5432/{db}'


DB_URL = get_db_uri(
    user=os.environ.get('POSTGRES_USER'),
    password=os.environ.get('POSTGRES_PASSWORD'),
    host='db',  # docker-composeのservice名
    db=os.environ.get('POSTGRES_DB'),
)
database = databases.Database(DB_URL)
metadata = sqlalchemy.MetaData()


def setup_database(app: FastAPI):
    app.state.database = database

    @app.on_event("startup")
    async def startup() -> None:
        database_ = app.state.database
        if not database_.is_connected:
            await database_.connect()

    @app.on_event("shutdown")
    async def shutdown() -> None:
        database_ = app.state.database
        if database_.is_connected:
            await database_.disconnect()

I struggled there too. First of all this discussion will help you. Crux here. You need to use an environment variable that points to your sqlite db. I did use BaseSettings from FastAPI and added this method there

def get_db_uri(self) -> str:
    # set the below in your environment file when running tests
    if self.TESTING:
        return "sqlite:///../db.sqlite"

    if self.PRODUCTION:
        return self._get_db_uri(
            user="root",
            passwd=self.POSTGRES_PASSWORD,
            host=self.AURORA_DB_URI,
            port=self.POSTGRES_PORT,
            db=self.POSTGRES_DB,
        )

    return self._get_db_uri(
        user=self.POSTGRES_USER,
        passwd=self.POSTGRES_PASSWORD,
        host=self.POSTGRES_HOST,
        port=self.POSTGRES_PORT,
        db=self.POSTGRES_DB,
    )

One other thing which i was missing was that fixture for dropping and creating tables. You already have that there.

TEST_DB_URL = "sqlite:///../db.sqlite"

@pytest.fixture(autouse=True, scope="module")
def create_test_database():
    engine = sqlalchemy.create_engine(TEST_DB_URL)
    metadata.create_all(engine)
    yield
    metadata.drop_all(engine)

You can use pytest-env for setting environment variables in tests.