First of all, for your case, you may want to use global dependencies which are covered in fastapi's documentations (link).

About testing, you may wanna go through this issue in sqlalchemy's repo or If you just want an example:

from typing import AsyncGenerator, Generator

import pytest
from httpx import ASGITransport, AsyncClient
from sqlalchemy import create_engine, event, text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import Session, SessionTransaction

from api.config import settings
from api.database.registry import *  # noqa: F403
from api.database.setup import (
    async_database_url_scheme,
    get_session,
    sync_database_url_scheme,
)
from api.main import app

pass  # Trick to load `BaseDatabaseModel` the last, since all database models must be imported before base model.
from api.database.models import BaseDatabaseModel  # noqa: E402


@pytest.fixture
def anyio_backend() -> str:
    return "asyncio"


@pytest.fixture
async def ac() -> AsyncGenerator:
    transport = ASGITransport(app=app, raise_app_exceptions=False)
    async with AsyncClient(transport=transport, base_url="https://test") as c:
        yield c


@pytest.fixture(scope="session")
def setup_db() -> Generator:
    engine = create_engine(
        sync_database_url_scheme.format(
            settings.DATABASE_USERNAME,
            settings.DATABASE_PASSWORD,
            settings.DATABASE_HOST,
            settings.DATABASE_PORT,
            "",
        )
    )
    conn = engine.connect()
    # Terminate transaction
    conn.execute(text("commit"))
    try:
        conn.execute(text("drop database test"))
    except SQLAlchemyError:
        pass
    finally:
        conn.close()

    conn = engine.connect()
    # Terminate transaction
    conn.execute(text("commit"))
    conn.execute(text("create database test"))
    conn.close()

    yield

    conn = engine.connect()
    # Terminate transaction
    conn.execute(text("commit"))
    try:
        conn.execute(text("drop database test"))
    except SQLAlchemyError:
        pass
    conn.close()
    engine.dispose()


@pytest.fixture(scope="session", autouse=True)
def setup_test_db(setup_db: Generator) -> Generator:
    engine = create_engine(
        sync_database_url_scheme.format(
            settings.DATABASE_USERNAME,
            settings.DATABASE_PASSWORD,
            settings.DATABASE_HOST,
            settings.DATABASE_PORT,
            "test",
        )
    )

    with engine.begin():
        BaseDatabaseModel.metadata.drop_all(engine)
        BaseDatabaseModel.metadata.create_all(engine)
        yield
        BaseDatabaseModel.metadata.drop_all(engine)

    engine.dispose()


@pytest.fixture
async def session() -> AsyncGenerator:
    # https://github.com/sqlalchemy/sqlalchemy/issues/5811#issuecomment-756269881
    async_engine = create_async_engine(
        async_database_url_scheme.format(
            settings.DATABASE_USERNAME,
            settings.DATABASE_PASSWORD,
            settings.DATABASE_HOST,
            settings.DATABASE_PORT,
            "test",
        )
    )
    async with async_engine.connect() as conn:
        await conn.begin()
        await conn.begin_nested()
        AsyncSessionLocal = async_sessionmaker(
            autocommit=False,
            autoflush=False,
            expire_on_commit=False,
            bind=conn,
            future=True,
        )

        async_session = AsyncSessionLocal()

        @event.listens_for(async_session.sync_session, "after_transaction_end")
        def end_savepoint(session: Session, transaction: SessionTransaction) -> None:
            if conn.closed:
                return
            if not conn.in_nested_transaction():
                if conn.sync_connection:
                    conn.sync_connection.begin_nested()

        def test_get_session() -> Generator:
            try:
                yield AsyncSessionLocal
            except SQLAlchemyError:
                pass

        app.dependency_overrides[get_session] = test_get_session

        yield async_session
        await async_session.close()
        await conn.rollback()

    await async_engine.dispose()

Let me explain the piece of code that I have written:

• First of all, we need to import all models before BaseMetadata. This is what from api.database.registry import * does; in this file I have imported all models.
• ac is a asynchronous httpx client.
• Using setup_db fixture, in each test session we make sure we create a fresh testing database and drop it afterwards. setup_test_db creates all tables, enums, constraints, etc. based on given metadata class and drops all of them after testing.
• Last and the most important part is session fixture. This fixture joins all transactions in a single test and rollbacks all of them. This way you don't need to worry if have even committed some changes to database. In addition to what I said, also we get the original database session dependency and change it to what we have created using dependency overrides.

If you need more detail about what I have done, please let me know.