app/
 ├── main.py
 ├── database.py
 ├── models.py
 ├── routers.py   (optional)
tests/
 └── test_users_async_db.py

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, declarative_base

DATABASE_URL = "sqlite+aiosqlite:///./prod.db"

engine = create_async_engine(
    DATABASE_URL, echo=False, future=True
)

async_session_maker = sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)

Base = declarative_base()

async def get_async_session():
    async with async_session_maker() as session:
        yield session

from sqlalchemy.orm import mapped_column, Mapped
from sqlalchemy import String
from app.database import Base

class User(Base):
    __tablename__ = "users"

    username: Mapped[str] = mapped_column(String, primary_key=True)
    job: Mapped[str] = mapped_column(String)

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select

from app.database import get_async_session
from app.models import User

app = FastAPI()

@app.get("/users/{username}")
async def get_user(username: str, session: AsyncSession = Depends(get_async_session)):
    result = await session.execute(select(User).where(User.username == username))
    user = result.scalars().first()

    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    return {"username": user.username, "job": user.job}


@app.post("/users")
async def create_user(
    username: str,
    job: str,
    session: AsyncSession = Depends(get_async_session),
):
    # check existing
    result = await session.execute(select(User).where(User.username == username))
    if result.scalars().first():
        raise HTTPException(status_code=400, detail="User already exists")

    new_user = User(username=username, job=job)
    session.add(new_user)
    await session.commit()

    return {"message": "User created", "username": username}

import pytest
from httpx import AsyncClient
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

from app.main import app
from app.database import Base, get_async_session
from app.models import User


# Create TEST DB (in memory)
TEST_DATABASE_URL = "sqlite+aiosqlite:///:memory:"

test_engine = create_async_engine(
    TEST_DATABASE_URL, echo=False, future=True
)

TestSessionLocal = sessionmaker(
    test_engine, expire_on_commit=False, class_=AsyncSession
)


# Dependency override for tests
async def override_get_async_session():
    async with TestSessionLocal() as session:
        yield session


app.dependency_overrides[get_async_session] = override_get_async_session


# Create DB schema before tests
@pytest.fixture(scope="module", autouse=True)
async def prepare_database():
    async with test_engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    yield
    async with test_engine.begin() as conn:
        await conn.run_sync(Base.metadata.drop_all)


# -----------------------------------------
# TESTS
# -----------------------------------------

@pytest.mark.asyncio
async def test_create_user():
    async with AsyncClient(app=app, base_url="http://test") as client:
        response = await client.post("/users", params={"username": "alice", "job": "Engineer"})

    assert response.status_code == 200
    assert response.json()["message"] == "User created"


@pytest.mark.asyncio
async def test_get_user():
    # Insert user manually into mocked DB for this test
    async with TestSessionLocal() as session:
        session.add(User(username="bob", job="Designer"))
        await session.commit()

    async with AsyncClient(app=app, base_url="http://test") as client:
        response = await client.get("/users/bob")

    assert response.status_code == 200
    assert response.json() == {"username": "bob", "job": "Designer"}


@pytest.mark.asyncio
async def test_missing_user():
    async with AsyncClient(app=app, base_url="http://test") as client:
        response = await client.get("/users/unknown")

    assert response.status_code == 404
    assert response.json() == {"detail": "User not found"}